数据库是orcal
sql如下，其中uh_bl_cg_ip表中有数据400万条，并且还在不断增加预计能达到1000万条以上
问题是执行下面sql时不加分组速度还可以，但是加上分组就极慢
select sum(cg.amount * cg.eu_direct) amount
dept.pk_dept,
dept.name,
cont.itemid,
cont.itemname,
cont.pk_father,
cg.name_psn_phy,
fa.itemname faitemname
from uh_bl_cg_ip cg
inner join uh_pv pv
on cg.pk_pv = pv.pk_pv
and pv.flag_canc = 'N'
inner join org_dept dept
on cg.pk_dept_ord = dept.pk_dept
inner join bd_uh_srvudcate_cont_item contitem
on cg.pk_srvcate = contitem.pk_srvcate
inner join bd_uh_srvudcate_cont cont
on contitem.pk_udcatecont = cont.pk_udcatecont
and cont.pk_udcate = '1001UD1000000008G7FK'
left outer join bd_uh_srvudcate_cont fa
on cont.pk_father = fa.pk_udcatecont
and cont.pk_udcate = '1001UD1000000008G7FK'
where cg.date_cg between '2016-08-01 00:00:00' and
'2016-08-23 00:00:00'
and dept.pk_org = '0001UD10000000001Z3T'
and cg.pk_dept_ord = '1001UD1000000000IY40'
group by dept.pk_dept,
dept.name,
cont.itemid,
cont.itemname,
cont.pk_father,
cg.name_psn_phy,
fa.itemname
只要是uh_bl_cg_ip这张表就及会影响速度
索引已经建了
http://www.jb51.net/article/40281.htm
http://www.jb51.net/article/40281.htm
先explain下，把执行计划打印出来，再看哪里需要优化。
参考自：
MySQL如何优化GROUP BY http://www.data.5helpyou.com/article237.html
在数据量大且增长迅速的情况下，你这个SQL本身效率就不高，我以为最好的方式是新建中间表。