Single and compound indexes

The simplest index covers one field. The moment your queries filter or sort on two fields together, you reach for a compound index - a single index that orders documents by several fields at once. Compound indexes are where most of the real power lives, and where most of the confusion lives too, because a compound index does not help every query that mentions its fields. The rule that governs which queries it serves is the prefix rule.

Every output was captured on mongo 8.3.11, mongosh 2.11.1 and Node driver 7.7.0 on one Mac; the collection has 200 000 members over 10 cities and 26 join years, so city: "Berlin" matches 20 000 documents and city + joined matches 769. The lesson seeds its own data and defines the same one-line plan() helper as the previous lesson (stage chain, then results, index keys and documents read). Python, Go and Rust tabs were each run once in a scratch program.

mongosh/idx2-seed.js

const cities = ["Berlin", "Paris", "Lima", "Oslo", "Cairo", "Tokyo", "Rome", "Lagos", "Dhaka", "Quito"];
globalThis.plan = (cur) => {                       // stage chain, then keys/docs actually read
  const e = cur.explain("executionStats"), s = e.executionStats, stages = [];
  for (let p = e.queryPlanner.winningPlan; p; p = p.inputStage) stages.push(p.stage + (p.indexName ? "(" + p.indexName + ")" : ""));
  return stages.join(" > ") + "  returned=" + s.nReturned + " keys=" + s.totalKeysExamined + " docs=" + s.totalDocsExamined;
};
for (let s = 0; s < 200000; s += 50000) {
  db.members.insertMany(Array.from({ length: 50000 }, (_, k) => {
    const i = s + k;
    return { name: "m" + i, joined: NumberInt(2000 + (Math.floor(i / 10) % 26)), fines: i % 7, city: cities[i % 10] };
  }), { ordered: false });
}
console.log("members:", db.members.countDocuments());

members: 200000

A single-field index

The direction - 1 ascending, -1 descending - controls the stored order. For one field it rarely matters, since an index can be walked either way. Here fines ascending; both an equality filter and a top-N sort read almost nothing:

mongosh/idx2-single.js

console.log("createIndex ->", db.members.createIndex({ fines: 1 }));
console.log("fines = 3 :", plan(db.members.find({ fines: 3 })));
console.log("sort fines:", plan(db.members.find().sort({ fines: -1 }).limit(5)));

createIndex -> fines_1
fines = 3 : FETCH > IXSCAN(fines_1)  returned=28571 keys=28571 docs=28571
sort fines: LIMIT > FETCH > IXSCAN(fines_1)  returned=5 keys=5 docs=5

createIndex returns the name string. fines: 3 reads its 28 571 matching keys and documents; the limit(5) sort reads 5.

A compound index over several fields

A compound index lists more than one field, each with its own direction. The field order defines the sort order: first by the first field, then by the second within each group of equal first values. Here city ascending, then joined descending, so members group by city and, inside each city, newest first:

mongosh

db.members.createIndex({ city: 1, joined: -1 })

Node.js

await db.collection("members").createIndex({ city: 1, joined: -1 });

Python

db.members.create_index([("city", 1), ("joined", -1)])

Go

_, err := coll.Indexes().CreateOne(ctx, mongo.IndexModel{
    Keys: bson.D{{Key: "city", Value: 1}, {Key: "joined", Value: -1}},
})
if err != nil {
    return err
}

Rust

coll.create_index(
    IndexModel::builder().keys(doc! { "city": 1, "joined": -1 }).build(),
)
.await?;

The name strings the fields and directions together: city_1_joined_-1.

The prefix rule, measured

A compound index can serve a query only through a left-prefix of its fields - the first field, or the first two, always starting from the left and never skipping. city_1_joined_-1 has two usable prefixes: city, and city + joined. Five queries against it:

mongosh/idx2-compound.js

console.log("createIndex ->", db.members.createIndex({ city: 1, joined: -1 }));
const rows = [
  ["{ city }",                       db.members.find({ city: "Berlin" })],
  ["{ city, joined }",               db.members.find({ city: "Berlin", joined: 2023 })],
  ["{ city, joined >= 2020 }",       db.members.find({ city: "Berlin", joined: { $gte: 2020 } })],
  ["{ joined } (skips city)",        db.members.find({ joined: 2023 })],
  ["{ joined }, hint the index",     db.members.find({ joined: 2023 }).hint({ city: 1, joined: -1 })],
];
rows.forEach(([label, cur]) => console.log(label.padEnd(28), plan(cur)));
assert.match(plan(db.members.find({ joined: 2023 })), /^COLLSCAN/);

createIndex -> city_1_joined_-1
{ city }                     FETCH > IXSCAN(city_1_joined_-1)  returned=20000 keys=20000 docs=20000
{ city, joined }             FETCH > IXSCAN(city_1_joined_-1)  returned=769 keys=769 docs=769
{ city, joined >= 2020 }     FETCH > IXSCAN(city_1_joined_-1)  returned=4614 keys=4614 docs=4614
{ joined } (skips city)      COLLSCAN  returned=7690 keys=0 docs=200000
{ joined }, hint the index   FETCH > IXSCAN(city_1_joined_-1)  returned=7690 keys=200000 docs=200000

