Catalog My learning Blog
MongoDB Tutorial: Schema Design, Indexing and Sharding · Phase 2 · Lesson 7 · 3 min read

MongoDB Indexes Explained

On this page
  1. Symptom: a COLLSCAN reading 200,000 documents
  2. Fix: create an index
  3. Reading explain output: keys and documents examined
  4. MongoDB index types
  5. The write cost of indexes
  6. Check yourself

Symptom: a COLLSCAN reading 200,000 documents

The orders page for a customer gets slower every week. explain shows why. On a test collection of 200,000 orders in MongoDB 9:

JavaScript
db.orders.find({ customerId: 4021, status: "paid" }).explain("executionStats")
Code
stage: COLLSCAN   totalKeysExamined: 0   totalDocsExamined: 200000   nReturned: 1   executionTimeMillis: 26

COLLSCAN means a full collection scan: every document read to return one. The time grows in step with the collection.

Fix: create an index

JavaScript
db.orders.createIndex({ customerId: 1, status: 1 })
Code
stage: FETCH <- IXSCAN   totalKeysExamined: 1   totalDocsExamined: 1   nReturned: 1   executionTimeMillis: 2

IXSCAN means MongoDB walked the index to the matching entries, then FETCHed only those documents. One key examined, one document read.

An index is a separate, sorted B-tree of field values, each pointing to its document. Finding a value takes a few steps down the tree regardless of collection size, and because it's sorted, range queries and sorts on indexed fields are cheap too.

Without an index, MongoDB scans every document; with a B-tree index, it walks the tree to the matching keys and fetches only those documents.
Without an index, MongoDB scans every document; with a B-tree index, it walks the tree to the matching keys and fetches only those documents.

Reading explain output: keys and documents examined

explain("executionStats") is the tool you'll use most (Day 13 goes deeper). Compare three numbers:

  • nReturned: documents returned,
  • totalKeysExamined: index entries read,
  • totalDocsExamined: documents read.

Ideally all three are close. Many more documents examined than returned means the index is missing or doesn't match the query well.

MongoDB index types

TypeFor
Single field{ email: 1 }
Compound{ customerId: 1, placedAt: -1 }: several fields, order matters (Day 8)
MultikeyAutomatically used when an indexed field is an array: one entry per element
Unique{ email: 1 }, { unique: true }: rejects duplicates
PartialIndex only documents matching a filter: { partialFilterExpression: { status: "open" } }
TTLDelete documents after a time (Day 12)
Text, geospatial, wildcardSearch, location, unknown field names (Day 10)

Every collection has a unique index on _id, which you can't drop.

The write cost of indexes

Each index is updated on every insert, on updates that touch indexed fields, and on deletes. Each takes disk and, ideally, fits in RAM along with your working set. Ten indexes on a write-heavy collection can slow writes noticeably.

So index for real queries:

  • Find slow queries with the profiler or slow query log, and in Atlas, the Performance Advisor.
  • Check index usage with $indexStats; drop indexes that nothing uses.
  • Prefer one well-designed compound index over several single-field ones.
  • Build indexes on large production collections at quiet times; builds no longer block reads and writes for the whole duration, but they do add load.

Check yourself

There's no usable index for the query, so MongoDB read all 500,000 documents to return 12. The ratio of documents examined to returned is the key signal. Add an index that matches the query's filter fields.

Create a unique index: db.customers.createIndex({ email: 1 }, { unique: true }). Inserts or updates that would duplicate an email then fail with a duplicate key error. Normalize emails, such as lowercasing, before storing so A@x.com and a@x.com don't both get in.

Every index must be updated on every insert and on updates that touch its fields, so writes get slower, and each index uses disk and memory that competes with your working set. Indexes that no query uses only cost. Check $indexStats and drop unused ones.

An index on an array field. MongoDB creates an index entry for each array element, so queries on any element can use it, but large arrays create many entries.

Run it with .explain("executionStats") and look for an IXSCAN stage, then compare totalKeysExamined and totalDocsExamined with nReturned.