MySql NOT IN 性能问题

Question

我正在一个包含大约 100k 行的表上使用 NOT IN 运行下面的查询，性能非常糟糕。还有其他方法可以达到相同的结果吗？

SELECT c.Id, c.Name, c.address, c.town,
c.county, c.postcode, FROM contractor AS c
inner join Order w
on w.Id = c.Id WHERE (c.valid = 1) and c.Id not in
(select w.Id from Order w) ORDER BY c.Name ASC

谢谢

Answer 1

您似乎对 Order 有多余的内部联接。
我相信以下查询会产生相同的结果，并且可能会提高性能。

SELECT  c.Id
        , c.Name
        , c.address
        , c.town
        , c.county
        , c.postcode
FROM    contractor AS c 
        left outer join Order w on w.Id = c.Id 
WHERE   (c.valid = 1) 
        and w.Id IS NULL
ORDER BY 
        c.Name ASC