sql优化，求高人赐教，不胜感激

Question

sql:
select mainMPRuleID       ,
date_format(statetime, '%Y-%m-%d') statetime                      , 
sum(TradeCount) TradeCount       , 
sum(TradeCountSucc) TradeCountSucc       , 
sum(TradeCount) - sum(TradeCountSucc) as TradeCountFail       , 
round(sum(TradeCountSucc) * 100 / sum(TradeCount), 2) as TradeRateSucc       ,
 sum(UserCount) UserCount       ,
 sum(UserCountSucc) UserCountSucc       ,
 sum(UserCount) - sum(UserCountSucc) as UserCountFail       , 
round(sum(UserCountSucc) * 100 / sum(UserCount), 2) as UserRateSucc       ,
 sum(Income / 100) as Income      
from t_yx_trade_stat      
where 1 = 1        
and mainMPRuleID = 'MP20110729104637218'       
and statetime < '2013-01-01'                     
and statetime >= '2011-07-29'      
group by mainMPRuleID,  statetime order by statetime 
表结构是：
+-------------------+--------------+------+-----+------------+-------+
| Field             | Type         | Null | Key | Default    | Extra |
+-------------------+--------------+------+-----+------------+-------+
| MPRuleID          | varchar(128) | NO   | PRI |            |       | 
| PayChannel        | varchar(32)  | NO   | PRI |            |       | 
| ServiceCode       | varchar(64)  | NO   | PRI |            |       | 
| Discount          | varchar(50)  | NO   | PRI |            |       | 
| Buynum            | varchar(255) | NO   | PRI |            |       | 
| TradeCount        | int(12)      | YES  |     | NULL       |       | 
| FirstPriceIncome  | int(12)      | YES  |     | NULL       |       | 
| FirstPriceSucc    | int(12)      | YES  |     | NULL       |       | 
| FirstPriceFail    | int(12)      | YES  |     | NULL       |       | 
| SecondPriceIncome | int(12)      | YES  |     | NULL       |       | 
| SecondPriceSucc   | int(12)      | YES  |     | NULL       |       | 
| SecondPriceFail   | int(12)      | YES  |     | NULL       |       | 
| Income            | int(12)      | YES  |     | NULL       |       | 
| ProvideSucc       | int(12)      | YES  |     | NULL       |       | 
| ProvideFail       | int(12)      | YES  |     | NULL       |       | 
| PresentSucc       | int(12)      | YES  |     | NULL       |       | 
| PresentFail       | int(12)      | YES  |     | NULL       |       | 
| UserCount         | int(12)      | YES  |     | NULL       |       | 
| UserCountSucc     | int(12)      | YES  |     | NULL       |       | 
| TradeCountSucc    | int(12)      | YES  |     | NULL       |       | 
| StateTime         | date         | NO   | PRI | 0000-00-00 |       | 
| GroupsID          | varchar(64)  | NO   | PRI |            |       |
| bu                | varchar(128) | YES  |     | NULL       |       | 
| department        | varchar(128) | YES  |     | NULL       |       | 
| type              | varchar(128) | YES  |     | NULL       |       | 
| product           | varchar(128) | YES  |     | NULL       |       | 
| mainMPRuleID      | varchar(128) | YES  |     | NULL       |       | 
| serviceGroupID    | varchar(128) | YES  |     | NULL       |       | 
| DiscountStr       | varchar(128) | YES  |     | NULL       |       | 
| BuynumStr         | varchar(128) | YES  |     | NULL       |       | 
| ServiceGroupCode  | varchar(64)  | YES  |     | NULL       |       | 
| ServiceGroupName  | varchar(64)  | YES  |     | NULL       |       | 
| name              | varchar(128) | YES  |     | NULL       |       | 
| mainActivityName  | varchar(200) | YES  |     | NULL       |       | 
| beginTime         | date         | YES  |     | NULL       |       | 
| endTime           | date         | YES  |     | NULL       |       | 
| ActUrl            | varchar(255) | YES  |     | NULL       |       | 
| domain            | varchar(255) | YES  |     | NULL       |       | 
| req_url           | varchar(255) | YES  |     | NULL       |       | 
| pv                | int(11)      | YES  |     | NULL       |       | 
| uv                | int(11)      | YES  |     | NULL       |       | 
| TranRate          | float        | YES  |     | NULL       |       | 
+-------------------+--------------+------+-----+------------+-------+

Answer 1

explain sql语句mainMPRuleID, statetime上建立索引没有

Answer 2

mainMPRuleID = 'MP20110729104637218'   都已经明确了，还要group by 它干嘛

Answer 3

create index xxx on (mainMPRuleID,statetime)

Answer 4

where 1 = 1  你要这个干什么啊
  在where子句中创建索引

Answer 5

explain的结果
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_yx_trade_stat
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1739246
        Extra: Using where; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)

Answer 6

贴出你的 show index from ..

Answer 7

mysql> show index from t_yx_trade_stat;
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t_yx_trade_stat |          0 | PRIMARY  |            1 | MPRuleID    | A         |        NULL |     NULL | NULL   |      | BTREE      |         | 
| t_yx_trade_stat |          0 | PRIMARY  |            2 | StateTime   | A         |        NULL |     NULL | NULL   |      | BTREE      |         | 
| t_yx_trade_stat |          0 | PRIMARY  |            3 | PayChannel  | A         |        NULL |     NULL | NULL   |      | BTREE      |         | 
| t_yx_trade_stat |          0 | PRIMARY  |            4 | ServiceCode | A         |        NULL |     NULL | NULL   |      | BTREE      |         | 
| t_yx_trade_stat |          0 | PRIMARY  |            5 | Discount    | A         |        NULL |     NULL | NULL   |      | BTREE      |         | 
| t_yx_trade_stat |          0 | PRIMARY  |            6 | Buynum      | A         |        NULL |     NULL | NULL   |      | BTREE      |         | 
| t_yx_trade_stat |          0 | PRIMARY  |            7 | GroupsID    | A         |     1739246 |     NULL | NULL   |      | BTREE      |         | 
| t_yx_trade_stat |          1 | MPRuleID |            1 | MPRuleID    | A         |       19764 |     NULL | NULL   |      | BTREE      |         | 
| t_yx_trade_stat |          1 | MPRuleID |            2 | StateTime   | A         |     1739246 |     NULL | NULL   |      | BTREE      |         | 
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+