Compound index and field order in MongoDB - Deep Dive

Overview - Compound index and field order

What is it?

A compound index in MongoDB is an index that includes more than one field from documents in a collection. It helps speed up queries that filter or sort on multiple fields together. The order of fields in the compound index matters because MongoDB uses the index from left to right to match query conditions.

Why it matters

Without compound indexes, queries that filter on multiple fields would be slower because MongoDB would have to scan many documents. Compound indexes make these queries efficient by allowing MongoDB to quickly find matching documents using the combined fields. If field order is wrong, the index might not be used effectively, causing slower queries and higher resource use.

Where it fits

Before learning compound indexes, you should understand single-field indexes and basic MongoDB queries. After mastering compound indexes, you can learn about index types like text indexes, wildcard indexes, and how to analyze query performance with explain plans.

Mental Model

Core Idea

A compound index is like a multi-keyed address book sorted first by one key, then by the next, letting you quickly find entries matching multiple criteria in order.

Think of it like...

Imagine a phone book sorted first by last name, then by first name. If you look for someone by last name and first name, you find them quickly because the book is organized in that order. But if you search by first name only, the book is less helpful because it’s sorted starting with last names.

Compound Index Structure:
┌─────────────┬─────────────┬─────────────┐
│ Field 1     │ Field 2     │ Field 3     │
├─────────────┼─────────────┼─────────────┤
│ valueA1     │ valueB1     │ valueC1     │
│ valueA1     │ valueB2     │ valueC2     │
│ valueA2     │ valueB1     │ valueC3     │
└─────────────┴─────────────┴─────────────┘

Queries use fields from left to right to filter efficiently.

Build-Up - 7 Steps

1

Foundation

Understanding Single-Field Indexes

Concept: Learn what a single-field index is and how it speeds up queries on one field.

A single-field index in MongoDB is like a sorted list of values for one field. When you query by that field, MongoDB can quickly find matching documents without scanning the whole collection. For example, an index on { age: 1 } sorts documents by age ascending.

Result

Queries filtering on the indexed field run faster because MongoDB uses the index to jump directly to matching documents.

Understanding single-field indexes is essential because compound indexes build on this idea by combining multiple fields.

2

Foundation

Basics of Compound Indexes

Concept: Introduce compound indexes as indexes on multiple fields combined in a specific order.

A compound index is created on multiple fields, like { lastName: 1, firstName: 1 }. MongoDB sorts documents first by lastName, then by firstName within each lastName. This helps queries that filter or sort on both fields together.

Result

Queries using both fields in the order of the index run efficiently, using the compound index.

Knowing that compound indexes combine fields in order helps you understand why field order matters.

3

Intermediate

How Field Order Affects Query Use

🤔Before reading on: Do you think MongoDB can use a compound index if the query filters only on the second field? Commit to your answer.

Concept: Explain that MongoDB uses compound indexes from left to right, so queries must include the first field(s) to use the index effectively.

If you have a compound index on { a: 1, b: 1 }, a query filtering on 'a' or on 'a' and 'b' can use the index. But a query filtering only on 'b' cannot use this index efficiently because 'a' is the first field in the index order.

Result

Queries missing the first indexed field(s) will not benefit from the compound index and may be slower.

Understanding the left-to-right usage of compound indexes prevents mistakes in index design and query writing.

4

Intermediate

Prefix Rule and Index Prefixes

🤔Before reading on: Can a compound index on {x, y, z} speed up queries on just {x, y}? Commit to your answer.

Concept: Introduce the prefix rule: any leftmost subset of fields in a compound index can be used for queries.

A compound index on { x: 1, y: 1, z: 1 } can speed up queries filtering on { x }, { x, y }, or { x, y, z }. But queries on { y, z } or { z } alone cannot use this index efficiently.

Result

Queries using index prefixes run faster; others do not benefit from the compound index.

Knowing the prefix rule helps you design indexes that cover your most common query patterns.

