考虑以下查询:
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时,它显示我没有使用index3连接的索引(但是索引显示在“possible_keys”中).输入:’ALL’.按照manual:
Join type “ALL”: A full table scan is done for each combination of rows from the
previous tables.
查询非常慢.
但是,当我删除其中一个条件时,它将是:
LEFT JOIN table3 ON
table3.some_primary_key = table1.some_primary_key
要么
LEFT JOIN table3 ON
table3.column_with_index = table2.column_with_index
Mysql正在使用索引.在EXPLAIN中,结果索引显示在“键”列中,类型为“ref”.查询速度非常快.
如何在连接语句中使用OR时使mysql使用我的索引?
我试过LEFT JOIN table3 FORCE INDEX(PRIMARY,ind_column),但没有成功.