List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:May 29 2014 8:26pm
Subject:Re: About EXPLAIN: Extra column information
View as plain text  
Hi,

"Can anybody explain me the difference between "using index", "using index
condition" and "using where" in the EXTRA column information of EXPLAIN
query?"

using index:
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.

using where:
the where condition is not checked at storage engine level but all records
are returned and filtered by mysql

Hope this helps.

Best Regards

Claudio

Thread
About EXPLAIN: Extra column informationAlfonso Maestro Lira29 May 2014
  • Re: About EXPLAIN: Extra column informationClaudio Nanni29 May 2014