Chris Nizzardini, Salt Lake City Utah, Web Developer Specializing in LAMP+Ajax Since 2006

My Blog

Here is my awesome blog. You can find information on programming, linux, documentation, tips for code and database optimization, my thoughts and rants, and whatever else I feel like sharing. Feel free to contribute to the blog by posting comments and asking questions.
SQL

Using MySQL Functions in WHERE Clauses Breaks Speed Gains from Indexing?

Posted by chris on August 8th, 2008 in SQL

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.

Related posts:

  1. mysql date/time functions
  2. MySQL Case Statement

Tags: , ,

This entry was posted on Friday, August 8th, 2008 at 9:33 pm and is filed under SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply