principlejavascriptmongooseMajor
MongoDB compound indexes: field order and ESR rule
Viewed 0 times
mongodbcompound indexESR ruleequality sort rangequery plannerindex prefixcovered query
Problem
Compound indexes created in the wrong field order do not satisfy queries that filter on later fields without the earlier fields, wasting the index entirely and causing collection scans.
Solution
Follow the ESR rule: Equality fields first, Sort fields second, Range fields last. This order maximizes index prefix matching and allows the query planner to use the index for both filtering and sorting without a blocking sort stage.
Why
MongoDB index scans use a prefix of the compound key. An index on (a, b, c) satisfies queries on (a), (a, b), and (a, b, c) but not (b, c) alone. ESR ordering keeps the most selective, exact-match fields first to prune the index scan quickly.
Gotchas
- A sort on fields not in the index or in different order forces an in-memory sort
- Indexes on low-cardinality fields first (e.g. boolean) waste index depth
- Covered queries (returning only indexed fields) require _id: 0 in the projection
- Partial indexes ({partialFilterExpression}) can replace compound indexes for sparse data
Code Snippets
ESR-ordered compound index for a common query pattern
// Query: find active users in a date range, sorted by name
// ESR: status (equality), name (sort), createdAt (range)
db.users.createIndex({ status: 1, name: 1, createdAt: 1 });
// This query fully uses the index (no blocking sort)
db.users.find(
{ status: 'active', createdAt: { $gte: startDate, $lte: endDate } },
{ sort: { name: 1 } }
);Revisions (0)
No revisions yet.