SQL查询优化：以大量的时间

Question

我有用来获取一组订单的以下查询：

select count(distinct po.orderid) 
from PostOrders po, 
    ProcessedOrders pro 
where pro.state IN ('PENDING','COMPLETED') 
    and po.comp_code in (3,4) 
    and pro.orderid = po.orderid 

查询返回的4323的结果，这样做不够快。

但我不得不把另一个条件，例如它只返回，如果它是不存在的，我添加一个额外的条件查询另一个表DiscarderOrders：

select count(distinct po.orderid) 
from PostOrders po, 
    ProcessedOrders pro 
where pro.state IN ('PENDING','COMPLETED') 
    and po.comp_code in (3,4) 
    and pro.orderid = po.orderid 
    and po.orderid not in (select do.order_id from DiscardedOrders do) 

上面的查询需要大量的时间，只是继续运行。有什么我可以做的查询，以便它快速执行？或者我需要先执行第一个查询，然后根据条件通过拍摄另一个查询来过滤？

Answer 1

你可以试着更换：

and po.orderid not in (select do.order_id from DiscardedOrders do) 

通过

and not exists (select 1 from DiscardedOrders do where do.order_id = po.orderid) 

Answer 2

使用JOIN比NOT IN

SELECT COUNT(DISTINCT po.orderid) TotalCount 
FROM PostOrders po 
     INNER JOIN ProcessedOrders pro 
      ON po.orderid = pro.orderid 
     LEFT JOIN DiscardedOrders do 
      ON po.orderid = do.orderid 
WHERE po.comp_code IN (3,4) AND 
     pro.state IN ('PENDING','COMPLETED') AND 
     do.orderid IS NULL 

OR NOT EXISTS

试0

SELECT COUNT(DISTINCT po.orderid) TotalCount 
FROM PostOrders po 
     INNER JOIN ProcessedOrders pro 
      ON po.orderid = pro.orderid 
WHERE po.comp_code IN (3,4) AND 
     pro.state IN ('PENDING','COMPLETED') AND 
     NOT EXISTS 
     (
      SELECT 1 
      FROM DiscardedOrders do 
      WHERE po.orderid = do.orderid 
     ) 

Answer 3

同不同：

SELECT count(po.orderid) 
    FROM PostOrders po 
WHERE po.comp_code IN (3,4) 
    AND EXISTS 
    (
    SELECT 1 FROM ProcessedOrders pro 
    WHERE pro.orderid = po.orderid 
     AND pro.state IN ('PENDING','COMPLETED') 
    ) 
    .... 
/