Improve search when using IN() ORDER BY PRIMARY and LIMIT

[jon sans]

When you do a query like this one (innodb):

SELECT post_id FROM posts WHERE blog_id IN (2,3,...) ORDER BY post_id DESC LIMIT 10

Where post_id is PRIMARY, and blog id is index. If you do the search with the key blog_id, it has to do a filesort to order by the primary key. Because a secondary index its ordered by its key, not by the primary which in this case would be in the index already since it’s an innodb table.

If the index was in the primary order it would not need to do a filesort, but that would be another query, it will not do a filesort either if instead of IN() you search for just one index id, because all the primary ids are already in other within the same index id, like this visual example shows:

(blog_id,Primary)-> (1,55) (1,59) (1,69) (2,57) (2,71) (2,72) (3,12)

The problem comes when you add a LIMIT, imagine I do WHERE blog_id IN (1,2,3) ORDER BY post_id DESC LIMIT 1

It will catch (55,59,69,57,71,72,12) and order them, but it should only catch (69,72,12) and order them.

You can clearly see this is happening if you replicate and improve it with union subqueries:

FROM ((SELECT post_id
FROM posts
WHERE blog_id = 2
ORDER BY post_id DESC
LIMIT 10
) UNION ALL
(SELECT post_id
FROM posts
WHERE blog_id = 3
ORDER BY post_id DESC
LIMIT 10
)
) p
ORDER BY post_id DESC LIMIT 10;

Imagine there are millions of blog_id 2 and 3 matches, the original query would be insensibly slower than this union one.

Am I right with my findings? Can this be solved?