MySQL EXPLAIN: "Using index" vs. "Using index condition"

Question

The MySQL 5.4 documentation, on Optimizing Queries with EXPLAIN, says this about these Extra remarks:

• 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.

[...]

• Using index condition

Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer (“push down”) reading full table rows unless it is necessary.

Am I missing something, or do these two mean the same thing (i.e. "didn't read the row, index was enough")?

Answer 1

The difference is that "Using index" doesn't need a lookup from the index to the table, while "Using index condition" sometimes has to. I'll try to illustrate this with an example. Say you have this table:

id, name, location

With an index on

name, id

Then this query doesn't need the table for anything, it can retrieve all it's information "Using index":

select id, name from table where name = 'Piskvor'

But this query needs a table lookup for all rows where name equals 'Piskvor', because it can't retrieve location from the index:

select id from table where name = 'Piskvor' and location = 'North Pole'

The query can still use the index to limit the results to the small sets of row with a particular name, but it has to look at those rows in the table to check if the location matches too.

Answer 2

An example explains it best:

SELECT Year, Make --- possilby more fields and/or from extra tables
FROM myUsedCarInventory
WHERE Make = 'Toyota' AND Year > '2006'

Assuming the Available indexes are:
  CarId
  VIN
  Make
  Make and Year

This query would EXPLAIN with 'Using Index' because it doesn't need, at all, to "hit" the myUserCarInventory table itself since the "Make and Year" index "cover" its need with regards to the elements of the WHERE clause that pertain to that table.

Now, imagine, we keep the query the same, but for the addition of a condition on the color

...
WHERE Make = 'Toyota' AND Year > '2006' AND Color = 'Red'

This query would likely EXPLAIN with 'Using Index Condition' (the 'likely', here is for the case that Toyota + year would not be estimated to be selective enough, and the optimizer may decide to just scan the table). This would mean that MySQL would FIRST use the index to resolve the Make + Year, and it would have to lookup the correspnding row in the table as well, only for the rows that satisfy the Make + Year conditions. That's what is sometimes referred as "push down optimization".