Compound Index in MongoDB: Definition and Usage

A compound index in MongoDB is an index on multiple fields within a document. It helps speed up queries that filter or sort by more than one field by creating a combined index key.

How It Works

Think of a compound index like a multi-level address book. Instead of just looking up a person by their first name or last name alone, you look them up by both first and last name together. MongoDB creates an index that stores values from multiple fields in a specific order, allowing it to quickly find documents matching all those fields.

This index stores entries sorted first by the first field, then by the second, and so on. When you run a query that filters or sorts on these fields in the same order, MongoDB can use this index to jump directly to matching documents without scanning the entire collection.

Example

This example creates a compound index on the fields category and price. It helps queries that filter by both category and price.

mongodb

db.products.createIndex({ category: 1, price: -1 })

// Query using the compound index
db.products.find({ category: 'books', price: { $lt: 20 } })

Output

[
  { "_id": ObjectId("..."), "category": "books", "price": 15, "name": "Learn MongoDB" },
  { "_id": ObjectId("..."), "category": "books", "price": 10, "name": "MongoDB Basics" }
]

When to Use

Use a compound index when your queries often filter or sort by multiple fields together. For example, if you frequently search products by both category and price, a compound index on these fields speeds up those queries.

It is especially useful when the order of fields in the index matches the order used in queries. This reduces the amount of data MongoDB scans, improving performance.

Key Points

• A compound index includes multiple fields in a defined order.
• It improves query speed when filtering or sorting by those fields together.
• The order of fields in the index matters for query optimization.
• It can support queries on the prefix fields of the index.

Key Takeaways

A compound index speeds up queries filtering or sorting on multiple fields together.

The order of fields in a compound index affects which queries can use it efficiently.

Use compound indexes when your queries commonly involve multiple fields.

MongoDB can use a compound index for queries on the first fields in the index sequence.