是否可以使用EXISTS而不是DISTINCT的IN子句来优化查询 [英] Is it possible to optimize query using the EXISTS instead of IN clause with DISTINCT
问题描述
我有一个有效的查询。
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;
我意识到查询速度很慢,所以我想知道是否仍然可以优化该查询?
我当时在考虑也许使用EXISTS而不是IN,但是在那种情况下,我不能以 EXISTS(从....中选择SELECT 1,其中contract_no = contract_no)
开始我需要这个 DISTINCT
子句。
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.
查询1(返回72行是正确的):
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;
查询2(返回不正确的75行),此方法有您的建议(包括两个内的查询):
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;
推荐答案
尝试此版本:
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;
您要在设备(合同编号,tstamp)上建立索引
。
这篇关于是否可以使用EXISTS而不是DISTINCT的IN子句来优化查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!