Is it possible to optimize query using the EXISTS

2019-09-21 09:15发布

问题:

I have query which works.

select contract_no AS c_no, cm_mac AS c_mc, MIN(tstamp) as time2, sum(1) as aps
from devices where 
contract_no in 
(select distinct(contract_no) from devices where 
tstamp >= '2018-10-28 06:59:59' AND tstamp <= '2018-10-29 07:00:00')
group by contract_no, cm_mac;

I realized that query is slow so I was wondering if it is possible in anyway to optimize this query? I was thinking maybe to use EXISTS instead of IN but in that case I cannot start with EXISTS (SELECT 1 from .... where contract_no= contract_no ) because I need this DISTINCT clause.

Of course I need to return same results. Is this possible to somehow optimize this query?

UPDATE:

I checked feedbacks and you are right. If those two queries are executed I am getting the same results. But the point is that the full query is more complex, and if I do not have this sub query I am getting more results.

QUERY 1 (Returns 72 rows which is Correct):

    SELECT id, contract_no, customer, address, cm_mac, aps 
    FROM (select * from new_installed_devices where  insert4date >='2018-10-28' 
    AND insert4date <='2018-10-28' AND install_mark<2) as d1 
left join 
( select * from (select contract_no AS c_no, cm_mac AS c_mc, 
MIN(tstamp) as time2, sum(1) as aps from devices_change 
where contract_no in (select distinct(contract_no) from devices_change 
where tstamp >= '2018-10-28 06:59:59' AND tstamp <= '2018-10-29 07:00:00') 
group by contract_no, cm_mac ) as mtmbl 
where mtmbl.time2 >= '2018-10-28 06:59:59' and mtmbl.time2 <= '2018-10-29 
07:00:00' ) as tmp on d1.contract_no=tmp.c_no 
where aps>0 group by contract_no, customer, address, cm_mac;

QUERY 2 (Returns 75 rows which is not Correct) and this approach has your recommendation (to include two queries within the one):

SELECT id, contract_no, customer, address, cm_mac, aps  
FROM (select * from new_installed_devices where  insert4date >='2018-10-28' 
AND insert4date <='2018-10-28' AND install_mark<2) as d1 left join 
( select * from (select distinct(contract_no) AS c_no, cm_mac AS c_mc, 
MIN(tstamp) as time2, sum(1) as aps from devices_change 
where  tstamp >= '2018-10-28 06:59:59' AND tstamp <= '2018-10-29 07:00:00'
 group by contract_no, cm_mac ) as mtmbl 
where mtmbl.time2 >= '2018-10-28 06:59:59' and 
mtmbl.time2 <= '2018-10-29 07:00:00' ) as tmp 
on d1.contract_no=tmp.c_no 
where aps>0 group by contract_no, customer, address, cm_mac;

回答1:

Try this version:

select contract_no AS c_no, cm_mac AS c_mc, min(tstamp) as time2, count(*) as aps
from devices d
where exists (select 1
              from devices d2
              where d2.contract_no = d.contract_no and
                    tstamp >= '2018-10-28 06:59:59' and
                    tstamp <= '2018-10-29 07:00:00'
              )
group by contract_no, cm_mac;

You want an index on devices(contract-no, tstamp).