When Multiple Indexes Meet: How MySQL’s Index Merge Optimizer Works

1. Problem Reproduction

To illustrate the issue, the article uses the official MySQL employee test database (available at https://dev.mysql.com/doc/employee/en/ and https://github.com/datacharmer/test_db). The film_actor table has a composite primary key (actor_id, film_id) and a secondary index on film_id:

CREATE TABLE `film_actor` (
  `actor_id` smallint unsigned NOT NULL,
  `film_id` smallint unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`,`film_id`),
  KEY `idx_fk_film_id` (`film_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

The sample query is:

select * from film_actor where film_id=1 or actor_id=1;

Running EXPLAIN on this query yields an execution plan where type is index_merge, possible_keys and key list both indexes, and Extra shows Using union(idx_fk_film_id,PRIMARY); Using where, indicating that MySQL is using index merge.

2. Index Merge

When a query’s WHERE clause can use multiple indexes, MySQL may scan each index separately and then combine the results. The combination can be:

Union (OR) – combine result sets.

Intersection (AND) – keep only rows present in all sets.

Sort‑union – a hybrid that sorts intermediate results before merging.

Four example queries from the official documentation illustrate these cases.

2.1 Using intersect(...)

Intersection is applied when multiple indexes are used with AND conditions, but only under strict rules:

Secondary indexes must be equality predicates and must cover all columns of a composite index.

The primary key index may use range predicates.

Example:

SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1 = 20;

Another example with a composite index and a normal index:

SELECT * FROM tbl_name WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;

Running EXPLAIN on such queries shows Using intersect in the Extra column.

2.2 Using union(...)

Union is triggered when the conditions are combined with OR and each involved index satisfies the same strict requirements as intersection (equality on all columns of a composite index). Range predicates on the primary key can also cause a union.

2.3 Using sort_union(...)

Sort‑union relaxes the constraints: secondary indexes may use range predicates, and composite indexes need not cover every column. MySQL first collects primary key values from each index, sorts them, then merges, which incurs extra CPU and memory overhead.

2.4 Index Merge Principle

Both intersect and union require each index scan to produce an ordered list of primary key values, enabling efficient set operations. Primary‑key scans are naturally ordered; secondary‑index scans must meet equality or full‑coverage conditions to preserve order.

3. Index Merge Issues

While index merge can improve query performance, it often signals suboptimal index design. The additional steps—temporary data caching, sorting, and set operations—consume CPU and memory but are not reflected in the optimizer’s cost model, so in extreme cases a full table scan may be faster.

If index merge is undesirable, you can force MySQL to ignore specific indexes with IGNORE INDEX or disable the feature globally using the optimizer_switch system variable.

Example screenshots (omitted here) demonstrate the use of IGNORE INDEX and the optimizer_switch='index_merge=off' setting.

In summary, understanding when MySQL applies index merge, how the three strategies differ, and how to control the optimizer helps you design more efficient indexes and avoid hidden performance costs.