Compound Indexes
A compound index covers multiple fields in a defined order, such as status then created date. The field order matters because the index is sorted left to right.
- A query can use the index if it filters on a prefix of the fields, like status alone or status plus date.
- A query filtering only on created date cannot use a status first index efficiently.
- The same index can also satisfy a sort that matches its order.
Multikey Indexes
When you index a field that holds an array, MongoDB creates a multikey index with one entry per array element. A query matching any element can then use the index.
- A collection can have a multikey index on at most one array field per compound index.
- Multikey indexes can grow large because each element adds an entry.
The ESR Guideline
Order compound index fields as Equality, then Sort, then Range to maximize the documents the index can skip and to support ordered output.
Key idea
Compound indexes cover ordered field prefixes and follow the equality sort range rule, while multikey indexes add one entry per array element so queries can match any item.