Lesson 24 of the SQL Optimization Course: Index Condition Pushdown

Introduction

For relational databases, the design of tables and SQL is written are particularly crucial. It wouldn’t be an exaggeration to say that they account for 90% of performance. So this time, specifically targeting these two major knowledge areas, we’ll conduct a detailed analysis for you, peeling back the layers.

This Series uses plain and understandable language and selects a large number of examples to elaborate on the subtleties for you.

🧑‍💻 Target audience:

DBA
Database developers
Students

We will use MySQL as the demonstration database.

Understanding Index Condition Pushdown (ICP)

What is ICP?
Index Condition Pushdown (ICP) is an optimization technique introduced in MySQL 5.6 to reduce unnecessary I/O operations. It allows the storage engine to apply filtering conditions during index traversal, rather than fetching entire rows and filtering at the MySQL Server layer.

Key benefits of ICP:

Reduced I/O: Minimizes data transfer between storage and server layers.
Faster execution: Filters out non-matching rows early in the index scan.

How ICP Works

Without ICP

The storage engine reads every row matching the index key.
The server layer filters rows after fetching them from the table.
Inefficient for complex conditions (e.g., LIKE '%dog%').

With ICP

The storage engine applies partial filtering during index traversal.
Only relevant rows are fetched from the table.
Optimized for secondary indexes and range scans.

Enabling/Disabling ICP

ICP is enabled by default. To toggle it:

1
2
3
4
5
6
7
8
-- Disable ICP (for testing)  
SET optimizer_switch = 'index_condition_pushdown=off';  

-- Enable ICP  
SET optimizer_switch = 'index_condition_pushdown=on';  

-- Use hints in SQL  
SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;  

Verify ICP usage with EXPLAIN:

1
EXPLAIN SELECT * FROM t1 WHERE r1 = 1 AND r2 LIKE '%dog%' AND r4 = 5\G  

Extra: Using index condition indicates ICP is active.

Performance Benchmark

Test Setup

Table t1 with 1M rows.
Query:

1
2
3
4
SELECT * FROM t1 
WHERE r1 = 1 
  AND r2 LIKE '%dog%' 
  AND r4 = 5;

Results

Limitations of ICP

Applicable Scenarios:
- Works with RANGE, REF, EQ_REF, REF_OR_NULL access methods.
- Ineffective for INDEX_SCAN or FULLTEXT indexes.
Unsupported Features:
- Virtual columns or function-based indexes.
- Subqueries in filter conditions.
Partitioned Tables:
- ICP is applied per-partition but may not optimize cross-partition scans.

Key Takeaways

ICP reduces I/O overhead by pushing filtering to the storage engine.
Use EXPLAIN and SHOW STATUS to validate performance gains.
Avoid over-reliance on ICP for complex string operations.

👋 See you in the next lesson.

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.