Indexing & Performance

Indexing & Performance

An index is a separate, sorted data structure (a B-tree in MongoDB) that maps field values to the documents containing them, the way a book's index maps words to page numbers. Without a suitable index, MongoDB must read every document in the collection to answer a query. That's called a collection scan (COLLSCAN). It's harmless on 1,000 documents and ruinous on 50 million.

Indexes aren't free, though: each one uses memory and disk, and every insert, update and delete must also update every index on the collection. Indexing well means creating the few indexes your real queries need, in the right field order.

Creating indexes

db.users.createIndex({ email: 1 }, { unique: true })         // single field; also enforces uniqueness
db.orders.createIndex({ customerId: 1, createdAt: -1 })       // compound: two fields, in order
db.sessions.createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 })   // TTL: auto-delete after 1 hour
db.users.createIndex({ referralCode: 1 }, { unique: true, partialFilterExpression: { referralCode: { $exists: true } } })
db.articles.createIndex({ title: "text", body: "text" })      // full-text search
db.places.createIndex({ location: "2dsphere" })               // geospatial queries

• 1 / -1 is ascending/descending. For a single field it doesn't matter; for compound indexes it matters when sorting on several fields in different directions.
• _id is always indexed automatically.
• Partial indexes only include documents matching a filter. They're smaller and faster, and they let you enforce uniqueness only where a field is present. They're generally preferred over the older sparse indexes.

In Spring Data, @Indexed and @CompoundIndex declare indexes, but automatic index creation is off by default (since Spring Data MongoDB 3.0). Create them deliberately in production (see Spring Data MongoDB).

Reading explain(): is the index being used?

db.orders.find({ customerId: "c42", status: "PAID" })
         .sort({ createdAt: -1 })
         .explain("executionStats")

The fields that matter:

The key ratio is documents examined ÷ documents returned. Examining 50,000 documents to return 20 means the index is missing or in the wrong order, even if the query "uses an index".

Compound indexes: the prefix rule

A compound index { customerId: 1, status: 1, createdAt: -1 } is sorted by customerId, then status within each customer, then createdAt within each status. It can serve queries on any prefix of its fields:

find({ customerId: "c1" })                                   // ✅ uses prefix {customerId}
find({ customerId: "c1", status: "PAID" })                   // ✅ uses prefix {customerId, status}
find({ customerId: "c1", status: "PAID" }).sort({ createdAt: -1 })   // ✅ uses the whole index, no in-memory sort
find({ status: "PAID" })                                     // ❌ status isn't a prefix → can't use it efficiently

So one well-ordered compound index can replace several single-field ones. That's why you design compound indexes around your actual queries rather than indexing every field separately.

The ESR rule: ordering fields in a compound index

For a query that has equality filters, a sort, and a range filter, order the index fields as Equality → Sort → Range:

// Query: a customer's paid orders over 1000, newest first
db.orders.find({ customerId: "c1", status: "PAID", total: { $gt: 1000 } }).sort({ createdAt: -1 })

// ESR index
db.orders.createIndex({ customerId: 1, status: 1, createdAt: -1, total: 1 })
//                      └── Equality ──┘   └─ Sort ─┘     └─ Range ┘

Why: equality fields narrow the index to one contiguous section. Putting the sort field next means that section is already in the right order, so no in-memory sort is needed. A range field placed before the sort field would break the ordering and force a SORT stage.

Covered queries

If every field the query filters on and returns is in the index, MongoDB answers from the index alone without reading any document (totalDocsExamined: 0):

// index { customerId: 1, status: 1 }
db.orders.find({ customerId: "c1" }, { _id: 0, customerId: 1, status: 1 })

You must exclude _id in the projection unless it's part of the index. Covered queries are the fastest reads MongoDB can do, which is useful for hot, narrow lookups.

Patterns that defeat indexes

• Unanchored or case-insensitive regex: /abc/ or /^abc/i must scan all index keys. Only a case-sensitive prefix regex, /^abc/, uses the index efficiently. Use a text index, Atlas Search, or a lowercase copy of the field instead.
• $ne, $nin and $not match most of the index, so they're rarely selective.
• Low-selectivity fields on their own (a boolean, a status with 3 values) narrow little. They're better as the equality part of a compound index than as a standalone index.
• Computed conditions ($expr over two fields, $where) can't use normal indexes.
• Large $skip still walks all skipped entries. Paginate by range (createdAt < lastSeen) instead.

The cost side: don't over-index

• Every write updates every index. A collection with 12 indexes makes inserts noticeably slower.
• Indexes should fit in RAM. When the working set of indexes exceeds memory, reads start hitting disk.
• Find unused indexes with db.collection.aggregate([{ $indexStats: {} }]) and drop them.
• Build indexes on large production collections carefully (off-peak or rolling across replica-set members), and test the impact first.

Follow-up questions this topic invites — and their answers

Q: Why is my query slow even though explain shows IXSCAN? A: An index was used, but not a selective one, or one in the wrong order. Compare totalKeysExamined and totalDocsExamined with nReturned, and check for a SORT stage. Usually the fix is a compound index in ESR order that matches the query's filters and sort.

Q: Do I need a separate index on customerId if I have { customerId: 1, createdAt: -1 }? A: No. The compound index serves queries on its prefix customerId alone. A separate single-field index would only add write cost and memory.

Q: How does MongoDB choose between several possible indexes? A: The query planner runs the candidate plans briefly in a trial, picks the one that returns results fastest, and caches that choice for the query's shape. The cache is cleared when indexes change or the collection changes substantially. You can override it with .hint(), but that's a last resort, because a hint can become wrong as data changes.

Q: What is a TTL index and what are its limits? A: An index on a date field with expireAfterSeconds. A background task deletes documents once that date plus the interval has passed. Deletion runs about once a minute, so documents can live slightly longer than the TTL. It works only on single-field indexes on date values (or arrays of dates).

Q: How do you index for case-insensitive search on a name? A: Either store a normalized copy of the field (nameLower) and index that, or create the index with a collation of strength 2 (case-insensitive) and run queries with the same collation. A case-insensitive regex can't use a normal index efficiently.