mysql查找另一表中没有的项,MySQL：快速查找在另一个表中没有对应行的行

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.

解决方案

Cover a_id field with index in b table

Replace WHERE b.id IS NULL with WHERE b.a_id IS NULL