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