MySQL Forums
Forum List  »  Optimizer & Parser

Improve search when using IN() ORDER BY PRIMARY and LIMIT
Posted by: jon sans
Date: February 11, 2017 07:17AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Improve search when using IN() ORDER BY PRIMARY and LIMIT
651
February 11, 2017 07:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.