mysql - 如何优化SQL查询？ key 未被使用

Question

如何优化以下查询？我已尝试创建索引(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)

Answer 1

在函数中包装索引列可防止优化器使用它。看起来您需要在开始日期释放索引，以便为您的 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 的截止点上.