BestHub
Sign in
Tagged articles

index merge

12 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.

OR queryQuery OptimizationSQL
0 likes · 10 min read
Why Does an OR Between Two Indexed Columns Still Trigger a Full Table Scan?
Aikesheng Open Source Community
Aikesheng Open Source Community
Jan 6, 2025 · Databases

Understanding MySQL EXPLAIN Type Column and JOIN Types: ALL, index_merge, and Optimization Techniques

This article explains MySQL EXPLAIN type column, detailing the inefficiencies of type=ALL, how adding ORDER BY can change it to index, and demonstrates the use of INDEX_MERGE for UNION and INTERSECT queries, including practical examples and cost comparisons to improve query performance.

Database PerformanceEXPLAINQuery Optimization
0 likes · 8 min read
Understanding MySQL EXPLAIN Type Column and JOIN Types: ALL, index_merge, and Optimization Techniques
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.

Query Optimizationdatabaseindex merge
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.

Query OptimizationSQLalgorithms
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.

DeadlockInnoDBLocking
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 IndexQuery Optimizationdatabase indexing
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.

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