mysql> EXPLAIN SELECT rc_result,count(1) as RcNum
-> FROM rc_history WHERE
-> add_time BETWEEN '2011-11-30 00:00:00' AND '2011-11-30 23:59:59'
-> AND UNIX_TIMESTAMP(add_time)>0 AND event_name='draw' GROUP BY rc_result
-> ;
+----+-------------+------------+-------+---------------+----------+---------+------+-------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+----------+---------+------+-------+-----------------------------------------------------------+
| 1 | SIMPLE | rc_history | range | add_time | add_time | 202 | NULL | 25792 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+------------+-------+---------------+----------+---------+------+-------+-----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT SUM(IF(rc_result='Agree',1,0)) AS AutoAgreeNum, SUM(IF(rc_result='Audit',1,0)) AS AuditNum, SUM(IF(rc_result='PassBy',1,0)) AS PassNum
-> FROM rc_history WHERE
-> add_time BETWEEN '2011-11-30 00:00:00' AND '2011-11-30 23:59:59'
-> AND UNIX_TIMESTAMP(add_time)>0 AND event_name='draw';
+----+-------------+------------+-------+---------------+----------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+----------+---------+------+-------+--------------------------+
| 1 | SIMPLE | rc_history | range | add_time | add_time | 202 | NULL | 25792 | Using where; Using index |
+----+-------------+------------+-------+---------------+----------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUM(IF(rc_result='Agree',1,0)) AS AutoAgreeNum, SUM(IF(rc_result='Audit',1,0)) AS AuditNum, SUM(IF(rc_result='PassBy',1,0)) AS PassNum
-> FROM rc_history WHERE
-> add_time BETWEEN '2011-11-30 00:00:00' AND '2011-11-30 23:59:59'
-> AND UNIX_TIMESTAMP(add_time)>0 AND event_name='draw';
+--------------+----------+---------+
| AutoAgreeNum | AuditNum | PassNum |
+--------------+----------+---------+
| 900 | 125 | 0 |
+--------------+----------+---------+
1 row in set (0.01 sec)
mysql> SELECT rc_result,count(1) as RcNum
-> FROM rc_history WHERE
-> add_time BETWEEN '2011-11-30 00:00:00' AND '2011-11-30 23:59:59'
-> AND UNIX_TIMESTAMP(add_time)>0 AND event_name='draw' GROUP BY rc_result;
+-----------+-------+
| rc_result | RcNum |
+-----------+-------+
| Agree | 900 |
| Audit | 125 |
+-----------+-------+
2 rows in set (0.02 sec)
总结：sum是在做表扫描的时候做了运算 group by是表扫描以后在做运算