Re: avoid file sort for IN() ORDRE BY

[Sergey Petrunya]

MySQL is able to avoid using filesort if requested ordering is "compatible" with ordering produced by the index.
For the first query the ordering is compatible, because for all returned rows index_part_1=1 and so using
"... ORDER BY index_part_2"
has the same effect as
"ORDER BY index_part_1 DESC, index_part_2 DESC".

For the second query index_part_1 is not constant so MySQL has to use filesort. If the number after LIMIT is low and you're willing to go some lengths to avoid using filesort for the second query you could rewrite it as a 4-way UNION with ORDER BY.. LIMIT for each member and for entire UNION.

Sergey Petrunia, Software Developer
MySQL AB, www.mysql.com
My blog: http://s.petrunia.net/blog