关于mysql查询语句优化问题!!!求优化

风情万种 发布于 2014/07/12 11:15
阅读 293
收藏 0

select phone_web.webid,phone_web.webname,phone_web.weburl,(select count(*) from phone_phone where sname ='' and phone_phone.webid = phone_web.webid) as Direct,(select count(*) from phone_phone where sname ='百度' and phone_phone.webid = phone_web.webid) as baidu,(select count(*) from phone_phone where sname ='谷歌' and phone_phone.webid = phone_web.webid) as google,(select count(*) from phone_phone where sname ='SOSO' and phone_phone.webid = phone_web.webid) as soso,(select count(*) from phone_phone where sname ='360' and phone_phone.webid = phone_web.webid) as qihu,(select count(*) from phone_phone where sname ='搜狗' and phone_phone.webid = phone_web.webid) as sogou,(select count(*) from phone_phone where sname ='其它来源' and phone_phone.webid = phone_web.webid) as other from phone_web where phone_web.uid = 5

这条语句查询很慢 小白求优化!

加载中
2
开源中国投资人
开源中国投资人

首先得批评你,你这事什么SQL 啊,你没有格式化一下、然后还不取别名 ,看着真心难受


SELECT p.webid, p.webname, p.weburl,
 sum(if(p.sname = NULL,1,0)) AS Direct,
 sum(if(p.sname = '百度',1,0)) AS baidu,
 sum(if(p.sname = '谷歌',1,0)) AS google,
 sum(if(p.sname = 'SOSO',1,0)) AS soso,
 sum(if(p.sname = '360',1,0)) AS qihu,
 sum(if(p.sname = '搜狗',1,0)) AS sogou,
 sum(if(p.sname = '其它来源',1,0)) AS other
FROM phone_web p
WHERE p.uid = 5
AND phone_phone.webid = phone_web.webid
group by p.webid, p.webname, p.weburl

风情万种
风情万种
回复 @jesusy : 看截图
开源中国投资人
开源中国投资人
回复 @风情万种 : 本地没环境,你试一下,大体是这个意思,不行的话我再看
风情万种
风情万种
......大神,见谅
0
NealFeng
NealFeng

执行下面两部分语句,把输出贴出来让其它人分析吧:

desc <查询语句>\G

=================

set @@profiling=1;

<执行查询语句>

show profile\G

风情万种
风情万种
我这有30万的数据
0
官方入员
官方入员
修改程序逻辑吧....做个冗余
0
开源中国投资人
开源中国投资人
SELECT p.webid, p.webname, p.weburl,
 sum(if(p.sname = NULL,1,0)) AS Direct,
 sum(if(p.sname = '百度',1,0)) AS baidu,
 sum(if(p.sname = '谷歌',1,0)) AS google,
 sum(if(p.sname = 'SOSO',1,0)) AS soso,
 sum(if(p.sname = '360',1,0)) AS qihu,
 sum(if(p.sname = '搜狗',1,0)) AS sogou,
 sum(if(p.sname = '其它来源',1,0)) AS other
FROM phone_web p
WHERE p.uid = 5
AND phone_phone.webid = phone_web.webid
group by p.webid, p.webname, p.weburl
开源中国投资人
开源中国投资人
不好意思,我粗心看错了,以为只一张表,所以说写SQL 取别名是很重要的,我再看一下
风情万种
风情万种
这只查了一个表啊 phone表没查
0
bigtiger02
bigtiger02

引用来自“jesusy”的评论

SELECT p.webid, p.webname, p.weburl,
 sum(if(p.sname = NULL,1,0)) AS Direct,
 sum(if(p.sname = '百度',1,0)) AS baidu,
 sum(if(p.sname = '谷歌',1,0)) AS google,
 sum(if(p.sname = 'SOSO',1,0)) AS soso,
 sum(if(p.sname = '360',1,0)) AS qihu,
 sum(if(p.sname = '搜狗',1,0)) AS sogou,
 sum(if(p.sname = '其它来源',1,0)) AS other
FROM phone_web p
WHERE p.uid = 5
AND phone_phone.webid = phone_web.webid
group by p.webid, p.webname, p.weburl
+1
0
开源中国投资人
开源中国投资人

引用来自“风情万种”的评论

@jesusy 
SELECT PW.webid,
        PW.webname,
        PW.weburl,
        PP.Direct,
         PP.baidu,
         PP.google,
         PP.soso,
         PP.qihu,
         PP.sogou,
         PP.other
FROM phone_web PW
LEFT JOIN
(
    SELECT  
    sum(if(p.sname = NULL,1,0)) AS Direct,
     sum(if(p.sname = '百度',1,0)) AS baidu,
     sum(if(p.sname = '谷歌',1,0)) AS google,
     sum(if(p.sname = 'SOSO',1,0)) AS soso,
     sum(if(p.sname = '360',1,0)) AS qihu,
     sum(if(p.sname = '搜狗',1,0)) AS sogou,
     sum(if(p.sname = '其它来源',1,0)) AS other,
     p.webid
     FROM phone_phone p
     group by p.webid
) PP ON PP.webid = PW.webid

WHERE PW.uid = 5

再试一次 看是不是你要的结果

0
风情万种
风情万种

引用来自“风情万种”的评论

@jesusy 

引用来自“jesusy”的评论

SELECT PW.webid,
        PW.webname,
        PW.weburl,
        PP.Direct,
         PP.baidu,
         PP.google,
         PP.soso,
         PP.qihu,
         PP.sogou,
         PP.other
FROM phone_web PW
LEFT JOIN
(
    SELECT  
    sum(if(p.sname = NULL,1,0)) AS Direct,
     sum(if(p.sname = '百度',1,0)) AS baidu,
     sum(if(p.sname = '谷歌',1,0)) AS google,
     sum(if(p.sname = 'SOSO',1,0)) AS soso,
     sum(if(p.sname = '360',1,0)) AS qihu,
     sum(if(p.sname = '搜狗',1,0)) AS sogou,
     sum(if(p.sname = '其它来源',1,0)) AS other,
     p.webid
     FROM phone_phone p
     group by p.webid
) PP ON PP.webid = PW.webid

WHERE PW.uid = 5

再试一次 看是不是你要的结果

完美!速度0.48秒! 膜拜啊  我研究研究你这条
风情万种
风情万种
点击此处输入评论
开源中国投资人
开源中国投资人
能给积分就好了....
返回顶部
顶部