Why is this query using where instead of index?
EXPLAIN EXTENDED SELECT `board` . *
FROM `board`
WHERE `board`.`category_id` = '5'
AND `board`.`board_id` = '0'
AND `board`.`display` = '1'
ORDER BY `board`.`order` ASC
The output of the above query is
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE board ref category_id_2 category_id_2 9 const,const,const 4 100.00 Using where
I'm a little confused by this because I have an index that contains the columns that I'm using in the same order they're used in th开发者_开发知识库e query...:
category_id_2 BTREE No No
category_id 33 A
board_id 33 A
display 33 A
order 66 A
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.
It is actually using index category_id_2
.
It's using the index category_id_2
properly, as shown by the key
field of the EXPLAIN
.
Using where
just means that you're selecting only some rows by using the WHERE
statement, so you won't get the entire table back ;)
精彩评论