mysql如何优化联合查询速度

Question

SELECT a.*,b.company,b.industry,b.area,b.website FROM stat_detail AS a LEFT JOIN stat_total AS b ON a.company_id=b.company_id WHERE b.rank='2' AND b.industry='1010000' AND b.area='45' ORDER BY a.detail_date DESC LIMIT 0,30  stat_detail ,stat_total分别有30多W的数据。b.company,b.industry,b.area,都建立了单列索引，a.company_id建立了单列索引。现在查询一次要差不多6秒，查询效率还能提高吗？要如何提高？谢谢

Answer 1

贴出你的 explain SELECT a.*,b.company,b.industry,b.area,b.website FROM stat_detail AS a LEFT JOIN stat_total AS b ON a.company_id=b.company_id WHERE b.rank='2' AND b.industry='1010000' AND b.area='45' ORDER BY a.detail_date DESC LIMIT 0,30和 show index from xxx的结果

Answer 2

explain SELECT a.*,b.company,b.industry,b.area,b.website FROM stat_detail AS a LEFT JOIN stat_total AS b ON a.company_id=b.company_id WHERE b.rank='2' AND b.industry='1010000' AND b.area='45' ORDER BY a.detail_date DESC LIMIT 0,30：1 SIMPLE a ALL company_id \N \N \N 325318 Using filesort
1 SIMPLE b eq_ref PRIMARY,rank,industry,area PRIMARY 4 stat.a.company_id 1 Using where
show index from stat_totalstat_total 0 PRIMARY 1 company_id A 325314 \N \N BTREE
stat_total 1 talk_avg 1 talk_avg A 4 \N \N YES BTREE
stat_total 1 rank 1 rank A 3 \N \N YES BTREE
stat_total 1 industry 1 industry A 54219 \N \N YES BTREE
stat_total 1 company 1 company A 325314 \N \N YES BTREE
stat_total 1 area 1 area A 14787 \N \N YES BTREE show index from stat_detail:stat_detail 0 PRIMARY 1 id A 325318 \N \N BTREE
stat_detail 1 company_id 1 company_id A 325318 \N \N BTREE

Answer 3

改成INNER JOINSELECT a.*,b.company,b.industry,b.area,b.website 
FROM stat_detail AS a INNER JOIN stat_total AS b ON a.company_id=b.company_id 
WHERE b.rank='2' 
AND b.industry='1010000' 
AND b.area='45' 
ORDER BY a.detail_date DESC LIMIT 0,30