Mysql Datetime Indexes And Intervals

Today I ran into an interesting (though afterwards 'doh! of course...') problem involving a slow MySQL query that should have been a hell of a lot quicker. The application I work on supports users in many timezones, so when it comes to query time we always need to adjust system recorded times (which are always UTC) to allow for the users current timezone.

So for example consider a table...

1 CREATE TABLE mytable (
2     id INT UNSIGNED NOT NULL AUTO_INCREMENT,
3     date_created DATETIME NOT NULL,
4     KEY date_created_idx (date_created),
5     PRIMARY KEY (id)
6 );

And a made-up query against it, where :tzOffset is the users UTC offset in hours, and :theDate is a datetime we'd like to query against...

1 SELECT *
2 FROM mytable
3 WHERE date_created + INTERVAL :tzOffset HOUR > :theDate

Given a relatively small number of rows you'll have no problem with this, but open the EXPLAIN output and you'll notice MySQL ignores the index.

Change Perspective

I suppose it makes sense that MySQL is now unable to use the index, it's an index on the date_created column, not on the date_created_plus_some_timezone_ column. So the fix is to move the date wrangling to the query parameter instead.

1 SELECT *
2 FROM mytable
3 WHERE date_created > :theDate - INTERVAL :tzOffset HOUR

Notice how the sign on the INTERVAL modifier changes as we're moving the date the other way. You could of course do this calculation on the date before binding it to the query, but in my case we often need to do the INTERVAL adjustment on the column itself so it's nicer to keep it in one place.

A reminder to always keep one eye on EXPLAIN...