Why Indexes Matter

An index in MongoDB is a separate, ordered data structure that lets the query engine find matching documents without looking at every document in a collection. Without the right index, a query that should take a few milliseconds can take seconds or minutes once a collection grows past a few thousand documents. This lesson explains what actually happens on disk and in memory when a query runs with and without an index, so you can reason about performance instead of guessing.

Overview: How Indexes Work

A MongoDB collection is just a pile of BSON documents with no guaranteed order. When you run db.orders.find({ status: "shipped" }) and there is no usable index, MongoDB has exactly one option: read every document in the collection, check its status field, and keep the ones that match. This is called a collection scan (COLLSCAN). It is O(n) in the number of documents, and it happens no matter how few documents actually match — the engine cannot know a document doesn’t match without opening it.

An index solves this by maintaining a separate structure — a B-tree in MongoDB’s case — that stores the indexed field’s values in sorted order, with each entry pointing back to the document’s location on disk (its RecordId). When you query on an indexed field, MongoDB walks the B-tree to find the matching values directly, then fetches only those documents. This is an index scan (IXSCAN), and it is O(log n) to locate the starting point plus O(k) to read the k matching entries — dramatically cheaper than reading all n documents when k is small relative to n.

Every collection automatically gets one index for free: a unique index on _id. Every other index you want — on status, on email, on a combination of fields — you create explicitly with createIndex(). The tradeoff is that indexes are not free: each one takes disk space and memory, and every write (insert, update, delete) that touches an indexed field must also update every index that includes that field. Indexes trade write cost and storage for read speed, which is why choosing which fields to index is a design decision, not something you do reflexively to every field.

Index type	Created with	Use case
Single field	{ status: 1 }	Equality or range filters on one field
Compound	{ customerId: 1, status: 1 }	Queries that filter/sort on multiple fields together
Multikey	{ tags: 1 } (field holding an array)	Automatically created when the indexed field is an array
Text	{ description: "text" }	Basic full-text search
Unique	{ email: 1 }, { unique: true }	Enforce no duplicate values

Syntax

db.<collection>.createIndex( <keyPattern>, <options> );
db.<collection>.find( <query> ).explain( <verbosity> );

• keyPattern — an object mapping field names to 1 (ascending) or -1 (descending), e.g. { status: 1 } or { customerId: 1, orderDate: -1 } for a compound index. Direction only matters for sorting and for compound indexes used with multi-field sorts.
• options — an optional object: { unique: true } rejects duplicate values, { name: "myIndexName" } gives it a custom name, { partialFilterExpression: {...} } indexes only documents matching a condition, { expireAfterSeconds: n } creates a TTL index.
• verbosity (for explain()) — one of "queryPlanner" (default, shows the chosen plan without running it), "executionStats" (runs the query and reports documents examined, time taken, and index usage), or "allPlansExecution" (also shows plans the optimizer rejected).

Examples

Example 1: A query without an index

Suppose db.orders has a million documents and no index on status.

db.orders.find({ status: "shipped" }).explain("executionStats");

Output:

{
  queryPlanner: {
    winningPlan: {
      stage: 'COLLSCAN',
      filter: { status: { '$eq': 'shipped' } },
      direction: 'forward'
    }
  },
  executionStats: {
    nReturned: 241830,
    executionTimeMillis: 812,
    totalKeysExamined: 0,
    totalDocsExamined: 1000000
  }
}

The stage: 'COLLSCAN' confirms MongoDB read all 1,000,000 documents (totalDocsExamined) to return roughly 240,000 matches, taking over 800ms. totalKeysExamined: 0 means no index was used at all.

Example 2: The same query with an index

db.orders.createIndex({ status: 1 });

db.orders.find({ status: "shipped" }).explain("executionStats");

Output:

// createIndex result
'status_1'

// explain result
{
  queryPlanner: {
    winningPlan: {
      stage: 'FETCH',
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: { status: 1 },
        indexName: 'status_1',
        direction: 'forward'
      }
    }
  },
  executionStats: {
    nReturned: 241830,
    executionTimeMillis: 96,
    totalKeysExamined: 241830,
    totalDocsExamined: 241830
  }
}

Now the plan is IXSCAN feeding a FETCH stage. MongoDB examined only the 241,830 index entries that actually matched "shipped", fetched exactly that many documents, and finished in roughly 96ms instead of 812ms — about 8x faster, and the gap widens as the collection grows because the scan is no longer proportional to total collection size.

Example 3: A compound index for filter + sort

A common real query filters on a customer and status, then sorts by date — a single-field index can’t fully serve this efficiently.

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

db.orders
  .find({ customerId: ObjectId("64f1a2b3c4d5e6f7a8b9c0d1"), status: "shipped" })
  .sort({ orderDate: -1 })
  .explain("executionStats");

Output:

{
  queryPlanner: {
    winningPlan: {
      stage: 'FETCH',
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: { customerId: 1, status: 1, orderDate: -1 },
        indexName: 'customerId_1_status_1_orderDate_-1',
        direction: 'forward'
      }
    }
  },
  executionStats: {
    nReturned: 14,
    executionTimeMillis: 1,
    totalKeysExamined: 14,
    totalDocsExamined: 14
  }
}

