How significant is performance difference between "DATE(mydate) = CURDATE()" vs "mydate = CURDATE()"?
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 &gt;= (CURDATE() + INTERVAL 0 SECOND) AND
mydate &lt;  (CURDATE() + INTERVAL 86400 SECOND)


or

mydate &gt;= (CURDATE() + INTERVAL 0 SECOND) AND
mydate &lt;  (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.