Whats the difference between “Using index” and “Using where; Using index” in the EXPLAIN

https://stackoverflow.com/questions/25672552/whats-the-difference-between-using-index-and-using-where-using-index-in-the

14 down vote favorite

4

In the extra field of the explain in mysql you can get:

• Using index
• Using where; Using index

What's the difference between the two?

To explain my question better I'm going to use the following table:

CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `another_field` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO test() VALUES(),(),(),(),();

Which ends up with the content like:

SELECT * FROM `test`;

id  another_field
1   0
2   0
3   0
4   0
5   0

On my research I found

Why is this query using where instead of index?

The output of EXPLAIN can sometimes be misleading.

For instance, filesort has nothing to do with files, using where does not mean you are using a WHERE clause, and using index can show up on the tables without a single index defined.

Using where just means there is some restricting clause on the table (WHERE or ON), and not all record will be returned. Note that LIMIT does not count as a restricting clause (though it can be).

Using index means that all information is returned from the index, without seeking the records in the table. This is only possible if all fields required by the query are covered by the index.

Since you are selecting *, this is impossible. Fields other than category_id, board_id, display and order are not covered by the index and should be looked up.

and I also found

https://dev.mysql.com/doc/refman/5.1/en/explain-output.html#explain-extra-information

Using index

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.

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.

For InnoDB tables that have a user-defined clustered index, that index can be used even when Using index is absent from the Extra column. This is the case if type is index and key is PRIMARY.

(Look at the second paragraph)

My problem with this:

First: I didn't understand the second paragraph the way it's written.

Second:

The following query returns

EXPLAIN SELECT id FROM test WHERE id = 5;

id  select_type table   type    possible_keys   key     key_len ref     rows    Extra
1   SIMPLE      test    const   PRIMARY         PRIMARY 8       const   1       Using index

(Scroll to the right)

And this other query returns:

EXPLAIN SELECT id FROM test WHERE id > 5;

id  select_type table   type    possible_keys   key     key_len ref     rows    Extra
1   SIMPLE      test    range   PRIMARY         PRIMARY 8       NULL    1       Using where; Using index

(Scroll to the right)

Other than the fact that one query uses a range search and another uses the constant search, both queries are using some restricting clause on the table (WHERE or ON), and not all record will be returned.

What does the Using where; mean on the second query mean? and what does the fact that it's not on the first query mean?

---

EXTRA

What is the difference with Using index condition; Using where? (I'm not adding an example of this because I have not been able to reproduce it in a small self contained piece os code)

mysql

share improve this question

edited May 23 '17 at 12:31

Community ♦

1 1

asked  Sep 4 '14 at 18:24

J-Rou

976 5 18 36

• stackoverflow.com/questions/1687548/… Responds the extra. (I didn't find it before because I was looking at an old version of the mysql documentation <slapped my face>. –  J-Rou  Sep 4 '14 at 18:30

add a comment

1 Answer

active oldest votes

up vote 8 down vote accepted

When you see Using Index in the Extra part of an explain it means that the (covering) index is adequate for the query.
In your example: SELECT id FROM test WHERE id = 5; the server doesn't need to access the actual table as it can satisfy the query (you only access id) only using the index (as the explain says). In case you are not aware the PK is implemented via a unique index.

When you see Using Index; Using where it means that first the index is used to retrieve the records (an actual access to the table is not needed) and then on top of this result set the filtering of the where clause is done.
In this example: SELECT id FROM test WHERE id > 5; you still fetch for id from the index and then apply the greater than condition to filter out the records non matching the condition

share improve this answer

answered  Sep 4 '14 at 18:59

Cratylus

31.5k 49 161 284

• Just to check: SELECT id FROM test WHERE id > 5; doesn't read the row from the disk, does it? Also, in both cases, mysql covers the same part of the index and get's to the same value in the btree. Is there any important computational difference? –  J-Rou  Sep 4 '14 at 19:27
• Another detail, just to complement your response. The uniqe status of the key doesnt affect. alter table test add key another_fileld(another_field); explain select another_field from test where another_field = 0 returns only Using index. –  J-Rou  Sep 4 '14 at 19:37
• @J-Rou:No it does not access the HD.Computational difference between what? –  Cratylus  Sep 5 '14 at 20:25
• The uniqe status of the key doesnt affect. No it doesn't.I only mentioned to you how the PK is implemented –  Cratylus  Sep 5 '14 at 20:25