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:
1 | 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:
1 | 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.
Chris Nizzardini has been developing web applications since 2006. He lives and works in beautiful Salt Lake City, Utah. If you’re interested in hiring me for contract work please visit IO Spring LLC.
a dynamic javascript calendar that works with mootools mocha ui writing classes in javascript using mootools