CODEWITHSUNDEEP
mysqlexplain
Zaar Hai
Zaar Hai

Reputation: 9859

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!

Upvotes: 4

Views: 1426

Answers (3)

newtover
newtover

Reputation: 32094

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.

Upvotes: 3

Lepidosteus
Lepidosteus

Reputation: 12027

"Using index" means that only the index is used for doing the actual query (finding which rows to return), that means mysql does not need to read the actual rows.

The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

"Using where" means that the index is also used when doing data lookup (for the rows it needs to return, find the corresponding data for the column you selected)

If the Extra column also says Using where, it means the index is being used to perform lookups of key values. Without Using where, the optimizer may be reading the index to avoid reading data rows but not using it for lookups. For example, if the index is a covering index for the query, the optimizer may scan it without using it for lookups.

Source in mysql's documentation (search for 'using index')

Upvotes: 0

Mahesh Patil
Mahesh Patil

Reputation: 1551

Using Where

A WHERE expression (in additional to the possible key lookup) is used to check if the row should be accepted. If you don't have 'Using where' together with a join type of ALL, you are probably doing something wrong!

Using Index

Only the index is used to retrieve the needed information from the table. There is no need to perform an extra seek to retrieve the actual record.

Upvotes: 0

Related Questions