MySQL查询在where子句中花费很长时间

Question

我的表结构如下:

id item_id created
1  5       2012-09-05 09:37:59
2  5       2012-09-05 10:25:09
3  5       2012-09-05 11:05:09
4  1       2012-09-05 10:25:09
5  3       2012-09-05 03:05:01

我想通过使用 WHERE 子句传递当前日期来了解哪个 item_id 被查看最多:

SELECT item_id, COUNT( id ) AS TOTAL
FROM stats_item
WHERE DAY( created ) =  '05'
AND MONTH( created ) =  '07'
AND YEAR( created ) =  '2013'
GROUP BY item_id
ORDER BY TOTAL DESC 
LIMIT 0 , 30

MySql中的结果查询

Showing rows 0 - 29 ( 30 total, Query took 4.1747 sec)

最多需要 4.1747 秒

下面是表中的索引

Table        Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
stats_item   0          PRIMARY  1            id          A         2575580     NULL     NULL         BTREE
stats_item   1          created  1            created     A          515116     NULL     NULL   YES   BTREE

为什么使用 WHERE 子句和使用 YEAR、MONTH 和 DAY 进行过滤的查询需要这么长时间？

====================================使用 EXPLAIN 进行编辑:

Field   Type                Null Key    Default Extra
id      int(11) unsigned    NO   PRI    NULL    auto_increment
item_id int(11) unsigned    YES         NULL    
created timestamp           YES  MUL    NULL    

Answer 1

尝试添加复合索引:created+item_id
尝试使用如下查询:

SELECT item_id, COUNT( id ) AS TOTAL FROM stats_item
WHERE created >= "2013-07-05" and created <= "2013-07-05 23:59:59" 
GROUP BY item_id ORDER BY TOTAL DESC LIMIT 0 , 30