Build an exact mental model of ordered MongoDB indexes by tracing equality, range, sort, coverage, explain evidence, and write/storage cost on AtlasMart orders.
How B-Tree-Like Indexes Support Equality, Range, Sort, and Covered Queries
Batch heterogeneous writes safely, interpret partial success, compare ordered and unordered execution, and use modern cross-namespace bulk APIs without assuming all-or-nothing behavior.
Learning objectives
Model a conventional MongoDB index as an ordered B-tree-like key structure rather than a magic “fast query” switch.
Connect equality, range, and sort predicates to contiguous regions of an ordered index.
Read explain execution statistics before and after index creation and distinguish keys examined from documents examined.
Recognize a covered query by the absence of collection document fetches and understand how projection choices can destroy coverage.
Measure index storage and write-maintenance cost before deciding an index is worth keeping.
This lesson pins MongoDB Community Server
8.3.8 with
mongodb/mongodb-community-server:8.3.8-ubuntu2204-slim
and mongosh 2.10.0. The server is a disposable
standalone published only on loopback
127.0.0.1:27062. Authentication and TLS are
disabled only for this isolated lab. Feature Compatibility
Version (FCV) is observed but never changed. Default read/write
concern and primary read preference apply. Atlas, Search, KMS,
Enterprise Advanced, and paid services are not required. Runtime
output shown as “expected” is documentation-derived because this
generation environment has no Docker/mongod/mongosh runtime.
Index behavior depends on query shape, projection, sort, data
distribution, planner choice, cache state, topology, and patch
version. The labs therefore inspect winningPlan,
totalKeysExamined, totalDocsExamined,
index definitions/sizes, and target query results. Small
fixtures prove semantics, not production latency. Timing
snippets are comparative demonstrations only, not benchmarks.
1. AtlasMart problem: a paid-order timeline is correct but scans too much
AtlasMart repeatedly asks for recent paid orders for one tenant above a minimum amount. Without a suitable secondary index, MongoDB may examine collection documents to find the answer. A conventional MongoDB index stores ordered key values plus references back to documents. “B-tree-like” describes the ordered search structure at the level needed for query design; the lesson does not pretend application developers manage physical B-tree pages directly.
| Concept | What it means |
|---|---|
| Index key | One ordered value or tuple derived from indexed document fields. |
| Equality predicate | Narrows the scan to one value or a contiguous prefix of values. |
| Range predicate |
Selects an interval such as
$gte/$lt; it can widen the
number of keys scanned.
|
| Sort support | The index already stores keys in an order compatible with the requested sort, avoiding a blocking sort stage. |
| Covered query | All matching and returned fields come from index keys, so collection documents need not be fetched. |
| Write amplification | Every relevant insert/update/delete must also maintain each affected index structure. |
2. Seed a deterministic workload and observe the no-index baseline
docker rm -f atlasmart-mongo-ch10-l1 2>/dev/null || truedocker volume rm atlasmart-mongo-ch10-l1-data 2>/dev/null || truedocker run -d --name atlasmart-mongo-ch10-l1 \ -p 127.0.0.1:27062:27017 \ -v atlasmart-mongo-ch10-l1-data:/data/db \ mongodb/mongodb-community-server:8.3.8-ubuntu2204-slimmongosh "mongodb://127.0.0.1:27062/atlasmart?directConnection=true" --quiet --eval \'printjson({server:db.version(),hello:db.hello().isWritablePrimary}); printjson(db.getSiblingDB("admin").runCommand({getParameter:1,featureCompatibilityVersion:1}))' const c=db.orders_ch10_l1;c.drop();c.insertMany([ {_id:1,orderId:"A-1001",tenantId:"tenant-a",status:"paid",createdAt:ISODate("2026-08-01T09:00:00Z"),totalCents:1200}, {_id:2,orderId:"A-1002",tenantId:"tenant-a",status:"paid",createdAt:ISODate("2026-08-01T10:00:00Z"),totalCents:3200}, {_id:3,orderId:"A-1003",tenantId:"tenant-a",status:"paid",createdAt:ISODate("2026-08-01T11:00:00Z"),totalCents:2500}, {_id:4,orderId:"A-1004",tenantId:"tenant-a",status:"cancelled",createdAt:ISODate("2026-08-01T12:00:00Z"),totalCents:9000}, {_id:5,orderId:"A-1005",tenantId:"tenant-a",status:"paid",createdAt:ISODate("2026-08-02T09:00:00Z"),totalCents:1800}, {_id:6,orderId:"A-1006",tenantId:"tenant-a",status:"paid",createdAt:ISODate("2026-08-02T10:00:00Z"),totalCents:7400}, {_id:7,orderId:"B-2001",tenantId:"tenant-b",status:"paid",createdAt:ISODate("2026-08-01T09:30:00Z"),totalCents:5000}, {_id:8,orderId:"B-2002",tenantId:"tenant-b",status:"paid",createdAt:ISODate("2026-08-01T10:30:00Z"),totalCents:1300}, {_id:9,orderId:"B-2003",tenantId:"tenant-b",status:"refunded",createdAt:ISODate("2026-08-01T11:30:00Z"),totalCents:5000}, {_id:10,orderId:"A-1007",tenantId:"tenant-a",status:"paid",createdAt:ISODate("2026-08-03T08:00:00Z"),totalCents:2100}, {_id:11,orderId:"A-1008",tenantId:"tenant-a",status:"paid",createdAt:ISODate("2026-08-03T09:00:00Z"),totalCents:2200}, {_id:12,orderId:"A-1009",tenantId:"tenant-a",status:"paid",createdAt:ISODate("2026-08-03T10:00:00Z"),totalCents:2300}]);printjson({count:c.countDocuments({}),indexes:c.getIndexes()});const c=db.orders_ch10_l1;const filter={tenantId:"tenant-a",status:"paid",totalCents:{$gte:2000}};const projection={_id:0,orderId:1,createdAt:1,totalCents:1};const sort={createdAt:-1};printjson(c.find(filter,projection).sort(sort).toArray());printjson(c.find(filter,projection).sort(sort).explain("executionStats"));A-1009 2026-08-03T10:00:00Z 2300A-1008 2026-08-03T09:00:00Z 2200A-1007 2026-08-03T08:00:00Z 2100A-1006 2026-08-02T10:00:00Z 7400A-1003 2026-08-01T11:00:00Z 2500A-1002 2026-08-01T10:00:00Z 3200The exact winning-plan JSON is version-sensitive, but before the secondary index the important evidence is that MongoDB has no workload-specific ordered access path. On this tiny collection a collection scan is cheap; that does not make the shape production-safe at millions of documents.
3. Build one index whose key order matches the request
The index below sorts first by tenant and status (equality
fields), then by descending creation time (the requested order),
then stores amount and order identifier. Conceptually, nearby
keys look like tuples such as
(tenant-a, paid, 2026-08-03T10:00Z, 2300, A-1009).
The index contains encoded BSON key values, not a separate copy
of the whole document.
const c=db.orders_ch10_l1;print(c.createIndex( {tenantId:1,status:1,createdAt:-1,totalCents:1,orderId:1}, {name:"idx_tenant_status_created_total_order"}));printjson(c.getIndexes());printjson(c.stats().indexSizes);
Keys for the same tenant/status form one contiguous region.
Within that region, createdAt:-1 orders newer
timestamps before older timestamps.
totalCents comes after the sort field, so a
minimum-amount predicate can be evaluated from index keys but
may not bound the scan as tightly as if it appeared earlier.
That tradeoff becomes Lesson 2’s ESR versus ERS decision.
4. Re-run with hint to isolate the candidate index
const c=db.orders_ch10_l1;const q=c.find( {tenantId:"tenant-a",status:"paid",totalCents:{$gte:2000}}, {_id:0,orderId:1,createdAt:1,totalCents:1}).sort({createdAt:-1}).hint("idx_tenant_status_created_total_order");printjson(q.toArray());printjson(c.find( {tenantId:"tenant-a",status:"paid",totalCents:{$gte:2000}}, {_id:0,orderId:1,createdAt:1,totalCents:1}).sort({createdAt:-1}).hint("idx_tenant_status_created_total_order").explain("executionStats"));
Compare totalKeysExamined,
totalDocsExamined, the presence of an
IXSCAN, and whether a FETCH stage
exists. executionTimeMillis on 12 documents is
not a meaningful benchmark. A correct index can even look
slower in a tiny warm-cache test because fixed overhead
dominates.
5. Covered query boundary: one extra projected field changes the plan
The first projection needs only fields represented in the index
and excludes _id; MongoDB can potentially answer
from the index alone. Ask for statusNote, which is
not indexed, and a document fetch becomes necessary even if that
field is absent in most documents.
const c=db.orders_ch10_l1;printjson(c.find( {tenantId:"tenant-a",status:"paid",totalCents:{$gte:2000}}, {_id:0,orderId:1,createdAt:1,totalCents:1,statusNote:1}).sort({createdAt:-1}).hint("idx_tenant_status_created_total_order").explain("executionStats"));An index is not “covered” in the abstract. Coverage belongs to a specific filter + projection + sort combination. Returning the indexed array field in a multikey query has additional restrictions covered in Lesson 3.
6. Indexes consume storage and every write maintains them
Index size is visible through collection statistics, but physical bytes depend on WiredTiger compression, data distribution, and allocation state. Write cost can be compared experimentally using identical disposable collections. The following test deliberately uses wall-clock timing only to demonstrate directionality; it is not a latency benchmark and should not be used for SLO sizing.
const plain=db.orders_ch10_l1_plain;const indexed=db.orders_ch10_l1_indexed;plain.drop(); indexed.drop();indexed.createIndex({tenantId:1,status:1,createdAt:-1,totalCents:1,orderId:1},{name:"workload_idx"});function batch(n){ const a=[]; for(let i=0;i<n;i++) a.push({_id:i,orderId:`T-${i}`,tenantId:`t-${i%20}`,status:i%5?"paid":"cancelled",createdAt:new Date(1760000000000+i*1000),totalCents:500+(i%10000)}); return a;}const docs=batch(3000);let t=Date.now(); plain.insertMany(docs); const plainMs=Date.now()-t;t=Date.now(); indexed.insertMany(docs); const indexedMs=Date.now()-t;printjson({plainMs,indexedMs,plainIndexBytes:plain.totalIndexSize(),indexedIndexBytes:indexed.totalIndexSize()});Run controlled representative workloads, multiple trials, warm/cold-state variants, and tail-latency measurements. More indexes can reduce read latency while increasing insert/update latency, cache pressure, build time, backup size, and recovery/rebuild work.
7. Verification, cleanup, and production judgment
Verification checklist
- Exactly 12 source orders exist before the index experiment.
- The paid-order business result remains identical before and after index creation.
-
The candidate index definition and size are visible through
getIndexes()/indexSizes. - Explain evidence distinguishes collection document reads from index-key reads.
- Adding a non-indexed projected field destroys the covered-query condition.
- The write-cost demonstration is treated as comparative evidence, not a universal performance number.
Production judgment. Add an index only when a recurring query shape or invariant justifies its storage and write-maintenance cost. Indexes improve access paths; they do not change write concern, replication durability, or read consistency. On replica sets, every index is maintained on replica members; on sharded collections, index requirements interact with shard-key routing and unique-index restrictions. Treat index builds as operational changes with disk/headroom, replication lag, rollback, and observation plans. Lesson 2 turns this one good shape into a systematic compound-key ordering decision.
docker rm -f atlasmart-mongo-ch10-l1docker volume rm atlasmart-mongo-ch10-l1-dataCheck your understanding
- Why can an ordered index support both filtering and sorting?
- What does totalDocsExamined measure?
- What plan property indicates a covered query?
- Why is a tiny executionTimeMillis comparison weak evidence?
- What costs increase when another secondary index is added?
Review the answers
1. Equality/range predicates can select contiguous index regions while the stored key order can already match the requested sort.
2. The number of collection-document examinations during execution; it is not the number of returned documents.
3. The plan can answer from index keys without a collection fetch; explain shows index access without an IXSCAN descending from/feeding a FETCH for the covered portion.
4. Twelve documents are dominated by fixed overhead, cache state, and planner behavior; it does not represent production latency distributions.
5. Storage, cache footprint, insert/update/delete maintenance, build/rebuild work, backup footprint, and potentially replication/recovery cost.
Authoritative references
- MongoDB 8.3 release notes — Current 8.3 baseline and patch-sensitive behavior; re-check before reproduction.
- Indexes overview — Index concepts, names, build considerations, and index-management overview.
- Explain results — IXSCAN/FETCH/COLLSCAN evidence, covered-query plans, keys/documents examined, and execution statistics.
- Measure index use — Using $indexStats and explain evidence instead of intuition to manage indexes.
- mongosh release notes — mongosh version used for chapter commands.
- Create indexes to support queries — Covered-query concept, workload-driven indexing, and the warning that excessive indexes hurt write-heavy workloads.
- Compound indexes — Ordered compound keys, field order, prefixes, sort order, and field-count limit.