Using index in LEFT JOIN with OR condition

Question

Consider following query:

SELECT *
FROM table1
LEFT JOIN table2 ON
   table2.some_primary_key = table1.some_primary_key
LEFT JOIN table3 ON
   table3.some_primary_key = table1.some_primary_key OR -- this is the issue
   table3.column_with_index = table2.column_with_index

while I check EXPLAIN it shows me index is not in use for table3 join (however indexes are shown in "possible_keys"). Type: 'ALL'. As per manual:

Join type "ALL": A full table scan is done for each combination of rows from the previous tables.

Query is awful slow.

But when I remove one of the conditions so it will be:

LEFT JOIN table3 ON
   table3.some_primary_key = table1.some_primary_key

OR

LEFT JOIN table3 ON
   table3.column_with_index = table2.column_with_index

Mysql is using indexes properly. In EXPLAIN result indexes are shown in 'keys' column, type is 'ref'. Queries are blazing fast.

What to do to make mysql use my indexes while using OR in join statement?

I tried LEFT JOIN table3 FORCE INDEX(PRIMARY, ind_column) but no success.

I think many databases have trouble using indexes with `OR`. — Barmar, Jan 15 '14 at 10:25
How much freedom do you have on the projection part of your query? — VH-NZZ, Jan 15 '14 at 10:33
I want to load all data in one big query, sorry I can't be more specific. So far what comes to my mind is two table3 joins (table3a,table3b) and `CASE` in `SELECT` part (if table3a primary not null, use table3a fields. if table3b primary not null use table3b fields). Maybe I can go other workarounds but I asked this question because I am curious is there any real good solution for this problem. — Peter, Jan 15 '14 at 10:36
Ok. Also: have you tried using a composite index on `some_primary_key` AND `column_width_index` ? — VH-NZZ, Jan 15 '14 at 10:42
possible duplicate of [MySQL: how to index an "OR" clause](http://stackoverflow.com/questions/2829544/mysql-how-to-index-an-or-clause) — Barmar, Jan 15 '14 at 10:46

score 2 · Answer 1 · answered Jan 15 '14 at 10:28

2

Split your query into two queries, each using a different condition from the OR, and then combine them using UNION.

SELECT *
FROM table1
LEFT JOIN table2 ON
   table2.some_primary_key = table1.some_primary_key
LEFT JOIN table3 ON
   table3.some_primary_key = table1.some_primary_key

UNION

SELECT *
FROM table1
LEFT JOIN table2 ON
   table2.some_primary_key = table1.some_primary_key
LEFT JOIN table3 ON
   table3.column_with_index = table2.column_with_index

answered Jan 15 '14 at 10:28

Barmar

741,623
53
500
612

Agreed but the downside is that the `table2` join operation occurs twice, and `UNION` is expensive. – VH-NZZ Jan 15 '14 at 10:33
Unfortunately, I don't think MySQL is able to optimize `OR` well. I think it can only use one index per table for any particular join, and this would require using a different index for each condition in the `OR`. – Barmar Jan 15 '14 at 10:45

score 2 · Accepted Answer · edited May 23 '17 at 12:24

2

I can suggest you the use of the CASE statement,

Here is a similar question you can take and extend to your needs:

Hope this helps

edited May 23 '17 at 12:24

Community

1
1

answered Jan 15 '14 at 10:46

linuxatico

1,878
30
43

1

Yes I will go with that. In my case 2xJOIN with CASE statement will be less expensive than UNION – Peter Jan 15 '14 at 10:56

Using index in LEFT JOIN with OR condition

2 Answers2

Linked