How do you handle 'Index Intersection'? Is it better to have two single-field indexes or one compound index?

Question

Accepted Answer

Index intersection is a feature that allows MongoDB to use more than one index to fulfill a single query by intersecting the results from multiple index scans . While this capability exists, the official MongoDB documentation and community experts consistently warn against relying on it. In practice, the query optimizer rarely selects index intersection plans because they are less efficient than using a properly designed compound index . The general principle is that schema designs should not rely on index intersection; instead, compound indexes should be used . When MongoDB uses index intersection, it scans multiple indexes separately and then intersects the results to find documents that match all query conditions. This can happen in two ways: AND_SORTED (which merges sorted index results) or AND_HASH (which builds a hash table of results from one index and probes it with results from another) . For example, with indexes on { qty: 1 } and { item: 1 }, MongoDB could intersect them to satisfy a query like `find({ item: "abc123", qty: { $gt: 15 } })` . However, this intersection process introduces significant overhead—it requires scanning potentially large portions of multiple indexes and performing computationally expensive set operations in memory . ```javascript
// Given these two single-field indexes
db.orders.createIndex({ qty: 1 })
db.orders.createIndex({ item: 1 })

// Query that could potentially use index intersection
db.orders.find({ item: "abc123", qty: { $gt: 15 } })

// In explain output, you might see an AND_HASH or AND_SORTED stage
// But this is rarely chosen by the optimizer

db.orders.find({ item: "abc123", qty: { $gt: 15 } }).explain("executionStats")
```

While MongoDB technically supports this, the query planner will almost always prefer a compound index if one exists . A single compound index is almost always superior to index intersection for several reasons. First, compound indexes provide more selective scans—they can narrow down results using multiple fields in a single index traversal, whereas index intersection requires scanning potentially large portions of multiple indexes and then intersecting them in memory . Second, compound indexes require only one index lookup operation, while index intersection requires multiple index scans plus the intersection computation . Third, compound indexes can support sorting directly, while index intersection cannot be used when the query includes a sort that doesn't match one of the intersected indexes . A MongoDB Developer Advocate confirms that index intersection is "never used in practice because in real-world workloads it almost never gets chosen by the query planner" . **Rarely selected by optimizer**: The query planner disfavors index intersection plans to prevent bad plan selection  **Cannot support sorting**: Index intersection does not apply when the sort operation requires an index separate from the query predicate  **Higher overhead**: Intersecting results requires additional computation and memory compared to a single index scan  **Hash-based intersection disabled**: Hash-based index intersection is disabled by default, and sort-based intersection is disfavored  Despite the superiority of compound indexes, there are scenarios where maintaining separate single-field indexes is still valuable. If you have multiple query patterns that use different combinations of fields, a set of single-field indexes might cover all patterns, whereas you would need multiple compound indexes to achieve the same coverage . For example, if you have queries that filter on `a` alone, `b` alone, and sometimes both `a` and `b`, having single-field indexes on `a` and `b` could support all three patterns. However, the query for both fields would ideally use index intersection, which is suboptimal. A better approach might be to have single-field indexes for the frequent single-field queries and a compound index for the combined query, accepting the write overhead of an additional index. ```javascript
// Query patterns:
// 1. db.collection.find({ a: 123 })
// 2. db.collection.find({ b: "value" })
// 3. db.collection.find({ a: 123, b: "value" })

// Option 1: Two single-field indexes
// - Supports pattern 1 efficiently
// - Supports pattern 2 efficiently
// - Pattern 3 may attempt index intersection (inefficient)
db.collection.createIndex({ a: 1 })
db.collection.createIndex({ b: 1 })

// Option 2: Two single-field indexes + one compound index
// - Supports all patterns efficiently
// - Higher write overhead (3 indexes to maintain)
db.collection.createIndex({ a: 1 })
db.collection.createIndex({ b: 1 })
db.collection.createIndex({ a: 1, b: 1 })

// Option 3: Just the compound index
// - Pattern 1 can use index prefix
// - Pattern 2 cannot use this index at all (full collection scan)
db.collection.createIndex({ a: 1, b: 1 })
``` The MongoDB documentation is unequivocal: "Schema designs should not rely on index intersection. Instead, compound indexes should be used"

How do you handle 'Index Intersection'? Is it better to have two single-field indexes or one compound index?

Index intersection is a query optimization technique where MongoDB combines multiple indexes to satisfy a query, but it is rarely used in practice and should not be relied upon; a single well-designed compound index is almost always the better choice.

How do you handle 'Index Intersection'? Is it better to have two single-field indexes or one compound index?

Index intersection is a query optimization technique where MongoDB combines multiple indexes to satisfy a query, but it is rarely used in practice and should not be relied upon; a single well-designed compound index is almost always the better choice.

Scenario Questions

Follow-up Questions