BestHub
Sign in
Tagged articles

index condition pushdown

13 articles · Page 1 of 1
Senior Brother's Insights
Senior Brother's Insights
Sep 28, 2025 · Databases

Understanding MySQL’s Three Index Processing Stages: Key, Filter, and Table

MySQL query optimization involves three distinct index processing stages—Index Key, Index Filter, and Table Filter—each handling different parts of condition matching and data retrieval, and mastering their roles and execution flow can dramatically improve query performance, especially with index condition pushdown.

databaseindex condition pushdownmysql
0 likes · 8 min read
Understanding MySQL’s Three Index Processing Stages: Key, Filter, and Table
Senior Brother's Insights
Senior Brother's Insights
Sep 23, 2025 · Databases

How Index Condition Pushdown Supercharges MySQL Query Performance

Index Condition Pushdown (ICP) in MySQL pushes filter conditions to the storage engine, dramatically cutting data transferred to the server layer, speeding up queries especially when using composite indexes, and requiring no changes to the original SQL statements.

Database PerformanceInnoDBindex condition pushdown
0 likes · 6 min read
How Index Condition Pushdown Supercharges MySQL Query Performance
Su San Talks Tech
Su San Talks Tech
Jul 21, 2025 · Databases

Master MySQL Table Lookups and Index Condition Pushdown

The article explains MySQL’s table lookup (回表) and index condition pushdown (索引下推), illustrating their mechanisms with sample tables, SQL statements, execution plans, performance drawbacks, and practical techniques such as covering indexes to reduce I/O and improve query efficiency.

SQLTable Lookupdatabase optimization
0 likes · 9 min read
Master MySQL Table Lookups and Index Condition Pushdown
IT Services Circle
IT Services Circle
Jul 17, 2025 · Databases

Understanding MySQL Table Lookups and Index Condition Pushdown

This article explains the concepts of table lookups (回表) and index condition pushdown (索引下推) in MySQL, demonstrates how they work with example tables and queries, discusses their performance impact, and provides practical techniques such as covering indexes and selective column queries to minimize costly table lookups.

SQLTable Lookupdatabase optimization
0 likes · 8 min read
Understanding MySQL Table Lookups and Index Condition Pushdown
Tech Freedom Circle
Tech Freedom Circle
Jun 6, 2025 · Databases

How to Design the Optimal Index for a 27‑Column Table Query (Alibaba Interview)

This article analyzes a typical Alibaba interview question that asks for the best index on a table with 27 columns for the query SELECT a, b, c FROM table WHERE d = X ORDER BY e, covering index design, field ordering, comparisons with alternative indexes, index condition pushdown, and the detailed MySQL execution process.

Covering IndexExecution PlanQuery Optimization
0 likes · 28 min read
How to Design the Optimal Index for a 27‑Column Table Query (Alibaba Interview)
Cognitive Technology Team
Cognitive Technology Team
Mar 28, 2025 · Databases

Index Condition Pushdown (ICP) Optimization in MySQL

Index Condition Pushdown (ICP) is a MySQL query‑optimization technique that pushes eligible WHERE predicates to the storage engine so that index entries can filter rows early, reducing row fetches and server‑engine communication.

DatabasesQuery OptimizationSQL
0 likes · 5 min read
Index Condition Pushdown (ICP) Optimization in MySQL
vivo Internet Technology
vivo Internet Technology
May 22, 2024 · Databases

Why Your MySQL Pagination Is Slow and How Index Condition Pushdown Fixes It

This article analyzes a slow MySQL pagination query caused by uneven data distribution and range scans, demonstrates how to examine execution plans and data distribution, and shows that adding a composite index with Index Condition Pushdown dramatically reduces scanned rows and query time.

Query Optimizationdatabase indexingindex condition pushdown
0 likes · 15 min read
Why Your MySQL Pagination Is Slow and How Index Condition Pushdown Fixes It
Aikesheng Open Source Community
Aikesheng Open Source Community
Nov 30, 2022 · Databases

Understanding Index Condition Pushdown (ICP) in MySQL and Its Impact on Query Cost

Index Condition Pushdown (ICP) in MySQL pushes filter conditions to the storage engine to reduce row lookups, but this article demonstrates through experiments that while ICP lowers runtime by decreasing back‑table accesses, the optimizer’s cost model often ignores its benefits, leading to suboptimal plan choices.

Cost Modeldatabaseindex condition pushdown
0 likes · 10 min read
Understanding Index Condition Pushdown (ICP) in MySQL and Its Impact on Query Cost
Tencent Database Technology
Tencent Database Technology
Oct 27, 2022 · Databases

Analysis of Unexpected InnoDB Lock Waits Caused by Missing End‑Range Push‑Down and Semi‑Consistent Reads

The article explains why a seemingly non‑conflicting UPDATE on a secondary index can cause lock‑wait alerts under RC isolation, detailing the missing end‑range condition push‑down, the InnoDB row‑search flow, semi‑consistent read behavior, and differences between primary‑key and secondary‑index locking.

InnoDBLock WaitSemi‑consistent Read
0 likes · 12 min read
Analysis of Unexpected InnoDB Lock Waits Caused by Missing End‑Range Push‑Down and Semi‑Consistent Reads
Su San Talks Tech
Su San Talks Tech
Jul 21, 2021 · Databases

Why Index Hits Still Cause Slow Queries and How to Optimize MySQL

This article explains why a query that uses an index can still be slow, demonstrates how to diagnose index usage and row scans with EXPLAIN and the slow query log, and presents optimization techniques such as composite indexes, index condition pushdown, and virtual columns.

EXPLAINIndex Optimizationindex condition pushdown
0 likes · 10 min read
Why Index Hits Still Cause Slow Queries and How to Optimize MySQL
Java Captain
Java Captain
Apr 12, 2019 · Databases

Understanding MySQL Indexes in InnoDB: Structures, Types, and Optimization Techniques

This article explains MySQL indexes—defining what they are, classifying them by structure, storage, and logic, illustrating InnoDB clustered and secondary index structures with examples, and covering advanced concepts such as covering indexes, the leftmost‑prefix rule, and index condition pushdown to improve query performance.

Covering IndexDatabase IndexesInnoDB
0 likes · 10 min read
Understanding MySQL Indexes in InnoDB: Structures, Types, and Optimization Techniques