CODEWITHSUNDEEP
mysql
Berry Blue
Berry Blue

Reputation: 16482

mysql query runs slow with multiple conditionals in where

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.

Upvotes: 3

Views: 138

Answers (2)

BK435
BK435

Reputation: 3176

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.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269943

In your second query, the second join is turned to an inner join, because of the where clause:

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

This query can make use of an index on A(name) and B(other_name), in each of the two parts.

The first query can only use one index. But, it neither index fully satisfies the query, so it ends up processing the query without an index.

Upvotes: 3

Related Questions