Compound index and field order in MongoDB

Introduction

A compound index helps you find data faster when you search using more than one field. The order of fields in the index matters because it changes how quickly you get results.

You want to speed up searches that use two or more fields together.

You have queries that filter by one field and then sort by another.

You want to improve performance for reports that group by multiple fields.

You often search by a main category and then by a subcategory.

You want to avoid creating many single-field indexes and save space.

Syntax

MongoDB

db.collection.createIndex({ field1: 1, field2: -1 })

Use 1 for ascending order and -1 for descending order.

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

Examples

This index helps queries that filter by category and then by price in ascending order.

MongoDB

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

This index speeds up queries filtering by customerId and sorting orders by newest date first.

MongoDB

db.orders.createIndex({ customerId: 1, orderDate: -1 })

This index supports searches by last name and then first name in alphabetical order.

MongoDB

db.users.createIndex({ lastName: 1, firstName: 1 })

Sample Program

First, we create a compound index on region (ascending) and salesAmount (descending). Then, we find sales in the 'West' region and sort them by salesAmount from highest to lowest. The index helps this query run faster.

MongoDB

db.sales.createIndex({ region: 1, salesAmount: -1 })
db.sales.find({ region: 'West' }).sort({ salesAmount: -1 })

Important Notes

If you search only by the first field in the compound index, MongoDB can still use the index efficiently.

But if you search only by the second field, the index is not helpful.

Think of the index like a phone book sorted first by last name, then by first name.

Summary

Compound indexes speed up queries using multiple fields together.

The order of fields in the index matters for query performance.

Use ascending (1) or descending (-1) to control sorting in the index.