# Compound Indexes and the Order of Fields

A compound index keeps its entries in order by one field and then by the next, so a single index can serve a filter and a sort together, and the order of its fields (equal, then sort, then range) decides how much of it MongoDB has to read.

Journeyman · MongoDB · Database · Updated November 19, 2026

By Gregor Dzierzon

## After this lesson you can

- Create a compound index that serves both a filter and a sort, and predict which queries its prefix can serve
- Order a compound index's fields as equality, then sort, then range, and explain why that order reads fewer index entries

## Key takeaways

- A compound index on customerId and then orderedAt keeps each customer's orders together, in date order, so one index serves both the filter and the sort.
- A SORT stage in explain means MongoDB sorted the documents itself, in memory, because no index already held them in that order.
- An index also serves queries on its first field, or its first few, which are its prefix. It can't serve a query on a later field alone.
- Put the fields in the order equal, sort, range. Fields matched exactly first, then the field you sort by, then the fields with a range.
- Every index costs space and slows writes, so drop an index that another one's prefix already covers.

Real pages ask for more than one thing at a time. **My orders** wants one customer's orders, newest first. The returns desk wants the returned orders from last month, biggest first. One single-field index can't serve both parts of questions like these, and adding more single-field indexes doesn't fix it. Whenever a query filters on one field and sorts or ranges on another, the index you want is built on both, and the order you list the fields in decides whether it works.

The examples use Riverbend's 100,000 orders from *Why Queries Get Slow, and What an Index Is*. In `mongosh`, after `use riverbend`, load them again and add the `customerId` index back:

```mongodb
load("riverbend-big-store.js")
```

```text
products: 12, customers: 2000, orders: 100000
true
```

```mongodb
db.orders.createIndex({ customerId: 1 })
```

```text
customerId_1
```

## One field isn't enough: a sort in memory

**My orders** shows the newest order first, and explain shows how MongoDB does it with the index it has. As in *Reading explain(): COLLSCAN or IXSCAN*, the report keeps only the fields that matter:

```mongodb
db.orders.find({ customerId: 42 }).sort({ orderedAt: -1 }).explain("executionStats")
```

```text
{
  ...
  queryPlanner: {
    ...
    winningPlan: {
      queryPlan: {
        stage: 'SORT',
        ...
        sortPattern: { orderedAt: -1 },
        ...
        inputStage: {
          stage: 'FETCH',
          ...
          inputStage: {
            stage: 'IXSCAN',
            ...
            indexName: 'customerId_1',
            ...
          }
        }
      },
      ...
    },
    ...
  },
  ...
}
```

Read it from the inside out: `IXSCAN` found customer 42's entries, `FETCH` fetched the 50 orders, and then `SORT` put them in date order itself, in memory. The index knows the customer but not the date, so MongoDB had to sort. For 50 orders that's quick; for a list of thousands it's work on every visit, and it grows with the list.

## An index on two fields

A phone book is sorted by last name, and within each last name by first name. Finding "Garcia, Maya" takes one look, and every Garcia sits together in first-name order. A **compound index** works the same way: its entries are in order by its first field, and within each value of that field, by the second:

```mongodb
db.orders.createIndex({ customerId: 1, orderedAt: -1 })
```

```text
customerId_1_orderedAt_-1
```

Its name lists both fields with their directions. Customer 42's entries now sit together, newest first, so MongoDB reads them in the order the page wants:

```mongodb
db.orders.find({ customerId: 42 }).sort({ orderedAt: -1 }).explain("executionStats")
```

```text
{
  ...
  queryPlanner: {
    ...
    winningPlan: {
      queryPlan: {
        stage: 'FETCH',
        ...
        inputStage: {
          stage: 'IXSCAN',
          ...
          keyPattern: { customerId: 1, orderedAt: -1 },
          indexName: 'customerId_1_orderedAt_-1',
          ...
        }
      },
      ...
    },
    rejectedPlans: [
      ...
    ]
  },
  ...
}
```

No `SORT` stage. The plan that used `customerId_1` and sorted in memory lost, and it's listed in `rejectedPlans`.

