CODEWITHSUNDEEP
mysqlperformance
Matt
Matt

Reputation: 1307

MySQL: Quickly find rows that do not have a corresponding row in another table

I have two tables in MySQL that are related. I would like to find the rows in table A that do not have a corresponding row table B. The manual and other threads here on SO recommend doing this:

SELECT a.id
  FROM a LEFT JOIN b ON a.id = b.a_id
  WHERE b.id IS NULL;

However, this is very slow. In my case, table A has less than 5000 rows, and table B is at around 40000, but this query is taking up to 8 minutes.

Does anybody know how to achieve this faster?

Thank you very much, matt

EDIT: The index was the problem. After creating one, the query runs in 10 microseconds.

Upvotes: 4

Views: 1937

Answers (3)

Karolis
Karolis

Reputation: 9562

SELECT a.id
FROM a 
WHERE NOT EXISTS (
    SELECT *
    FROM b
    WHERE a.id = b.a_id
)

And of course you should have an index on b.a_id

Upvotes: 2

Bohemian
Bohemian

Reputation: 425083

You could make it slightly faster by checking if the foreign key column is null, rather than the key (it would not account for that much slowness though):

SELECT a.id
FROM a
LEFT JOIN b ON a.id = b.a_id
WHERE b.a_id IS NULL;

If that doesn't help, try this:

create index b_a_id on b(a_id);

Upvotes: 1

zerkms
zerkms

Reputation: 254944

  1. Cover a_id field with index in b table
  2. Replace WHERE b.id IS NULL with WHERE b.a_id IS NULL

Upvotes: 5

Related Questions