Problem Statement
What is a compound index and when should you use it? Explain the index prefix rule.
Explanation
A compound index is an index on multiple fields in a specific order. For example, an index on age and city creates a sorted structure first by age, then by city within each age group. This supports queries that filter or sort by these fields.
You should use compound indexes when your queries frequently filter or sort by multiple fields together. For example, if you often query users by age and city, a compound index on these fields is more efficient than separate indexes.
The index prefix rule states that MongoDB can use a compound index for queries on any prefix of the indexed fields. If you have an index on fields A, B, and C, MongoDB can use it for queries on A alone, A and B together, or A, B, and C together. However, it cannot efficiently use this index for queries on just B, just C, or B and C without A. Field order matters in compound indexes, so place the most selective or frequently queried fields first.
Code Solution
SolutionRead Only
// Compound index on age, city, status
db.users.createIndex({ age: 1, city: 1, status: 1 })
// Can use index (prefix rule):
db.users.find({ age: 30 })
db.users.find({ age: 30, city: "NYC" })
db.users.find({ age: 30, city: "NYC", status: "active" })
// Cannot use index efficiently:
db.users.find({ city: "NYC" })
db.users.find({ status: "active" })
db.users.find({ city: "NYC", status: "active" })Practice Sets
This question appears in the following practice sets:
