MySQL Forums
Forum List  »  Optimizer & Parser

Re: Improve search when using IN() ORDER BY PRIMARY and LIMIT
Posted by: Øystein Grøvlen
Date: February 13, 2017 05:58AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Improve search when using IN() ORDER BY PRIMARY and LIMIT
572
February 13, 2017 05:58AM


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.