MySql查询计划选择行数较少的索引来检查，但执行速度慢得多

Question

我有一个简单的查询 - 它只查看一个表，并可以选择 2 列用于索引、ErrorTypeId(指向大约 20 个唯一值的查找表)和 DateOccurred(可以包含任何日期的日期时间列)在里面)。

在大多数情况下都很好，但在某些情况下查询会超时。仔细观察，所选索引 (ErrorTypeId) 的计划中的行数比另一个索引 (DateOccurred) 少，但由于某种原因，运行时间比我手动强制它使用其他索引要长得多。

/* This execution plan has fewer rows than the second one - and as a result this is
 * the one chosen.  But it is much much slower.
 */
EXPLAIN
SELECT * FROM my_log_table
WHERE DateOccurred BETWEEN '2013-06-11 00:00:00' AND '2013-06-22 00:00:00' AND ErrorTypeId = 4
ORDER BY DateOccurred DESC
LIMIT 1000;


/* Many more rows - but much much quicker to execute. 
 * Is it the spread of data across pages?  Is there a
 * way other than USE/FORCE INDEX to improve execution plan?
 */
EXPLAIN
SELECT * FROM my_log_table USE INDEX (DateOccurred_Index)
WHERE DateOccurred BETWEEN '2013-06-11 00:00:00' AND '2013-06-22 00:00:00' AND ErrorTypeId = 4
ORDER BY DateOccurred DESC
LIMIT 1000;

为什么当有更多行需要查看时，第二个查询会更快？

有没有办法帮助MySql选择更快的索引而不使用USE/FORCE索引？

Answer 1

查询速度慢的原因是索引效率低下。

在(ErrorTypeId, DateOccurred)上添加复合索引，您将不需要任何强制或提示。