Re: Optimize subquery, replace IN() with JOIN
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.
Subject
Views
Written By
Posted
2332
November 15, 2016 02:37PM
943
November 15, 2016 03:34PM
1081
November 15, 2016 04:25PM
925
November 17, 2016 08:11PM
904
November 22, 2016 02:44PM
913
November 16, 2016 04:03AM
Re: Optimize subquery, replace IN() with JOIN
937
November 16, 2016 12:51PM
1040
November 16, 2016 03:22PM
894
November 16, 2016 04:44PM
698
December 27, 2016 05:32AM
798
November 16, 2016 01:50AM
849
November 16, 2016 04:01AM
1004
November 16, 2016 06:23AM