Tagged articles
index_merge
13 articles · Page 1 of 1
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

The article explains MySQL's Index Merge optimization, detailing its three types—Intersection, Union, and Sort‑Union—when it is beneficial, how to design indexes and rewrite queries for better performance, and the associated costs and limitations.
Database IndexingMySQLPerformance Tuning
0 likes · 5 min read

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 PerformanceEXPLAINMySQL
0 likes · 8 min read

This article explains MySQL's optimizer_switch system variable, how it consists of on/off flags that fine‑tune the query optimizer, shows how to view and modify the flag set, and details the most important flags such as index_merge, ICP, materialization, and others.
ICPMySQLindex_merge
0 likes · 10 min read

This article explains the MySQL Index Merge access method, details the underlying merge‑sort algorithm, enumerates the three index‑merge strategies, analyzes source‑code snippets, presents a real‑world case where index merge hurts performance, and offers practical optimization recommendations.
Database PerformanceMySQLSQL Optimization
0 likes · 16 min read


ITPUBSep 28, 2023 · Databases
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.
MySQLQuery Optimizationdatabase
0 likes · 9 min read

This article explains the principle behind MySQL's index‑merge optimization, demonstrates how to set up test data, walks through the three merge algorithms (intersect, union, sort‑union), provides practical SQL examples, and shares tips for tuning and avoiding common pitfalls.
MySQLQuery OptimizationSQL
0 likes · 8 min read


ITPUBApr 18, 2023 · Databases
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.
AlgorithmsMySQLQuery Optimization
0 likes · 9 min read

This article analyzes a production MySQL deadlock caused by two identical UPDATE statements that trigger an index‑merge optimization, explains the lock interactions between the idx_status and PRIMARY indexes, and provides code‑level and MySQL‑level solutions to prevent such deadlocks.
DeadlockInnoDBMySQL
0 likes · 10 min read

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

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 IndexDatabase IndexingMySQL
0 likes · 11 min read


ITPUBJan 28, 2021 · Databases
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

This article explains the concepts of cardinality and HINT in MySQL, demonstrates how inaccurate cardinality can lead to sub‑optimal execution plans, and shows how applying index_merge hints dramatically reduces query cost and row scans for multi‑column predicates.
CardinalityIndex HintMySQL
0 likes · 7 min read
