Can MySQL use index in a RANGE QUERY with ORDER BY?

Question

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

If I add this index to mytable:

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

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

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?

Thanks.

Answer 1

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

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.

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]

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.