MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize subquery, replace IN() with JOIN
Posted by: Øystein Grøvlen
Date: November 16, 2016 06:23AM


EXPLAIN shows that the optimizer has chosen the so-called MatLookup strategy for the semi-join execution. That is, it will store result of joining the tables of the IN-subqueries in a temporary table. That is, join category_content_se with itself. During this process duplicates will be removed. It then goes through the content table using the published index, and for each row that match the where predicate, it will do a look-up into the temporary table to see if there is a match on ID.

It might be that there are more optimal semijoin strategies. You could try to see what happens if you do "SET optimizer_switch='materialization=off'".

However, I am not convinced that it is the subqueries that give you problems. I rather suspect that the reason is that MySQL will generate all potential rows, then sort them, and pick the 10 newest. If you were able to process the rows in descending date order, you will be able to stop when 10 rows have been found. To do that you will need an index on date_add column. Then best would be to create a compound index on (published, typ, date_add). Then only the interesting rows of content table need to be access.

(I assume that the EXPLAIN output is from an example database. I have a hard time understanding how this query could take 5 minutes with the row numbers presented.)

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway

Options: ReplyQuote

Written By
Re: Optimize subquery, replace IN() with JOIN
November 16, 2016 06:23AM

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.