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 aWHERE
clause, andusing 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
orON
), and not all record will be returned. Note thatLIMIT
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 thancategory_id
,board_id
,display
andorder
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)