求助帖 优化mysql 查询 多表关联 并在其中用到sum

[David宫洪深]

select h.*,u.user_name,adb.payment as payment ,b.building_name from t_house h
LEFT JOIN (select house_id,sum(payment) as payment
FROM t_advance_bill where `status` = 0 GROUP BY house_id) as adb ON h.house_id = adb.house_id
LEFT JOIN t_building b ON h.building_id = b.building_id
LEFT JOIN t_user_house uh ON h.house_id = uh.house_id AND uh.user_type=0
LEFT JOIN t_user u ON u.user_id = uh.user_id
WHERE h.community_id = "34823b22605f7da0426485f2a3e07ae01881"
order BY h.update_time DESC

这一段sql 大概执行时间要3秒左右浮动
如果去掉中间的 sum 和 left adb 就能实现1秒内出现
求大牛优化

[吉普赛的歌]

#加入到临时表
CREATE TEMPORARY TABLE tmp_adb 
SELECT house_id,SUM(payment) AS payment
FROM   t_advance_bill
WHERE  `status` = 0
GROUP BY house_id;
#临时表加索引
ALTER TABLE tmp_adb ADD PRIMARY KEY (house_id);

#查询
SELECT h.*,
       u.user_name,
       adb.payment    AS payment,
       b.building_name
FROM   t_house h
       LEFT JOIN tmp_adb AS adb
            ON  h.house_id = adb.house_id
       LEFT JOIN t_building b
            ON  h.building_id = b.building_id
       LEFT JOIN t_user_house uh
            ON  h.house_id = uh.house_id
            AND uh.user_type = 0
       LEFT JOIN t_user u
            ON  u.user_id = uh.user_id
WHERE  h.community_id = "34823b22605f7da0426485f2a3e07ae01881"
ORDER BY
       h.update_time     DESC;
       
#删除临时表
DROP TABLE tmp_adb;

试下有没有效果