mysql query runs slow with multiple conditionals in where

Question

I have two tables A and B both are very large. I'm trying to do a query like this but it's very slow taking a couple minutes to complete. The table A has an index for name and the table B has an index for other_name.

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

If I do a single conditional each using UNION the query runs very fast as expected.

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'

I don't understand why the first runs so much slower than the second.

Answer 1

Just to add to Gordon Linoff's answer, straight from 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.