Compound Indexes
A compound index is an index on multiple fields together. Think of it like a phone book sorted by last name, then first name. If two people share the last name "Smith," the book breaks the tie by looking at first names. Compound indexes work the same way — they combine fields to create a more specific lookup path.
You'll use compound indexes when your queries frequently filter or sort by two or more fields. They're incredibly powerful because a single compound index can support multiple different queries, saving you from creating many separate indexes.
Creating Compound Indexes
Creating a compound index is straightforward — just list multiple fields in the createIndex() call. The order of fields matters, and we'll get to why in a moment.
db.orders.createIndex({ status: 1, createdAt: -1 })This index supports queries that filter by status and sort by creation date. It's perfect for dashboards showing recent pending orders or filtering orders by their current state. One index, multiple query patterns supported.
You can include up to 32 fields in a single compound index, but in practice you rarely need more than 3 or 4. Adding too many fields makes the index bulky and harder to maintain. Keep it focused on your actual query patterns.
Index Prefix Rules
Here's the critical thing to understand about compound indexes — the prefix rule. A compound index can support queries that use a prefix of the indexed fields. So an index on { a: 1, b: 1, c: 1 } can support queries on { a }, { a, b }, or { a, b, c }, but NOT on { b } or { b, c } alone.
Think of it like looking up a word in a dictionary. You can find words starting with "app" easily, but finding all words containing "app" anywhere in the word would require a different approach. The prefix is your entry point into the sorted structure.
db.users.createIndex({ lastName: 1, firstName: 1, age: 1 })This index supports queries filtering by lastName, or lastName + firstName, or all three fields. But it won't help a query filtering only by firstName. This is why field order in compound indexes is so important.
Sort Order with Compound Indexes
When sorting with a compound index, the sort fields must appear in the same direction as the index. If your index uses { a: 1, b: -1 }, you can sort by a ascending, b descending efficiently. But sorting by a ascending, b ascending would force MongoDB to do an in-memory sort.
This trips up a lot of people. They create an index that looks right but the sort directions don't match, and suddenly their query is slow again. Always check that your sort order aligns with your index definition.
db.events.find({ type: "click" }).sort({ userId: 1, timestamp: -1 })For this query, you'd want an index like { type: 1, userId: 1, timestamp: -1 }. The filter field comes first, then the sort fields with matching directions. This way MongoDB can use the index for both filtering and sorting without touching the documents.