"Can anybody explain me the difference between "using index", "using index
condition" and "using where" in the EXTRA column information of EXPLAIN
the columns selected are part of an index that is used to return the
results, there is no need then to read the full table record and the
columns will be returned using the already read index record.
using index condition:
The so called index condition pushdown.
It is an optimization to try to avoid reading the full table record when
the access method is using an index that is part of the where condition,
if the index does not satisfy this partial condition the other conditions
checks will be skipped, this is valid with AND conditions.
Not sure if the reverse is valid with OR operator, that is if the index
satsfies the condition the record should be read anyway so that the
condition check is skipped.
the where condition is not checked at storage engine level but all records
are returned and filtered by mysql
Hope this helps.