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.