MENU


Indexes and Query Performance

A query without an index reads every document in the collection: fine for the fourteen orders of The Aggregation Pipeline, ruinous for a real one. Every claim in this section is backed by a number a real server produced.

Those numbers come from one machine: MongoDB 8.3.11 1,815 on Windows 11, an Intel Core i9-7980XE, 128 GB of RAM and a SATA SSD, WiredTiger cache at its 63 GiB default. The collection fits in that cache, so the timings measure CPU work, not disk. The box was not idle: elapsed times vary by a factor of two between runs while examined-document counts never move, so read the counts as fact and the milliseconds as an order of magnitude.

Seeding 100,000 orders for the index experimentsPython
db.orders.drop();
const A = { s: ['pending', 'paid', 'shipped', 'delivered', 'cancelled'],
  r: ['us-east', 'us-west', 'eu-west', 'eu-central', 'apac', 'latam'],
  t: ['gift', 'rush', 'fragile', 'bulk', 'reorder', 'b2b'] };
const t0 = Date.parse('2024-01-01'), span = 730 * 86400 * 1000;
let z = 20260922, b = [];                       // mulberry32: same data every run
const r = () => { z = (z + 0x6D2B79F5) | 0;
  let t = Math.imul(z ^ (z >>> 15), 1 | z); t = (t + Math.imul(t ^ (t >>> 7), 61 | t)) ^ t;
  return ((t ^ (t >>> 14)) >>> 0) / 4294967296; };
const pick = a => a[Math.floor(r() * a.length)];
const many = (n, f) => Array.from({ length: 1 + Math.floor(r() * n) }, f);
for (let i = 0; i < 100000; i++) {
  const items = many(4, () => ({ sku: 'SKU-' + (1000 + Math.floor(r() * 5000)),
    qty: 1 + Math.floor(r() * 5), price: Math.round(r() * 20000) / 100 }));
  const tags = [...new Set(many(3, () => pick(A.t)))];
  const customerId = 1 + Math.floor(r() * 20000);
  const d = { orderNo: 100000 + i, customerId, email: `user${customerId}@example.com`,
    status: pick(A.s), region: pick(A.r), total: Math.round(r() * 500000) / 100,
    createdAt: new Date(t0 + Math.floor(r() * span)), items, tags };
  if (r() < 0.04) d.coupon = 'CPN-' + d.orderNo;
  b.push(d); if (b.length === 5000) { db.orders.insertMany(b); b = []; }
}
if (b.length) db.orders.insertMany(b);
print(db.orders.countDocuments() + ' orders, ' + db.orders.stats().size + ' bytes');
Output
100000 orders, 32898243 bytes

Thirty-three megabytes, about 330 bytes per document, spread over two years, five statuses and six regions in near-equal proportions, 19,868 distinct customers and 4,009 orders (4.0%) with a coupon.

Subsections