Re: Optimize subquery, replace IN() with JOIN

[Øystein Grøvlen]

Hi,

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