What is the most efficient way to search in date ranges in MySQL?

Question

I'd like to ask what is the most efficient (and fastest) way to search for data between 2 dates?

Let's consider having following simple query:

SELECT Date_,counter1,counter2,...
WHERE (date range condition)

The "Date_" column is a DATETIME type.

I tried different options like:

WHERE Date_ >= '2012-12-01 00:00:00' AND Date_ <= '2012-12-05 00:00:00'

and

WHERE Date_ BETWEEN '2012-12-01 00:00:00' AND '2012-12-05 00:00:00'

and

WHERE Date_ = '2012-12-01 00:00:00' OR Date_ = '2012-12-02 00:00:00' OR
Date_ = '2012-12-03 00:00:00' OR Date_ = '2012-12-04 00:00:00' OR
Date_ = '2012-12-05 00:00:00'

In fact this Select query is a lot more complicated (with joins and more conditions). This is only the simplified version. According to EXPLAIN there is no difference, how the query is executed. I need to say that Date_ column is indexed. Unfortunately, I cannot test the real query speed, because I cannot avoid OS caching, but at least MySQL cache was not used (SQL_NO_CACHE).

Is there according to your experince a faster way to search in date intervals (without functions)?

Which of the three methods is faster (in case that there is a difference at all)?

Thanks a lot in advance!

Answer 1

The big factor in efficiency is going to be the availability of suitable indexes, and the generation of an efficient execution plan.

Our normative pattern for searching a datetime range is to use a greater than and equal to, and a less than (but not equal to) comparisons.

To get all datetimes for a single day, for example, '2012-12-01', we typically code this like:

WHERE datetimecol >= '2012-12-01'
  AND datetimecol <  '2012-12-02'

-or-

WHERE datetimecol >= '2012-12-01'
  AND datetimecol <  '2012-12-01' + INTERVAL 1 DAY

Similarly, to get the five day range in your example:

WHERE datetimecol >= '2012-12-01'
  AND datetimecol <  '2012-12-06'

-or-

WHERE datetimecol >= '2012-12-01'
  AND datetimecol <  '2012-12-01' + INTERVAL 5 DAY

---

To answer your question, "is there a faster way?" No. A predicate of that form enables MySQL to perform a range scan on an index (given a suitable index available.) It doesn't get any more efficient than that.