Is It Possible To Optimize Query Using The Exists Instead Of In Clause With Distinct

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 dev

Solution 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 (select1from 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'
              )
groupby contract_no, cm_mac;

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