CODEWITHSUNDEEP
mysql
Dilum Ranatunga
Dilum Ranatunga

Reputation: 13374

Performance of MYSQL WHERE DATE(time) = 'yyyy-mm-dd'

Suppose I have a table 'Tasks' with a DATETIME column approve_time. I have an index on said column.

If I were to write a query of the form:

SELECT task_id, task_desc, task_owner, approve_time 
  FROM Tasks
 WHERE DATE(approve_time) = '2011-08-31'

My question is about the performance of such a query:

Does MYSQL index DATETIME columns in a way that allows constraining by the date component to be fast?

Or does MYSQL know how to optimize the query into something like the following?

WHERE approve_time >= '2011-08-31 00:00:00'
  AND approve_time <  '2011-09-01 00:00:00'

Or does the query incur a tablescan?

Upvotes: 1

Views: 225

Answers (1)

ajreal
ajreal

Reputation: 47321

Does MYSQL index DATETIME columns in a way that allows constraining by the date component to be fast?

NO

Or does MYSQL know how to optimize the query into something like the following?

YES

the second query will lead to range filter,
try

explain extended query_1; <--- number of rows sent for scan is more, 
                               which should be aLL rows

vs

explain extended query_2;

the value of DATE(approve_time) only can determined after the function applied to column approve_time in all the row, which mean there is not going to make use on index

Upvotes: 3

Related Questions