Single-Field and Compound Indexes: ESR-Style Ordering and Prefix Rules

Learning objectives

1. One query can justify different compound orders

AtlasMart needs “paid tenant-a orders above 8,000 cents, newest first.” The Equality–Sort–Range (ESR) guideline places exact-match fields first, then fields that must preserve sort order, then the range. But ESR is not a universal optimizer law. If a range is extremely selective, Equality–Range–Sort (ERS) may scan far fewer keys and accept a blocking sort. The correct choice depends on actual cardinality, sort cost, result limit, and latency objective.

2. Seed a larger deterministic fixture and build three candidates

docker rm -f atlasmart-mongo-ch10-l2 2>/dev/null || truedocker volume rm atlasmart-mongo-ch10-l2-data 2>/dev/null || truedocker run -d --name atlasmart-mongo-ch10-l2 \  -p 127.0.0.1:27063:27017 \  -v atlasmart-mongo-ch10-l2-data:/data/db \  mongodb/mongodb-community-server:8.3.8-ubuntu2204-slimmongosh "mongodb://127.0.0.1:27063/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_l2;c.drop();const docs=[];for(let i=0;i<120;i++) docs.push({ _id:i+1, tenantId:i<90?"tenant-a":"tenant-b", status:i%6===0?"cancelled":"paid", createdAt:new Date(Date.UTC(2026,7,1)+i*3600000), totalCents:500+(i*137)%12000, orderId:`O-${String(i+1).padStart(4,"0")}`});c.insertMany(docs);printjson({count:c.countDocuments({}),paidA:c.countDocuments({tenantId:"tenant-a",status:"paid"})});

const c=db.orders_ch10_l2;c.createIndex({tenantId:1,status:1,createdAt:-1,totalCents:1},{name:"idx_esr"});c.createIndex({totalCents:1,tenantId:1,status:1,createdAt:-1},{name:"idx_range_first"});c.createIndex({tenantId:1,status:1,totalCents:1,createdAt:-1},{name:"idx_ers"});printjson(c.getIndexes());

3. Compare identical query semantics with forced candidates

hint() is used here only to isolate each candidate. It is not a recommendation to hard-code hints in application code. Compare keys examined, documents examined, explicit sort stages, and returned rows.

const c=db.orders_ch10_l2;const filter={tenantId:"tenant-a",status:"paid",totalCents:{$gte:8000}};const sort={createdAt:-1};for(const name of ["idx_esr","idx_range_first","idx_ers"]){ print(`--- ${name} ---`); printjson(c.find(filter,{_id:0,orderId:1,createdAt:1,totalCents:1}).sort(sort).hint(name).explain("executionStats"));}

4. Prefix rules: “can use” is not the same as “uses every field efficiently”

For {tenantId, status, createdAt, totalCents}, the leading prefixes are {tenantId}, {tenantId,status}, and {tenantId,status,createdAt}. MongoDB can efficiently seek on leading prefixes. A query on status alone has no leading tenantId bound. A query that constrains tenant but skips status can still use the tenant prefix, but the later createdAt condition cannot be used as if status had also been bound.

const c=db.orders_ch10_l2;const tests=[ ["prefix tenant",{tenantId:"tenant-a"}], ["prefix tenant+status",{tenantId:"tenant-a",status:"paid"}], ["skip leading field",{status:"paid"}], ["prefix plus skipped middle",{tenantId:"tenant-a",createdAt:{$gte:ISODate("2026-08-03T00:00:00Z")}}]];for(const [label,filter] of tests){ print(`--- ${label} ---`); printjson(c.find(filter,{_id:0,orderId:1}).hint("idx_esr").explain("executionStats"));}

5. Sort direction: same pattern or full reversal

An index with createdAt:-1 can traverse that sort key forward for descending order or backward for ascending order when the rest of the compound constraints are compatible. Mixed-direction multi-field sorts require the index directions to match the requested pattern or its full inverse; a partially inverted compound sort is different.

const c=db.orders_ch10_l2;print("same direction");printjson(c.find({tenantId:"tenant-a",status:"paid"},{_id:0,createdAt:1,totalCents:1}).sort({createdAt:-1}).hint("idx_esr").explain("executionStats"));print("reverse direction");printjson(c.find({tenantId:"tenant-a",status:"paid"},{_id:0,createdAt:1,totalCents:1}).sort({createdAt:1}).hint("idx_esr").explain("executionStats"));

6. Avoid “one index per query” explosion

MongoDB allows many indexes, but the practical limit is usually reached before the hard collection limit because every index consumes disk/cache and must be maintained. If a compound index safely serves a leading-prefix query, a redundant prefix index may be removable—but uniqueness, sparsity/partial filtering, collation, and other properties can make apparently similar indexes semantically different. Do not drop a prefix index merely because the key names look redundant.

7. Verification, cleanup, and production judgment

Verification checklist

• All three candidate indexes exist with intentionally different field orders.
• The same filter, projection, and sort are used for every forced comparison.
• ESR and ERS are evaluated from execution evidence, not naming preference.
• Prefix tests distinguish a leading-prefix seek from a forced scan of a weak/skipped prefix.
• Ascending traversal is tested against the descending createdAt index direction.
• No index is dropped solely because another index has a visually similar prefix.

Production judgment. Compound ordering is a workload contract. Equality fields usually lead; choose sort-before-range when avoiding a blocking sort matters, or range-before-sort when a highly selective range is more valuable. Limits, distributions, skew, and changing query shapes can reverse the winner. On replicas and shards, every added index has cluster-wide operational cost. Treat index consolidation as a change with observation and rollback—not a cleanup script. Lesson 3 adds arrays, where one document can generate multiple index keys and compound design gains additional hard constraints.

docker rm -f atlasmart-mongo-ch10-l2docker volume rm atlasmart-mongo-ch10-l2-data

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.
• ESR guideline — Equality-first guidance and the documented ESR-versus-ERS tradeoff.
• Compound indexes — Prefix rules, field order, sort direction, and compound-index limits.