MySQL可以使用ORDER BY在RANGE QUERY中使用索引吗？ [英] Can MySQL use index in a RANGE QUERY with ORDER BY?

问题描述

我有一个MySQL表：

I have a MySQL table:

CREATE TABLE mytable (
     id INT NOT NULL AUTO_INCREMENT,
     other_id INT NOT NULL,
     expiration_datetime DATETIME,
     score INT,
     PRIMARY KEY (id)
) 

我需要以下列形式运行查询：

I need to run query in the form of:

SELECT * FROM mytable
WHERE other_id=1 AND expiration_datetime > NOW() 
ORDER BY score LIMIT 10

如果我将此索引添加到mytable：

If I add this index to mytable:

CREATE INDEX order_by_index
ON mytable ( other_id, expiration_datetime, score);

MySQL能否使用整个 order_by_index 在上面的查询中？

Would MySQL be able to use the entire order_by_index in the query above?

看起来应该可以，但是根据MySQL的文档：即使ORDER BY与索引不完全匹配，也可以使用索引，只要索引的所有未使用部分和所有额外的ORDER BY列都是WHERE子句中的常量。

It seems like it should be able to, but then according to MySQL's documentation: "The index can also be used even if the ORDER BY does not match the index exactly, as long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause."

以上段落似乎建议索引只用于常量查询，而我的是范围查询。

The above passage seems to suggest that index would only be used in a constant query while mine is a range query.

有人可以澄清在这种情况下是否会使用索引？如果没有，我可以强制使用索引吗？

Can anyone clarify if index would be used in this case? If not, any way I could force the use of index?

谢谢。

推荐答案

MySQL将使用索引来满足where子句，并将使用filesort来对结果进行排序。

MySQL will use the index to satisfy the where clause, and will use a filesort to order the results.

它不能使用索引因为您没有将expiration_datetime与常量进行比较。因此，返回的行并不总是在索引中都有一个公共前缀，因此索引不能用于排序。

It can't use the index for the order by because you are not comparing expiration_datetime to a constant. Therefore, the rows being returned will not always all have a common prefix in the index, so the index can't be used for the sort.

例如，考虑一个您的表的4个索引记录的样本集：

For example, consider a sample set of 4 index records for your table:

a) [1,'2010-11-03 12:00',1]
b) [1,'2010-11-03 12:00',3]
c) [1,'2010-11-03 13:00',2]
d) [2,'2010-11-03 12:00',1]

如果我在2010-11-03 11:00，它将返回索引中不连续的行a，c，d。因此，MySQL需要执行额外的传递来对结果进行排序，并且在这种情况下不能使用索引。

If I run your query at 2010-11-03 11:00, it will return rows a,c,d which are not consecutive in the index. Thus MySQL needs to do the extra pass to sort the results and can't use an index in this case.

这篇关于MySQL可以使用ORDER BY在RANGE QUERY中使用索引吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！