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
------解决方案--------------------

SQL code

select     cCusCode, CCusName, paytype, iPrice, dVeriDate    from (select rdfk, paytype, sum(iPrice) iPrice   from rdrecords   where uid='1'    and rdsrowstatus='0'  group by rdfk, paytype) ainner join(select rdfk, cCusCode, CCusName, dVeriDate    from rdrecord   where uid='1'    and cStatus='1'    and cCusCode='10014'    and bRdFlag='1'    and (cVouchType='xxck' or cVouchType='xscj' or cVouchType='fpck' or cVouchType ='fpckcj')) bon a.rdfk=b.rdfk