mysql查询在多个条件下运行缓慢

Question

我有两个表 A 和 B 都非常大。我正在尝试执行这样的查询，但需要几分钟才能完成，速度非常慢。表 A 具有 name 的索引，表 B 具有 other_name 的索引。

SELECT * FROM A
LEFT JOIN B ON A.id = B.id
WHERE A.name = 'text' OR B.other_name = 'text'

如果我使用 UNION 执行单个条件，则查询运行速度非常快。

SELECT * FROM A
LEFT JOIN B ON A.id = B.id
WHERE A.name = 'text'

UNION

SELECT * FROM A
LEFT JOIN B ON A.id = B.id
WHERE B.other_name = 'text'

我不明白为什么第一个运行起来比第二个慢得多。

Answer 1

只是添加到 Gordon Linoff 的回答中，直接来自 dev.mysql :

Minimize the OR keywords in your WHERE clauses. If there is no index that helps to locate the values on both sides of the OR, any row could potentially be part of the result set, so all rows must be tested, and that requires a full table scan. If you have one index that helps to optimize one side of an OR query, and a different index that helps to optimize the other side, use a UNION operator to run separate fast queries and merge the results afterward.