0
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?

3
  • 1st variant cannot use index.
    – Akina
    Feb 27 '20 at 12:44
  • 1
    mydate >= CURDATE() AND mydate < CURDATE() + INTERVAL 1 DAY (edit depending on your DBMS and how it implements date arithmetic). Works for date / datetime / timestamp datatypes. Feb 27 '20 at 13:18
  • Sorry guys I tagged this question with mysql I completely forgot about that. Feb 27 '20 at 13:47
1

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.

3
  • Won't it be significantly slower to have >= and < instead of = though? The thing is, that my data make sense only on the "day" scale, so I'm not really concerned about querying across multiple days, as I simply do not expect it to ever happen. Feb 27 '20 at 18:21
  • If mydate is indexed, it will traverse the index first. This will be faster. If mydate is not indexed, then there will be no improvement. Feb 27 '20 at 20:13
  • I@RolandoMySQLDBA - I think that ` + INTERVAL 0 SECOND` is unnecessary in all the places you used it. Also, DATETIMEs and DATE can be compared, with the understanding that a DATE is implicitly midnight that morning.
    – Rick James
    Mar 3 '20 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.