Mastering MySQL Index Merge Optimization: Algorithms, Usage, and Best Practices

The article introduces MySQL's index‑merge optimization, a technique that combines multiple indexes to accelerate query execution when a query contains several conditions that match different indexes.

What Is Index Merge Optimization

When the type column in an EXPLAIN output shows index_merge, MySQL is using this optimization; the key column lists the specific indexes involved.

Index merge works by scanning each relevant index separately, merging the primary‑key results, and then performing a single table lookup, reducing the number of row‑fetch operations.

Preparation

A sample table test_table is created with four indexes (primary key on id, unique index on user_id, and secondary indexes on merchant_id and area) and populated with over 100 rows for testing.

CREATE TABLE `test_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT '',
  `merchant_id` bigint(20) NOT NULL,
  `area` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_id` (`user_id`) USING BTREE,
  KEY `idx_merchant_id` (`merchant_id`) USING BTREE,
  KEY `idx_area` (`area`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=410 DEFAULT CHARSET=utf8mb4;

Basic Usage

Index merge performs multiple range scans and merges their results. It applies only to indexes on a single table, producing either a union, intersection, or sorted‑union of the scanned rows.

Example queries that can trigger index merge:

SELECT * FROM test_table WHERE merchant_id = 3 OR area = 3;
SELECT * FROM test_table WHERE (merchant_id = 3 OR area = 3) AND name = 'daniel';
SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1 = t1.some_col;
SELECT * FROM t1, t2 WHERE t1.key1 = 1 AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

Important notes for index‑merge optimization:

If a query has a complex WHERE clause with deep AND / OR nesting and MySQL does not choose the optimal plan, try logical equivalence transformations such as (x AND y) OR z => (x OR z) AND (y OR z) and (x OR y) AND z => (x AND z) OR (y AND z).

Index merge does not work with full‑text indexes.

The Three Algorithms

The Extra column in EXPLAIN indicates which algorithm is used:

Intersection algorithm – Using intersect(...) Union algorithm – Using union(...) Sorted‑union algorithm – Using sort_union(...) The optimizer selects among them based on cost estimates and the availability of system variables ( index_merge, index_merge_intersection, index_merge_union, index_merge_sort_union) controlled by optimizer_switch. By default all flags are on; disabling index_merge turns off the whole feature.

1. Intersection Algorithm

Used when multiple conditions are combined with AND. It scans each index, then returns the intersection of the primary‑key sets. If all columns are covered by the indexes, the table rows are not fetched.

key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN

In InnoDB, if one of the merged conditions is a range on the primary key, it is used only for filtering, not for row retrieval.

explain select * from test_table where id < 100 and area = 3;

2. Union Algorithm

Applies when the WHERE clause can be expressed as multiple range conditions combined with OR on different index columns.

key_part1 = const1 OR key_part2 = const2 ... OR key_partN = constN

Example:

explain select * from test_table where merchant_id = 3 or area = 3;

3. Sorted‑Union Algorithm

Used for multiple range queries combined with OR. It first gathers all row IDs, sorts them, and then performs a single table lookup.

Example:

explain select * from test_table where merchant_id < 3 or area < 3;

In summary, index‑merge optimization can significantly reduce row‑fetch operations when queries involve multiple indexed columns, but it requires appropriate index design, awareness of the three algorithms, and careful tuning of optimizer switches.