MySQL Forums
Forum List  »  Optimizer & Parser

How to avoid filesort in ORDER BY?
Posted by: Andy S
Date: January 26, 2012 10:58PM

I need to do a query in the form of:

SELECT *
FROM t1
WHERE group_id = 123 AND (f1 = "abc" OR f1 = "efg") AND f2 = "xyz"
ORDER BY score DESC LIMIT 10;

(group_id, score) is a secondary index

Can MySQL run this query without any filesort? It should be able to
use the index to find the rows with group_id=123. Those rows are
already ordered by "score" in the index. So MySQL should be able to
get the rows one by one and check to see for each row if "f1" and "f2"
satisfy the WHERE clause conditions.

But I looked at the manual on ORDER BY (
http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html )
and it doesn't mention any cases similar to my query.

So can MySQL run this query without filesort? If not is there any way
to rewrite the query to optimize it?

Thanks.

Options: ReplyQuote


Subject
Views
Written By
Posted
How to avoid filesort in ORDER BY?
4379
January 26, 2012 10:58PM


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.