mysql - 优化缓慢的 mysql 查询

Question

以下 mysql 查询执行时间为 40 秒，时间过长

我该如何优化？

SELECT 
    downloads.*, 
    COUNT(DISTINCT comment.id) AS commentsNum, 
    AVG(users_votes.rate) AS score, 
    COUNT(DISTINCT users_votes.id) AS rateNum, 
    COUNT(DISTINCT users_download.id) AS downloadsNum 
FROM downloads 
LEFT JOIN (
    SELECT * FROM comment WHERE type='download' 
) comment ON downloads.id = comment.typeID 
LEFT JOIN (
    SELECT * FROM users_votes WHERE section='download' AND rate>0 
) users_votes ON downloads.id = users_votes.voteID 
LEFT JOIN (
    SELECT id,downloadID FROM users_download GROUP BY userID,downloadID
) users_download ON downloads.id = users_download.downloadID 
GROUP BY downloads.id

解释的结果:

Answer 1

您应该按 download_id 对 join 中的结果集进行分组。

SELECT 
    downloads.*, 
    comment.commentsNum, 
    users_votes.AvgScore, 
    users_votes.rateNum, 
    users_download.downloadsNum 
FROM downloads 
LEFT JOIN (
    SELECT TypeID,COUNT(*) AS commentsNum FROM comment WHERE type='download'  GROUP BY TypeID
) comment ON downloads.id = comment.typeID 

LEFT JOIN (
    SELECT voteID,AVG(rate) AS AvgScore,COUNT(*) AS rateNum FROM users_votes WHERE section='download' AND rate > 0  GROUP BY voteID
) users_votes ON downloads.id = users_votes.voteID 

LEFT JOIN (
    SELECT downloadID,COUNT(userID) AS downloadsNum FROM users_download GROUP BY downloadID
) users_download ON downloads.id = users_download.downloadID