Mysql没有使用带有两列的索引 [英] Mysql not using an index on where in with two columns

问题描述

我有以下查询：

select * from my_table 
where (col1, col2) in ( (1000,1), (2000,2) )

我在col1，col2和col1上定义了索引和col2在一起。如果我对它进行解释，我发现mysql没有找到可用的索引。有趣的是，如果我只在IN子句中放入一对，则解释会找到所有索引。

I have indexes defined on col1, col2 and col1 and col2 together. If I run an explain on it, I see that mysql does not find an available index to use. Interestingly enough if I put only one pair in the IN clause, the explain finds all the indexes.

尝试使用力量指数，但没有帮助

Tried force index, does not help

任何想法，我如何让mysql识别索引？

Any ideas, how can I make mysql recognize the index?

推荐答案

我成功解决了这个问题通过在其中一列上添加额外的条件来升级mysql版本：

I managed to solve this without upgrading the mysql version, by adding an extra where in condition on one of the columns only:

select * from my_table 
where (col1, col2) in ( (1000,1), (2000,2) )
and col1 in (1000,2000)

这样，优化器将在col1上找到索引，并将通过它进行搜索，这为我完成了这项工作。

This way, the optimizer will find the index on col1, and will search by it, which did the job for me.

这篇关于Mysql没有使用带有两列的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！