• May
  • 23
  • 2010

Fixed MySQL slow queries using indexes by removing string to date conversion warnings

by Andrew Kandels

This is just a quick one for an issue I ran into today when I was tuning some MySQL indexes for better performance. Some of our older PHP code was performing a SELECT using a BETWEEN to specify a date range. After adding an index to speed up this range query, I noticed that it wasnt taking and was still performing a table scan/file sort.

The code was using PHPs date function with the c format string, which inserts a date/time string like so: 2010-05-23T16:45:08-05:00. When inserting or comparing dates, MySQL will automatically convert this to its internal date/time format; but, it will throw a warning.

It turns out that the warning was causing MySQL to ignore the new index for the range query even through it was listed as the used index in the EXPLAIN. My guess is that this has something to do with MySQL having to convert each string to a date on a per-row basis, causing its optimizer to use the index inefficiently.

Simply changing the PHP date functions format string from c to Y-m-d H:i:a (which is a native MySQL date/time format) did the trick. The explain remained the same, but the number of rows was drastically reduced from hundreds of thousands to a few hundred (the result of the range) which took a 20 second query down to about a tenth of a second.

I havent tested it, but my guess is this will occur with any type of conversion that results in a warning, such as a string with text in it being converted to a number, truncated values, etc.. MySQL probably doesnt cache those conversions up front, which leads to expensive conversions on a per-row basis which ultimately tanks any benefit an index will give you - despite what the EXPLAIN tells you!

Something to keep in mind - watch those warnings!