Avoiding filesort in ORDER BY

[Andy]

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.

[Shlomi N.]

@Andy,
Absolutely, yes.
But I'm not sure from your question whether you found out it does not work as expected, or is this a preliminary question.
Question is: what does EXPLAIN say?

You can always use a FORCE INDEX clause to indicate you prefer using this index over a full table scan. This is a non-standard extension to SQL, bear in mind.
The index, at any case, should work well with the query, at least assuming "group_id = 123" is a good filtering condition (i.e. returns relatively few rows).

[Andy]

Shlomi,

Yes it was a preliminary question. I'm designing the database schema
so wanted to make sure the schema I have would not be requiring
filesort.

Just to be sure, by "yes" you meant there would not be any filesort,
correct?

Thanks.

[Shlomi N.]

Andy,
By "yes" I meant the index *could* help out. It is a correct index, but please re-read my post: MySQL may yet prefer a full table scan, depending on how well it filters.