MySQL index push-Down Technology

MySQL index push-Down Technology

The whole idea of index push-down is as follows:

To see how this optimization works, consider first how an index scan proceeds when Index Condition Pushdown is not used:

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

2. Test the part ofWHERECondition 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:

1. Get the next row's index tuple (but not the full table row ).

2. Test the part ofWHERECondition that applies to this table and can be checked using only index columns. If the condition is not satisfied, proceed to the index tuple for the next row.

3. If the condition is satisfied, use the index tuple to locate and read the full table row.

4. Test the remaining part ofWHERECondition that applies to this table. Accept or reject the row based on the test result.

Example:

Suppose that we have a table containing information about people and their addresses and that the table has an index definedINDEX (zipcode, lastname, firstname). If we know a person'szipcodeValue 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%';

The people table (zipcode, lastname, firstname) constitutes an index.

If the index push-down technology is not used, MySQL will query the corresponding ancestor from the storage engine through zipcode = '000000' and return it to the MySQL server, the MySQL server then uses lastname LIKE '% etrunia %' and address LIKE '% Main Street %' to determine whether the ancestor meets the conditions.

If index push-down technology is used, MYSQL first returns an index that complies with zipcode = '20160301, then, the indexes are determined based on lastname LIKE '% etrunia %' and address LIKE '% Main Street %. If the condition is met, the corresponding ancestor is located based on the index. If the condition is not met, the corresponding ancestor is directly reject.

This article permanently updates the link address: