Slow simple MySQL query (date range)

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.
ZipbangAsked:
Who is Participating?
 
Tomas Helgi JohannssonConnect With a Mentor Commented:
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
 
David KrollConnect With a Mentor Commented:
Are there indexes on chamberid and actiondate?
0
 
Ray PaseurCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.