Compound indexes are the secret sauce for making MongoDB queries fly, but most people get them wrong by treating them like SQL indexes, which is a fundamentally different beast.
Let’s watch one in action. Imagine we have a collection of users with documents like this:
{
"_id": ObjectId("..."),
"username": "alice",
"status": "active",
"createdAt": ISODate("2023-10-26T10:00:00Z"),
"lastLogin": ISODate("2023-10-26T14:30:00Z")
}
We want to find all active users who logged in recently, sorted by their creation date. A naive query might look like this:
db.users.find({ status: "active", lastLogin: { $gte: ISODate("2023-10-26T12:00:00Z") } }).sort({ createdAt: 1 })
Without a compound index, MongoDB would have to scan a significant portion of the users collection, filter by status, then filter by lastLogin, and then sort the results. This is slow, especially on large collections.
Now, let’s create a compound index designed for this specific query pattern:
db.users.createIndex({ status: 1, lastLogin: 1, createdAt: 1 })
This single index, { status: 1, lastLogin: 1, createdAt: 1 }, tells MongoDB how to efficiently satisfy multiple parts of our query. When we run the same query again:
db.users.find({ status: "active", lastLogin: { $gte: ISODate("2023-10-26T12:00:00Z") } }).sort({ createdAt: 1 })
MongoDB can now use the index. It first uses the status: 1 part to quickly locate all documents with status: "active". Then, within that subset, it uses the lastLogin: 1 part to filter for those where lastLogin is greater than or equal to our specified date. Finally, because createdAt: 1 is the last field in the index, MongoDB can read the createdAt values directly from the index in the correct sorted order, avoiding a separate sort operation entirely. The index essentially pre-sorts the data for us based on the order of fields defined.
The problem this solves is the performance bottleneck of scanning and sorting large datasets. Compound indexes allow MongoDB to efficiently filter and sort data by leveraging the index structure. The order of fields in the index is crucial. MongoDB can use a compound index for queries that filter on a prefix of the indexed fields. For our index { status: 1, lastLogin: 1, createdAt: 1 }, MongoDB can efficiently handle queries filtering on:
statusstatusandlastLoginstatus,lastLogin, andcreatedAt
It can also be used for sorting if the sort order matches a prefix of the index fields.
The exact levers you control are the fields you include in the index and their order. For a query like db.users.find({ status: "active" }).sort({ createdAt: 1 }), the index { status: 1, createdAt: 1 } would be very effective. The index { createdAt: 1, status: 1 } would not be as effective for this specific query because status is not the first field.
The most surprising thing about compound indexes is that they don’t just speed up equality matches; they are incredibly powerful for range queries and sorting, but only when those operations align with the leading fields of the index. If you have an index { a: 1, b: 1 } and query for { a: 5, b: { $gt: 10 } }, MongoDB will use the index for both a and b. However, if you query for { b: { $gt: 10 } }, the index will not be used because b is not the leading field. The index is structured like a B-tree, and to traverse it efficiently, you need to start at the root and follow paths dictated by the indexed fields in order.
It’s also important to understand that an index on { a: 1, b: 1 } is not the same as two separate indexes on { a: 1 } and { b: 1 }. While MongoDB can use the compound index for queries targeting just a, it cannot use the index for queries targeting just b. The order matters because the index is a single, ordered structure.
The next concept you’ll run into is how MongoDB uses indexes for sort operations, and the crucial difference between a sort that can use an index and one that requires an in-memory sort.