Indexes, and why the same rules apply

Here is the good news: almost everything you learned about indexes in the SQL performance module applies to MongoDB unchanged. MongoDB uses B-trees, the leftmost-prefix rule holds, selectivity decides whether an index is used, and writes pay for every index. If module 7 landed, this lesson is mostly confirming that your knowledge transfers, plus the few MongoDB-specific kinds.

The same rules, demonstrated

On a 50,000-document collection, a query with no index scans everything; with one, it touches only the matches:

member_id = 42, no index :  COLLSCAN,  examined 50000,  returned 50
member_id = 42, indexed  :  IXSCAN,    examined 50,     returned 50

That is the SQL story exactly — COLLSCAN is a sequential scan, IXSCAN is an index scan, and the win is examining 50 documents instead of 50,000.

db.big.createIndex({ member_id: 1 })       // 1 ascending, -1 descending

The leftmost-prefix rule is identical. With a compound index { member_id: 1, tag: 1 }:

{ member_id: 42 }              →  IXSCAN   (prefix — uses it)
{ member_id: 42, tag: "tag7" } →  IXSCAN   (full — uses it)
{ tag: "tag7" }                →  COLLSCAN (not a prefix — cannot use it)

Same reason as before: the index is sorted by member_id first, so tag-only has no contiguous range. The column-ordering rules from module 7 — equality before range, then higher cardinality, then match your sort — carry over word for word. Mongo calls this ESR (Equality, Sort, Range), which is the same idea named for the pipeline.

Selectivity still decides. An index on a field where one value covers most documents will be ignored for that value, because a scan is cheaper — exactly as in PostgreSQL.

Reading the plan. db.coll.find(q).explain("executionStats") is EXPLAIN ANALYZE. The fields to read: the winning plan's stage (IXSCAN or COLLSCAN), totalDocsExamined, and nReturned. The number to watch is the ratio of examined to returned — the equivalent of buffers-per-row. Examined ≈ returned is a good index; examined far larger than returned means the index is not selective or not being used.

Covered queries — same as PostgreSQL's index-only scan

If the index contains every field the query needs — filter and projection — MongoDB answers from the index and never touches the document:

db.big.find({ member_id: 42 }, { _id: 0, member_id: 1 }).explain("executionStats")
// → totalDocsExamined: 0

totalDocsExamined: 0 is the proof — the Heap Fetches: 0 of the SQL world. You must exclude _id (it is not in the index unless you put it there) and project only indexed fields. Same technique, same payoff.

The MongoDB-specific kinds

Four index types the document model needs that SQL does not.

Multikey — an index on an array field

Index an array and MongoDB indexes every element, so an array-contains query uses it:

db.books.createIndex({ categories: 1 })
db.books.find({ categories: "fiction" })     // IXSCAN, isMultiKey: true

The plan reports isMultiKey: true. This is what makes array queries fast, and it is automatic — you do not ask for a multikey index, you get one by indexing an array field. The one restriction: a compound index can include at most one array field, because indexing two arrays together would multiply into every combination of their elements.

Text — for word search

db.books.createIndex({ title: "text" })
db.books.find({ $text: { $search: "monsoon" } })     // → Monsoon Physics, The Long Monsoon

Word-level search with stemming — "monsoon" matches "Monsoon". This is MongoDB's equivalent of PostgreSQL's to_tsvector/GIN. It is fine for basic search; for anything serious you reach for a real search engine, which is module 12. A collection may have only one text index, though it can span several fields.

Partial — index only some documents

The same idea and the same payoff as PostgreSQL's partial index:

db.big.createIndex({ amount: 1 }, { partialFilterExpression: { city: "Pune" } })

{ amount: 100, city: "Pune" }  →  IXSCAN   (query implies the condition)
{ amount: 100 }                →  COLLSCAN (does not imply it — cannot use the partial index)

Smaller index, no write cost for excluded documents, and — exactly as in SQL — the query must imply the partial filter or the index is not eligible. MongoDB's older sparse index (index only documents where the field exists) is a special case; prefer partial, which is more general.

TTL — a partial index that deletes

One MongoDB has that SQL does not, and it is genuinely handy:

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

