MySQL Forums
Forum List  »  Optimizer & Parser

Can MySQL use index in a RANGE QUERY with ORDER BY?
Posted by: John Smith
Date: November 02, 2010 07:53PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Can MySQL use index in a RANGE QUERY with ORDER BY?
2766
November 02, 2010 07:53PM


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.