比如有这么一个SQL语句:SELECT cm.username AS useraccount,cm.email AS email,if(b.registTime,b.realname,cmf.realname) AS username,
if(b.registTime,b.phonenumber,cmf.phonenumber) AS phonenumber,
if(b.registTime,b.provinceid,cmf.provinceid) AS provinceid,
if(b.registTime,b.cityid,cmf.cityid) AS cityid
FROM c_members cm JOIN c_memberfields cmf ON cm.uid=cmf.uid LEFT JOIN b_user b ON cm.uid=b.uid
WHERE cmf.updatedate<=1299135667 and cmf.updatedate>1299135667-3600;想要把IF判断放到PHP处理,应该如何改写这个SQL。或者说应该如何优化这个语句以达到更好的效果,比如说涉及到数百万级数据的检索。谢谢!!
if(b.registTime,b.phonenumber,cmf.phonenumber) AS phonenumber,
if(b.registTime,b.provinceid,cmf.provinceid) AS provinceid,
if(b.registTime,b.cityid,cmf.cityid) AS cityid
FROM c_members cm JOIN c_memberfields cmf ON cm.uid=cmf.uid LEFT JOIN b_user b ON cm.uid=b.uid
WHERE cmf.updatedate<=1299135667 and cmf.updatedate>1299135667-3600;想要把IF判断放到PHP处理,应该如何改写这个SQL。或者说应该如何优化这个语句以达到更好的效果,比如说涉及到数百万级数据的检索。谢谢!!
解释一下
FROM c_members cm JOIN c_memberfields cmf ON cm.uid=cmf.uid LEFT JOIN b_user b ON cm.uid=b.uid
WHERE cmf.updatedate<=1299135667 and cmf.updatedate>1299135667-3600;这段的效率。贴出你的explain select *
FROM c_members cm JOIN c_memberfields cmf ON cm.uid=cmf.uid LEFT JOIN b_user b ON cm.uid=b.uid
WHERE cmf.updatedate<=1299135667 and cmf.updatedate>1299135667-3600;和
show index from c_members;
show index from c_memberfields;
show index from b_user;