How can I optimize this MySQL query?

Question

can someone explain why adding a group with a subquery makes this request long (30 seconds):

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) 

If I delete the "group by" (and increasing the results of the subquery), it takes 0.07 seconds:

 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) 

There are no external links in the subquery, so it should only be executed once, right? Doing this yourself:

 SELECT sources.musical_work_id FROM sources GROUP BY sources.musical_work_id HAVING COUNT(sources.musical_work_id) > 8 

takes only 0.01 seconds.

Any explanation? Any suggestions on how to change it?

Answer 1

There are no external links in the subquery, so it should only be executed once, right?

You would think so, but no. If you look at EXPLAIN, you will see that the subquery is called “DEPENDENT SUBKER” and not “SUBQUERY”. This means that it is re-executed every time. This is a known bug in MySQL 5.0 and fixed in MySQL 6.0.

To get around this, you can use one of the other approaches to check if a row exists in another table. Three common methods DO NOT ENTER, DO NOT EXIST, and LEFT INTRODUCTION ... WHERE ... NO, so you still have two options.

Answer 2

Perhaps NOT is NOT your problem. Try joining it instead (you need to flip the HAVING clause):

 SELECT * FROM aggregate_songlist AS a INNER JOIN musical_works AS m ON a.musical_work_id = m.id LEFT JOIN ( SELECT sources.musical_work_id FROM sources GROUP BY sources.musical_work_id HAVING COUNT(sources.musical_work_id) <= 8) AS t ON m.id = t.musical_work_id WHERE m.genre='rock' AND t IS NULL 

[updated to reflect @Mark Byers comment, thanks!]

Answer 3

 SELECT * FROM aggregate_songlist AS a INNER JOIN musical_works AS m ON a.musical_work_id = m.id LEFT JOIN ( SELECT sources.musical_work_id FROM sources GROUP BY sources.musical_work_id HAVING COUNT(sources.musical_work_id) <= 8) AS t ON m.id = t.musical_work_id WHERE m.genre='rock' AND t.musical_work_id IS NULL