单表级联查询优化

优化前的糟糕语句

select t.userid ,

(select count(*) from snsjob.mxt_temp_lxw_voice_info m where m.statename='广东' and m.userid=t.userid ) totals,

(select count(*) from snsjob.mxt_temp_lxw_voice_info m where m.statename='广东' and m.userid=t.userid and m.isvalid = 0 ) uncheck,

(select count(*) from snsjob.mxt_temp_lxw_voice_info n where m.statename='广东' and n.userid=t.userid and n.isvalid = 1 ) checkedpass

from snsjob.mxt_temp_lxw_voice_info t

group by t.userid order by checkedpass desc

优化后如下

select t.userid,

count(*),

sum(case when isvalid = 0 then 1 else 0 end ),

sum(case when isvalid = 1 then 1 else 0 end ) checkedpass

from snsjob.mxt_temp_lxw_voice_info t where statename='广东'

group by t.useridby checkedpass desc

order

速度快20倍以上，数据越多越明显


只有注册用户登录后才能发表评论。




网站导航: 博客园 IT新闻知识库 C++博客博问管理
相关文章: Mysql 查找当天记录语句 MYSQL 主从服务器配置工作原理 Mysql-5.5.3-m3 主从同步不支持master-host问题的解决办法[转] Mysql 实现split字符串分割 Oracle 数据库常用命令 NoSQL数据库笔谈（转） Oracle触发器及sql使用技巧 oracle 查询不重复多列sql写法【原创】解决MYSQL ERROR 1045 (28000)问题 MySQL索引分析和优化

都市淘沙者