mysql - 有什么办法可以优化这个 MySQL 查询吗？

Question

完成需要很长时间，但希望能够快速提取收集到的信息。

SELECT *
FROM releases
WHERE (artist IN (SELECT artist FROM artist_love WHERE user='Quickinho')
OR
label IN (SELECT label FROM label_love WHERE user='Quickinho')
OR
id IN (SELECT release_id FROM charts_extended WHERE artist IN (SELECT dj FROM dj_love WHERE user='Quickinho'))
OR
id IN (SELECT artist FROM releases WHERE id IN (SELECT release_id FROM charts_extended WHERE user='Quickinho'))
OR
id IN (SELECT label FROM releases WHERE id IN (SELECT release_id FROM charts_extended WHERE user='Quickinho')))
AND
id NOT IN (SELECT release_id FROM charts_extended WHERE user='Quickinho')
ORDER BY date DESC
LIMIT 0,102

Answer 1

避免任何子选择(虽然没有测试所以请原谅任何拼写错误)

SELECT *
FROM releases
LEFT OUTER JOIN artist_love ON releases.artist = artist_love.artist AND artist_love.user = 'Quickinho'
LEFT OUTER JOIN label_love ON releases.label = label_love.label AND label_love.user = 'Quickinho'
LEFT OUTER JOIN charts_extended ON releases.id = charts_extended.release_id
LEFT OUTER JOIN dj_love ON charts_extended.artist = dj_love.dj AND dj_love.user = 'Quickinho'
LEFT OUTER JOIN releases releases1 ON releases.id = releases1.artist
LEFT OUTER JOIN charts_extended charts_extended1 ON charts_extended1.artist = releases1.id AND charts_extended1.user = 'Quickinho'
LEFT OUTER JOIN releases releases2 ON releases.id = releases2.label
LEFT OUTER JOIN charts_extended charts_extended2 ON charts_extended2.artist = releases2.id AND charts_extended2.user = 'Quickinho'
LEFT OUTER JOIN charts_extended charts_extended3 ON charts_extended3.release_id = releases.id AND charts_extended3.user = 'Quickinho'
WHERE (artist_love.user IS NOT NULL
OR label_love.user IS NOT NULL
OR dj_love.user IS NOT NULL
OR charts_extended1.user IS NOT NULL
OR charts_extended2.user IS NOT NULL)
AND charts_extended3.user IS NULL