Mastering MySQL Index Merge: How Three Algorithms Boost Query Performance

What Is Index Merge Optimization

When you run EXPLAIN on a MySQL query, the type column shows how the table is accessed. If the value is index_merge, MySQL is using the index‑merge optimization, and the key column lists the specific indexes involved.

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

Both index1 and index2 match the query conditions.

The primary‑key values found via each index are collected and merged.

The merged primary‑key list is used to fetch rows from the clustered index in a single back‑table lookup.

Preparation

The following test table and three indexes are used for demonstration:

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

Basic Usage

Index Merge works by performing multiple range scans, merging their result sets, and then accessing the clustered index once. It only merges scans from the same table, and the merged result can be the union, intersection, or a union‑then‑sort of the underlying scans, giving rise to three algorithms.

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

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

(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 Extra column of EXPLAIN shows which algorithm is used: Using intersect(...) – Intersection algorithm Using union(...) – Union algorithm Using sort_union(...) – Sort‑union algorithm

The use of Index Merge is controlled by the optimizer_switch system variable flags index_merge , index_merge_intersection , index_merge_union , and index_merge_sort_union . By default all are on . To disable specific algorithms, set the corresponding flag to off .

1. Intersection Algorithm

This algorithm is chosen when the query conditions are combined with AND and each condition can be satisfied by a separate index. It scans all involved indexes simultaneously and returns the intersection of the primary‑key sets.

Typical form:

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 engine can return results without fetching full rows.

2. Union Algorithm

The union algorithm applies when the WHERE clause can be expressed as a series of OR conditions, each using a different ordinary index. MySQL builds separate range scans for each condition and then unions the primary‑key sets.

Typical form:

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. Sort‑Union Algorithm

The sort‑union algorithm is used when multiple range conditions are combined with OR. It first retrieves all matching row IDs, sorts them, and then performs a single back‑table lookup.

Example:

EXPLAIN SELECT * FROM test_table WHERE merchant_id < 3 OR area < 3;

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

Understanding these three algorithms, their applicable query patterns, and the relevant optimizer flags enables you to fine‑tune MySQL queries for better performance.