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.