What is a compound index and when should you use it?

A compound index is an index on multiple fields, allowing efficient queries on combinations of those fields. db.users.createIndex({ lastName: 1, firstName: 1, age: -1 }). Leftmost prefix rule: a compound index on (A, B, C) supports queries on: A; A + B; A + B + C; but NOT B alone, C alone, or B + C (without A). The index can be used for queries that use a prefix of the index fields from left to right. ESR rule (Equality, Sort, Range): when designing compound indexes for queries with equality, sort, and range conditions, put: Equality fields first (they narrow the search most efficiently), Sort fields next (allows index-based sort, avoiding SORT stage), Range fields last. Example query: find({ status: "active", age: { $gte: 18 } }).sort({ name: 1 }) → index: { status: 1, name: 1, age: 1 }. Sort optimization: a compound index can serve as a sort if the sort fields appear in the index in the correct prefix order. Covered queries: if the query only needs fields in the index (including _id: 0 in projection), MongoDB never reads the documents — ultra-fast. Index intersection: MongoDB can combine multiple single-field indexes for some queries, but compound indexes are almost always more efficient. Limit: maximum 32 fields per compound index. Index key size limit: 8,192 bytes per index entry.