CODEWITHSUNDEEP
mysqlindexing
Will
Will

Reputation: 5580

MySQL - Why doesn't this query use an index (according to explain)

I was playing with EXPLAIN and ran it on this simple query:

EXPLAIN SELECT * FROM actions WHERE user_id = 17;

And was quite suprised to see this output:

select_type    SIMPLE
table          actions
type           ALL
possible_keys  user_id
key            null
key_len        null
ref            null
rows           6
extra          Using where

My understanding is this means that no index is being used in the look up, is that correct? (There are only 6 rows total in the table at this time, but there will be many more)

The table definition is (inpart):

CREATE TABLE `actions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  ...
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1

Why wouldn't this be using the key value on user_id?

Upvotes: 2

Views: 660

Answers (1)

stivlo
stivlo

Reputation: 85476

Sometimes MySQL does not use an index, even if one is available. This is when it would require fewer seeks than reading the table directly. It seems that with 6 rows you're in this situation.

Remember to periodically run OPTIMIZE TABLE and ANALYZE TABLE when you'll have a more realistic data set.

If you think that you can do a better job than the optimizer, you can use the Index Hint Syntax.

Upvotes: 5

Related Questions