In this lesson, you will learn how to design multi-field indexes to drastically reduce the number of documents your database engine must scan during complex queries.
The Power of Compound Indexing
When your document model stores data like user profiles or activity logs, you often query by multiple criteria simultaneously—such as filtering by status and sorting by timestamp. If you only have a single-field index on status, the database engine will find all "active" documents but then must perform an expensive, in-memory sort to order them by time.
A Compound Index bundles multiple fields into a single, ordered data structure. By including both fields in the index, the database can jump directly to the correct subset of data and traverse it in the requested order without additional processing.
The ESR Rule
When designing a compound index, follow the ESR (Equality, Sort, Range) rule to maximize efficiency:
- Equality: Place fields that you query for an exact match (e.g.,
status: "active") first. - Sort: Place fields used for ordering next (e.g.,
createdAt: -1). - Range: Place fields used for range queries (e.g.,
price: { $gt: 100 }) last.
Practical Example
Imagine an e-commerce document model where you frequently find "delivered" orders from a specific customer sorted by date. Instead of indexing fields separately, you create one compound index:
// Indexing customerId (Equality) and orderDate (Sort)
db.orders.createIndex({ "customerId": 1, "orderDate": -1 });
With this index, the engine doesn't have to fetch every order for a customer and sort them; it simply traverses the index tree to the exact branch where that customer’s data lives and retrieves the results pre-sorted.
Why Order Matters
The order of fields in your index is critical. Because indexes are essentially sorted structures, a compound index on { "customerId": 1, "orderDate": -1 } can be used to query by customerId alone, or by both fields, but it cannot be used to efficiently query by orderDate alone. Designing your indexes based on your most frequent query patterns—rather than individual fields—is the hallmark of an efficient document-based architecture.
Key Takeaway: Design your compound indexes by prioritizing equality matches first, followed by sort requirements, and finally range filters to ensure the database engine performs the minimal amount of work per query.