Index Condition Pushdown optimization is used for the
range
,
ref
,
eq_ref
, and
ref_or_null
access methods
when there is a need to access full table rows. The optimization
is that the server tries to use index information to defer
(“push down”) reading of full table rows unless it
is known to be necessary. This is done by performing early tests
on index tuples first. This strategy can be used for
MyISAM
tables.
To see how this optimization works, consider first how an index scan proceeds when Index Condition Pushdown is not used:
Get the next row, first by reading the index tuple, and then by using the index tuple to locate and read the full table row.
Test the part of the WHERE
condition that
applies to this table. Accept or reject the row based on the
test result.
When Index Condition Pushdown is used, the scan proceeds like this instead:
Get the next rows's index tuple (but not the full table row).
Test the part of the WHERE
condition that
applies to this table and can be checked using only index
columns. If the condition is not satisfied, proceed to the
next row.
Use the index tuple to locate and read the full table row.
Test the part of the WHERE
condition that
applies to this table. Accept or reject the row based on the
test result.
When Index Condition Pushdown is used, the
Extra
column in
EXPLAIN
output shows
Using index condition
. It will not show
Index only
because that does not apply when
full table rows must be read.
Suppose that we have a table containing information about people
and their addresses and that the table has an index defined as
INDEX (zipcode, lastname, firstname)
. If we
know a person's zipcode
value but are not
sure about the last name, we can search like this:
SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%'
MySQL can use the index to scan through people with
zipcode='95054'
. The second part
(lastname LIKE '%etrunia%'
) cannot be used to
limit the number of rows that must be scanned, so without Index
Condition Pushdown, this query must retrieve full table rows for
all the people who have zipcode='95054'
.
With Index Condition Pushdown, MySQL will check the
lastname LIKE '%etrunia%'
part before reading
the full table row. This avoids reading full rows corresponding
to all index tuples that do not match the
lastname
condition. The full-row test is
“pushed down” to take place later than the
index-only test.
Index Condition Pushdown is enabled by default; it can be
controlled with the
engine_condition_pushdown
system variable. This variable also controls table Condition
Pushdown as used for NDB; see
Section 7.2.7, “Condition Pushdown Optimization”.
User Comments
Add your own comment.