sql优化，求高人赐教，不胜感激
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 | |
+-------------------+--------------+------+-----+------------+-------+
[解决办法]
explain sql语句
mainMPRuleID, statetime上建立索引没有
[解决办法]
mainMPRuleID = 'MP20110729104637218' 都已经明确了，还要group by 它干嘛
[解决办法]
create index xxx on (mainMPRuleID,statetime)
[解决办法]
where 1 = 1 你要这个干什么啊
在where子句中创建索引
