SoFunction
Updated on 2025-03-03

MySQL index pushdown implementation example

Index push down(Index Condition Pushdown, referred to as ICP) is an optimization technology introduced in MySQL 5.6. It reduces unnecessary row access and table back operations and improves query performance by "pushing down" some query conditions to the index scanning stage.

1. The concept of index pushdown

In the traditional index scanning process, MySQL will first find a record that meets the index conditions through the index, then return to the table (that is, accessing the actual table data rows) to read the required columns, and finally apply other filtering conditions (non-index conditions) to determine whether the record meets the query requirements. This means that even if some records are eventually filtered out, MySQL must first read their entire data back to the table, which results in additional I/O operation and performance overhead.

Index push-down optimizationThe idea is: during the index scanning stage, some query conditions are directly applied to the index record, thereby reducing table back operations. Only records that meet all conditions in the index will be returned to the table to read their complete data.

2. How index pushdown works

The working principle of index pushdown can be understood through the following steps:

  • Index Scan: MySQL scans records that meet the index criteria in the index.
  • Index condition filtering: When scanning index records, MySQL will "push down" the query conditions that can be applied to the index to the index scanning stage. If the records in the index do not meet these conditions, MySQL will skip the record directly and do not perform a table return operation.
  • Table return operation: Only those records that meet both the index and the pushdown conditions in the index will MySQL return to the table to read the complete data row.
  • Remaining condition filtering: The data rows read by the back table will be further filtered using other query conditions to ensure that the final returned result set is accurate.

3. Example of index pushdown

Suppose we have a tableemployees, the table structure is as follows:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2),
    INDEX idx_lastname_salary(last_name, salary)
);

Now, we have a query:

SELECT * FROM employees WHERE last_name LIKE 'S%' AND salary > 50000;
  • Indexidx_lastname_salaryIncludelast_nameandsalaryTwo columns.
  • In the query conditionslast_name LIKE 'S%'You can use indexes to speed up searches.
  • In the query conditionssalary > 50000Tooidx_lastname_salaryPart of the index, but in traditional cases, it is not applied during the index scanning phase, but filters after returning to the table.

Execution process without index pushdown：

  • MySQL usagelast_name LIKE 'S%'Find all records that meet the criteria in the index.
  • For each record that meets the criteria, MySQL will return to the table to read.salaryThe value of the column.
  • The data row after the return table will be checkedsalary > 50000For this condition, unsatisfied records will be filtered out.

Enable the execution process after index pushdown：

  • MySQL usagelast_name LIKE 'S%'Find records that meet the criteria in the index.
  • During the index scanning process, MySQL checks directly in the indexsalary > 50000For this condition, only records that meet the conditions will be returned to the table.
  • Because many are not in linesalary > 50000The records are filtered out during the index scanning stage, the table return operation is greatly reduced, and the query performance is improved.

4. Benefits of index pushdown

  • Reduce table return operations: By applying more conditions in the index scanning phase, index pushdown reduces unnecessary tableback operations and reduces I/O overhead.
  • Improve query performance: Due to the reduction of the number of accesses to data rows, index pushdown can significantly improve the overall performance of the query.
  • Especially suitable for combined indexing: When using composite indexes (joint indexes of multiple columns), the optimization effect of index pushdown is particularly obvious.

5. Applicable conditions for index pushdown

Applicable conditions for index push-down optimization include:

  • The conditions contained in the query can be evaluated in the index. For example, if the columns contained in the index can satisfy some of the conditions in the query, these conditions can be pushed down to the index scanning phase.
  • The query uses a composite index, and multiple columns in the index participate in the judgment of the query conditions.

6. How to check whether the index pushdown takes effect

We can useEXPLAINStatement to check whether the index pushdown takes effect in the query.

EXPLAIN SELECT * FROM employees WHERE last_name LIKE 'S%' AND salary > 50000;

existEXPLAINOutputExtraIn the column, ifUsing index condition, which means that MySQL uses push-down optimization in this query.

7. Applicable and Not Applicable Scenarios

Applicable scenarios：

  • When using a combined index and query involves multiple columns in the index, index pushdown can effectively reduce table back operations.
  • When the query conditions are relatively complex and partial judgment can be made in the index, index pushdown can improve efficiency.

Not applicable：

  • If the conditions involved in the query cannot be evaluated in the index (such as involve calculations or function operations), the index pushdown cannot be used.
  • If the columns involved in the condition in the query are not in the index, the index pushdown cannot be used.

8. Sample data and execution plan

Assume that there is the following data in the table:

INSERT INTO employees VALUES 
(1, 'Smith', 'John', 10, 60000),
(2, 'Smith', 'Alice', 10, 40000),
(3, 'Brown', 'Charlie', 20, 55000),
(4, 'Davis', 'David', 30, 45000);

Execute the query:

EXPLAIN SELECT * FROM employees WHERE last_name LIKE 'S%' AND salary > 50000;

existEXPLAINIn the output, we may see results similar to the following:

id | select_type | table     | type  | possible_keys   | key               | key_len | ref  | rows | Extra
-----------------------------------------------------------------------------------------------------------
1  | SIMPLE      | employees | range | idx_lastname_salary | idx_lastname_salary | 102    | NULL |  2   | Using index condition; Using where

existExtraDisplayed in the columnUsing index condition, means that MySQL uses index pushdown to optimize this query.

9. Summary

Index Pushdown (ICP) is an important optimization technique introduced in MySQL 5.6. It reduces tableback operations by "pushing down" some query conditions to the index scanning phase, thereby improving query performance. Index push-down is particularly suitable for scenarios where composite indexes are used. By effectively reducing unnecessary I/O operations, the execution efficiency of query can be significantly improved. In practical applications, it can be done byEXPLAINStatement to see if the index pushdown takes effect, and combine query mode and index design to make full use of this optimization technology.

This is the end of this article about the implementation example of MySQL index pushdown. For more related content on MySQL index pushdown, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!