I have the below schema
我有以下架构
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`d` date NOT NULL,
`y` year(4) NOT NULL,
PRIMARY KEY (`id`),
KEY `y` (`y`)
) ENGINE=MyISAM CHARSET=utf8;
when i run "EXPLAIN SELECT * FROM test where y = '2010';"
当我运行“EXPLAIN SELECT * FROM test y = '2010'时;”
Although there is an index defined in the table for column "y", it is not being used by the query optimizer.
虽然表中为列“y”定义了索引，但查询优化器未使用它。
What could be the reason?
可能是什么原因？
1 个解决方案
#1
1
MySQL must find it worth using the index.
MySQL必须发现它值得使用索引。
This is usually the case when the dbms expects to find just a few records. Even something as low as 10% or even 5% of all records may be regarded as too many and the dbms decides then better to scan through the whole table instead of having to muddle its way through the index.
当dbms期望只找到几条记录时，通常就是这种情况。即使是所有记录中低至10％甚至5％的东西都可能被认为太多而且dbms决定更好地扫描整个表格而不必混淆整个索引。
So the answer is: MySQL doesn't consider it appropriate to use an index here. It thinks that a full table scan is probably faster.
所以答案是：MySQL认为在这里使用索引并不合适。它认为全表扫描可能更快。
#1
1
MySQL must find it worth using the index.
MySQL必须发现它值得使用索引。
This is usually the case when the dbms expects to find just a few records. Even something as low as 10% or even 5% of all records may be regarded as too many and the dbms decides then better to scan through the whole table instead of having to muddle its way through the index.
当dbms期望只找到几条记录时，通常就是这种情况。即使是所有记录中低至10％甚至5％的东西都可能被认为太多而且dbms决定更好地扫描整个表格而不必混淆整个索引。
So the answer is: MySQL doesn't consider it appropriate to use an index here. It thinks that a full table scan is probably faster.
所以答案是：MySQL认为在这里使用索引并不合适。它认为全表扫描可能更快。