一条SQL执行多次 sum 特别慢-php教程-PHP中文网

一条SQL执行多次 sum 特别慢

php中文网

发布： 2016-06-06 20:29:55

原创

2082人浏览过

<code>SELECT agentid,count(id) AS allnum,
    sum(if(review_flag = 'p'AND delflag IN ('N' , 'P'),1,0)) AS approved_1,
    sum(if(review_flag = 'E', 1, 0)) AS approved_2,
    sum(if(review_mode = '3' AND review_flag = 'Y',1,0)) AS approved_3,
    sum(if(review_mode = '2' AND review_flag = 'Y',1,0)) AS approved_4,
    sum(if(review_mode = '1' AND review_flag = 'Y',1,0)) AS approved_5,
    sum(if(review_flag = 'N' AND resubmit = 'Y',1,0)) AS approved_6,
    sum(if(review_flag = 'N' AND resubmit = 'N',1,0)) AS approved_7
    FROM talbe WHERE agentid IN ('A' , 'B', 'C') AND delflag IN ('N' , 'P', 'H') 
    GROUP BY agentid

      
该语句执行的时候 需要20多S才完成。
</code>

登录后复制

回复内容：

<code>SELECT agentid,count(id) AS allnum,
    sum(if(review_flag = 'p'AND delflag IN ('N' , 'P'),1,0)) AS approved_1,
    sum(if(review_flag = 'E', 1, 0)) AS approved_2,
    sum(if(review_mode = '3' AND review_flag = 'Y',1,0)) AS approved_3,
    sum(if(review_mode = '2' AND review_flag = 'Y',1,0)) AS approved_4,
    sum(if(review_mode = '1' AND review_flag = 'Y',1,0)) AS approved_5,
    sum(if(review_flag = 'N' AND resubmit = 'Y',1,0)) AS approved_6,
    sum(if(review_flag = 'N' AND resubmit = 'N',1,0)) AS approved_7
    FROM talbe WHERE agentid IN ('A' , 'B', 'C') AND delflag IN ('N' , 'P', 'H') 
    GROUP BY agentid

      
该语句执行的时候 需要20多S才完成。
</code>

登录后复制

调整表结构. 似乎你把很复杂的逻辑放在数据表结构里了.
多次sum不是问题. 主要在于这个group by上, 一般的, mysql 全表扫一边, 创建临时表, 排序分组. 这个是很慢的. 你可以试一下建立 (agentid, delflag, review_flag, review_mode, resubmit, id) 的联合索引.

索引，从新设计你自己标的结构。。。怎么会sum这么多次。。。

这不是sum的问题啊，这条语句本来就复杂。