sql优化,减少表扫描次数一个例子.

select count(*)
  from (select *
          from messageview
         where threadid in
               (select threadid
                  from (select b.*, c.dreplycount
                          from (select threadid, count(*) as replycount
                                  from messageview
                                 where isthread = 0
                                 group by threadid) b,
                               (select threadid, count(*) as dreplycount
                                  from messageview a
                                 where a.isthread = 0
                                   and a.state = 1
                                 group by threadid) c
                         where b.replycount >= 10
                           and b.threadid = c.threadid)
                 where dreplycount / replycount >= 0.5)
           and isthread = 1
           and state = 0
         order by creationdate desc);
elapsed:3748s result: 728
分析:

a.state = 1是不良贴,  a.isthread = 0是回复帖, a.isthread = 1是主贴.
这个SQL是分析有回复超过10个的,不良贴达到50%的贴子有多少.但扫描messageview三次,很浪费.

 

改进后,

 

select count(1)
  from (select mv.threadid
          from messageview mv, messageview mv2
         where mv.isthread = 0
           and (mv2.state = 0 and mv.threadid = mv2.threadid and
               mv2.isthread = 1)
         group by mv.threadid
        having sum(mv.state) / count(1) >= 0.5 and count(1) >= 10);

elapsed:416s result: 727