如何优化以下查询?我试图创建索引(idx_events, idx_events_startdate,但他们没有帮助。查询耗时10秒,实在是太长了。
....
131 rows in set (10.25 sec)
mysql> explain SELECT results.event from results
INNER JOIN events ON results.event=events.id
where (DATEDIFF(NOW(), events.startdate) < 30)
AND (DATEDIFF(NOW(), events.startdate) > -1)
AND results.status='OK'
group by events.id;
+----+-------------+---------+--------+----------------------------------------------+------------+---------+-----------------------+--------+---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+--------+----------------------------------------------+------------+---------+-----------------------+--------+---------------------------------------------------------------------+
| 1 | SIMPLE | results | ref | idx_event,idx_status | idx_status | 53 | const | 773425 | Using index condition; Using where; Using temporary; Using filesort |
| 1 | SIMPLE | events | eq_ref | PRIMARY,idx_events_name,idx_events_startdate | PRIMARY | 4 | eventor.results.event | 1 | Using where |
+----+-------------+---------+--------+----------------------------------------------+------------+---------+-----------------------+--------+---------------------------------------------------------------------+
2 rows in set (0.01 sec)
在函数中包装索引列可以防止优化器使用它。看起来您需要在startdate释放索引,以便为JOIN:
获得一个不错的过滤结果集。SELECT r.event from results
JOIN events e
ON e.id = r.event
AND e.startdate > CURDATE() - INTERVAL 30 DAY
AND e.startdate < CURDATE() + INTERVAL 1 DAY
WHERE r.status = 'OK'
我喜欢将连接表条件放在ON子句中,并在可能的情况下使用表别名。
我也更喜欢一个整数状态列,而不是字符串'OK',如果你需要的话,你可以将它外键到一个带有描述的状态表。
顺便说一句……我还喜欢使用>=
和<
作为日期范围,例如,如果startdate
是DATETIME
或TIMESTAMP
,则此查询将排除2014-08-01 00:00:00
的值,如果它位于CURDATE() - INTERVAL 30 DAY
的截止点上。