Solved

Slow simple MySQL query (date range)

Posted on 2013-06-14
3
307 Views
Last Modified: 2013-06-17
This is a very simple table with 6 columns.  Currently the table has more than 4 million records.  This is the query:


SELECT * FROM chamber_ghst
WHERE chamberid='Ambient' and
actiondate BETWEEN '2013/06/12 12:06:00' and '2013/06/13 12:06:00'
ORDER BY actiondate ASC



This query results in 288 records.  It takes about 16 seconds.  I just don't see why or understand how I can optimize this.
0
Comment
Question by:Zipbang
3 Comments
 
LVL 11

Assisted Solution

by:David Kroll
David Kroll earned 200 total points
ID: 39248867
Are there indexes on chamberid and actiondate?
0
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 300 total points
ID: 39249292
You should add an index on chamberid and actiondate like this

Create index chixactd on chamber_ghst ( chamber asc, actiondate desc) using btree;

This index will speed up your query.

Regards,
    Tomas Helgi
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39251709
NEVER EVER USE SELECT *  ALWAYS USE LIMIT

But that said, there are some really good articles here at EE about MySQL performance.  Example here:
http://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
A short film showing how OnPage and Connectwise integration works.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now