mysql group by using filesort优化

最新推荐文章于 2024-08-12 15:22:14 发布

b10l07

最新推荐文章于 2024-08-12 15:22:14 发布

阅读量670

点赞数

文章标签：数据库

原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;