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 >=开发者_JS百科 '2011-08-31 00:00:00'
AND approve_time < '2011-09-01 00:00:00'
Or does the query incur a tablescan?
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
精彩评论