请问为什么添加group by子查询会导致这个查询需要花费如此长的时间(30秒):
SELECT *
FROM aggregate_songlist AS a
INNER JOIN musical_works AS m
ON a.musical_work_id = m.id
WHERE m.genre='rock' AND m.id NOT IN
(SELECT sources.musical_work_id FROM sources GROUP BY sources.musical_work_id HAVING COUNT(sources.musical_work_id) > 8)
如果我去掉“group by”(并增加子查询的结果),那么它只需要0.07秒:
SELECT *
FROM aggregate_songlist AS a
INNER JOIN musical_works AS m
ON a.musical_work_id = m.id
WHERE m.genre='rock' AND m.id NOT IN
(SELECT sources.musical_work_id FROM sources)
子查询中没有外部引用,所以它应该只被执行一次,对吗? 单独执行它:
SELECT sources.musical_work_id FROM sources GROUP BY sources.musical_work_id HAVING COUNT(sources.musical_work_id) > 8
只需要0.01秒。
有什么解释吗?有没有建议如何改变它?