一个sql优化例子:用sum取代count

原SQL:select count(*) as total from table a where a.row1='0'

修改后:
select
  sum(case a.row1 when '0' then 1 else 0 end) as total
from
  table a

或则

select
  sum(if(a.row1='0',1,0)) as total
from
  table a

SQL优化例子:

优化前

select
  s.Name,
  (select count(*) from CheckCertLog where StoreId=s.StoreId and State=0 and CheckTime between '2010-02-02' and '2010-9-2')as fail_count,
  (select count(*) from CheckCertLog where StoreId=s.StoreId and State=1 and CheckTime between '2010-02-02' and '2010-9-2')as success_count,
  (select count(*) from CheckCertLog where StoreId=s.StoreId and CheckTime between '2010-02-02' and '2010-9-2')as tcount
from
  Store as s

优化后

select 
  a.Name,
  sum(case b.State when 0 then 1 else 0 end ) as fail_count,
  sum(case b.State when 1 then 1 else 0 end ) as success_count,
  sum(case b.State when 1 then 1 else 1 end) as total
from
  Store a, CheckCertLog b
where
  a.StoreId=b.StoreId and b.CheckTime between '2010-02-02' and '2010-9-2'

评论

1 楼 cectsky 2011-04-22  

请问大哥 ，这个效率有多大的提升呢？以前还真么这么弄过！