开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