MySQL:快速查找在另一个表中没有对应行的行 [英] MySQL: Quickly find rows that do not have a corresponding row in another table

问题描述

我在MySQL中有两个相关的表.我想在表A中找到没有对应的行表B的行.SO上的手册和其他线程建议这样做:

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;

但是，这非常慢.就我而言，表A的行数少于5000，而表B的行数约为40000，但是此查询最多需要8分钟.

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?

非常感谢你， 哑光

索引是问题所在.创建一个查询后，查询将在10微秒内运行.

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

推荐答案

1. 使用b表中的索引覆盖a_id字段
2. 将WHERE b.id IS NULL替换为WHERE b.a_id IS NULL

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

这篇关于MySQL:快速查找在另一个表中没有对应行的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！