5

Intermediate

Sorting and Compound Indexes

Concept: Explain how compound indexes can also speed up sorting operations if the sort order matches the index field order.

If you have a compound index on { age: 1, score: -1 }, a query sorting by age ascending and score descending can use the index to avoid an extra sort step. But if the sort order or fields differ, the index may not help sorting.

Result

Queries with matching sort orders run faster because MongoDB uses the index to return sorted results directly.

Understanding sorting with compound indexes helps optimize both filtering and sorting in queries.

6

Advanced

Index Intersection vs Compound Index

🤔Before reading on: Do you think MongoDB can combine two single-field indexes as efficiently as one compound index? Commit to your answer.

Concept: Explain the difference between using multiple single-field indexes together (index intersection) and a single compound index.

MongoDB can combine single-field indexes on fields 'a' and 'b' to answer queries filtering on both. But this is usually less efficient than a compound index on { a, b } because the compound index stores combined field values in order.

Result

Compound indexes generally provide better performance for multi-field queries than index intersection.

Knowing the performance difference guides better index design for complex queries.

7

Expert

Sparse and Partial Compound Indexes Impact

🤔Before reading on: Can a sparse compound index speed up queries on missing fields? Commit to your answer.

Concept: Explore how sparse and partial indexes affect compound indexes and query behavior.

Sparse compound indexes only include documents where all indexed fields exist, which can reduce index size but may exclude some documents. Partial indexes include documents matching a filter condition. Both affect which queries can use the index and how results are returned.

Result

Using sparse or partial compound indexes can improve performance but requires careful query design to avoid missing documents.

Understanding these index types prevents subtle bugs and helps optimize storage and query speed in production.

Under the Hood

MongoDB stores compound indexes as B-tree structures sorted by the combined values of the indexed fields in the specified order. When a query runs, MongoDB traverses the B-tree from the root, comparing query field values to index entries from left to right. This allows efficient skipping of non-matching branches. If the query does not include the leftmost fields, the B-tree traversal cannot narrow down results effectively, so the index is not used.

Why designed this way?

The left-to-right ordering in compound indexes reflects how B-trees work best with sorted keys. This design balances index size, update cost, and query speed. Alternatives like unordered indexes or multiple single-field indexes would be less efficient for multi-field queries. The prefix rule simplifies index usage and reduces the number of indexes needed.

Compound Index B-Tree Traversal:

Root
 │
 ├─ Compare Field1
 │    ├─ Match Field1 Value
 │    │    ├─ Compare Field2
 │    │    │    ├─ Match Field2 Value
 │    │    │    │    ├─ Compare Field3
 │    │    │    │    │    ├─ Match Field3 Value
 │    │    │    │    │    │    └─ Return Documents
 │    │    │    │    │    └─ No Match → Skip Branch
 │    │    │    │    └─ No Match → Skip Branch
 │    │    │    └─ No Match → Skip Branch
 │    │    └─ No Match → Skip Branch
 │    └─ No Match → Skip Branch
 └─ No Match → Skip Branch

Myth Busters - 4 Common Misconceptions

Quick: Can a compound index on {a, b} speed up queries filtering only on b? Commit to yes or no.

Common Belief: A compound index on {a, b} can speed up queries filtering only on b because both fields are indexed.

Reality: MongoDB uses compound indexes from left to right, so queries filtering only on b cannot efficiently use the index on {a, b}.

Why it matters: Believing otherwise leads to missing indexes for important queries and poor performance.

Quick: Does changing the order of fields in a compound index have no effect on query speed? Commit to yes or no.

Common Belief: The order of fields in a compound index does not affect query performance as long as the fields are included.

Reality: Field order is critical; MongoDB uses the index starting from the first field. Changing order can make the index useless for some queries.

Why it matters: Ignoring field order can cause slow queries and wasted storage on ineffective indexes.

