Hi Jon,
I think there are two different questions asked here: 1) How many index entries need to be accessed and 2) how many need to be sorted.
To answer the second part first. For LIMIT queries with a small limit, MYSQL do no actually do a full sort. Instead, it will during the scan keep the top n ids in a priority queue. Hence, at the end of the scan the qualifying rows have been found without the need for further sorting. (See
http://didrikdidrik.blogspot.co.uk/2011/04/optimizing-mysql-filesort-with-small.html#comment-form).
Wrt how many index entries need to be accessed, you are right that in the general case, your union query will probably perform better than the index range scan the MySQL will use for the original query.
Note that in the special case of LIMIT 1, using a query that finds the max value may be more efficient. Especially, in combination with GROUP BY, MySQL will be able to "jump" between the maximums for each group. (This is called Loose Index Scan.) Hence, a query like this may perform better than the original query:
SELECT MAX(post_id) FROM posts WHERE blog_id IN (1,2,3) GROUP BY blog_id ORDER BY m DESC LIMIT 1;
Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway