Parameters (Index Merge)
About this exampleAbout 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".