Intersección de índices frente a índices compuestos

Index intersection is MongoDB's ability to use two or more single-field indexes simultaneously to satisfy a single query. Instead of building one compound index that covers all filter fields, MongoDB scans multiple indexes independently, then takes the intersection of their matching document IDs. It sounds convenient, but in practice it is rarely as fast as a well-designed compound index.

When MongoDB considers intersecting indexes, the query planner: 1) Scans index A for documents matching condition 1 and collects their record IDs. 2) Scans index B for documents matching condition 2. 3) Computes the intersection of the two ID sets. 4) Fetches the actual documents using those IDs. This is called an AND_SORTED or AND_HASH stage in explain() output.

MongoDB uses index intersection only when its query planner calculates that it is cheaper than alternatives. The planner runs up to 200 candidate plans in parallel (using trial execution) and picks the one with the lowest estimated cost. Intersection is more likely to be chosen when the collection is large and each individual index is highly selective — both narrow the candidate set dramatically before the intersection step.

A compound index stores keys from multiple fields in a single, pre-sorted B-tree. A query on those fields does a single, efficient index scan and returns documents in sorted order. Index intersection does multiple separate scans and then merges the results in memory. The merge step adds CPU and memory overhead that a compound index avoids entirely.

Index intersection cannot satisfy a sort — the merged result set is not in any particular order relative to the sort key, so MongoDB must perform an in-memory SORT stage. A compound index that includes the sort field delivers results in order directly from the index, avoiding the sort overhead entirely. For queries that both filter and sort, a compound index almost always wins.

Index intersection occasionally beats a compound index when: 1) Both individual indexes are highly selective (each returns very few documents). 2) The query is ad-hoc — you cannot predict which fields will be filtered together so building a compound index for every combination is impractical. 3) The collection is write-heavy — fewer indexes means lower write overhead, so using intersection from two existing indexes avoids adding a third.

You can force MongoDB to use a specific index (or intersection strategy) with .hint(). This bypasses the query planner's automatic selection and is useful for benchmarking — you can compare the execution stats of your manually chosen compound index versus what the planner would do with individual indexes.

When MongoDB uses index intersection, the explain() output shows an AND_SORTED or AND_HASH stage as the parent of two IXSCAN stages. AND_SORTED is used when both indexes return results in the same sorted order; AND_HASH builds an in-memory hash of one result set and probes it with the other. Both are signs that a well-chosen compound index could be faster.

For known, repeated query patterns, a compound index is almost always faster than relying on intersection. The only reasons to prefer intersection are: queries are too unpredictable to cover with compound indexes, or write throughput is so high that adding more indexes is too costly. In those cases, keep individual indexes lean and let the planner intersect when it helps.

As applications evolve, developers add indexes reactively. Over time, collections accumulate redundant indexes that slow writes without benefiting reads. Review regularly using $indexStats: any index with zero accesses.ops over a long period is unused and can be dropped. Also look for indexes made redundant by the compound prefix rule.

Use this framework: Query pattern is known and repeated? → Build a compound index using ESR. Query is ad-hoc or hard to predict? → Rely on individual indexes and accept possible intersection. Write throughput is critical? → Minimise total index count; remove unused indexes. Query involves a sort? → Always use a compound index; intersection never satisfies sorts.

Test your understanding of MongoDB & NoSQL Databases concepts from this lesson.

In this lesson you learned: index intersection uses two single-field indexes and merges their results in memory, adding overhead, compound indexes are almost always faster for known, repeated query patterns — especially those with sorts, and use $indexStats to find and remove unused or redundant indexes that slow writes. Next up we explore aggregation pipeline optimization tips.