Using index in LEFT JOIN with OR condition
join, mysql
Solution
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
Problem
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.