mysql group by using filesort优化

1. 原join 连接语句

SELECT 
    SUM(video_flowers.number) AS num,
    video_flowers.flower_id,
    flowers.title,
    flowers.image
FROM
    `video_flowers`
        JOIN
    `flowers` ON `video_flowers`.`flower_id` = `flowers`.`id`
        JOIN
    `video_posts` ON `video_flowers`.`video_post_id` = `video_posts`.`id`
WHERE
    `video_posts`.`user_id` = 36
GROUP BY `video_flowers`.`flower_id`

可以优化成

SELECT 
    vf.num, flowers.title, flowers.image
FROM
    `flowers`
        
        join
    (SELECT 
        SUM(video_flowers.number) AS num, video_flowers.flower_id, video_flowers.video_post_id
    FROM
        video_flowers 
    GROUP BY `video_flowers`.`flower_id`) AS vf ON `vf`.`flower_id` = `flowers`.`id`
    join `video_posts` on `video_posts`.`id` = vf.`video_post_id`
    where video_posts.user_id = 36;

这样就没有using filesort 和using temporary

转载于:https://www.cnblogs.com/binxyz/p/7998528.html