Index Intersection vs Compound Indexes
Learners will understand when MongoDB intersects multiple single-field indexes and when a compound index outperforms intersection.
What Is Index Intersection?
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.
How Index Intersection Works Internally
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.
// With two single-field indexes:
db.orders.createIndex({ status: 1 })
db.orders.createIndex({ customerId: 1 })
// Query may intersect both indexes
db.orders.find({ status: 'pending', customerId: 'c001' })
.explain('executionStats')
// Look for 'AND_SORTED' or 'AND_HASH' stage in winningPlanAll lessons in this course
- The Database Profiler and Slow Query Log
- Compound Index Prefix Rule and ESR Principle
- Index Intersection vs Compound Indexes
- Aggregation Pipeline Optimization Tips