My simple MySql query does not use index

Question

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 cannot use the index for this simple query. here is the result of the explanation:

 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 am changing the query to this, Mysql can use the index. What for?

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

here is the explanation:

 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 

Answer 1

I assume there are several lines in the comment table, so MySQL does a full table scan instead of using the index in your first query. He estimates that the cost of a full table scan might be lower than the first index match, and then look for rows.

Your second query uses an index because you can get all the columns of the query (the "id" column) directly from the index without having to look for table rows after matching the index. This is the value of the index usage additional information.

Try if, with a significant number of lines in the comments, MySQL still uses full crawl, I think this will be strange behavior. In fact, I tested exactly the same in MySQL 5.1 and always used an “index” even with multiple rows.

Answer 2

Have you tried the standard something like?

Answer 3

The second query is a query that spans the index. All requested information can be read from the index (since the primary key is part of any secondary index in InnoDB).

In the first query, MySQL should read PK from the index, and then read the rows. Since the table has such a small number of rows, the optimizer decides that it will be faster if it reads the rows directly and discards those that do not match