Can MySQL use index in a RANGE QUERY with ORDER BY

问题:

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.

回答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.

回答2:

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

You have a range in filtering condition and the ORDER BY not matching the range.

These conditions cannot be served with a single index.

To choose which index to create, you need to run these queries

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

and

SELECT  COUNT(*)
FROM    mytable
WHERE   other_id = 1
        AND expiration_datetime >= NOW()

and compare their outputs.

If the second query yields about same or more values as the first one, then you should use an index on (other_id, score) (and let it filter on expiration_datetime).

If the second query yields significantly less values than the first one, you should use an index on (other_id, expiration_datetime) (and let it sort on score).

This article might be interesting to you:

• Choosing index

回答3:

Sounds like you've already checked the documentation and setup the index. Use EXPLAIN and see...

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