哪一个更好从DATE_FORMATE（）或MONTH（），YEAR（）

Question

我必须每月获得记录/计数。 没有方法可以做到---

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

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

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

​​

哪一个最好。

当前Senerio： 有一个月过滤器，我们选择月，得到04-2016。这样我们就可以使用DATE_FORMAT（％间％Y）或月= 04 &年= 2016匹配得到records.but哪一个性能更好的明智

实际查询优化是：

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两个不同的列或已批准的用户也可以拒绝

Answer 1

我觉得第一个是Better--

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

Answer 2

您正在尝试对日期范围内的记录进行计数。请注意，您的建议查询都不是有效的。没有人可以利用您的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>>> 

这可以在你的approved_date列的索引做一个index scan的查询。

所以，现在需要的技巧是从NOW()获取<<<00:00 on first date>>和<<<00:00 on day after last date>>>的正确值。这就是你所做的。

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软件中的查询规划模块会对其进行优化。）