How to optimize the following query if the OR operator is used in a join condition to avoid the OR expansion in terms of SQL tuning?
SELECT t1.A, t2.B, t1.C, t1.D, t2.E
FROM t1 LEFT JOIN t2
ON t1.A=t2.A OR t1.B=t2.C;
How to avoid OR expansion?
Viewed 170
1 Answers
Having OR condition will ignore using indexes. So once you have the following indexes -
t1 (A, B)
t2 (A, C)
You may try below query having UNION ALL clause -
SELECT t1.A, t2.B, t1.C, t1.D, t2.E
FROM t1 LEFT JOIN t2 ON t1.A=t2.A
UNION ALL
SELECT t1.A, t2.B, t1.C, t1.D, t2.E
FROM t1 LEFT JOIN t2 ON t1.B=t2.C;
This query will use index and might perform faster.
Related