Index merge: combining two index scansMySQL 5.1+ • MariaDB 5.1+

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

Lesson familyIndex access

Parameters (Index Merge)

About this example
Ready: press Next step, or Play to run it all
0.0s 0.0s

About this example

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 single index serves both conditions, but MySQL and MariaDB can scan both single-column indexes and merge their id lists before reading rows from the clustered index. The query follows the Merge variant.

What you'll see in the animation

  • The two boxes at the top are index scans: idx_category on the left, idx_supplier on the right. Each line is one index entry with the product's id (#12 Webcam); a dot lights up when the entry is read.
  • For an equality the ids come out in id order; for a range (Sort-union) they come in key order, unsorted.
  • The purple middle box merges the two id lists: union and sort-union keep each id once (OR), intersection keeps the ids found in both (AND). Purple dots mark the products found by both scans.
  • The green box at the bottom reads the rows that are left from the clustered index, one lookup per id, in id order.
  • Next step shows one scan, the merge, then the row reads, and the simplified code beside the drawing highlights the lines each step runs.

Cost readout (Index Merge)

idx_category rows ?Ids (primary keys) returned by the first index scan. For an equality they come out sorted by primary key, because InnoDB stores equal keys in id order.

—

idx_supplier rows ?Ids (primary keys) returned by the second index scan.

—

Overlapping rows ?Ids found by both index scans. A union reads each of them once; an intersection keeps only these.

—

Rows fetched ?Rows read from the clustered index after the merge, one lookup per id. This is the row-read cost of the plan.

—

Variant ?Union = OR of equalities (both id lists are already sorted). Intersection = AND (keep only ids in both lists). Sort-union = OR of ranges (the ids are sorted first).

—

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): each index is scanned with an equality, so it returns its row IDs (primary keys) in order; the two sorted streams are merged and each ID is kept once. 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): range scans return their row IDs in key order, not primary key order, so the row IDs from every scan are collected in a sort buffer (sort_buffer_size), sorted and de-duplicated, and then the rows are read in that order.

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

For AND conditions, a composite index usually beats an intersection: one index on (category_id, supplier_id) finds the matching entries in a single range. For OR conditions across two columns no single index can help, so a union of two index scans (or the query rewritten as a UNION of two SELECTs) is the usual answer.

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