Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm having an issue on my server with a simple PHP send emails script that runs every minute via a cronjob. The script contains a MySQL query that runs very fast most of the time, but randomly will take "forever".

It feels like a similar problem to this: http://stackoverflow.com/questions/976739/mysql-query-randomly-takes-forever

At seemingly random times while the MySQL query is running the load on my server spikes from an average of 0.5 up to 8 and my entire server slowly grinds to a near halt. The query does eventually complete but sometimes it runs for over 10 minutes -- on a "normal" occasion the query completes in 0.2 seconds.

This is the query:

SELECT * FROM email_messages 
WHERE time_created <= "2013-04-22 10:40:00" AND is_sent = 0 AND is_cancelled = 0 
LIMIT 6

time_created is (should be) indexed - its purpose is to create a 10 minute delay before sending so emails can be cancelled if necessary.

I've isolated it to this query because my server trucks along nicely without the load average spikes when this cronjob is turned off.

My server is a VPS with 2GB of RAM. The database isn't huge (750mb in total) but the email_messages table being selected from is by far the largest at around 400mb.

Through research, I thought it was a MySQL query cache problem because query cache was turned on. But turning off query cache has not resolved the issue.

I turned off query_cache by setting query_cache_size = 0.

Any ideas at all as to what could be going on here?

[CONCLUSION]

I did an experiment by including a condition in the query using my primary key so that only the last 1000 table entries where being search through.

... AND id > 131000 ...

Running the cronjob with this condition resolved the issue, and further investigation in the mysql console with EXPLAIN revealed that the index on time_created was not created as I had expected.

I now need to read up on how to use HeidiSQL to properly create indexes because HeidiSQL is telling me there is an index key on time_created but EXPLAIN says otherwise. This may be a trap for young players!

share|improve this question

migrated from stackoverflow.com Apr 22 '13 at 8:14

This question came from our site for professional and enthusiast programmers.

    
Please update your question with the EXPLAIN and structure –  Kermit Apr 21 '13 at 23:36
    
I would make an index on (is_sent, is_cancelled, time_created) if this is crucial query. –  ypercube Apr 22 '13 at 8:19
    
It would be good to add the SHOW CREATE TABLE email_messages ; output. –  ypercube Apr 22 '13 at 8:20
add comment

1 Answer

up vote 1 down vote accepted

To speed up the execution of your query, you need to create a compound index:

ALTER TABLE email_messages
ADD INDEX idx1(is_sent, is_cancelled, time_created);

More information: Multiple-Column Indexes

share|improve this answer
    
How do you know one doesn't already exist? –  Kermit Apr 21 '13 at 23:39
1  
@FreshPrinceOfSO Because the question is very detailed, and the OP tells us there is an index on time_created. He would have mentioned other indexes too if they existed. –  Jocelyn Apr 21 '13 at 23:44
2  
If you create time_created, is_sent, is_cancelled index and query time_created <= "2013-04-22 10:40:00" AND is_sent = 0 AND is_cancelled = 0 -then is_sent, is_cancelled index part will not be used ever, since time_created is range comparison. The order of columns should be the opposite. –  zerkms Apr 21 '13 at 23:49
    
I will consider using a compound index. But as mentioned the query usually executes (maybe 99% of the time) in 0.2 seconds. –  mike14 Apr 21 '13 at 23:54
2  
@mike14: when you try it - use is_sent, is_cancelled, time_created order, not what is proposed in this answer. PS: as long as it's VPS - no wondering that sometimes I/O isn't good, so I won't expect this compound index will fix it, but without doubts it will make the execution plan better –  zerkms Apr 21 '13 at 23:56
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.