SQL语句优化

SQL code

select cCusCode,CCusName,paytype,sum(iPrice) iPrice,dVeriDate 
from rdrecords as rs 
left join rdrecord as r on rs.rdfk = r.rdpk  
where r.uid = '1' and rs.uid = '1' and r.cStatus = '1' and rs.rdsrowstatus='0'  and r.cCusCode = '10014' 
and (r.cVouchType ='xxck' or r.cVouchType ='xscj' or r.cVouchType ='fpck' or r.cVouchType ='fpckcj')  and r.bRdFlag='1'  
group by  rs.paytype

这个SQL语句第一次执行的时候 需要3-4秒，因为表中的数据量已经比较多了，但是再次执行的时候，就非常快，
这个SQL语句放到程序里执行，总是需要3-4秒的时候才能得出结果，请问如何优化，谢谢！

------解决方案--------------------
explain select cCusCode,CCusName,paytype,sum(iPrice) iPrice,dVeriDate 
from rdrecords as rs 
left join rdrecord as r on rs.rdfk = r.rdpk
where r.uid = '1' and rs.uid = '1' and r.cStatus = '1' and rs.rdsrowstatus='0' and r.cCusCode = '10014' 
and (r.cVouchType ='xxck' or r.cVouchType ='xscj' or r.cVouchType ='fpck' or r.cVouchType ='fpckcj') and r.bRdFlag='1'
group by rs.paytype

show index from 连接表名
贴结果
在连接字段上建立索引没有
------解决方案--------------------
create index xx on rdrecords(paytype,uid,rdsrowstatus)
------解决方案--------------------
SQL语句不是标准的
select cCusCode,CCusName,paytype,sum(iPrice) iPrice,dVeriDate 
from rdrecords as rs 
left join rdrecord as r on rs.rdfk = r.rdpk
where r.uid = '1' and rs.uid = '1' and r.cStatus = '1' and rs.rdsrowstatus='0' and r.cCusCode = '10014' 
and (r.cVouchType ='xxck' or r.cVouchType ='xscj' or r.cVouchType ='fpck' or r.cVouchType ='fpckcj') and r.bRdFlag='1'
group by cCusCode,CCusName,paytype

将OR->UNION ALL
------解决方案--------------------
select cCusCode,CCusName,paytype,sum(iPrice) iPrice,dVeriDate
from rdrecords as rs
left join rdrecord as r on rs.rdfk = r.rdpk
where r.uid = '1' and rs.uid = '1' and r.cStatus = '1' and rs.rdsrowstatus='0' and r.cCusCode = '10014'
and r.cVouchType ='xxck'and r.bRdFlag='1'
group by cCusCode,CCusName,paytype
union all
select cCusCode,CCusName,paytype,sum(iPrice) iPrice,dVeriDate
from rdrecords as rs
left join rdrecord as r on rs.rdfk = r.rdpk
where r.uid = '1' and rs.uid = '1' and r.cStatus = '1' and rs.rdsrowstatus='0' and r.cCusCode = '10014'
and r.cVouchType ='xscj'and r.bRdFlag='1'
group by cCusCode,CCusName,paytype
....
------解决方案--------------------
贴出你的show index from rdrecords;
show index from rdrecord;

explain select cCusCode,CCusName,paytype,sum(iPrice) iPrice,dVeriDate 
from rdrecords as rs 
left join rdrecord as r on rs.rdfk = r.rdpk
where r.uid = '1' and rs.uid = '1' and r.cStatus = '1' and rs.rdsrowstatus='0' and r.cCusCode = '10014' 
and (r.cVouchType ='xxck' or r.cVouchType ='xscj' or r.cVouchType ='fpck' or r.cVouchType ='fpckcj') and r.bRdFlag='1'
group by rs.paytype
------解决方案--------------------

引用:

create index xx2 on rdrecord(cStatus,uid,cCusCode,bRdFlag)

索引已经全部建立完，依然是：

------解决方案--------------------
1） #11楼 上的信息反应 根本没有创建需要的索引。rdrecord(uid,cCusCode,cStatus,bRdFlag) 
2）MYSQL在第一次执行完一个SQL语句后，后进行缓存。这样第二次执行相同语句的时候，则可以直接从缓存中取数据。
------解决方案--------------------
再贴一下你的 show index 和 explain
------解决方案--------------------