![Index entries for customerId then orderedAt, drawn as three groups in a column. Customer 41's entries come first and customer 43's last, each shown as gray lines. Customer 42's group, in the middle, is highlighted, and its entries read top to bottom: 42, 2026-09-10; 42, 2026-08-29; 42, 2026-08-16; and more below. A label beside the group reads: one customer's orders, already newest first.](https://fsdtrainingmedia.blob.core.windows.net/public/images/2026/11/fc822e37c640440fac092db22085fdaf.webp)

## The prefix: the fields at the start

A phone book also finds every Garcia, but it can't find every Maya: the first names are scattered through the book. An index works from its first field the same way. Its first field, or its first few, are its **prefix**, and the index serves a query on its prefix. So `customerId_1` isn't needed anymore:

```mongodb
db.orders.dropIndex("customerId_1")
```

```text
{ nIndexesWas: 3, ok: 1 }
```

```mongodb
db.orders.find({ customerId: 42 }).explain("executionStats").executionStats.totalDocsExamined
```

```text
50
```

But a query on `orderedAt` alone, every order in March, can't use the index: it reads every document.

```mongodb
db.orders.find({ orderedAt: { $gte: ISODate("2026-03-01"), $lt: ISODate("2026-04-01") } }).explain("executionStats").executionStats.totalDocsExamined
```

```text
100000
```

## The order of fields: equal, sort, range

The returns desk asks for the returned orders in March 2026: an exact value for `status` and a range for `orderedAt`. There are 709:

```mongodb
db.orders.countDocuments({ status: "returned", orderedAt: { $gte: ISODate("2026-03-01"), $lt: ISODate("2026-04-01") } })
```

```text
709
```

Try an index with the date first, and count the index entries MongoDB reads:

```mongodb
db.orders.createIndex({ orderedAt: 1, status: 1 })
db.orders.find({ status: "returned", orderedAt: { $gte: ISODate("2026-03-01"), $lt: ISODate("2026-04-01") } }).explain("executionStats").executionStats.totalKeysExamined
```

```text
orderedAt_1_status_1
4960
```

March's entries sit together, but every status is mixed in among them, so MongoDB read all 4,960 of March's entries to keep 709. Now status first:

```mongodb
db.orders.createIndex({ status: 1, orderedAt: 1 })
db.orders.find({ status: "returned", orderedAt: { $gte: ISODate("2026-03-01"), $lt: ISODate("2026-04-01") } }).explain("executionStats").executionStats.totalKeysExamined
```

```text
status_1_orderedAt_1
709
```

Every returned order sits together, in date order, so March's returns are one unbroken stretch: 709 read, 709 kept. A sort adds one more place. The five biggest returns in March, by `total`, need an index with the sort field between the two:

```mongodb
db.orders.createIndex({ status: 1, total: -1, orderedAt: 1 })
db.orders.find({ status: "returned", orderedAt: { $gte: ISODate("2026-03-01"), $lt: ISODate("2026-04-01") } }).sort({ total: -1 }).limit(5).explain("executionStats").executionStats.totalKeysExamined
```

```text
status_1_total_-1_orderedAt_1
20
```

With `status_1_orderedAt_1`, this query read 709 entries and sorted them in memory. This index keeps the returned orders biggest first, so MongoDB read down from the top, skipped the ones not in March, and stopped at the fifth: 20 entries. That's the rule for ordering an index's fields, often called the **ESR rule**:

1. **Equality:** the fields the query matches exactly, such as `status: "returned"`.
2. **Sort:** the field it sorts by, such as `total`.
3. **Range:** the fields with `$gte`, `$lt` and the like, such as `orderedAt`.

## Too many indexes

The orders now have five indexes, and every new order writes an entry into each of them. `orderedAt_1_status_1` loses to `status_1_orderedAt_1` every time, so it only costs:

```mongodb
db.orders.dropIndex("orderedAt_1_status_1")
```

```text
{ nIndexesWas: 5, ok: 1 }
```

Before adding an index, check `getIndexes()` for one that already starts with the fields you need.

## Check yourself

2 questions to try on the lesson page.

## So what

One well-ordered compound index can serve a page's filter, its sort and its range at once. Equal, sort, range is the order to write the fields in, and explain is how you check you got it right.

## Key terms

- [Compound Index](https://www.ask-codey.com/glossary/compound-index)
- [Index Prefix](https://www.ask-codey.com/glossary/index-prefix)
- [ESR Rule](https://www.ask-codey.com/glossary/esr-rule)
- [sort, limit and skip](https://www.ask-codey.com/glossary/sort-limit-and-skip)

## Related

- [Reading explain(): COLLSCAN or IXSCAN](https://www.ask-codey.com/lessons/mongodb-reading-explain)
- [Sorting and Paging: sort, limit and skip](https://www.ask-codey.com/lessons/mongodb-sorting-and-paging)

Source: https://www.ask-codey.com/lessons/mongodb-compound-indexes
