Indexes: COLLSCAN vs IXSCAN

conceptThe concept

Open the interactive lesson to use this diagram.

An index is a sorted structure (a B-tree in WiredTiger) of key values, each pointing to a document. Instead of reading every document (COLLSCAN), the server seeks to the first matching key and walks forward (IXSCAN), then fetches the documents (FETCH).

Every collection has a unique index on _id. You add others with createIndex({ field: 1 }). Direction (1 or -1) matters only for compound indexes with mixed sort orders.

The key metrics in explain("executionStats"):

• nReturned: documents returned.
• totalKeysExamined: index keys read.
• totalDocsExamined: documents fetched.
• executionTimeMillis.

The ideal ratio is keys examined = docs examined = returned. A large gap tells you the index is not selective enough or the wrong one was used.

whyWhy it matters

Missing indexes are the most common cause of slow MongoDB applications: a COLLSCAN reads the whole collection, evicting useful data from cache, so one bad query slows down everything else. Every index also costs RAM and slows every insert, update and delete, so you add the right ones, not all of them.

Use caseReal-world scenario

The customer service app searches orders by customer: find({ customerId: 372 }). Without an index it examines 5,000 documents to return 10; with { customerId: 1 } it examines 10 keys and 10 documents.

Try itExamples

Run and edit these in the interactive lesson.

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

db.orders.createIndex({ customerId: 1 })
db.orders.find({ customerId: 372 }).explain("executionStats")

db.orders.getIndexes()
db.orders.stats().indexSizes
db.orders.aggregate([{ $indexStats: {} }, { $project: { name: 1, "accesses.ops": 1 } }])

db.orders.createIndex({ status: 1 })
db.orders.find({ status: "delivered" }).explain("executionStats")

internalsUnder the hood

WiredTiger stores each index as its own B-tree table, with prefix compression on keys. Index entries contain the encoded key and the RecordId of the document. Since 4.2, index builds use an optimized process that holds an exclusive lock only briefly at the start and end; on replica sets builds run simultaneously on all data-bearing members (4.4+) and commit once a quorum is ready.

Avoid / preferDo this, not that

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

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

db.customers.createIndex({ marketingOptIn: 1 })

db.customers.createIndex({ tier: 1 }, { partialFilterExpression: { marketingOptIn: true } })

PitfallsCommon mistakes

• Indexing every field "just in case": more RAM, slower writes, and the planner has more plans to try.
• Indexing low-cardinality fields alone (status, boolean flags) and expecting big gains.
• Never checking $indexStats; unused indexes cost write performance for nothing.
• Building large indexes on a busy primary during peak hours without checking impact.

LimitsWhen not to use it

Do not index for a query that runs once a month, and do not bother on collections of a few hundred documents: a collection scan is fine there. On write-heavy collections every index is paid for on every insert and update, so each one must earn its keep. $indexStats shows which ones never get used.

SecuritySecurity notes

Unique indexes enforce security-relevant rules, such as one account per email. Application users should not hold dropIndex or createIndex: dropping a unique index silently removes the rule.

ChallengeTry it yourself

db.orders.createIndex({ customerId: 1, createdAt: -1 })
const s = db.orders.find({ customerId: 66 }).sort({ createdAt: -1 }).explain("executionStats")
const plan = s.queryPlanner.winningPlan
printjson({ stage: (plan.inputStage || plan).stage, keys: s.executionStats.totalKeysExamined, docs: s.executionStats.totalDocsExamined, returned: s.executionStats.nReturned })

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.