Compound indexes in MongoDB are used to index multiple fields within a document. They are particularly useful for queries that filter or sort on multiple fields. By combining multiple fields into a single index, MongoDB can optimize complex queries and improve performance significantly.

Key Points

• Supports complex queries: Compound indexes can handle queries that involve multiple fields.
• Improves query performance: Indexes on multiple fields allow MongoDB to quickly locate documents that match complex query conditions.
• Supports efficient sorting: Compound indexes can also be used to sort query results by multiple fields.

Syntax

db.collection.createIndex({ <field1>: <type1>, <field2>: <type2> }, { <option1>: <value1>, <option2>: <value2>, ... })

• field1, field2: The fields to be indexed.
• type1, type2: The type of index for each field. Use 1 for ascending order or -1 for descending order.
• option1, option2: Optional parameters to customize the index behavior (e.g., unique, background).

Example Setup

Let's insert some documents into the orders collection:

db.orders.insertMany([
  { order_id: 1, customer: "John Doe", total: 150, date: new Date("2023-01-01") },
  { order_id: 2, customer: "Jane Smith", total: 200, date: new Date("2023-01-02") },
  { order_id: 3, customer: "Emily Davis", total: 300, date: new Date("2023-01-03") },
  { order_id: 4, customer: "Michael Brown", total: 250, date: new Date("2023-01-04") },
  { order_id: 5, customer: "Sarah Wilson", total: 100, date: new Date("2023-01-05") }
])

Index Creation Example

Create a compound index on the customer and date fields:

db.orders.createIndex({ customer: 1, date: -1 })

• db.orders.createIndex({ customer: 1, date: -1 }): This command creates a compound index on the customer field in ascending order and the date field in descending order. This index will speed up queries that filter or sort by both customer and date.

Querying Using the Indexes

Example 1: Query by Indexed Fields

db.orders.find({ customer: "Jane Smith" }).sort({ date: -1 })

• This query uses the compound index on customer and date fields to quickly locate documents where the customer is "Jane Smith" and sorts them by date in descending order.

Example 2: Query with Range and Sorting

db.orders.find({ date: { $gte: new Date("2023-01-01"), $lte: new Date("2023-01-04") } }).sort({ customer: 1 })

• This query uses the compound index to efficiently find orders placed between "2023-01-01" and "2023-01-04" and sorts the results by customer in ascending order.

Compound indexes in MongoDB are essential for optimizing complex queries that involve multiple fields. By combining multiple fields into a single index, MongoDB can enhance query performance and support efficient sorting.