Notice there is no separate SORT stage — because orderDate is the last key in the compound index and it’s already stored in descending order, MongoDB returns results pre-sorted straight from the index. This field ordering follows the ESR rule: put Equality fields first, then Sort fields, then Range fields.

How It Works Step by Step

1. You send a query. The query planner looks at the query shape (which fields are filtered, sorted) and checks which indexes on the collection could satisfy it.
2. If multiple indexes could work, the planner runs a short trial of each candidate plan and caches the plan that examines the fewest documents/index keys as the “winning plan” for that query shape.
3. For an IXSCAN, MongoDB walks the index’s B-tree to the first matching key, then reads forward (or backward) through consecutive keys until the range is exhausted — each key holds a pointer to the document’s location.
4. Each pointer triggers a FETCH: the actual document is read from the collection’s storage and, if there’s a remaining filter the index couldn’t fully cover, re-checked against it.
5. If no index matches the query shape at all, the planner falls back to COLLSCAN, iterating every document in natural (roughly insertion) order.
6. On writes, every index containing the written field(s) is updated synchronously as part of the write — this is why an over-indexed collection can slow down insertOne/updateOne even though reads get faster.

Common Mistakes

Mistake 1: Querying a large collection with no supporting index

This is by far the most common cause of “MongoDB is slow” reports. It’s easy to miss because it works fine on a development database with a few hundred documents and only becomes visible at production scale.

// No index on email — fine at 500 docs, a COLLSCAN disaster at 5,000,000
db.users.find({ email: "jane@example.com" });

Fix: index the fields you actually query on, and verify with explain() rather than assuming.

db.users.createIndex({ email: 1 }, { unique: true });
db.users.find({ email: "jane@example.com" }).explain("executionStats");
// confirm winningPlan.inputStage.stage === 'IXSCAN'

Mistake 2: Wrong field order in a compound index

A compound index’s field order determines which query shapes it can serve efficiently. Putting a range/sort field before the equality fields breaks the ESR rule and forces MongoDB to scan far more index entries than necessary.

// Wrong: orderDate (a range/sort field) placed before the equality field
db.orders.createIndex({ orderDate: -1, customerId: 1 });

// This query filters customerId (equality) then sorts orderDate,
// but the index above can't narrow by customerId first —
// MongoDB must scan a wide swath of the index and filter in memory.
db.orders.find({ customerId: ObjectId("64f1a2b3c4d5e6f7a8b9c0d1") }).sort({ orderDate: -1 });

// Correct: equality field(s) first, then sort field
db.orders.createIndex({ customerId: 1, orderDate: -1 });

Mistake 3: Creating an index for every field “just in case”

Each extra index adds write overhead and memory pressure (indexes should generally fit in RAM to stay fast). Check which indexes actually get used before keeping them:

db.orders.aggregate([{ $indexStats: {} }]);
// look for entries with accesses.ops close to 0 — candidates to drop
db.orders.dropIndex("orderDate_-1_customerId_1");

Best Practices

• Always check explain("executionStats") on new or slow queries — don’t assume a query is index-backed, verify totalDocsExamined is close to nReturned.
• Follow the ESR rule for compound indexes: Equality fields, then Sort fields, then Range fields.
• Prefer a few well-chosen compound indexes over many overlapping single-field indexes for the same query patterns.
• Use $indexStats periodically to find and drop indexes that are never used.
• Index fields used in $match, sort(), and lookup/join conditions ($lookup local/foreign fields) — not every field in a document.
• Remember indexes must fit comfortably in available RAM to stay fast; a huge index that doesn’t fit in memory causes disk reads on every lookup.
• Use unique: true indexes to enforce data integrity (like email) instead of checking uniqueness in application code.

Practice Exercises

• Given a db.products collection with fields category, price, and name, write and run explain("executionStats") for db.products.find({ category: "electronics", price: { $lt: 500 } }) before and after creating an index. Compare totalDocsExamined in both runs.
• Design a single compound index that serves this query efficiently, including the sort: db.reviews.find({ productId: someId, rating: { $gte: 4 } }).sort({ createdAt: -1 }). Which field goes first, and why?
• Run db.orders.aggregate([{ $indexStats: {} }]) on a collection with a few indexes you created earlier in this course, and identify any index with near-zero usage that could be dropped.

Summary

• Without a usable index, MongoDB performs a COLLSCAN, reading every document — cost grows linearly with collection size regardless of how few documents match.
• An index is a sorted B-tree structure that lets MongoDB perform an IXSCAN, jumping directly to matching entries instead of scanning everything.
• explain("executionStats") is the tool for confirming which plan actually ran — look at totalDocsExamined vs nReturned and whether the stage is COLLSCAN or IXSCAN.
• Compound indexes should order fields by the ESR rule: Equality, Sort, Range.
• Indexes aren’t free — they cost write throughput, disk space, and memory, so index deliberately and prune unused indexes with $indexStats.