mysql - 嵌套子查询太慢

Question

以下使用双重嵌套子查询的 mysql 查询太慢

SELECT t.name
FROM creatives AS c
INNER JOIN term_relationships AS tr ON tr.creative_id=c.creative_id
INNER JOIN terms AS t ON t.term_id=tr.term_id
WHERE c.creative_id IN
   (SELECT creative_id
    FROM term_relationships
    WHERE term_id IN
       (SELECT offer_term_id
        FROM offer_urls))
AND t.taxonomy LIKE 'ad_network';

内部查询

SELECT creative_id
    FROM term_relationships
    WHERE term_id IN
       (SELECT offer_term_id
        FROM offer_urls)

非常快(0.04秒)

但是完整的查询不会提供结果。等了大约5分钟我就放弃了。

有什么办法可以优化这个吗？

Answer 1

尝试:

SELECT t.name
FROM creatives AS c
JOIN term_relationships AS tr ON tr.creative_id=c.creative_id
JOIN terms AS t ON t.term_id=tr.term_id
JOIN term_relationships tr2 ON tr2.creative_id = c.creative_id
JOIN offer_urls ou ON ou.offer_term_id = tr2.term_id
WHERE t.taxonomy LIKE 'ad_network';

这个仅使用 JOIN 编写的版本会快得多。