php - WHERE 子句中 DATE 或 DATE_FORMAT 的开销是多少？

Question

event_date 的类型为 DATETIME 且 $date = '2014-11-07'

我可以运行查询

$this->Select(" * FROM events WHERE DATE(event_date) = ?",
                 array($date));

或

$this->Select(
      " * FROM events WHERE DATE_FORMAT(event_date,'%Y-%m-%d') = ?",
                 array($date));

但我想知道这会导致它在每一行执行日期操作吗？

也许用起来会更好

$this->Select(" * FROM events WHERE event_date = ?",
                 array($date. ' 00:00:00'));

代替？

Answer 1

显示上述问题的基准:

SELECT * FROM `test` WHERE date(event_date) ='2014-11-07' limit 0,10000;
--Showing rows 0 - 29 ( 10,000 total, Query took 0.0087 sec)

SELECT * FROM `test` WHERE date_format(event_date,'%Y-%m-%d') ='2014-11-07' limit 0,10000;
--Showing rows 0 - 29 ( 10,000 total, Query took 0.0119 sec)

SELECT * FROM `test` WHERE event_date   ='2014-11-07 00:00:00' limit 0,10000;
--Showing rows 0 - 29 ( 10,000 total, Query took 0.0076 sec)

输出如phpmyadmin中所示