SELECT p.planid ,p.uid ,p.planname, p.username ,p.price ,p.priceadv ,p.budget ,p.expire ,p.checkplan ,p.plantype ,p.profit ,p.deduction ,p.clearing ,p.datatime ,p.audit ,p.status ,sum(s.num) as n ,sum(s.deduction) as d ,sum(s.views) as v FROM plan as p LEFT JOIN (stats as s) On (s.planid=p.planid) Where p.plantype='cpm' GROUP BY p.planid Order By p.planid DESC 当sum求和的时候,统计stats 表的数据大时会很慢。有没有办法优化一下?谢谢各位
如果你已经设置了planid 的索引,则基本上没有什么可以优化的了。
索引情况,plantype='cpm'有多少条记录
1 SIMPLE p ALL NULL NULL NULL NULL 10 Using temporary; Using filesort
1 SIMPLE s ref planid planid 3 35dudb.p.planid 8847
是这个id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p ref plantype plantype 1 const 1 Using where; Using temporary; Using filesort
1 SIMPLE s ref planid planid 3 35dudb.p.planid 8847
show index from stats;贴出来看一下。
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
stats 0 PRIMARY 1 statsid A 1 NULL NULL BTREE
stats 1 adsid 1 adsid A NULL NULL NULL BTREE
stats 1 zoneid 1 zoneid A NULL NULL NULL BTREE
stats 1 uid 1 uid A NULL NULL NULL BTREE
stats 1 day 1 day A NULL NULL NULL BTREE
stats 1 hour 1 hour A NULL NULL NULL BTREE
stats 1 udp 1 uid A NULL NULL NULL BTREE
stats 1 udp 2 day A NULL NULL NULL BTREE
stats 1 udp 3 planid A NULL NULL NULL BTREE
stats 1 up 1 uid A NULL NULL NULL BTREE
stats 1 up 2 planid A NULL NULL NULL BTREE
stats 1 planid 1 planid A NULL NULL NULL BTREE
stats 1 ndv 1 num A NULL NULL NULL BTREE
stats 1 ndv 2 deduction A NULL NULL NULL BTREE
stats 1 ndv 3 views A NULL NULL NULL BTREE
show index from plan;Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
plan 0 PRIMARY 1 planid A 10 NULL NULL BTREE
plan 1 plantype 1 plantype A 2 NULL NULL BTREE
plan 1 uid 1 uid A 10 NULL NULL BTREE
plan 1 status 1 status A 1 NULL NULL BTREE
plan 1 pps 1 planid A 10 NULL NULL BTREE
plan 1 pps 2 plantype A 10 NULL NULL BTREE
plan 1 pps 3 status A 10 NULL NULL BTREE
plan 1 ps 1 planid A 10 NULL NULL BTREE
plan 1 ps 2 status A 10 NULL NULL BTREE
plan 1 ppsc 1 planid A 10 NULL NULL BTREE
plan 1 ppsc 2 plantype A 10 NULL NULL BTREE
plan 1 ppsc 3 status A 10 NULL NULL BTREE
plan 1 ppsc 4 checkplan A 10 10 NULL BTREE
plan 1 pppu 1 planid A 10 NULL NULL BTREE
plan 1 pppu 2 planname A 10 NULL NULL BTREE
plan 1 pppu 3 plantype A 10 NULL NULL BTREE
plan 1 pppu 4 uid A 10 NULL NULL BTREE