Search code examples
mysqlsqlquery-performance

Mysql optimizing date filter

Is there any difference in any sql engines (and particularly in mysql) in the following two queries?

SELECT * FROM table where date = '2019-01-01'

And:

SELECT * FROM table where date = DATE('2019-01-01')

Doing an explain returns the same result, but perhaps there's some sort of difference that I'm not catching? I need to run a query against a multi-billion row table and am trying to optimize it before running.

Solution

  • There should not be. The expression DATE('2019-01-01') should be evaluated during the compilation phase turning the result into a date. Similarly, the constant value '2019-01-01' is implicitly converted to a date for the comparison.

    This allows MySQL (and most other databases) to use indexes and partitions defined on that column.