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: https://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 

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?


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!

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

1 Answer 1


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

  • How do you know one doesn't already exist?
    – Kermit
    Commented Apr 21, 2013 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
    Commented Apr 21, 2013 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
    Commented Apr 21, 2013 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
    Commented Apr 21, 2013 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
    Commented Apr 21, 2013 at 23:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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