CODEWITHSUNDEEP
mysqlquery-optimization
Ben
Ben

Reputation: 62356

Shouldn't this be using an index instead of where?

EXPLAIN EXTENDED SELECT  `member`.`id` ,  `member`.`name` 
FROM  `member` 
WHERE  `member`.`last_active` >  '1289348406'

Shows the following output despite last_active having an index on it.... shouldn't it say index instead of where ?

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  member  range   last_active last_active 4   NULL    2   100.00  Using where

Upvotes: 1

Views: 57

Answers (1)

Quassnoi
Quassnoi

Reputation: 425251

Using index means the query does not touch the table at all:

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.

Since not all fields are covered by your index, it's impossible.

The index itself is of course being used (since the access type is range), but it still needs to do the row lookup in the table to retrieve the values of name and id.

Create a covering index on (last_active, name, id) if you want to see Using index.

Upvotes: 1

Related Questions