Aug 8 2008
Using MySQL Functions in WHERE Clauses Breaks Speed Gains from Indexing?
I was conversing with a co-worker today and asked him to take a look at some queries of mine hoping he could determine why they were so sluggish. I had already ruled out a bad inner join, sub select, and lack of indexes. The query looked at a months worth of data using something like:
WHERE DATE(date_time) '2008-08-08'
For whatever reason this will cause MySQL (at least MySQL 4) to ignore the indexing on the date_time field. Changing your query to this:
WHERE date_time BETWEEN '2008-08-08 00:00:00' AND '2008-08-08 23:59:59'
Can save significant execution time. In my tests on a table with over 300,000 records this dropped by query execution time from roughly 4 seconds to .4 seconds, 10x faster! Using mysql functions in your select clause does not seem to negatively impact execution time.