Index Merge — combining two index scansMySQL 5.1+ • MariaDB 5.1+

Watch MySQL scan two separate indexes, collect row-IDs, and merge them with union, intersection, or sort-union.

Lesson familyIndex Access

Parameters (Index Merge)

Rows returned by the idx_category index scan.
Rows returned by the idx_supplier index scan.
Percentage of rows present in both index scans (duplicates).
Union for OR, intersection for AND, sort-union when row-IDs aren't pre-sorted.

Example query this animation executes

-- Separate indexes on category_id and supplier_id
SELECT *
FROM   products
WHERE  category_id = 5
   OR  supplier_id = 12;

No composite index covers both conditions, but MySQL can scan both single-column indexes and merge row-ID sets before clustered fetch.

What you'll see in the animation

  • Engine executes two independent index scans and collects row-ID lists.
  • Merge stage combines lists as union/sort-union (OR) or intersection (AND).
  • Duplicate row-IDs are removed before table fetch to avoid repeated reads.
  • Only merged row-IDs are fetched from clustered index in the final step.
  • Composite indexes can remove this merge stage entirely.
Ready — press Play
0.0s 0.0s

Cost readout (Index Merge)

idx_category rows ?Row-IDs returned by the first index scan. These are sorted by primary key for InnoDB.

—

idx_supplier rows ?Row-IDs returned by the second index scan.

—

Overlapping rows ?Row-IDs present in both index scans. For union, these are de-duplicated. For intersection, these are the result.

—

Rows fetched ?Final number of rows fetched from the clustered index after merging. This is the actual I/O cost.

—

Variant ?Union = OR conditions (combine both sets). Intersection = AND conditions (keep only common rows). Sort-union = OR with unsorted ranges.

—

Rows fetched vs index scan size (log–log)

Learn more — when does MySQL choose index merge?

The optimizer considers index merge when a query has OR or AND conditions on columns with separate single-column indexes and no composite index covers the full predicate.

Index merge union (OR): The optimizer scans each index separately, then merges the sorted row-ID streams with de-duplication. This avoids a full table scan when each index is selective enough.

Index merge intersection (AND): Both indexes are scanned, and only row-IDs present in both streams are kept. This is useful when no single index is selective enough, but together they are.

Index merge sort-union (OR with ranges): When the row-IDs from each index scan are not guaranteed to be in PK order (e.g. range scans), MySQL sorts each set first before merging. Slower than union but still faster than a full table scan.

You can control this behaviour with optimizer_switch: index_merge=on, index_merge_union=on, index_merge_intersection=on, index_merge_sort_union=on.

A composite index is almost always better than index merge. If you see index merge in your EXPLAIN output, consider whether a composite index would serve the query more efficiently.

Sources: MySQL 8.4 reference manual §10.2.1.3 "Index Merge Optimization"; MariaDB Knowledge Base "Index Merge Optimization".