Compound indexes
A compound index covers several fields, and the order of fields decides which queries it can serve.
In this page:
Syntax
db.collection_name.createIndex({ field1: 1, field2: -1 })Compound indexes
db.collection.createIndex({ a: 1, b: -1 }) sorts by a then b. The index supports queries on a, and on a and b together (the prefix rule), but not on b alone. Follow the guideline: equality fields first, then sort fields, then range fields (ESR).
Note:
Remember the ESR rule: Equality, Sort, Range.
Example: Compound indexes
test> db.orders.createIndex({ status: 1, createdAt: -1, total: 1 })
status_1_createdAt_-1_total_1
// Uses the index: status equality, then sorted by createdAt, then total range
test> db.orders.find({ status: "paid", total: { $gt: 100 } }).sort({ createdAt: -1 })
// Cannot use it efficiently: skips the leading field status
test> db.orders.find({ total: { $gt: 100 } })⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Putting range fields before equality fields
- Expecting b-only queries to use an {a, b} index
- Creating many overlapping indexes
Chapter Summary
- One index over several fields
- Prefix rule: leading fields matter
- ESR: equality, sort, range
- Field order is important
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: