indexed query, but still searching every row

Question

I have the following mysql query

select points_for_user from items where user_id = '38415';

explain on the query returns this

id  select_type table   type    possible_keys   key                     key_len ref     rows    Extra
1   SIMPLE      items   index   NULL            points_for_user_index   2       NULL    1000511 Using index

The problem is, shouldn't the number of rows be FAR less then the number of rows in the table because of the index?

user_id is the primary index, so I tried creating an index on just points_for_user and that still look through every row. An index on user_id AND points_for_user still searches every row.

What am I missing?

Thanks!

CREATE TABLE IF NOT EXISTS `items` (
  `capture_id` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) NOT NULL,
  `creator_user_id` bigint(20) NOT NULL DEFAULT '0',
  `user_id` int(11) NOT NULL,
  `accuracy` int(11) NOT NULL,
  `captured_at` timestamp NOT NULL DEFAULT '2011-01-01 06:00:00',
  `ip` varchar(30) NOT NULL,
  `capture_type_id` smallint(6) NOT NULL DEFAULT '0',
  `points` smallint(6) NOT NULL DEFAULT '5',
  `points_for_user` smallint(6) NOT NULL DEFAULT '3',
  PRIMARY KEY (`capture_id`),
  KEY `capture_user` (`capture_id`,`id`,`user_id`),
  KEY `user_id` (`user_id`,`id`),
  KEY `id` (`id`),
  KEY `capture_creator_index` (`capture_id`,`creator_user_id`),
  KEY `points_capture_index` (`points_for_user`,`creator_user_id`),
  KEY `points_for_user_index` (`points_for_user`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1008992 ;

---

select count(*) from items where user_id = '38415'

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  captures    ref user_munzee_id  user_munzee_id  4   const   81  Using index

Answer 1

the mysql optimizer try to use the best possible index during the query.

In your first query the optimizer is considering points_for_user_index the best choice, in fact the Extra column show the "Using index" status, this means a "Covering index".

The "Covering index" occurs when all fields required for a query (in your case select points_for_user from ... ) are contained in an index, this avoid the access to the full mysql data (.MYD) in favour of the direct index access (.MYI)

First of all you can try to rebuild the index tree analyzing table

ANALYZE TABLE itemes;

Note for very large tables:

ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for InnoDB and MyISAM. This statement works with InnoDB, NDB, and MyISAM tables. For MyISAM tables, this statement is equivalent to using myisamchk --analyze.

If "the problem" persist and you want to bypass the optimizer choice you can explicit try to force the usage of an index

EXPLAIN SELECT points_for_user FROM items USE INDEX ( user_id ) WHERE user_id = '38415'

More details: http://dev.mysql.com/doc/refman/5.5/en/index-hints.html

Cristian