问题描述
考虑以下查询:
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
当我检查EXPLAIN
时,它表明索引未用于table3
联接(但是索引显示在"possible_keys"中).输入:"ALL".按照手册:
查询非常慢.
但是当我删除其中一个条件时,它将是:
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正确使用了索引.在EXPLAIN
结果索引显示在键"列中,类型为"ref".查询快速增长.
如何使mysql在join语句中使用OR
时使用我的索引?
我尝试了LEFT JOIN table3 FORCE INDEX(PRIMARY, ind_column)
,但没有成功.
此处是您可以采取的类似问题,扩展您的需求:
希望这会有所帮助
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:
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 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
这篇关于在带有OR条件的LEFT JOIN中使用索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!