嵌套SQL查询花费的时间太长

Question

我正在寻找一种方法来优化我拥有的一个SQL查询。我想知道有多少首诗属于某一流派。

查询如下所示：

SELECT
    COUNT(*)  
FROM
    `poems`
WHERE `id` IN (    
                  SELECT `poem_id`
                  FROM `poems_genres`  
                  WHERE `genre_title` = 'derision'
              )
       AND `status` = 'finished';

它花费的时间太长(大约6-10秒)，因为它不能使用索引(我想是因为IN()？)。有没有办法以不同的方式重写这个查询，以更快地获得相同的结果？

Answer 1

使用in的MySQL has a problem，它反复重新评估不相关的子查询，就好像它们是相关的一样。作为连接的重写能改善事情吗？

SELECT
    COUNT(distinct p.`id`)  
FROM `poems` p
JOIN `poems_genres` pg
ON  p.`id` = pg.`poem_id`  
WHERE pg.`genre_title` = 'derision' AND p.`status` = 'finished';

如果不是这样，那么根据this article (请参阅“如何强制内部查询首先执行”一节)，将其包装在派生表中可能会有所帮助。

SELECT
    COUNT(*)  
FROM
    `poems`
WHERE `id` IN
(
 select  `poem_id` from ( SELECT `poem_id`
                  FROM `poems_genres`  
                  WHERE `genre_title` = 'derision') x

) AND `status` = 'finished';

Answer 2

还可以使用EXISTS子句并对id和poem_id字段进行关联

SELECT
    COUNT(*)  
FROM
    `poems` p1
WHERE EXISTS
(
 SELECT `poem_id`
                  FROM `poems_genres`  pg
                  WHERE `genre_title` = 'derision'  and p1.id = pg.poem_id

) AND `status` = 'finished';

与IN的不同之处在于，它将尝试使用索引，而不是执行全表扫描。