MySQL fastest way to search by DATE if a record exist

Question

I have found many way to search a mysql record by DATE

Method 1:

SELECT id FROM table WHERE datetime LIKE '2015-01-01%' LIMIT 1

Method 2 (same as method 1 + ORDER BY):

SELECT id FROM table WHERE datetime LIKE '2015-01-01%' ORDER BY datetime DESC LIMIT 1

Method 3:

SELECT id FROM table WHERE datetime BETWEEN '2015-01-01' AND '2015-01-01 23:59:59' LIMIT 1

Method 4:

SELECT id FROM table WHERE DATE_FORMAT( datetime, '%y.%m.%d' ) = DATE_FORMAT( '2015-01-01', '%y.%m.%d' )

Method 5 (I think is the slowest):

SELECT id FROM table WHERE DATE(`datetime`) = '2015-01-01' LIMIT 1

What is the fastest?

In my case the table has 1 million rows, and the date to search is always recent.

Answer 1

The fastest of the methods you've mentioned is

SELECT id 
  FROM table 
 WHERE datetime BETWEEN '2015-01-01' AND '2015-01-01 23:59:59'
 LIMIT 1

This is made fast when you create an index on the datetime column. The index can be random-accessed to find the first matching row, and then scanned until the last matching row. So it's not necessary to read the whole table, or even the whole index. And, when you use LIMIT 1, it just reads the single row. Very fast, even on an enormous table.

Your other means of search apply a function to each row:

• datetime LIKE '2011-01-01%' casts datetime as a string for each row.
• Methods 3,4, and 5 all use explicit functions like DATE() on the contents of each row.

The use of these functions defeats the use of indexes to find your data.

Pro tip: Don't use BETWEEN for date arithmetic because it handles the ending condition poorly. Instead use

 WHERE datetime >= '2015-01-01' 
   AND datetime <  '2015-01-02'

This performs just as well as BETWEEN and gets you out of having to write the last moment of 2015-01-01 explicitly as 23:59:59. That isn't correct with higher precision timestamps anyway.