嵌套SQL查询耗时太长

MySQL数据库 has a problem in 它重复地重新计算不相关的子查询,就好像它们是相关的一样。作为一个连接重写是否能改进一些事情?

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';

您还可以使用EXISTS子句并在id和poem\u 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的区别在于它将尝试使用索引,而不是执行全表扫描。