Skip to content

MongoDB index strategies — picking, building, and verifying indexes

How MongoDB indexes are stored, when to use compound or partial or TTL indexes, the ESR rule for compound order, and how to verify with explain().

MongoDB query performance is dominated by index design. A schema that looks identical can serve a query in milliseconds on one cluster and seconds on another — the difference is in the indexes. This reference covers what each index type does, the ESR (Equality-Sort-Range) rule for ordering compound-index keys, the partial-index pattern that saves space without losing coverage, and the explain() output that tells you whether the query actually used the index you built.

How to verify

To see every index on a collection and inspect its usage:

// List all indexes
db.orders.getIndexes()

// Index size on disk
db.orders.stats({indexDetails: true}).indexSizes

// Index usage statistics (since last server restart)
db.orders.aggregate([{ $indexStats: {} }])

// Did this query use an index?
db.orders.find({customer_id: 123, status: "open"}).explain("executionStats")

In explain output, look at winningPlan.stage — IXSCAN is good, COLLSCAN is bad (full collection scan). totalDocsExamined should be close to nReturned; a ratio over 10 means your index is poor.

What’s happening

MongoDB indexes are B-trees stored as part of the WiredTiger storage layer. Each index has its own file on disk and its own portion of the WiredTiger cache. Like in any relational database, an index lets the engine seek directly to documents matching a query rather than scanning the collection.

MongoDB supports several index types:

  • Single-field — the simplest, one field.
  • Compound — multiple fields ordered by the leftmost-prefix rule. The order matters.
  • Multikey — implicit when indexing an array field; one index entry per array element.
  • Text — full-text search support for string fields.
  • Geospatial (2dsphere) — for $near, $geoWithin queries.
  • Hashed — for $hash-based sharding, not for range queries.
  • TTL — auto-deletes documents whose indexed date field has passed the TTL.
  • Partial — indexes only documents matching a filter expression; smaller and faster.
  • Sparse — only indexes documents that have the field; superseded by partial in most cases.
  • Unique — enforces uniqueness on the indexed key.

The ESR rule for compound indexes: order fields so Equality keys come first, then Sort keys, then Range keys. A query like find({status: "open"}).sort({created_at: -1}).limit(20) benefits from a compound index {status: 1, created_at: -1} — status (equality), then created_at (sort).

The procedure

  1. Index single fields that filter or sort frequently.

    db.orders.createIndex({customer_id: 1})
    db.orders.createIndex({created_at: -1})
  2. Index compound keys following ESR. For the query find({customer_id: 123, status: "open"}).sort({created_at: -1}):

    db.orders.createIndex({customer_id: 1, status: 1, created_at: -1})

    The order is: customer_id (equality), status (equality), created_at (sort). The same index covers find({customer_id: 123}).sort({created_at: -1}) and find({customer_id: 123, status: "open"}) because of the leftmost-prefix rule.

  3. Use partial indexes to save space. If 90% of orders are completed and queries only touch open ones, partially index:

    db.orders.createIndex(
      {customer_id: 1, created_at: -1},
      {partialFilterExpression: {status: "open"}}
    )

    The index is smaller and faster to maintain. Queries that include {status: "open"} can use it; queries without that predicate fall back to a full scan.

  4. Use TTL indexes for session, cache, or audit data with a natural expiry.

    db.sessions.createIndex(
      {created_at: 1},
      {expireAfterSeconds: 86400}   // 24 hours
    )

    A background thread deletes documents where created_at + 86400 < now. The deletion is not instant — typically within 60 seconds of expiry.

  5. Build indexes in the background on production. Foreground builds lock the database for the duration; background builds do not but take longer.

    db.orders.createIndex({customer_id: 1, created_at: -1}, {background: true})

    In MongoDB 4.4+, all index builds use the hybrid algorithm which is essentially background by default — the background option still exists but is mostly informational.

  6. Verify the index is actually used.

    db.orders.find({customer_id: 123, status: "open"})
             .sort({created_at: -1})
             .limit(20)
             .explain("executionStats")

    Read the winningPlan — should show IXSCAN with the index name, not COLLSCAN. nReturned, totalKeysExamined, and totalDocsExamined should be small and close in magnitude.

  7. Remove unused indexes. Every index costs memory, write amplification, and recovery time.

    db.orders.aggregate([{$indexStats: {}}]).forEach(idx => {
      print(`${idx.name}: ${idx.accesses.ops} uses since restart`)
    })

    Drop indexes with zero or negligible usage:

    db.orders.dropIndex("legacy_status_1")
  8. Avoid indexes on low-cardinality fields. A field with only 3 distinct values (pending, open, closed) gives the engine little selectivity — the index does not narrow the search much. Compound it with a higher-cardinality field instead.

Common pitfalls

  • A compound index with the wrong field order — for example {created_at: -1, customer_id: 1} cannot serve find({customer_id: 123}) because customer_id is not in the leftmost prefix.
  • Indexing an array field implicitly creates a multikey index — every array element gets an entry. On a document with 1000-element arrays this is enormous; index per-element, not the array as a whole.
  • TTL indexes work on the indexed field’s value plus the TTL. If you store dates as strings the deletion silently does nothing.
  • Unique indexes on a sparse field can fail to insert because two documents missing the field are treated as both having null — use {unique: true, sparse: true} or partial-index with a $exists filter.
  • Background-built indexes in pre-4.4 versions could lag the data if writes overwhelm the build worker — verify with db.currentOp() that the build finished before relying on the index.

In the engagements we run, MongoDB index reviews happen monthly — $indexStats to find unused or under-used indexes, slow-query log analysis to find missing ones, and explain plans for the top-10 application queries baked into a regression harness. Operated under Managed Operations.