MySQL Performance – DATE(mydate) vs mydate = CURDATE()

MySQLperformancequery-performance

DATE(mydate) = CURDATE()

vs

mydate = CURDATE()

I have a pretty performance sensitive situation with tens of millions of rows, and I'm wondering if storing only date in datetime field and time in a separate columns, just to have a simple mydate = CURDATE() comparison when looking up records by days, would be significantly faster than merging both information in datetime field and using DATE(mydate) = CURDATE().

(yes, I cannot use "date" data type, because of the abstraction layer between me and the database)

Would there be a significant difference or is there a better way to get all records from the given day when using datetime field?

Best Answer

You just asked

How significant is performance difference between “DATE(mydate) = CURDATE()” vs “mydate = CURDATE()”?

My Answer

Very Gigantic Difference

When you use

DATE(mydate) = CURDATE()

The Query Optimizer will suggest a full table scan even if the column is indexed. Why ? The Query Optimizer is obligated to see the need to call the DATE() function against mydate in every row, thus eliminating the idea of using the index. Additionally, using FORCE INDEX will simply get you nowhere.

If mydate is datetime and is indexed, you could then change the query to

mydate >= (CURDATE() + INTERVAL 0 SECOND) AND
mydate <  (CURDATE() + INTERVAL 86400 SECOND)

or

mydate >= (CURDATE() + INTERVAL 0 SECOND) AND
mydate <  (CURDATE() + INTERVAL 1 DAY + INTERVAL 0 SECOND)

so you are comparing datetime to datetime and isolating just today. In this instance, an index on mydate will be used.

You do not want to split the date and time into spearate columns as that will cause comparisons of datetime ranges across multiple dates and times very difficult to express in SQL.

Related Question