Blog

Compound Index | MongoDB Indexing Tutorial

Key Takeaways

  • A MongoDB compound index stores multiple fields in one ordered structure. Its leading fields determine which queries can efficiently narrow the search.
  • Equality, Sort, Range (ESR) is a starting guideline, not a universal rule. A highly selective range can make Equality, Range, Sort (ERS) worth testing.
  • Validate candidate indexes with explain("executionStats"): inspect keys examined, documents examined, and whether the plan needs a blocking sort.
  • Indexes consume storage and cache space and add write-maintenance work. Keep indexes that earn their operational cost.

A MongoDB compound index is a single index containing multiple fields, arranged in a specified order. It can improve queries that filter or sort on those fields, but the benefit depends on field order, data distribution, and the selected execution plan.

The practical approach is to design an index for a query shape—the combination of filters, sorting, and projection—then measure it against representative data. Start with the examples below, not with a rule that every collection needs more indexes.

Preface: What Is an Index?

An index is a separate structure containing indexed field values and references to documents. The ordinary ascending and descending indexes used here have a B-tree-style structure: ordered keys let MongoDB locate relevant entries without necessarily scanning the entire collection. Text, geospatial, hashed, and other specialized index types have distinct behavior; don't apply every rule in this tutorial to them.

These are mongosh examples. Select a disposable database before creating the introductory indexes; the later comparison includes its own database setup.

db.people.createIndex({name: 1});
db.people.find({name: "Eric"});

The first command creates a single-field ascending index on name. That index is a candidate for the following equality lookup, not a guarantee that an indexed plan will always be fastest. For the foundations, see Introduction to MongoDB Indexing.

What Is a Compound Index?

A compound key behaves like a directory ordered first by one attribute, then by another within matching first attributes. Two separate single-field indexes do not provide that same combined ordering.

Creating a Compound Index in MongoDB

db.people.createIndex({name: 1, email: -1});

db.people.find({name: "Sam", email: "fred@example.com"});

Here, 1 means ascending and -1 means descending. The index orders entries by name ascending, then by email descending within each name; it does not physically reorder the collection. The equality query can use both indexed fields to locate matching entries.

For additional creation examples, see our MongoDB create index tutorial.

MongoDB Compound Index Order and Prefixes

A prefix is an initial sequence of index fields, with no skipped leading field. Consider this separate index:

db.people.createIndex({name: 1, email: 1, address: 1});
Index key order: (name ASC, email ASC, address ASC)

Example ordered keys:
(Eric, eric@example.com, Boston)
(Sam,  a@example.com,    Austin)
(Sam,  z@example.com,    Denver)

Usable leading field sets:
(name)
(name, email)
(name, email, address)

(email) alone: matching entries may be spread across name groups

The index can efficiently target its leading field sets. A query on name and address can still use the name prefix, but skipping email can require examining more keys than an index on {name: 1, address: 1}.

db.people.find({email: "sam@example.com"});

Without name, this email-only query generally cannot make an efficient targeted seek using the leading key. MongoDB may still choose an index scan, so “the index cannot be used” is too absolute; inspect the plan and scan counts. If email-only or address-only lookups are important, test an index beginning with that field.

Compound Sort Direction: Forward or Complete Reverse

For {name: 1, email: -1}, the index can supply its declared sort order or the complete reverse. Reversing only one field does not produce a compatible two-field ordering.

// Matches the index ordering.
db.people.find().sort({name: 1, email: -1});

// Complete reverse: also supported by this index.
db.people.find().sort({name: -1, email: 1});

// Not supplied by this index's ordering.
db.people.find().sort({name: 1, email: 1});

// Equality fixes name, so email can be traversed either way.
db.people.find({name: "Sam"}).sort({email: 1});

A sort on a later index field can use index ordering when all preceding fields have equality conditions. These examples assume scalar fields and matching query/index collations; arrays introduce additional restrictions.

When to Use a Compound Index: ESR Versus ERS

Use compound indexes for recurring queries that combine filters, sorting, or both. For a directory query that finds people named Sam below an age threshold and orders them by email, ESR suggests this candidate:

db.people.createIndex(
  {name: 1, email: 1, age: 1},
  {name: "people_esr"}
);

db.people.find({name: "Sam", age: {$lt: 30}})
  .sort({email: 1});

name is equality, email supplies sorting, and age is the range. ESR preserves email ordering within the selected name, but the intervening email field can make the age condition less effective at narrowing the scan. This is an example plan to verify, not a promised performance result.

CandidateKey orderTradeoff for this query
ESR{name: 1, email: 1, age: 1}Can provide email order without a blocking sort, but may examine more keys.
ERS{name: 1, age: 1, email: 1}Can narrow the age range earlier, but generally needs a blocking sort for email.

A blocking sort collects candidate results and sorts them rather than receiving them in index order. Accepting that work can be worthwhile when the range eliminates most candidates. MongoDB's ESR guideline explicitly recommends considering ERS for highly selective ranges.

Ordering Multiple Equality Fields

When a query constrains several fields by exact equality, those fields can generally appear in either order within the equality portion of the index. Choose their order around other queries that need useful leading prefixes, rather than mechanically putting the highest-cardinality field first. The order of properties in the query filter does not need to mirror the index definition.

Runnable Comparison with Skewed Data

This synthetic dataset deliberately makes young Sams rare. It creates 10,000 documents, including 5,000 named Sam; 50 of those Sams match age < 30, while all 5,000 match age < 60. Those are properties of the generated fixture, not measured benchmark results.

// Paste into mongosh connected to a disposable deployment.
{
  const lab = db.getSiblingDB("stackchief_compound_lab");
  lab.people.drop();

  const documents = [];
  for (let i = 0; i < 10000; i++) {
    documents[i] = {
      name: i < 5000 ? "Sam" : "Eric",
      email: "person" + ((i * 7919) % 10000) + "@example.com",
      age: i % 100 === 0 ? 20 : 45
    };
  }
  lab.people.insertMany(documents);

  lab.people.createIndex(
    {name: 1, email: 1, age: 1},
    {name: "people_esr"}
  );
  lab.people.createIndex(
    {name: 1, age: 1, email: 1},
    {name: "people_ers"}
  );

  for (const threshold of [30, 60]) {
    for (const indexName of ["people_esr", "people_ers"]) {
      const result = lab.people
        .find({name: "Sam", age: {$lt: threshold}})
        .sort({email: 1})
        .hint(indexName)
        .explain("executionStats");

      printjson({
        threshold,
        indexName,
        winningPlan: result.queryPlanner.winningPlan,
        executionStats: result.executionStats
      });
    }
  }

  // Also inspect the planner's choice without forcing an index.
  printjson(lab.people
    .find({name: "Sam", age: {$lt: 30}})
    .sort({email: 1})
    .explain("executionStats"));
}

hint(indexName) forces each candidate during the comparison. Without a hint, explain("executionStats") reports the selected plan and executes it to completion to collect statistics.

Compare both thresholds, then replace the fixture with anonymized production-like data. Include common and rare names, realistic age distributions, the actual projection, and any application limit. Don't promote a hint into application code just because it won one synthetic test.

How to Read the Explain Results

Use MongoDB's explain results reference to inspect these signals:

  • IXSCAN versus COLLSCAN: an index scan versus a collection scan. An index scan alone does not prove efficiency.
  • totalKeysExamined: index entries scanned. Compare it with nReturned, the number of returned documents.
  • totalDocsExamined: document examinations, including work performed by fetch and collection-scan stages.
  • SORT: an explicit blocking sort in the selected plan. Inspect the winning plan, not just rejected alternatives.
  • Index bounds: the key intervals searched. Check whether the scan fixes name and how effectively it restricts age.

Stage nesting varies by execution engine and release. Compare scan counts and plan structure before treating a small difference in executionTimeMillis as meaningful.

Two Birds, One Stone: Prefix Reuse and Covered Queries

The {name: 1, email: 1, age: 1} index can serve a name-only lookup because name is its leading field. That is prefix reuse; it is not automatically a covered query.

