Index Ordering and ESR

ESR is how you order keys in a compound index: Equality, then Sort, then Range. Equality is { userId: id } or { status: "delivered" }. Sort is .sort({ createdAt: -1 }). Range is $gt, $gte, $lt, $in with many values behaves like a range too.

// find({ userId }).sort({ createdAt: -1 })
db.orders.createIndex({ userId: 1, createdAt: -1 })

// find({ inStock: true }).sort({ price: 1 })
db.products.createIndex({ inStock: 1, price: 1 })

// find({ city: "Bangalore", age: { $gte: 25 } }).sort({ name: 1 })
db.users.createIndex({ city: 1, name: 1, age: 1 })

If range comes before sort, the index walks a slice of keys that are not in sort order — you get an in-memory SORT (or a spill). { city: 1, age: 1, name: 1 } for that last query does not follow ESR: age is range, name is sort. Swap to { city, name, age }. When sort and range are the same field (createdAt), one key does both — { userId: 1, createdAt: -1 } is enough.