BestHub
Sign in
Tagged articles
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.

Database OptimizationSQLTable Lookup
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.

Database OptimizationSQLTable Lookup
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 planindex condition pushdown
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.

SQLdatabasesindex condition pushdown
0 likes · 5 min read
Index Condition Pushdown (ICP) Optimization in MySQL
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.

Index Optimizationexplainindex 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.

Database IndexesInnoDBSQL
0 likes · 10 min read
Understanding MySQL Indexes in InnoDB: Structures, Types, and Optimization Techniques