#Why indexes matter
Without an index, the only way MongoDB can answer find({ email: "..." }) is a collection scan (COLLSCAN): read every document, check the condition, keep the matches. That's fine for 100 documents and ruinous for 100 million. An index is a separate, sorted structure (a B-tree, like InnoDB's) holding the values of one or more fields, each with a pointer to the document. Because it's sorted, the server can jump straight to "user77777@example.com" in a few steps instead of reading everything.
The mental model is the index at the back of a textbook. To find "replication" you don't read all 600 pages; you look it up alphabetically and go straight to pages 212 and 340. But the index isn't free: it takes pages of its own, and every time the author adds a paragraph about replication, someone has to update the index too. In MongoDB, every index makes writes slower and uses disk and RAM, so you create the ones your queries need and no others.
Every collection starts with exactly one index, the unique index on _id, which can't be dropped. The examples in this chapter use a users collection of 100,000 generated documents (name, unique email, gender, dob: { date, age }, city, a hobbies array, plan, score and signup date) in the ch19 database, so the explain numbers are real. A lookup by email, first with only the _id index, then after indexing email:
db.users.find({ email: "user77777@example.com" }, { _id: 0, name: 1, city: 1 })
const s1 = db.users.find({ email: "user77777@example.com" }).explain("executionStats").executionStats
({ stage: s1.executionStages.stage, nReturned: s1.nReturned, totalKeysExamined: s1.totalKeysExamined,
totalDocsExamined: s1.totalDocsExamined, executionTimeMillis: s1.executionTimeMillis })
db.users.createIndex({ email: 1 })
const s2 = db.users.find({ email: "user77777@example.com" }).explain("executionStats").executionStats
({ stage: s2.executionStages.stage, inputStage: s2.executionStages.inputStage.stage, nReturned: s2.nReturned,
totalKeysExamined: s2.totalKeysExamined, totalDocsExamined: s2.totalDocsExamined,
executionTimeMillis: s2.executionTimeMillis })[ { name: 'Chloe Khan', city: 'Mumbai' } ]
{
stage: 'COLLSCAN',
nReturned: 1,
totalKeysExamined: 0,
totalDocsExamined: 100000,
executionTimeMillis: 11
}
email_1
{
stage: 'FETCH',
inputStage: 'IXSCAN',
nReturned: 1,
totalKeysExamined: 1,
totalDocsExamined: 1,
executionTimeMillis: 1
}- The query finds one user. The interesting part is how much work it took, which
explain("executionStats")reports; the two({ ... })lines just pick the important fields out of its long output. - Without an index on
email, the plan isCOLLSCAN: 100,000 documents examined to return 1. It took 11 ms here only because the whole collection is cached in RAM; on disk-bound data it would be far slower. createIndex({ email: 1 })builds an ascending index onemailand returns its generated name,email_1.- Now the plan is
IXSCAN(walk the index to the one matching key) feedingFETCH(load that one document): 1 key and 1 document examined. - That ratio, documents examined vs documents returned, is the number to look at for every query. 100,000 : 1 is a missing index; 1 : 1 is perfect.
A single lookup is too quick to time by hand on cached data, so here are 200 lookups by email from mongosh, without and with the index (lookups() runs findOne for 200 different emails and returns the elapsed milliseconds):
const lookups = (n) => { const t = Date.now(); for (let i = 1; i <= n; i++) db.users.findOne({ email: "user" + (i * 397 % 100000 + 1) + "@example.com" }); return Date.now() - t }
db.users.dropIndex("email_1").ok
({ withoutIndexMs: lookups(200) })
db.users.createIndex({ email: 1 })
({ withIndexMs: lookups(200) })1
{ withoutIndexMs: 793 }
email_1
{ withIndexMs: 69 }- Without the index, each lookup scans 100,000 documents: about 4 ms each, 793 ms for 200.
- With the index, 200 lookups take 69 ms, about 0.35 ms each, and most of that is the round trip between mongosh and the server, not the search.
- A second run gave 827 ms and 72 ms. The gap grows with the collection: the scan is O(n), the index lookup O(log n). At 100 million users, the scan would take seconds per query and the index lookup would still be under a millisecond.
Here 100,000 user documents are inserted into an empty collection twice, first with only the _id index, then with four more indexes. load() drops and recreates the collection, creates the given indexes, inserts in batches of 1,000, forces a checkpoint so the sizes are up to date, and reports timings and sizes:
load([])
load([ { email: 1 }, { city: 1, score: -1 }, { hobbies: 1 }, { "dob.age": 1 } ]){ indexes: 1, insertMs: 727, dataMB: 24.7, perIndexMB: { _id_: 2.6 } }
{
indexes: 5,
insertMs: 1092,
dataMB: 24.7,
perIndexMB: {
_id_: 2.6,
email_1: 4.1,
'city_1_score_-1': 2.6,
hobbies_1: 5.7,
'dob.age_1': 2.2
}
}- With only
_id, the inserts take 727 ms. With four extra indexes they take 1,092 ms: 50% slower, because every insert now also adds an entry to five B-trees. A second run gave 761 ms vs 1,197 ms. dataMBis the uncompressed size of the documents (24.7 MB). The index sizes are on-disk sizes, which WiredTiger shrinks with prefix compression (sorted keys that share a beginning are stored once).hobbies_1is the largest because it's multikey: each user has 1 to 4 hobbies, so the index holds about 250,000 keys for 100,000 documents (see Multikey indexes).- Together the four indexes take 14.6 MB, more than half the size of the data itself. Indexes need to fit in RAM to be fast, so every index you add competes for the cache with your documents.
- Write-heavy collections with rare reads (logs, events): each index slows every insert. Index only what's queried.
- Low-selectivity fields on their own (a boolean, a
statuswith three values): an index that matches 40% of the collection is often slower than a scan; see the selectivity example in explain() and executionStats. - Tiny collections: a scan of 50 documents is as fast as an index lookup.
- Duplicate coverage: an index on
{ a: 1 }is redundant if{ a: 1, b: 1 }exists, because the compound index servesa-only queries too.