Quick: Can MongoDB combine multiple single-field indexes as efficiently as one compound index? Commit to yes or no.

Common Belief: Using multiple single-field indexes together is just as fast as a compound index for multi-field queries.

Reality: Compound indexes are generally more efficient because they store combined field values in order, reducing query complexity.

Why it matters: Relying on index intersection can cause slower queries and higher resource use.

Quick: Does a sparse compound index include all documents regardless of missing fields? Commit to yes or no.

Common Belief: Sparse compound indexes include all documents even if some indexed fields are missing.

Reality: Sparse indexes only include documents where all indexed fields exist, potentially excluding some documents.

Why it matters: Misunderstanding this can cause queries to miss documents unexpectedly.

Expert Zone

1

Compound indexes can support queries on any prefix of the indexed fields, but not on arbitrary subsets, which affects index design for complex queries.

2

The order of fields in a compound index also affects sort operations; matching the sort order to the index order can avoid in-memory sorting.

3

Sparse and partial compound indexes reduce index size but require careful query design to avoid missing documents, especially when some fields are optional.

When NOT to use

Compound indexes are not ideal when queries filter on non-prefix fields only or when fields have very high cardinality and low selectivity individually. In such cases, consider single-field indexes, index intersection, or specialized index types like hashed or text indexes.

Production Patterns

In production, compound indexes are designed based on query patterns, often covering frequent multi-field filters and sorts. Indexes are monitored and adjusted using explain plans and performance metrics. Partial and sparse compound indexes are used to optimize storage and speed for documents with optional fields.

Connections

B-tree Data Structure

Compound indexes use B-tree structures internally to store sorted keys.

Understanding B-trees helps grasp why compound indexes are ordered and why field order matters for efficient searching.

Sorting Algorithms

Compound indexes can optimize sorting by returning results already sorted according to index order.

Knowing sorting principles clarifies how indexes can eliminate extra sorting steps in queries.

Library Cataloging Systems

Like compound indexes, library catalogs organize books by multiple criteria (author, then title) to find items quickly.

Recognizing this connection shows how organizing data by multiple keys improves search speed in many fields.

Common Pitfalls

#1 Creating a compound index with fields in the wrong order for your queries.

Wrong approach:

db.collection.createIndex({ b: 1, a: 1 })
// But queries filter mostly on 'a' first, then 'b'.

Correct approach:

db.collection.createIndex({ a: 1, b: 1 })
// Matches query filter order for efficient use.

Root cause:

Misunderstanding that MongoDB uses compound indexes from left to right, so field order must match query patterns.

#2 Expecting a compound index to speed up queries filtering only on the second or later fields.

Wrong approach:

Query: db.collection.find({ b: 5 })
Index: { a: 1, b: 1 }
// Expecting index use but it is ignored.

Correct approach:

Add an index on { b: 1 } if queries filter only on 'b', or include 'a' in queries to use compound index.

Root cause:

Not knowing that queries must include the leftmost fields of a compound index to use it.

#3 Creating multiple single-field indexes instead of a compound index for multi-field queries.

Wrong approach:

db.collection.createIndex({ a: 1 })
db.collection.createIndex({ b: 1 })
// Expecting fast queries filtering on both 'a' and 'b'.

Correct approach:

db.collection.createIndex({ a: 1, b: 1 })
// Compound index supports multi-field queries efficiently.

Root cause:

Overestimating index intersection performance compared to compound indexes.

Key Takeaways

Compound indexes in MongoDB index multiple fields in a specific order to speed up queries filtering or sorting on those fields together.

The order of fields in a compound index is critical because MongoDB uses the index from left to right and can only use prefixes of the index.

Queries that do not include the leftmost fields of a compound index cannot use it efficiently, which can cause slow query performance.

Compound indexes can also optimize sorting if the sort order matches the index field order, avoiding extra sorting steps.

Understanding how compound indexes work internally and their limitations helps design better indexes and write faster queries in real-world applications.