我的简单MySql查询不使用索引

I have a very simple query:

我有一个非常简单的查询:

  SELECT   comments.*
  FROM comments 
  WHERE comments.imageid=46

And this is my table:

这是我的表:

CREATE TABLE IF NOT EXISTS `comments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `imageid` int(10) unsigned NOT NULL DEFAULT '0',
  `uid` bigint(20) unsigned NOT NULL DEFAULT '0',
  `content` text CHARACTER SET utf8,
  `adate` datetime DEFAULT NULL,
  `ip` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ids` (`imageid`) USING BTREE,
  KEY `dt` (`adate`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

But MySql can't use index on this simple query. here is the explain result:

但是MySql不能在这个简单的查询上使用索引。这是解释结果:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   SIMPLE  comments    ALL     ids     NULL    NULL    NULL    4   75.00   Using where

while I change the query to this, Mysql can use index. Why? :

当我将查询更改为此时,Mysql可以使用索引。为什么? :

  SELECT   comments.id
  FROM comments 
  WHERE comments.imageid=46

here is the explain:

这是解释:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   SIMPLE  comments    ref     ids     ids     4   const   4   100.00  Using index

3 个解决方案

#1

I guess that you have few rows in 'comments' table, this is why MySQL is doing a full table scan instead of using the index in your first query. It's estimating that the cost of a full table scan may be lower than first match the index and then lookup the rows.

我猜你在'comments'表中有几行,这就是为什么MySQL在你的第一个查询中进行全表扫描而不是使用索引。它估计全表扫描的成本可能低于首先匹配索引然后查找行。

In your second query is using the index because it is possible to get all the columns of the query (the 'id' column) directly from the index with no need to lookup the table rows after matching the index. This is the meaning of "Using index" extra information.

在您的第二个查询中使用索引,因为可以直接从索引获取查询的所有列('id'列),而不需要在匹配索引后查找表行。这就是“使用索引”额外信息的含义。

Try if with a significant number of rows in 'comments' MySQL still uses a full scan, I think that it would be a strange behaviour. In fact, I've tested exactly the same in a MySQL version 5.1 and it's always using the 'index' even with few rows.

尝试在“评论”中使用大量行仍然使用完整扫描,我认为这将是一种奇怪的行为。事实上,我在MySQL版本5.1中测试完全一样,并且即使只有很少的行,它总是使用'索引'。

#2

Did you try the standard sort of things?

你尝试过标准的东西吗?

#3

The second query is an index-covered query. The whole information requested can be read from the index (since the primary key is part of any secondary index in InnoDB).

第二个查询是索引覆盖的查询。可以从索引中读取所请求的全部信息(因为主键是InnoDB中任何二级索引的一部分)。

In the first query MySQL has to read the PKs from the index, then to read the rows. Because table has so small amount of rows, optimizer decides that it would be faster if it reads rows directly and discard the ones that do not match

在第一个查询中,MySQL必须从索引中读取PK,然后读取行。因为表具有如此少量的行,优化器决定如果直接读取行并丢弃不匹配的行会更快

#1