mysql子查询优化 

SELECT COUNT(*) AS tp_count FROM xxx_b2c_orders WHERE pay_status='1' and createtime>1413533130 and area_code in (1030,1031,1032,1033) and member_id in (select member_id from xxx_invite_invite where in_member_id=14273 or in_member_id=13742 or in_member_id=14299) LIMIT 1

第一种方式in where：2000ms

SELECT COUNT(*) AS tp_count
FROM xxx_b2c_orders o
WHERE pay_status = '1' and createtime > 1413533130 and area_code in (1030,1031,1032,1033) and
      exists (select 1
              from xxx_invite_invite i
              where i.member_id = o.member_id and i.in_member_id in (14273, 13742, 14299) 
             );

第二种方式exists：1600ms

SELECT COUNT(*) AS tp_count FROM xxx_b2c_orders AS bo INNER JOIN xxx_invite_invite AS ii ON bo.member_id=ii.member_id WHERE bo.pay_status='1' AND bo.area_code IN (1030,1031,1032,1033) AND bo.createtime>1413533130 AND ii.in_member_id IN (14273,13742,14299)

第三种方式join：110ms

 

效果非常明显

posted on 2014-12-19 15:57  walter371  阅读(151)  评论(0)  编辑  收藏  举报

导航