Re: Optimize subquery, replace IN() with JOIN

[Peter Brawley]

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.