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

What Is Index Merge Optimization

When you run EXPLAIN on a MySQL query, the type column shows how the tables are accessed; if it displays index_merge, MySQL is using index‑merge optimization, and the key column lists the specific indexes involved.

Index‑merge optimization combines multiple indexes to satisfy a query with several conditions, reducing the number of table‑row lookups (back‑table operations) and speeding up execution.

Simple process:

Query conditions reference index1 and index2.

The primary keys found via both indexes are merged before accessing the clustered index.

The merged primary keys are used to fetch rows in a single back‑table lookup.

Preparation

The following table and three indexes are created 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;

More than 100 rows are inserted for the experiments.

Basic Usage

Index Merge performs multiple range scans and merges the results. It works only on indexes of a single table and can produce a union, intersection, or sorted‑union of the underlying scans, giving rise to three algorithms.

Example queries that can use 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:

If a query has a complex WHERE clause with deep AND / OR nesting and MySQL does not pick the optimal plan, try the following logical equivalences:

(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)

Index Merge does not work with full‑text indexes.

Three Algorithms

The optimizer reports the chosen algorithm in the Extra column of EXPLAIN: Using intersect(...) – Intersection algorithm. Using union(...) – Union algorithm. Using sort_union(...) – Sorted‑union algorithm.

Whether an algorithm is used depends on the values of the system variables index_merge , index_merge_intersection , index_merge_union , and index_merge_sort_union in the optimizer_switch . By default all are on . To disable a specific algorithm, set its flag to off .

1. Intersection Algorithm

Used when multiple conditions are combined with AND. It merges the primary‑key results of each index and returns their intersection. It works when each index part is fully covered or when one condition is a range on the primary key (used only for filtering).

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

Example:

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

If all columns used in the query are covered by the participating indexes, the table rows are not fetched; the result appears in the Extra column.

2. Union Algorithm

Applicable when the WHERE clause can be rewritten as several range conditions on different index columns combined with OR. Each condition must be a simple equality or range on a single index.

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

The sorted‑union algorithm is used when multiple range queries are combined with OR. It first retrieves all row IDs, sorts them, and then performs the back‑table lookup.

Example:

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

The difference from the plain union algorithm is the additional sorting step before the final row fetch.