MySQL WHERE NOT IN extremely slow

Question

Below is a SQL statement inside a stored procedure (truncated for brevity):

SELECT * 
FROM item a 
WHERE a.orderId NOT IN (SELECT orderId FROM table_excluded_item);

This statement takes 30 seconds or so! But if I remove the inner SELECT query, it drops to 1s. table_excluded_item is not huge, but I suspect the inner query is being executed more than it needs to be.

Is there a more efficient way of doing this?

The inner query is a dependent subquery, which are notorious bottlenecks because the subquery is run against each row of the outer query. Check out [Optimizing Subqueries](http://dev.mysql.com/doc/refman/5.1/en/optimizing-subqueries.html) on the MySQL dev site. — Anthony, Jan 05 '13 at 02:41

score 23 · Accepted Answer · answered Jan 05 '13 at 02:04

23

use LEFT JOIN

SELECT  a.* 
FROM    item a 
        LEFT JOIN table_excluded_item b
            ON a.orderId = b.orderId
WHERE   b.orderId IS NULL

make sure that orderId from both tables has been indexed.

answered Jan 05 '13 at 02:04

John Woo

258,903
69
498
492

Hey, that works, thanks! Now it's down to 2s. The language always seems very counter-intuitive to me :( – pixelfreak Jan 05 '13 at 02:14
A clever solution, although I didn't see the performance improvement. But things have changed in the last 9 years. – Brad Rhoads Jan 16 '22 at 04:02

Gordon Linoff · Answer 2 · 2013-01-06T14:53:49.523

5

The problem with the left join approach is that duplicate records might be processed in generating the output. Sometimes, this is not the case . . . according to this article, MySQL does optimize the left outer join correctly when the columns are indexed, even in the presence of duplicates. I admit to remaining skeptical, though, that this optimization always happens.

MySQL sometimes has problems optimizing IN statements with a subquery. The best fix is a correlated subquery:

SELECT * 
FROM item a 
WHERE not exists (select 1
                  from table_excluded_item tei
                  where tei.orderid = a.orderid
                  limit 1
                 )

If you have an index on table_excluded_item.orderid, then this will scan the index and stop at the first value (the limit 1 may not be strictly necessary for this). This is the fastest and safest way to implement what you want in MySQL.

edited Jan 06 '13 at 14:53

answered Jan 05 '13 at 14:09

Gordon Linoff

1,242,037
58
646
786

2

Technically the `limit 1` is not necessary; the "anti-join" will do exactly the same thing anyway. (It *could be* that mysql is not smart enough to know this) – wildplasser Jan 05 '13 at 14:13
"The problem with the left join approach is that you can get duplicate records in the output." -- why? remember that you are searching for non-existent record. – John Woo Jan 05 '13 at 23:54
@jW . . . I rephrased this. – Gordon Linoff Jan 06 '13 at 14:54

score 1 · Answer 3 · answered Jan 05 '13 at 02:47

Try this out and compare to the LEFT JOIN query time:

SELECT * 
FROM item a 
HAVING orderId NOT IN (SELECT orderId FROM table_excluded_item);

This is frowned upon (using HAVING when WHERE can be used) since HAVING assumes that the limiting condition (orderId) is part of the result set. But I think in this scenarios it makes more sense (since it is part of the result set) and because it is clearer what is going on than the LEFT JOIN approach.

It may actually be a bit slower, but post the results so we know if it's any better than your original query.

cheers - on my dataset I consistently got ~7s for this vs ~8s for the left join approach — hoju, Dec 12 '17 at 16:07

MySQL WHERE NOT IN extremely slow

3 Answers3

Linked