MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize subquery, replace IN() with JOIN
Posted by: Peter Brawley
Date: November 16, 2016 12:51PM

Miron,

I missed a loop of the subquery logic, which is to find content_ids having a category_id with a particular content_id.

Here is that logic as a join, for the first id in your example content table, 1807:

SELECT a.content_id
FROM category_content_se a
JOIN category_content_se b ON a.category_id=b.category_id AND b.content_id=1807
WHERE a.content_typ = 'actual' ;

Substituting that into your original query, and adding DISTINCT because IN() semijoins remove dupes but JOINs do not, we get ...

SELECT DISTINCT c.id, c.title, c.alias
FROM content c
JOIN category_content_se a ON c.id=a.content_id
JOIN category_content_se b ON a.category_id=b.category_id AND b.content_id=1807
WHERE a.content_typ = 'actual' AND c.published = 1 AND c.typ = 'actual'
ORDER BY c.date_add DESC LIMIT 10 ;

which gives the same result as your original query on the example tables.

Now as Øystein says, the puzzle is why your original query performs so slowly. In 5.6 your original query runs in 0.00 sec on a modest machine. Can we see the Explain results for the query on production data, the one that takes 5 minutes? Also let's see the mysql version number, machine RAM, and innodb_buffer_pool_size setting.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimize subquery, replace IN() with JOIN
587
November 16, 2016 12:51PM


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.