MySQL Forums
Forum List  »  Optimizer & Parser

Re: Can MySQL use index in a RANGE QUERY with ORDER BY?
Posted by: Rick James
Date: November 03, 2010 09:17PM

Here's my analysis of this one query:

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

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.

Options: ReplyQuote

Written By
Re: Can MySQL use index in a RANGE QUERY with ORDER BY?
November 03, 2010 09:17PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.