My simple MySql query doesn't 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 can't use index on this simple query. here is the explain result:

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? :

  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

It's not "But MySql can't use index". It's: "But MySql won't use index". The possible_keys = ids and key = NULL suggest that using the index was examined by the optimizer, and rejected. Full table scan was thought to be faster - and it probably is in your case. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Apr 15, 2012 at 9:54

jordeu · Accepted Answer · 2012-04-15 09:42:45Z

8

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.

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.

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.

answered Apr 15, 2012 at 9:42

jordeu

6,8171 gold badge21 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nes1983 · Accepted Answer · 2012-04-15 08:54:17Z

1

Did you try the standard sort of things?

answered Apr 15, 2012 at 8:54

nes1983

15.8k4 gold badges46 silver badges64 bronze badges

Comments

Maxim Krizhanovsky · Accepted Answer · 2012-04-15 11:06:47Z

1

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).

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

answered Apr 15, 2012 at 11:06

Maxim Krizhanovsky

26.8k5 gold badges62 silver badges91 bronze badges

Collectives™ on Stack Overflow

My simple MySql query doesn't use index

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related