Piskvor Piskvor - 2 years ago 288
SQL Question

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

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

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

mjv mjv
Answer Source

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:
  Make and Year

This query would EXPLAIN with 'Using Index' because it doesn't need, at all, to "hit" the myUsedCarInventory 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 corresponding 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".

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download