Tagged articles
index condition pushdown
13 articles · Page 1 of 1
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.
MySQLdatabaseindex condition pushdown
0 likes · 8 min read

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 PerformanceInnoDBMySQL
0 likes · 6 min read

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.
MySQLSQLTable Lookup
0 likes · 9 min read

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.
MySQLSQLTable Lookup
0 likes · 8 min read

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 IndexMySQLexecution plan
0 likes · 28 min read

Index Condition Pushdown (ICP) is a MySQL 5.6 feature that pushes part of WHERE filtering to the storage engine, reducing row reads and I/O; this article explains its principles, activation, usage examples, performance testing, and conditions under which ICP can be applied.
ICPMySQLSQL Performance
0 likes · 10 min read

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.
MySQLSQLdatabases
0 likes · 5 min read

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.
MySQLPaginationdatabase indexing
0 likes · 15 min read

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.
MySQLcost-modeldatabase
0 likes · 10 min read

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 WaitMySQL
0 likes · 12 min read

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 OptimizationMySQL
0 likes · 10 min read

Index Condition Pushdown (ICP) in MySQL 5.6 optimizes secondary index scans by pushing filter conditions to the storage engine, reducing row lookups and data transfer, with examples showing performance gains, usage guidelines, EXPLAIN checks, and limitations.
ICPMySQLdatabase optimization
0 likes · 10 min read

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