A background task deletes documents once the indexed date is older than the expiry. Perfect for sessions, carts, verification tokens — anything with a natural lifespan. It is the housekeeping you would otherwise write a cron job for, and it previews the expiry ideas in the Redis module.

Unique — a constraint, as in SQL

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

Enforces uniqueness — but mind the null-matches-missing rule from the querying lesson: several documents missing the field all count as one null and collide. Combine unique with a partial filter to allow the missing ones — the document equivalent of a partial unique index.

What indexes cost — identical to SQL

Writes get slower. Every index is maintained on every insert, update of an indexed field, and delete — the same ~8× territory as module 7.
They take space. On the test collection the compound index was larger than the single-field one; you can read the sizes from db.coll.stats().indexSizes.
Memory. MongoDB wants the working set of indexes in RAM (its WiredTiger cache); indexes you do not use evict pages you do.

So the discipline is the same: index for your actual queries, and drop what is unused. MongoDB tracks usage — db.coll.aggregate([{ $indexStats: {} }]) gives an accesses.ops count per index, the equivalent of pg_stat_user_indexes, and an index with zero ops after real traffic is a candidate to drop.

Build indexes without blocking

Since MongoDB 4.2, createIndex builds in the background by default, holding only brief locks — the equivalent of CREATE INDEX CONCURRENTLY, and on by default rather than opt-in. On a large production collection it is still work; do it in a quiet window and watch it, but you do not have the "blocks all writes for minutes" hazard that a naive SQL CREATE INDEX has.

Check your work

How much of the SQL index knowledge transfers. Almost all — B-trees, leftmost prefix, selectivity, write cost.

COLLSCAN versus IXSCAN. Sequential scan versus index scan — measured 50,000 versus 50 documents examined.

How to create an index and what 1/-1 mean. createIndex({ field: 1 }); ascending and descending.

Why a tag-only query cannot use { member_id, tag }. Same as SQL — not a leftmost prefix, so no contiguous range.

What ESR stands for. Equality, Sort, Range — MongoDB's name for the column-ordering rule.

What to read in .explain("executionStats"). The stage, totalDocsExamined, nReturned — and especially the examined-to-returned ratio.

What proves a covered query. totalDocsExamined: 0, with _id excluded and only indexed fields projected.

What a multikey index is. An automatic index on every element of an array field; at most one array field per compound index.

MongoDB's text-search index. { field: "text" }, one per collection, with stemming — the to_tsvector equivalent.

What a partial index requires. The query must imply its partialFilterExpression.

What a TTL index does. Deletes documents once the indexed date passes the expiry — sessions, tokens, carts.

The unique-index gotcha. Missing fields all count as one null and collide; use a partial unique index.

How to find unused indexes. $indexStats and its accesses.ops count.

Why index builds do not block by default. Since 4.2 they build in the background — like CONCURRENTLY, but the default.

Practice

Load 50,000 documents. explain a filter with no index and read COLLSCAN and totalDocsExamined.
Add the index and re-run. Compare examined and returned.
Build a compound index and test all three prefix cases from the table above.
Reorder the two fields and re-test. Apply the ESR rule to predict which queries win.
Make a covered query with a matching projection and confirm totalDocsExamined: 0.
Index an array field, query it with a scalar, and confirm isMultiKey: true in the plan.
Try to build a compound index on two array fields and read the error.
Build a text index and search for a word in a different case. Confirm stemming.
Build a partial index and confirm it is used only when the query implies its condition.
Build a unique index on a field some documents lack. Insert two such documents and watch them collide. Fix it with a partial unique index.
Build a TTL index with a short expiry, insert a document, and confirm it disappears within a minute or two.
Run $indexStats, generate some queries, and find an index with zero accesses.ops.
Read db.coll.stats().indexSizes and compare a single-field with a compound index.

Official documentation

MongoDB — Indexes — The overview and every index type.
MongoDB — Compound indexes and the ESR rule — Column ordering, named for the pipeline.
MongoDB — Multikey indexes — Array indexing and the one-array restriction.
MongoDB — Partial indexes — And why to prefer them over sparse.
MongoDB — TTL indexes — Automatic document expiry.
MongoDB — Analyze query performance / explain — Reading explain output.
MongoDB — $indexStats — Finding unused indexes.

Next: transactions, write concerns, and what you give up.