Covering Indexes & Index Condition Pushdown (ICP)0%

Full-Text Search Indexes (MATCH ... AGAINST)

Covering Indexes & Index Condition Pushdown (ICP)

Advanced13 min read•Updated: 2026-09-12

Covering Indexes & Index Condition Pushdown (ICP)

Two of the most impactful performance optimizations in modern MySQL database engineering are Covering Indexes and Index Condition Pushdown (ICP). Both drastically slash disk I/O and CPU overhead by resolving queries directly inside the storage engine.

1. Covering Indexes: Zero Double Lookups

A Covering Index is not a special type of index; it is an indexing design pattern where an index contains all the columns requested by a query (SELECT, WHERE, ORDER BY, GROUP BY).

When an index is covering:

1
InnoDB satisfies the entire query solely by reading the secondary index B+Tree.
2
It never touches the clustered index or physical table rows!
3
In EXPLAIN output, the Extra column displays: Using index.

1-- Problem Query:
2SELECT customer_id, order_date, total_amount 
3FROM orders 
4WHERE status = 'SHIPPED';
5 
6-- Standard Index on (status):
7-- Requires fetching PK, then doing a double lookup to read order_date and total_amount.
8 
9-- High-Performance Covering Index:
10CREATE INDEX idx_orders_covering 
11ON orders (status, customer_id, order_date, total_amount);

Now, MySQL reads everything directly from idx_orders_covering, completely bypassing table page lookups!

2. Index Condition Pushdown (ICP)

Prior to MySQL 5.6, when a query had multiple conditions on a composite index where some conditions could not be resolved by tree traversal (e.g., due to a range or wildcard), the storage engine fetched the full row and sent it up to the MySQL Server layer for filtering.

Index Condition Pushdown (ICP) pushes condition evaluation down into the storage engine:

The storage engine evaluates index columns against query predicates before reading the full table row.
If the row does not qualify, InnoDB discards it immediately without reading the clustered index!
In EXPLAIN output, the Extra column displays: Using index condition.

1-- Table has index on (zipcode, last_name, first_name)
2SELECT * FROM people 
3WHERE zipcode = '90210' 
4  AND last_name LIKE '%son' 
5  AND address LIKE '%Main%';

With ICP:

1
InnoDB uses zipcode = '90210' to navigate the index tree.
2
InnoDB inspects last_name LIKE '%son' directly inside the index leaf before fetching the table row!
3
Only rows matching the pattern cause a clustered index lookup for address.

Inspecting and Enabling ICP

ICP is enabled by default in MySQL:

1-- Check optimizer switch settings for ICP
2SELECT @@optimizer_switch LIKE '%index_condition_pushdown=on%';
3 
4-- Toggle ICP
5SET optimizer_switch = 'index_condition_pushdown=on';

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What defines a "Covering Index"?

Question 2

What indicator appears in EXPLAIN's Extra column when a covering index is used?

Question 3

What does Index Condition Pushdown (ICP) accomplish?

Question 4

What appears in EXPLAIN's Extra column when ICP is active for a query?

Question 5

Why do covering indexes dramatically improve high-concurrency read throughput?

Next Lesson

Full-Text Search Indexes (MATCH ... AGAINST)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Composite Multi-Column Indexes & Leftmost Prefix Rule	Full-Text Search Indexes (MATCH ... AGAINST)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Full-Text Search Indexes (MATCH ... AGAINST)

Covering Indexes & Index Condition Pushdown (ICP)

Advanced13 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Covering Indexes & Index Condition Pushdown (ICP)

1. Covering Indexes: Zero Double Lookups

A Covering Index is not a special type of index; it is an indexing design pattern where an index contains all the columns requested by a query (SELECT, WHERE, ORDER BY, GROUP BY).

When an index is covering:

1
InnoDB satisfies the entire query solely by reading the secondary index B+Tree.
2
It never touches the clustered index or physical table rows!
3
In EXPLAIN output, the Extra column displays: Using index.

1-- Problem Query:
2SELECT customer_id, order_date, total_amount 
3FROM orders 
4WHERE status = 'SHIPPED';
5 
6-- Standard Index on (status):
7-- Requires fetching PK, then doing a double lookup to read order_date and total_amount.
8 
9-- High-Performance Covering Index:
10CREATE INDEX idx_orders_covering 
11ON orders (status, customer_id, order_date, total_amount);

Now, MySQL reads everything directly from idx_orders_covering, completely bypassing table page lookups!

2. Index Condition Pushdown (ICP)

Index Condition Pushdown (ICP) pushes condition evaluation down into the storage engine:

The storage engine evaluates index columns against query predicates before reading the full table row.
If the row does not qualify, InnoDB discards it immediately without reading the clustered index!
In EXPLAIN output, the Extra column displays: Using index condition.

1-- Table has index on (zipcode, last_name, first_name)
2SELECT * FROM people 
3WHERE zipcode = '90210' 
4  AND last_name LIKE '%son' 
5  AND address LIKE '%Main%';

With ICP:

1
InnoDB uses zipcode = '90210' to navigate the index tree.
2
InnoDB inspects last_name LIKE '%son' directly inside the index leaf before fetching the table row!
3
Only rows matching the pattern cause a clustered index lookup for address.

Inspecting and Enabling ICP

ICP is enabled by default in MySQL:

1-- Check optimizer switch settings for ICP
2SELECT @@optimizer_switch LIKE '%index_condition_pushdown=on%';
3 
4-- Toggle ICP
5SET optimizer_switch = 'index_condition_pushdown=on';

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What defines a "Covering Index"?

Question 2

What indicator appears in EXPLAIN's Extra column when a covering index is used?

Question 3

What does Index Condition Pushdown (ICP) accomplish?

Question 4

What appears in EXPLAIN's Extra column when ICP is active for a query?

Question 5

Why do covering indexes dramatically improve high-concurrency read throughput?

Next Lesson

Full-Text Search Indexes (MATCH ... AGAINST)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Composite Multi-Column Indexes & Leftmost Prefix Rule	Full-Text Search Indexes (MATCH ... AGAINST)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next