MySQL explain output

Question

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!

`using where` says there is a filter (= `WHERE` clause) in your SQL. `using index` says the query uses an index. Both are different things. I don't understand the question. — Kaii, Jan 24 '12 at 12:02
the question is why mysql uses index only to execute query number one, while for execution of query number two "using where" is reported. I'm asking that because I can't understand the difference between the two queries - how the fact that one uses an integer in where condition, and the other one a varchar field affects the way mysql executes them (why the second query does not use "index only"?) — Zaar Hai, Jan 24 '12 at 12:57
what is the `kz` index that is mentioned and actually used in the first EXPLAIN? — newtover, Jan 24 '12 at 14:23
now after the edit your question makes more sense. but the `SHOW CREATE TABLE` does not match your `EXPLAIN SELECT` statements. You have keys "k3" and "kz" in your EXPLAINs, but not in your CREATE TABLE. i guess your problem is something we can not see because you didn't post the relevant part. — Kaii, Jan 24 '12 at 15:33
also "rows: 3" of your first explain does not match your example data when using "WHERE a = 1" .. you only have 2 rows with that. (similar for second explain which says 2 rows but there is only 1 in the example) — Kaii, Jan 24 '12 at 15:35
what happens if you do `COUNT(a)` or `COUNT(b)` instead of `COUNT(1)`? — Kaii, Jan 24 '12 at 15:36

score 3 · Accepted Answer · answered Jan 24 '12 at 17:17

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.

Thanks for introducing the world of `Handler_read_*` status variables. — Zaar Hai, Jan 08 '13 at 14:10

score 0 · Answer 2 · answered Jan 24 '12 at 12:01

0

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.

answered Jan 24 '12 at 12:01

Mahesh Patil

1,529
1
11
22

I have no JOIN, only a simple where condition (I added the schema and the sql command to the original question above). – Zaar Hai Jan 24 '12 at 12:37
Then only first part is applicable to your EXPLAIN output i.e. A WHERE expression (in additional to the possible key lookup) is used to check if the row should be accepted. – Mahesh Patil Jan 24 '12 at 13:34

score 0 · Answer 3 · answered Jan 24 '12 at 12:02

"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')

MySQL explain output

3 Answers3