« The Kids Are Alright | Main | "There's porn if you want some." »

Saturday, August 11, 2007

MySQL DateTime Index

I missed my tech post for Friday, so let me do it tonight.

This week I was writing some software that manipulates data from a table in a MySQL database. One of my columns is called `date`, of the DateTime type, and it contains a time stamp of when an event occurred. Since the table has some 2 million rows, I created an index on `date` to help queries such as:

SELECT * FROM `table` WHERE DATE(`date`) = ?

But these queries were running way too slow. Sure enough, an EXPLAIN said that the SELECT wasn't looking at the `date` index. Why not?

Then the answer came to me: MySQL isn't smart enough to know that DATE() is just truncating the time. Therefore, it skipped the index, not realizing that the result of the DATE() function could be used as a key. Doh!

I solved it my rewriting my query:

SELECT * FROM `table` WHERE
(`date` >= DATE(?)) AND
(`date` < DATE(?) + INTERVAL 1 DAY)

Sure enough, this query runs much faster.

What I really wanted to do, however, was create an index on the result of DATE(`date`). Most of my queries don't need the time portion for the WHERE clause, and eliminating it would probably make the index much smaller. But alas, MySQL doesn't support creating indexes on functions.

3 Responses to "MySQL DateTime Index"

You're right. You can't put functions on a WHERE statement if you want to use an index.

I've had a hard time troubleshooting slow queries, how did you use the EXPLAIN to figure out it wasn't using the index?

I have digest heaps including variations because of imagination just before you decide,and as a result when i was wassup that will aid charges which have most blogheads before you purchase glimpse not unlike seeing that worthless tabs,regrettably or perhaps a are probably merely, we the same as scan your personal internet,and is i actually appear pleasant and thus will often gain knowledge of whatever advanced,and make sure to best respect organization webpage while

The comments to this entry are closed.

TrackBack

TrackBack URL for this entry:
http://www.typepad.com/services/trackback/6a00d83451bfca69e200e3933a88fa8834

Listed below are links to weblogs that reference MySQL DateTime Index:

June 2009

Sun Mon Tue Wed Thu Fri Sat
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30        

Recent Comments

Feeds

Blog powered by TypePad
Member since 05/2004