Read it row by row. { city } reads 20 000 keys for 20 000 results and { city, joined } reads exactly its 769. A range on the second field still rides the index (joined >= 2020 reads only the matching keys inside the city block). But { joined } alone is a COLLSCAN of all 200 000 documents: the index is sorted by city first, so the join years are scattered across it. Forcing the index with hint() proves it is not merely a planner preference - the server has to walk all 200 000 keys because MongoDB has no skip scan.

The Node.js tab proves the same rule on its own 2 000-document collection: the city query reads keys equal to results, and the joined-only query scans all 2 000 documents.

app/idx2-compound.mjs

const c = db.collection("members");
await c.insertMany(Array.from({ length: 2000 }, (_, i) => ({ city: ["Berlin", "Oslo"][i % 2], joined: 2000 + (i % 26) })));
const name = await c.createIndex({ city: 1, joined: -1 });
const ex = async (f) => (await c.find(f).explain("executionStats")).executionStats;
const a = await ex({ city: "Berlin" }), b = await ex({ joined: 2023 });
console.log(name, "| city:", a.totalKeysExamined, "keys | joined only:", b.totalDocsExamined, "docs (COLLSCAN)");
if (a.totalKeysExamined !== a.nReturned || b.totalDocsExamined !== 2000) throw new Error("prefix rule not observed");

city_1_joined_-1 | city: 1000 keys | joined only: 2000 docs (COLLSCAN)

In Compass: the Indexes tab lists each index with its fields and directions in order, so you can read off the available prefixes at a glance, and it reports each index’s size on disk.

Direction matters for sort, not for equality

An index can be walked forwards or backwards, so the exact opposite of its declared order is free. What is not free is a mixed direction that the stored order cannot produce:

mongosh/idx2-sort.js

const q = { city: "Berlin" };
console.log("sort { joined: -1 }              :", plan(db.members.find(q).sort({ joined: -1 })));
console.log("sort { joined: 1 } (reverse walk):", plan(db.members.find(q).sort({ joined: 1 })));
console.log("no filter, sort { city: -1, joined: 1 }:", plan(db.members.find().sort({ city: -1, joined: 1 }).limit(5)));
console.log("no filter, sort { city: 1, joined: 1 }:", plan(db.members.find().sort({ city: 1, joined: 1 }).limit(5)));
assert.ok(!plan(db.members.find(q).sort({ joined: 1 })).includes("SORT"));
assert.ok(plan(db.members.find().sort({ city: 1, joined: 1 }).limit(5)).includes("SORT"));

sort { joined: -1 }              : FETCH > IXSCAN(city_1_joined_-1)  returned=20000 keys=20000 docs=20000
sort { joined: 1 } (reverse walk): FETCH > IXSCAN(city_1_joined_-1)  returned=20000 keys=20000 docs=20000
no filter, sort { city: -1, joined: 1 }: LIMIT > FETCH > IXSCAN(city_1_joined_-1)  returned=5 keys=5 docs=5
no filter, sort { city: 1, joined: 1 }: SORT > COLLSCAN  returned=5 keys=0 docs=200000

{ joined: -1 } and { joined: 1 } (a reverse walk) both come straight off the index, as does the exact reverse { city: -1, joined: 1 }. { city: 1, joined: 1 } disagrees with the stored joined: -1, so the index cannot deliver the order: the plan is SORT > COLLSCAN and all 200 000 documents are read.

Redundant prefixes

A separate { city: 1 } next to { city: 1, joined: -1 } duplicates the prefix. Create it and see what happens:

mongosh/idx2-redundant.js

db.members.createIndex({ city: 1 });                    // a "separate" city index
console.log("city query now :", plan(db.members.find({ city: "Berlin" })));
for (let i = 0; i < 3; i++) db.members.find({ city: "Berlin" }).toArray();          // real executions, so $indexStats has something to count
db.members.find({ city: "Berlin", joined: 2023 }).toArray();
const s = db.members.stats().indexSizes, kb = (n) => Math.round(n / 1024);
console.log("index sizes KB  :", { city_1: kb(s.city_1), city_1_joined_minus1: kb(s["city_1_joined_-1"]) });
console.log("$indexStats ops :", db.members.aggregate([{ $indexStats: {} }, { $project: { _id: 0, name: 1, ops: "$accesses.ops" } }, { $sort: { name: 1 } }]).toArray());
assert.ok(s.city_1 > 0);

city query now : FETCH > IXSCAN(city_1)  returned=20000 keys=20000 docs=20000
index sizes KB  : { city_1: 1080, city_1_joined_minus1: 1404 }
$indexStats ops : [
  { name: '_id_', ops: Long('0') },
  { name: 'city_1', ops: Long('3') },
  { name: 'city_1_joined_-1', ops: Long('1') },
  { name: 'fines_1', ops: Long('0') }
]

