Index Merge Strategies

Optimize queries that combine results from two independent indexes on the same table.

In this lesson, you will learn how the database engine intelligently combines results from multiple independent indexes to satisfy a single query.

When One Index Isn't Enough

Up until now, you have learned that the query optimizer usually chooses the "best" B-Tree index to narrow down your search. However, what happens when you have a query that filters by two different columns, each with its own independent index?

For example, consider a Products table with an index on category_id and a separate index on status. If you run this query:

SELECT * FROM Products 
WHERE category_id = 5 AND status = 'out_of_stock';

If the database only uses the category_id index, it might still have to scan through thousands of products to check the status. If it only uses the status index, it might scan through thousands of products to check the category_id.

The Merge Strategy

An Index Merge Strategy allows the engine to be smarter. Instead of picking just one index, it performs a scan on both:

It looks up all Row IDs (RIDs) where category_id = 5 using the first index.
It looks up all Row IDs where status = 'out_of_stock' using the second index.
It merges these two sets of Row IDs to find only the rows that satisfy both conditions (an Intersection).

Alternatively, if your query used OR (e.g., WHERE category_id = 5 OR status = 'out_of_stock'), the database would perform a Union of the Row IDs found in both indexes.

Why Does This Matter?

Index merging is a powerful way to avoid full table scans without needing a complex multi-column index for every possible query combination. It treats your existing indexes like building blocks, combining the results in memory before finally performing the actual RID Lookup (fetching the data rows) for only the specific items that match the combined criteria.

While this saves time by narrowing the search space significantly, remember that it does require extra CPU and memory to sort and merge those lists of Row IDs, so it is most efficient when the resulting sets are relatively small.

Key Takeaway: Index Merge Strategies allow the query optimizer to combine multiple independent indexes into one result set, significantly improving performance for complex filters where no single multi-column index exists.