A covered query returns its result entirely from an index without examining collection documents. For an ordinary unsharded collection with scalar fields, the index above can cover this query:

db.people.find({name: "Sam"}, {_id: 0, name: 1});

// Inspect coverage using the index created earlier.
db.people.find({name: "Sam"}, {_id: 0, name: 1})
  .hint("people_esr")
  .explain("executionStats");

The projection—the second argument to find()—requests only name and excludes _id. MongoDB returns _id by default, but this compound index does not contain it. Returning an unindexed field requires fetching documents.

For a matching covered execution, expect zero document examinations and an index scan without a document-fetch stage. Verify the plan rather than inferring coverage from the index definition. Our MongoDB projections tutorial explains field selection in more detail.

A separate {name: 1} index is often redundant, but don't delete it blindly. Compare options such as uniqueness, sparse or partial filtering, and collation, then check workload usage and whether the smaller index has a measured benefit.

Compound Index Performance and Constraints

Cardinality Is Not the Same as Selectivity

Cardinality is the number of distinct values; selectivity describes how narrowly a query filters documents. A boolean has low cardinality, yet a query for a rare boolean value can still be highly selective. A common value can match most of the collection and provide little filtering benefit.

Don't exclude status fields or flags from compound indexes just because they have few values. Evaluate their position, the combined predicates, query frequency, and actual keys and documents examined. See our MongoDB index performance guide for the broader tradeoffs.

Storage, Cache, and Write Costs

Indexes store key values and document references, consuming disk and competing for cache space. Inserts and deletes maintain affected index entries; changing an indexed value also requires index maintenance. Adding fields for coverage therefore trades possible read savings against a larger index and additional write work.

Options and Data Types Can Change Applicability

  • Partial indexes: index only documents matching partialFilterExpression. A query must imply that filter so using the index won't omit results.
  • Unique indexes: unique: true on a compound index enforces uniqueness of the combined key, not each field individually. Existing conflicting data prevents creation.
  • Multikey indexes: indexing an array creates a multikey index. Each document can have at most one array-valued indexed field in a compound multikey index; array sorting and coverage have additional restrictions.
  • Collation-aware indexes: collation defines string comparison rules. Queries need matching collation to use the index for those string comparisons and ordering.

Production Checklist

  • Record the exact filters, sort, projection, and limit before choosing field order.
  • Test common and rare parameter values. Record returned documents, examined keys/documents, and sort behavior.
  • Budget disk, memory, and deployment time for index builds. Builds can reduce application performance and take exclusive collection locks at their beginning and end.
  • Inventory existing definitions with our MongoDB list indexes tutorial. Check a full workload cycle before removing apparently redundant indexes.

The goal isn't to obey ESR at all costs. It's to keep a small, justified set of indexes that makes your important queries efficient without imposing unnecessary operational work.

FAQ

How do I create a compound index in MongoDB?

In mongosh, run db.people.createIndex({name: 1, email: -1}). This creates one index with name ascending and email descending within each name.

Does the order of fields matter in a MongoDB compound index?

Yes. Field order determines the leading prefixes and which filtering and sorting patterns the index can efficiently support. Properties in the query filter do not need to appear in index order.

Can a compound index be used when a query omits its first field?

MongoDB may still scan the index, but omitting its leading field generally prevents an efficient targeted seek using that leading key. For frequent second-field-only lookups, test an index beginning with that field and compare execution statistics.

How do I check whether MongoDB is using my compound index?

Append .explain("executionStats") to the query and inspect the selected index, scan stages, totalKeysExamined, and totalDocsExamined. For sorted queries, also inspect the winning plan for a blocking SORT stage.

When should I use ESR versus ERS?

Start with ESR when preserving index-provided sort order matters. Test ERS when the range condition eliminates most candidates and sorting the smaller result set could cost less than scanning additional keys.

Further Reading

Written by Sam Erickson

Software engineer. I write every article on StackChief myself. Author of Mastering the Tech Interview. More about me

Related reading