在带有OR条件的LEFT JOIN中使用索引 [英] Using index in LEFT JOIN with OR condition

问题描述

考虑以下查询:

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".按照手册:

联接类型"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:

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 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中使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！