mysql - DATE_FORMATE() OR MONTH(),YEAR() 哪个更好

Question

我必须按月获取记录/计数。没有办法做---

1

SELECT COUNT(1)AS approved FROM lu_registration 
WHERE MONTH(approved_date)=MONTH(NOW()) AND YEAR(approved_date)=YEAR(NOW());

2

SELECT COUNT(1)AS approved FROM lu_registration 
WHERE DATE_FORMATE(approved_date, '%Y-%m')=DATE_FORMATE(NOW(), '%Y-%m');

3

SELECT COUNT(CASE WHEN MONTH(approved_date)=MONTH(NOW()) 
AND YEAR(approved_date)=YEAR(NOW()) THEN 1 END)AS approved FROM lu_registration;

4

SELECT COUNT(CASE WHEN DATE_FORMATE(approved_date, '%Y-%m')=DATE_FORMATE(NOW(), '%Y-%m')
 THEN 1 END)AS approved FROM lu_registration;

哪个最好。

实际塞内里奥:有一个月份过滤器，我们可以通过它来选择月份，并得到 04-2016。这样我们就可以使用 date_format (%m-%y) 或 month=04 & year=2016 来匹配获取记录。但哪个性能更好

要优化的实际查询是:

SELECT COUNT(CASE WHEN status='A' AND MONTH(approved_date)=04
AND YEAR(approved_date)=2016 THEN 1 END)AS approved, COUNT(CASE WHEN status='D' AND MONTH(reject_date)=04 
AND YEAR(reject_date)=2016 THEN 1 END)AS rejected FROM lu_registration;

这里是approved_date & reject_date 两个不同的列或者approved user也被reject了

Answer 1

您正在尝试对某个日期范围内的记录进行计数。恕我直言，您建议的查询都没有效率。没有人可以利用您的 approved_date 上的 MySQL 索引柱子。他们都不是sargeable.这会损害性能，尤其是当您的应用程序添加日期可以追溯到几年前的记录时。

相反，您需要一个表单查询

 SELECT COUNT(*) AS approved
   FROM table
  WHERE approved_date >= <<<00:00 on first date>>>
    AND approved_date <  <<<00:00 on day after last date>>>

这可以做 index scan在 approved_date 列的索引上。

因此，现在需要的技巧是获取 <<<00:00 on first date>> 的正确值和 <<<00:00 on day after last date>>>来自 NOW() .这就是您要做的。

1. LAST_DAY(NOW())给出本月最后一天的 00:00。
2. LAST_DAY(NOW()) + INTERVAL 1 DAY在下个月的第一天给出 00:00。
3. LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 1 MONTH在本月的第一天给出 00:00。

因此，您的查询变为

 SELECT COUNT(*) AS approved
   FROM lu_registration
  WHERE approved_date >= LAST_DAY(NOW()) + INTERVAL 1 DAY
    AND approved_date <  LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 1 MONTH

它会选择正确的日期范围，而且运行速度很快。

(注意我用 COUNT(*) 代替了你的 COUNT(1) 。那是因为 COUNT(*) 是计算记录的常用方法。有时 RDMS 软件中的查询规划器模块会专门优化它。)