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

Question

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.

Answer 1

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