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

[Rick James]

Here's my analysis of this one query:

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

Assuming
INDEX( other_id, expiration_datetime, score);

1. Since `id` is not part of the INDEX, the query will have to use the data.

2. If id is usually 1, it may decide that it is faster to read the entire table and not bother bouncing between the INDEX and the data.

3. It will not use more than other_id and expiration_datetime -- score being in the index is not helpful since expiration_datetime is used in a 'range' context.

4. There is a chance (slim chance) that it would use INDEX(score) because of the ORDER BY. It is not smart enough to factor in the existence of LIMIT 10.

5. The optimal index for that query (item 2 notwithstanding) and table is
INDEX(other_id, expiration_datetime). The index you have is nearly is good. (The disadvantage is that yours is slightly bulkier.)

6. MySQL usually "does the right thing". That is, don't despair if fails to use the index.

7. INDEX(other_id, score) might be good, but I have never noticed it picking that. Would you like to try it?

8. If there are under 100 rows, not of this will make enough performance difference to notice. Even at 1000 rows, there won't be much difference. How many rows do you have (or will you have)?

9. There are soooo many variants, I would not expect that quote to cover many cases, and I would expect it to be a little off. Your case is a bit tricky.