Problem Statement
For a compound index { age: 1, city: 1 }, which query can use this index efficiently?
Explanation
A compound index on age and city can efficiently support queries on age alone, or on both age and city together, because age is the prefix of the index. However, it cannot efficiently support queries on city alone.
This is the index prefix rule. MongoDB can use a compound index for queries on any prefix of the indexed fields. In this case, queries on just age, or on age and city can use the index. But queries on just city cannot use this index efficiently and would need a separate index on city.
Code Solution
SolutionRead Only
// Compound index
db.users.createIndex({ age: 1, city: 1 })
// Efficient - uses index
db.users.find({ age: 30 })
db.users.find({ age: 30, city: "NYC" })
// Inefficient - cannot use this index
db.users.find({ city: "NYC" })