Alex Rivera | Logout

My simple MySql query doesn't use index

Asked 2012-04-15T08:51:23.523
8

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
Edit
Report

1 Answer

1
answered 2012-04-15T08:54:17.333

Your Answer