Learning MongoDB - Deep Dive Indexing Strategies

#Introduction

All the queries you've written so far can run without indexes — MongoDB just scans every document. The problem is, "it runs" is a very low standard. When a collection grows to millions of documents, queries without indexes become so slow they're unfit for production. That's the job of an index: making MongoDB find data without having to look at every document.

Episode 12 is the gateway to a truly high-performance MongoDB. The roadmap: first we understand why indexes are crucial, second single field indexes, third compound indexes with the ESR rule, fourth multikey and wildcard indexes, and fifth functional indexes — TTL, unique, and partial. Let's get started.

#Why Indexing Is Crucial

Without an index, MongoDB performs a COLLSCAN (collection scan): reading every document one by one to check for a match. Its complexity is linear, O(N) — multiply two million documents by the read time per document, and the query becomes very slow.

With an index, MongoDB performs an IXSCAN (index scan): searching an ordered B-Tree structure — like looking up a word in a dictionary rather than reading the whole book. Its complexity is logarithmic, O(log N). For 2 million documents, the difference can be hundreds of times:

Collection scan  : O(N)   -> read 2.000.000 documents
Index scan       : O(log N) -> read about 21 steps

The principle is like the table of contents in a big book: an index stores a copy of specific fields in sorted order, along with pointers to the locations of the original documents. A query using an indexed field goes straight to the right location without reading all documents.

Indexes use extra storage space and slightly slow down inserts/updates (because the index must be maintained), but the read advantage they provide far outweighs that cost for read-dominant workloads.

#Single Field Index

The simplest index: one field, sorted ascending (1) or descending (-1):

db.users.createIndex({ email: 1 })
 
db.products.createIndex({ price: -1 })

For queries that filter or sort by a single field — email lookups, price sorting — this index works directly. The sort direction (1/-1) barely matters for equality lookups, but is important for sorting: the { price: -1 } index can serve sort({ price: -1 }) without additional in-memory sorting.

#Compound Index

#Concept

A compound index combines several fields into a single index. Queries that filter or sort combinations of fields — e.g. { category: "elektronik", price: { $lt: 500000 } } — can be resolved entirely with one index, far faster than with two separate indexes:

db.products.createIndex({ category: 1, price: -1 })

The order of fields in a compound index is very important. The index { category: 1, price: -1 } serves queries filtering by category alone, category + price, or sorting by a combination of both — but is not efficient for queries filtering only by price.

#The ESR Rule (Equality, Sort, Range)

To design an optimal compound index, follow the ESR rule:

1. E (Equality) — fields filtered with equality go first.
2. S (Sort) — fields used for sorting go second.
3. R (Range) — fields filtered with a range ($lt, $gt) go last.

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

The index above is designed for the query { userId: "x", createdAt: { $gte: date }, total: { $gt: 100 } } sorted by createdAt — the equality field userId first, then the sort field createdAt, and the range field total last. This allows MongoDB to use a single index for both filtering and sorting without extra operations.

#Multikey Index

When the indexed field is an array, MongoDB automatically creates a multikey index — an index that stores every array element as an entry. You don't need to do anything special; just create a regular index on the array field:

db.products.createIndex({ tags: 1 })

This index turns the query find({ tags: "best-seller" }) into an index scan instead of a collection scan. Multikey indexes happen automatically when MongoDB detects an array on the indexed field. An important note: a multikey index can't be a geospatial component or compose an index over several array fields at once.

#Wildcard Index

A wildcard index indexes all fields of a document without having to list their names one by one. It's a lifesaver for dynamic schemas — e.g. products with unpredictable attributes:

db.products.createIndex({ "$**": 1 })

A wildcard index guarantees that whatever fields you add in the future get indexed automatically. But it uses more space than a selective index — use it as a safety net for documents with dynamic structures, not as a replacement for targeted indexes on fields that your primary queries definitely use.

#Functional Indexes

#TTL Index: Time-Based Auto-Delete

A TTL (Time To Live) index automatically deletes documents after a certain duration since their date field's value. Perfect for session data, temporary logs, or OTP codes:

db.sessions.createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 })

Every hour, a MongoDB monitor thread deletes documents whose createdAt is more than 3600 seconds old. "Expired" data is cleaned up without manual cron jobs. Note: the field used must contain a Date, and TTL doesn't apply to capped collections.

#Unique Index: Guaranteeing Uniqueness

A unique index prevents duplicate values on a field — the equivalent of a PRIMARY KEY or UNIQUE constraint in an RDBMS:

db.users.createIndex({ email: 1 }, { unique: true })

Once this index is created, two documents with the same email will be rejected — the most reliable protection against data duplication, stronger than application validation because it's enforced directly by the database.

#Partial Index: Indexing a Document Subset

A partial index only indexes documents matching partialFilterExpression. It saves space and speeds up inserts because documents outside the criteria aren't indexed:

db.users.createIndex(
  { email: 1 },
  { unique: true, partialFilterExpression: { status: "active" } }
)

The example above combines unique + partial: email uniqueness is only guaranteed among users with an active status. Disabled users can have duplicate emails — behavior that is sometimes desired for soft-deleted users whose emails get reused.

#Conclusion

In episode 12 you understood why indexing changes performance from a O(N) collection scan to a O(log N) index scan, and got to know seven index types: single field, compound with the ESR rule (Equality, Sort, Range), multikey which is automatic for arrays, wildcard for dynamic schemas, TTL for automatic time-based deletion, unique for guaranteeing uniqueness, and partial for indexing only a document subset.

Key takeaways:

• An index changes O(N) to O(log N) — the most impactful investment in MongoDB.
• Arrange compound indexes with the ESR order: Equality, Sort, Range.
• Multikey indexes are created automatically when an array field is indexed.
• TTL indexes clean up expired data automatically; unique indexes prevent duplicates.
• Don't pile up redundant indexes — audit and drop unused ones.

In the next episode, episode 13, we prove all the index theory with real evidence: Query Optimization & explain() Analysis. You'll read the execution plan, distinguish COLLSCAN from IXSCAN, understand winning plans versus rejected plans, and enable the database profiler to find slow queries in production. See you in episode 13!