The planner may pick the smaller single-field index for a city-only query, so it is not useless to that query - but it is the same work the compound index already does, and it costs disk, memory and a write on every insert. Use $indexStats to see which index actually earns its keep, and drop the duplicate if the compound one is fast enough.

Tips and gotchas

• One compound index can replace several single-field ones: { city: 1, joined: -1 } already covers city-only queries.
• Field order is a design decision. Reversing it changes which queries the index serves: { joined: 2023 } was a COLLSCAN on (city, joined).
• Direction matters for sorting, not equality. The exact reverse of the stored order is free; a mixed direction cannot use the index (SORT > COLLSCAN).
• Skipping a leading field breaks the prefix, however selective the later field is.

🍃 Read a compound index left to right

A compound index serves any query that uses an unbroken run of its leading fields, starting from the first. The moment a query skips the leftmost field, the index can no longer seek for it.

Reading AI-generated code

Asked to “index the members query on city and join year”, an assistant often writes one index per field:

🐛 Spot the bug

// AI comment: "one index per field - MongoDB combines them for multi-field queries"
await members.createIndex({ city: 1 });
await members.createIndex({ joined: 1 });
const rows = await members.find({ city: "Berlin", joined: 2023 }).toArray();

Show the answer

The comment describes index intersection as if it were guaranteed. In the run below the planner picked a single index and filtered the rest from fetched documents, so it read far more keys and documents than the 769 results. One compound index turns that into keys = documents = results:

mongosh/idx2-bug.js

db.members.dropIndexes();                               // start from _id only
db.members.createIndex({ city: 1 });
db.members.createIndex({ joined: 1 });                 // AI: "one index per field, MongoDB combines them"
console.log("two single indexes:", plan(db.members.find({ city: "Berlin", joined: 2023 })));
db.members.createIndex({ city: 1, joined: 1 });        // the fix: one compound index
console.log("one compound index:", plan(db.members.find({ city: "Berlin", joined: 2023 })));

two single indexes: FETCH > IXSCAN(joined_1)  returned=769 keys=7690 docs=7690
one compound index: FETCH > IXSCAN(city_1_joined_1)  returned=769 keys=769 docs=769

Mental Model

A compound index is a phone book sorted by last name, then first name. You can find every Smith, and every John Smith, quickly; you cannot find every John without reading the whole book. The stored order is the contract - the prefix rule and the sort rules are both consequences of it.

Trade-offs

Choice	Benefit	Cost
Single-field index	Simple; covers one query shape	Two single indexes did not combine into a seek for a two-field query
Compound index	One index serves several shapes through its prefixes and its sort order	Larger, slower to write; useless to a query that skips the first field
Extra prefix index ({ city: 1 })	Smaller, may be picked for city-only queries	Duplicates work the compound index already does; pays writes

Common Misconceptions

• “A compound index helps any query on any of its fields.” - { joined: 2023 } scanned all 200 000 documents; even hinted, it read all 200 000 keys.
• “Separate indexes on each field combine automatically.” - the planner used one and filtered the rest.
• “The direction of every field must match the sort.” - the exact reverse is free; only mixed directions defeat the index.
• “A separate single-field index on the first field is needed.” - the compound index already has that prefix.

Real-world example

The MongoDB manual’s compound-index page states the rule this lesson measured: “Compound indexes support queries on all fields included in the index prefix”, and suggests one when “your application repeatedly runs a query that contains multiple fields” (Compound Indexes, consulted 2026-09-30).

Python/Go/Rust tabs mirror the verified shell/Node behaviour; each was run once in a scratch program against the same server (the compound createIndex returned city_1_joined_-1) but they are not executed by the course’s automated checker.

You have a compound index on { city: 1, joined: -1 }. Which query can it serve with a seek?

A query filtering on joined only A query filtering on city only A query filtering on fines only No query, because compound indexes need all fields

What does the prefix rule say about a compound index?

It serves queries that use any subset of its fields It serves queries that use a left-prefix of its fields, starting from the first It serves only queries that use every field at once It serves queries that use its last field first

An index is { city: 1, joined: -1 }. Which sort on a Berlin-only query needs no SORT stage?

Only { joined: -1 } Both { joined: -1 } and { joined: 1 }, because the index can be walked backwards Neither Only { joined: 1 }

You create separate single-field indexes on city and joined for a query on both. What did the run show?

Both indexes were combined for a perfect seek The planner used one index and filtered the rest from fetched documents (more keys and docs than results) The query was rejected A COLLSCAN

Why is a separate { city: 1 } index redundant next to { city: 1, joined: -1 }?

The compound index already serves every city-only query MongoDB forbids it It is not redundant, it is always faster The compound index cannot serve city queries

Submit

Mark as complete