Start with no index and ask for one customer's orders. Customer 14907 is the busiest in the seeded data, with 17 of the 100,000.
db.orders.dropIndexes();
const q = { customerId: 14907 };
const run = () => { db.orders.find(q).toArray(); // warm the cache first
const s = db.orders.find(q).explain('executionStats').executionStats;
print(`${s.executionStages.stage} nReturned=${s.nReturned} keys=${s.totalKeysExamined}` +
` docs=${s.totalDocsExamined} ms=${s.executionTimeMillis}`); };
run(); db.orders.createIndex({ customerId: 1 }); run();COLLSCAN nReturned=17 keys=0 docs=100000 ms=47 FETCH nReturned=17 keys=17 docs=17 ms=0
That is the whole argument for indexing, and the index cost 634,880 bytes and 228 ms to build. Watch the ratio totalDocsExamined / nReturned: 5,882 before, 1.0 after; above about 10 for a selective query means the wrong index or none.
Compound indexes and the prefix rule
A compound index sorts keys by the first field, then the second within equal firsts, and so on. It serves a query using a prefix of its fields and nothing else. With only { status: 1, region: 1, createdAt: -1 } present, find({ status: 'shipped' }) uses it — status is a prefix — and examines the 20,135 matching documents; find({ region: 'eu-west' }) is not a prefix and falls back to a COLLSCAN of all 100,000.
Field order therefore decides which queries an index can answer, and one well-ordered compound index usually replaces several single-field ones. Run find({ status: 'shipped', region: 'eu-west' }).sort({ createdAt: -1 }).limit(20), matched by 3,258 documents, against three layouts. With no index it is a COLLSCAN feeding a blocking SORT: 100,000 documents examined, 64 ms. With { region: 1, status: 1 } the index finds the 3,258 matches but not their order, so all 3,258 are fetched and sorted in memory: 14 ms. With { status: 1, region: 1, createdAt: -1 } the scan is already in createdAt order inside each group and stops after 20 keys: 20 documents examined, 0 ms.