Mysql – Simple MySQL query randomly takes forever

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 
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!