MongoDB indexing explained: patterns, pitfalls and fixes

MongoDB indexing explained: patterns, pitfalls and fixes

Your MongoDB query is slow. You check the collection size—millions of documents—and realize the database is scanning every single one. An index would have solved this in milliseconds. MongoDB indexing explained: patterns, pitfalls and fixes is not optional work; it's the difference between a query that completes in 50ms and one that times out in 5 seconds. In this post, we'll walk through why indexes matter, how to build them correctly, and the traps that catch most teams.

Why indexes matter — collection scans are expensive

Without an index, MongoDB performs a collection scan. It opens a cursor, reads document one by one, evaluates your filter, and returns matches. On a collection of 10 million orders, this means 10 million disk reads and CPU cycles just to find 100 matching documents. That's unacceptable in production.

An index is a B-tree structure that stores sorted references to documents. When you query on an indexed field, MongoDB jumps directly to the relevant branch of the tree, reads far fewer documents, and returns results orders of magnitude faster. The cost is memory (indexes live in RAM) and write overhead (every insert, update, or delete must update the index tree too).

Here's the reality: a query without an index might complete in 5 seconds. With the right index, the same query runs in 10ms. For high-traffic applications—think ride-hailing or e-commerce—that difference is the difference between a responsive system and a crashed one.

Creating indexes — single-field and compound

The simplest index covers one field. If you frequently filter orders by customerId, add a single-field index:

db.orders.createIndex({ customerId: 1 })

The 1 means ascending order; -1 means descending. MongoDB doesn't care for equality queries, but descending helps if you sort by that field in your queries.

Most real-world queries filter on multiple fields or combine filtering with sorting. A compound index

db.orders.createIndex({ customerId: 1, createdAt: -1 })

This single index can serve queries like:

• Find all orders for customer 123
• Find all orders for customer 123, sorted by creation date (newest first)
• Find all orders for customer 123 created after 2024-01-01, sorted by date

But it won't help a query that only filters on createdAt (since customerId comes first in the index)—MongoDB can't skip the first field and use the second. This is where the ESR rule becomes critical.

The ESR rule — order matters

When designing a compound index, follow the ESR rule: Equality, Sort, Range. The order of fields in your index should match this pattern:

1. Equality — fields you filter with exact matches
2. Sort — fields you sort by
3. Range — fields you filter with ranges (>, <, between, etc.)

Let's say your query filters orders by status (exact), sorts by createdAt, and ranges on amount (greater than 100). The index should be:

db.orders.createIndex({ status: 1, createdAt: -1, amount: 1 })

Why does order matter? MongoDB traverses the index tree left to right. If createdAt comes before status, MongoDB can't skip status to use createdAt for sorting—it has to scan many index entries before getting sorted results.

Now consider a real e-commerce scenario: you query for orders with status = "shipped", amount > 50, and you want them sorted by createdAt descending. The wrong index looks like:

db.orders.createIndex({ amount: 1, status: 1, createdAt: -1 })

This forces MongoDB to scan all documents where amount > 50 across all statuses, then filter by status, then sort. The correct index is:

db.orders.createIndex({ status: 1, createdAt: -1, amount: 1 })

Now MongoDB finds all status = "shipped" entries (equality), gets them pre-sorted by createdAt, and only checks the amount range on those matching documents. Massive performance gain.

Index costs — memory, writes, and maintenance

Indexes are not free. Each index consumes RAM. On a collection with 100 million documents, a compound index can easily use 500MB to 2GB of memory depending on field sizes. If your cluster runs low on RAM, indexes cause eviction, which tanks performance across the board.

Writes also slow down. When you insert an order, MongoDB doesn't just append it to the collection—it updates every index that touches that document. With 5 indexes on a collection, that insert is 5 times more expensive than on an unindexed collection. In high-volume systems (think trade execution or ride-hailing), this adds up fast.

The fix is ruthless: only create indexes for queries that actually run. Use MongoDB's profiler or your APM tool to log slow queries, analyze them, and index only what matters. A common anti-pattern is creating an index "just in case" someone might query a field. Don't do that.

To see what indexes exist and their memory usage:

db.orders.aggregate([{ $indexStats: {} }])

To check if an index is being used:

db.orders.find({ customerId: 123 }).explain("executionStats")

Look for executionStages.stage. If it says COLLSCAN, MongoDB did a collection scan—your index isn't helping. If it says IXSCAN, you're winning.

Common mistakes

• Ignoring the ESR rule — Creating indexes without thinking about field order. Your compound indexes will be slow and bloated.
• Too many indexes — Trying to index every combination of fields. This wastes memory and slows writes. Profile first, then index.
• Indexes on high-cardinality fields in the wrong order — Putting a range filter (like timestamp) before an equality filter. MongoDB has to scan more index entries than necessary.
• Forgetting partial indexes — If you only query for status = "active", use a partial index to save memory: db.orders.createIndex({ customerId: 1 }, { partialFilterExpression: { status: "active" } }).
• Not monitoring index performance — Creating an index and forgetting about it. Use $indexStats and your profiler to spot unused indexes and delete them.
• Ascending vs. descending confusion — For a single-field index, it doesn't matter. For compound indexes, match your sort direction to avoid in-memory sorting.

Key takeaways

• Indexes transform collection scans into tree traversals, reducing query time from seconds to milliseconds.
• Single-field indexes are simple; compound indexes require the ESR rule: equality first, then sort fields, then range fields.
• Use explain("executionStats") to verify that your query uses an index (IXSCAN, not COLLSCAN).
• Indexes consume memory and slow writes—create them only for queries you actually run.
• Monitor index usage with $indexStats and delete unused indexes regularly.
• Consider partial indexes to reduce memory footprint when filtering on a subset of documents.
• Profile your application and let query patterns drive indexing decisions, not guesswork.