BestHub
Sign in
Tagged articles

index_merge

13 articles · Page 1 of 1
Programmer XiaoFu
Programmer XiaoFu
Jun 1, 2026 · Databases

Why Does an OR Between Two Indexed Columns Still Trigger a Full Table Scan?

Even though the phone and email columns each have a single‑column index, an OR condition forces MySQL's cost‑based optimizer to choose a full table scan because the estimated cost of index merge (random I/O and possible sort‑union) exceeds the cost of a sequential scan, and the article explains the underlying mechanics and practical workarounds.

MySQLOR queryQuery Optimization
0 likes · 10 min read
Why Does an OR Between Two Indexed Columns Still Trigger a Full Table Scan?
ITPUB
ITPUB
Sep 28, 2023 · Databases

Mastering MySQL Index Merge: How Three Algorithms Boost Query Performance

Learn how MySQL's index merge optimization works, explore its three algorithms—intersect, union, and sort_union—understand when each applies, see practical setup and sample queries, and discover configuration flags and best‑practice tips to improve query efficiency.

DatabaseMySQLQuery Optimization
0 likes · 9 min read
Mastering MySQL Index Merge: How Three Algorithms Boost Query Performance
ITPUB
ITPUB
Apr 18, 2023 · Databases

Mastering MySQL Index Merge: Principles, Algorithms, and Practical Tips

This article explains MySQL's index‑merge optimization, detailing how the EXPLAIN output indicates its use, the three underlying algorithms (intersect, union, sort‑union), practical query examples, configuration flags, and step‑by‑step guidance for testing with sample tables and indexes.

AlgorithmsDatabaseMySQL
0 likes · 9 min read
Mastering MySQL Index Merge: Principles, Algorithms, and Practical Tips
dbaplus Community
dbaplus Community
May 9, 2021 · Databases

Why MySQL Index Merge Triggers Deadlocks and How to Fix Them

This article examines a real‑world MySQL deadlock caused by the Index Merge optimizer, explains InnoDB's lock‑on‑index mechanism, walks through the deadlock logs, and presents four practical solutions including force‑index hints, disabling Index Merge, creating a composite index, and a two‑step update approach.

DeadlockInnoDBMySQL
0 likes · 15 min read
Why MySQL Index Merge Triggers Deadlocks and How to Fix Them
Aikesheng Open Source Community
Aikesheng Open Source Community
Mar 10, 2021 · Databases

Unsuitable Scenarios for MySQL Composite Indexes and Refactoring Strategies

This article reviews MySQL composite index syntax, explains the mandatory condition that the leftmost column must appear in the query filter, analyzes seven representative SQL statements that cannot benefit from a composite index, and provides practical refactoring suggestions such as adding single‑column indexes or creating more appropriate composite indexes.

Composite IndexMySQLQuery Optimization
0 likes · 11 min read
Unsuitable Scenarios for MySQL Composite Indexes and Refactoring Strategies
ITPUB
ITPUB
Jan 28, 2021 · Databases

Why MySQL Index Merge Triggers Deadlocks and How to Fix It

This article explains how MySQL's index‑merge optimization can cause row‑level deadlocks during inventory updates, analyzes the lock sequence and deadlock logs, and presents practical solutions such as forcing a specific index, disabling index‑merge, and creating a composite index.

DeadlockInnoDBMySQL
0 likes · 15 min read
Why MySQL Index Merge Triggers Deadlocks and How to Fix It