Lesson 0003 · Indexing
Mongo indexes are B-trees, so the leftmost-prefix rule you already know still holds. The one new discipline worth memorizing is the field order for a compound index: Equality, Sort, Range. Plus the genuinely new twist — indexing arrays.
Which per-write flag forces that write to be journaled to disk before it's acknowledged?
You delete millions of docs; the data file stays large. What returns that space to the OS?
A WiredTiger operation gets its consistent MVCC view of the data:
Good news for you specifically: MongoDB indexes are B-trees, so most of what you learned in MySQL 0002 and Postgres 0003 transfers directly — sorted keys, leftmost prefixes, covering reads. This lesson spends its budget on the two things that are different: a naming convention for compound-index field order that MongoDB makes explicit (the ESR rule), and what happens when you index an array (multikey indexes) — which relational tables can't even do.
Without a useful index, MongoDB must read every document to answer a query.
— MongoDB Manual: "Without indexes, MongoDB must scan every document in a collection to return query results. … Indexes are special data structures … MongoDB indexes use a B-tree data structure."
That full read is a collection scan (you'll see it as COLLSCAN
in explain() next lesson) — the exact cousin of a Postgres Seq Scan or a
MySQL full table scan. One index you never create yourself: the _id index,
which MongoDB builds automatically, keeps unique, and won't let you drop.
— MongoDB Manual: "MongoDB creates a unique index on the _id field during the creation of a collection. … You cannot drop this index."
A compound index stores keys sorted by the fields in the order you declare them,
and a query can only use a prefix of that order — the beginning subset.
— MongoDB Manual: "The order of the indexed fields impacts the effectiveness of a compound index. … Index prefixes are the beginning subsets of indexed fields. Compound indexes support queries on all fields included in the index prefix."
For an index on { item: 1, location: 1, stock: 1 }, a query can use
item; or item + location; or all three — but not
location alone, nor stock alone, because those skip the leading
field. This is identical to InnoDB's leftmost-prefix rule from
MySQL 0002;
nothing new to learn, just a new syntax.
Given that field order decides everything, in what order should you place them? MongoDB names the answer — the ESR rule: Equality first, then Sort, then Range.
x: 5). Most selective — put them first to shrink the keys examined..sort(). Next, so the index already returns rows in order — no in-memory sort.$gt, $lt, $in). Last — they scan a span of keys, so they can't stay sorted for anything after them.The reasoning is fully derivable, and it echoes ideas you already hold. Equality first because it's most selective and keeps the remaining index fields in sorted order. — MongoDB Manual: "Ensure that equality fields always come first. Placing equality fields first keeps the remaining index fields in sorted order." Sort before range because a range predicate reads a span of keys — after a range, the index is no longer in a single sorted run, so a sort placed after it would fall back to an expensive in-memory sort. — MongoDB Manual: "If avoiding in-memory sorts is critical, place sort fields before range fields."
Swap S and R only when the range is very selective — selective enough that filtering first leaves so few keys that sorting them in memory is cheaper than widening the scan. — MongoDB Manual: "If your range predicate in the query is very selective, then put it before sort fields." Default to ESR; reach for ERS only with a measured, highly-selective range. Either way, Equality is always first.
Relational columns are scalar; MongoDB fields can be arrays. Index a
field that holds an array and MongoDB automatically makes it a multikey
index — one index entry per array element, each pointing back to the same
document.
— MongoDB Manual: "If you create an index on a field that contains an array value, MongoDB automatically creates the index as a multikey index. … For each distinct value in the array, MongoDB creates a separate entry in the index, and each entry points back to the same document."
So a document with tags: ["a","b","c"] produces three index entries — which
is exactly what lets find({ tags: "b" }) jump straight to it.
The limitation to burn in: a compound index can include at most one array-valued field. — MongoDB Manual: "In a compound multikey index, each indexed document can have at most one indexed field whose value is an array. You cannot create a compound multikey index if more than one field in the index specification is an array." Two array fields in one index would multiply into a combinatorial explosion of entries, so MongoDB simply forbids it.
When every field a query needs — filter and returned fields — lives in the index, MongoDB answers it from the index alone and never touches the document. That's a covered query — MongoDB Manual — Covered Queries — the same win as a Postgres index-only scan (Postgres 0003) or an InnoDB covering index (MySQL 0002). One Mongo-specific gotcha: because a multikey index holds per-element entries, it cannot cover a query — MongoDB still fetches the document to return the whole array.
Answer from memory — the effortful recall is what builds retention. Feedback is immediate.
In what order should fields go in a compound index by default?
Index is { a: 1, b: 1, c: 1 }. Which query can it serve efficiently?
You index tags, a field holding an array of strings. MongoDB creates:
How many array-valued fields may a single compound index contain?
A query returns results straight from the index without reading documents. This is:
No Mongo handy yet, so this is a lab for later (Docker:
docker run --rm -p 27017:27017 mongo, then mongosh):
db.movies.createIndex({ directors: 1, year: 1, runtime: 1 }) // an ESR index
db.movies.find({ directors: "David Lynch", runtime: { $lt: 130 } })
.sort({ year: 1 }).explain("executionStats") // IXSCAN, no SORT stage
db.things.createIndex({ tags: 1 }) // tags is an array → multikey
db.things.getIndexes() // note "2dsphere"? no — look for multikey in explainThe tell you're chasing: an IXSCAN stage with no
separate SORT stage means the index served the sort for free — ESR working.
Bring the plan to your teacher and we'll read it together (that's lesson 0004).
MongoDB Manual — The ESR (Equality, Sort, Range) Guideline is the one page to read closely — short, with worked examples. Then Compound Indexes for prefixes and sort order, and Multikey Indexes for the array rules.