mysql表联合union优化为表连接join

      话说之前用php写的一个统计跑得好好的，突然一天不出数据了，追其原因出在sql语句上，由于数据量增大加上服务器老旧，运行sql语句的时候出现内存不足的提示，遂决定优化sql语句使其不产生临时表。需求清晰，下面举例说明：

 

原sql语句：
SELECT COUNT( DISTINCT md5 ) FROM active AS a
              WHERE a.md5 in ( select md5 from alive_20091220 union 
                                           select md5 from alive_20091221 union 
                                           select md5 from alive_20091222 union 
                                           select md5 from alive_20091223 )

优化后的sql语句：
SELECT COUNT( DISTINCT a.md5 ) FROM active AS a
               LEFT OUTER JOIN alive_20091220 AS b ON a.md5 = b.md5
               LEFT OUTER JOIN alive_20091221 AS c ON a.md5 = c.md5
               LEFT OUTER JOIN alive_20091222 AS d ON a.md5 = d.md5
               LEFT OUTER JOIN alive_20091223 AS e ON a.md5 = e.md5

     

      呵呵，去试试吧。从未满足，因为不断突破。