MySql NOT IN Performance problem

Question

Hi,

I am running the query below on a table with around 100k rows in it using a NOT IN and the performance is terrible. Is there another way of achieving the same result?

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

Thanks

Answer 1

I personally can't see further improvement to your code except that you might want to add indexing to your fields. The general rule is to add index to the columns you use in your WHERE clause.

Answer 2

You seem to have a redundant inner join to Order.
I believe following query would yield the same results and might improve performance.

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