MySQL explain output

Does anybody know the difference between

Using Index

and

Using where; Using index

in mysql's explain output (in Extra)?

Reproduction:

CREATE TABLE `tmp_t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL DEFAULT '0',
  `b` varchar(50) CHARACTER SET latin1 NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k1` (`a`),
  KEY `k2` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=5;

insert into tmp_t1 (a,b) values (1,'b1'), (1, 'b2'), (2, 'b3');

mysql> explain select count(1) from tmp_t1 where a=1 \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tmp_t1
         type: ref
possible_keys: k2,kz
          key: kz
      key_len: 4
          ref: const
         rows: 3
        Extra: Using index
1 row in set (0.11 sec)

mysql> explain select count(1) from tmp_t1 where b='b1' \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tmp_t1
         type: ref
possible_keys: k3
          key: k3
      key_len: 52
          ref: const
         rows: 2
        Extra: Using where; Using index
1 row in set (0.00 sec)

Does anyone know why in the first case there is only "Using index" in the extra field, while in the second one it's "Using where;Using index"? The difference between the cases is that the first case runs WHERE on an integer, and the second is executed on a varchar(50) field. But why does it matter??

Thanks for your help!

Solution

I should confirm that the difference is very confusing indeed. In other cases Using where; Using index might mean that the index is used but it is full scanned. As when you have a composite index on (a, c) but make a query like SELECT a FROM thetable WHERE c = 1. In this case MySQL will use the index (since it contains all necessary data and is in memory), but will do a full scan through the index.

As result you should see that the variable in

SHOW STATUS LIKE 'Handler_read_next';

will increase to a number of rows in the table.

But this is not the case for the query:

select count(*) from tmp_t1 where b='b1';

It examines the exact number of rows in the index. I think that this is some kind of bug or a feature and one more evidence that the result EXPLAIN as itself is not anything to rely much on. The interesting thing is that for queries on larger tables and with a composite index where the VARCHAR is the second in the index EXPLAIN sometimes does not show the Using where. I am confused.