MySQL SELECT WHERE datetime matches day (and not necessarily time)

by

NEVER EVER use a selector like DATE(datecolumns) = '2012-12-24' – it is a performance killer:

  • it will calculate DATE() for all rows, including those, that don’t match
  • it will make it impossible to use an index for the query

It is much faster to use

SELECT * FROM tablename 
WHERE columname BETWEEN '2012-12-25 00:00:00' AND '2012-12-25 23:59:59'

as this will allow index use without calculation.

EDIT

As pointed out by Used_By_Already, in the time since the inital answer in 2012, there have emerged versions of MySQL, where using ’23:59:59′ as a day end is no longer safe. An updated version should read

SELECT * FROM tablename 
WHERE columname >='2012-12-25 00:00:00'
AND columname <'2012-12-26 00:00:00'

The gist of the answer, i.e. the avoidance of a selector on a calculated expression, of course still stands.

More Related Contents:

  1. MySQL Query – Records between Today and Last 30 Days
  2. Select mysql query between date?
  3. How to get an age from a D.O.B field in MySQL?
  4. How do I get the first day of the week of a date in mysql?
  5. Compare only day and month with date field in mysql
  6. Left Outer Join doesn’t return all rows from my left table?
  7. quick selection of a random row from a large table in mysql
  8. MySQL: is a SELECT statement case sensitive?
  9. Error in MySQL when setting default value for DATE or DATETIME
  10. How to get the number of days of difference between two dates on MySQL?
  11. Difference between two dates in MySQL
  12. Combine Multiple child rows into one row MYSQL
  13. How do I select between the 1st day of the current month and current day in MySQL?
  14. How does MySQL’s ORDER BY RAND() work?
  15. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  16. MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?
  17. MySQL error 1241: Operand should contain 1 column(s)
  18. MySQL: How to select records for this week?
  19. MySQL – select data from database between two dates
  20. mysql select sum group by date
  21. MySQL – SQL_BIG_SELECTS
  22. MySQL INNER JOIN select only one row from second table
  23. How to select only date from a DATETIME field in MySQL?
  24. MySQL Select Query – Get only first 10 characters of a value
  25. Insert/ Update random date in MySQL
  26. What is the default MySQL JOIN behaviour, INNER or OUTER?
  27. How to use Table output from stored MYSQL Procedure
  28. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  29. MySQL cartesian product between two SELECT statements
  30. MySQL Select Multiple VALUES

Leave a Comment