Using an index for a date and time lookup

Many real-world database tables have many rows with DATETIME OR TIMESTAMP column values spanning a lot of time, including years or even decades. Often it’s necessary to use a WHERE clause to retrieve some subset of that timespan. For example, we might want to retrieve rows for the date 1-September-2016 from a table.

An inefficient way to do that is this:

WHERE DATE(x) = '2016-09-01'   /* slow! */

It’s inefficient because it applies a function – DATE() – to the values of a column. That means MySQL must examine each value of x, and an index cannot be used.

A better way to do the operation is this

WHERE x >= '2016-09-01'
  AND x <  '2016-09-01' + INTERVAL 1 DAY

This selects a range of values of x lying anywhere on the day in question, up until but not including (hence \<) midnight on the next day.

If the table has an index on the x column, then the database server can perform a range scan on the index. That means it can quickly find the first relevant value of x, and then scan the index sequentially until it finds the last relevant value. An index range scan is much more efficient than the full table scan required by DATE(x) = '2016-09-01.

Don’t be tempted to use this, even though it looks more efficient.

WHERE x BETWEEN '2016-09-01' AND '2016-09-01' + INTERVAL 1 DAY  /*  wrong! */

It has the same efficiency as the range scan, but it will select rows with values of x falling exactly at midnight on 2-Sept-2016, which is not what you want.