Re: Optimize subquery, replace IN() with JOIN

[Miron jajtic]

Hello,

I have replied to Peter with link to show table and datas, but it's moderated so it's waiting forum moderator to allow it.

EXPLAIN SELECT id, title, alias FROM content WHERE published = '1' AND typ = 'actual' AND id IN ( SELECT content_id FROM category_content_se WHERE category_id IN ( SELECT category_id FROM category_content_se WHERE content_id = '1842' ) AND content_typ = 'actual' ) ORDER BY date_add DESC LIMIT 10;
+------+--------------+---------------------+--------+------------------------------------+--------------+---------+-----------------------------------------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+---------------------+--------+------------------------------------+--------------+---------+-----------------------------------------------+------+----------------------------------------------------+
| 1 | PRIMARY | content | range | PRIMARY,published,typ | published | 1 | NULL | 27 | Using index condition; Using where; Using filesort |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | |
| 2 | MATERIALIZED | category_content_se | ref | category_id,content_id | content_id | 4 | const | 7 | |
| 2 | MATERIALIZED | category_content_se | ref | category_id,content_id,content_typ | category_id | 4 | slobodna_main.category_content_se.category_id | 2 | Using where |
+------+--------------+---------------------+--------+------------------------------------+--------------+---------+-----------------------------------------------+------+----------------------------------------------------+
4 rows in set (0.00 sec)