Difference between DATE(time_field) AND time_field in a WHERE clause
Just addressing this question


  What's the difference between time_field > 'x' AND DATE(time_field) > 'x' inside WHERE clause?


The difference is how the Query Optimizer treats them.


If the WHERE clause has time_field &gt; 'x', this signals the Query Optimizer to try looking for any index so as to take advantage of it doing a range scan.
If the WHERE clause has DATE(time_field) &gt; 'x', this signals the Query Optimizer to throw all indexes under the bus because the DATE function has to be called across the whole table (in the form of a full table scan) or the join result.


Depending on the key distribution of time_field, DATE(time_field) &gt; 'x' triggering a full table scan just happens to be better than a range scan on an index if the values make up a significant percentage of the index. This is even more true using InnoDB because a full table scan passes through the clustered index (where PRIMARY KEY and row data coexist), while a secondary index on time_field would cause a lookup of the secondary index  in addition to the clustered index. If such a secondary index had a lopsided key distribution, such an index would be ignored in favor of a full table scan anyway.

This conjuecture is only indicative of your current dataset. Someone else's dataset may have a better (evenly distributed, more balanced) key distribution, resulting in time_field &gt; 'x' working better than DATE(time_field) &gt; 'x'.

To see such differences, run the EXPLAIN on both SELECT queries. The results may be different.