性能优化
求优化:
SELECT * FROM pds_buy WHERE p_dt BETWEEN '1999-01-01' AND '2013-09-01'
AND p_buyid in (select distinct p_buyid from pds_buy_product where p_id like '%33300%')
以上这句执行结果为37秒
把上面二句单独
SELECT * FROM pds_buy WHERE p_dt BETWEEN '1999-01-01' AND '2013-09-01' (时间0.125S)
select distinct p_buyid from pds_buy_product where p_id like '%33300% (时间0.062S)
组合为IN后,结果36 37秒,有没什么优化方法,注意,第二表pds_buy_product 不能放进前面的FROM中,
因为这个是程序组合条件进去的,如果没有这个条件,就不用的,所以在FROM中加上第二个表是不行的 性能优化
[解决办法]
SELECT *
FROM pds_buy a
inner join (select distinct p_buyid from pds_buy_product where p_id like '%33300%') b
on a.p_buyid=b.p_buyid
WHERE a.p_dt BETWEEN '1999-01-01' AND '2013-09-01'
SELECT * FROM pds_buy
WHERE p_dt BETWEEN '1999-01-01' AND '2013-09-01'
and exists(
select 1 from pds_buy_product
where p_id like '%33300%'
and p_buyid = pds_buy.p_buyid
)