INNER JOIN with more than one OR operators in WHERE clause increases the execution time

Question

as we know - "INNER JOIN with complex condition dramatically increases the execution time please refer this"

consider the query

(
   SELECT ... 
   FROM Table1
   INNER JOIN Table2 ON Table1.P1 = Table2.P1 OR Table1.P2 = Table2.P2
)

Over here comparison will be done via "nested loops" so execution time will be more but if we have a query like-

(
    SELECT ... 
    FROM Table1 
    INNER JOIN Table2 ON Table1.P3 = Table2.P3 where Table1.P1 = "abc" OR 
    Table2.p2 = "xyz"
)

or like- ( SELECT ... FROM Table1 INNER JOIN Table2 ON Table1.P3 = Table2.P3 where Table1.P1 LIKE "abc" OR Table2.p2 LIKE "xyz" )

than also does the comparison will take place through nested loops only (for columns P1 ANd P2)?

I read that sql optimizer automatically chooses what's best for query.`on` and `where` in your case will work same — Prabhat G, Jul 11 '17 at 08:26
The `INNER JOIN` without `ON` in the third query is invalid SQL. MySQL lets it slip, but you should make this valid and readable by changing `INNER JOIN` to `CROSS JOIN`. The second query is syntactically correct but semantically wrong. You are not inner joining the tables on some condition; you are cross joining actually. (You are joining every table2 record to every abc/xyz table1 record.) — Thorsten Kettner, Jul 11 '17 at 08:26
I cannot answer your question on nested loops. But shouldn't `EXPLAIN PLAN` give you the answer? — Thorsten Kettner, Jul 11 '17 at 08:26
what are the available index on the Tables t1 and t2? You may try using union instead if you have separate index on P1 and P2 — pankajagarwal, Jul 11 '17 at 08:46
thanks for your response, I understand that I haven't questioned properly, I have edited the question please check the question again and answer it. Thanks for your support and patience. — farhan, Jul 11 '17 at 09:12

score 0 · Answer 1 · answered Jul 11 '17 at 09:11

0

Please Use Union instead of 'OR' condition in JOIN.

SELECT ... FROM Table1

INNER JOIN Table2 ON Table1.P1 = Table2.P1

UNION All

SELECT ... FROM Table1

INNER JOIN Table2 ON Table1.P2 = Table2.P2 AND Table1.P1 <> Table2.P1

answered Jul 11 '17 at 09:11

MohanaPriyan

1 Answers1