Indexes

// 1) Inspect what is happening — explain() is the first stop
db.orders.find({ customer_id: 42, status: 'paid' })
         .sort({ created_at: -1 })
         .limit(20)
         .explain('executionStats');

// Look at:
//   executionStats.totalDocsExamined   should be small (close to nReturned)
//   executionStats.totalKeysExamined   index lookups
//   queryPlanner.winningPlan.stage     COLLSCAN = no index used = bad
//   executionTimeMillis                wall-clock

// 2) Single-field index
db.orders.createIndex({ customer_id: 1 });

// 3) Compound index — equality fields FIRST, then sort, then range
db.orders.createIndex({ customer_id: 1, status: 1, created_at: -1 });
// Serves: WHERE customer_id=? AND status=? ORDER BY created_at DESC

// 4) Partial index — only some rows; saves space on hot subsets
db.orders.createIndex(
  { created_at: -1 },
  { partialFilterExpression: { status: { $in: ['new', 'paid'] } } }
);

// 5) TTL index — auto-delete after N seconds
db.sessions.createIndex({ expires_at: 1 }, { expireAfterSeconds: 0 });
// Set expires_at = ISODate('2026-06-19T...'); Mongo purges every 60s.

// 6) Multikey — automatic when the field is an array
db.products.createIndex({ tags: 1 });
db.products.find({ tags: 'sale' });            // uses the index per element

// 7) Text index for full-text search
db.products.createIndex({ name: 'text', description: 'text' });
db.products.find({ $text: { $search: 'wool jacket' } },
                  { score: { $meta: 'textScore' } })
            .sort({ score: { $meta: 'textScore' } });

// 8) 2dsphere for geospatial
db.stores.createIndex({ location: '2dsphere' });
db.stores.find({ location: { $nearSphere: {
  $geometry: { type: 'Point', coordinates: [151.21, -33.86] },
  $maxDistance: 5000,
} } });

// 9) Covering read — every projected field comes from the index
db.orders.createIndex({ customer_id: 1, status: 1, total_cents: 1 });
db.orders.find({ customer_id: 42, status: 'paid' },
                { _id: 0, customer_id: 1, status: 1, total_cents: 1 });
// totalDocsExamined: 0 — no docs read, only index scan.

// 10) Index hygiene
// List + sizes
db.orders.getIndexes();
db.orders.stats().indexSizes;
db.collection.aggregate([{ $indexStats: {} }]);   // usage counts since startup

// Find unused indexes (haven't been touched)
db.orders.aggregate([{ $indexStats: {} }, { $match: { 'accesses.ops': 0 } }]);

// Drop the dead weight
db.orders.dropIndex('old_idx_1');

// 11) Build big indexes in the background
db.orders.createIndex({ ... }, { background: true });   // legacy
// Modern: background build is the default; just be patient.

// 12) Anti-patterns to avoid
// - One index per query, even when the new one is a prefix of an existing index
// - Indexing every field 'just in case' (writes pay the index cost)
// - Sorting by a field that is not part of the index used for the filter
// - Equality on a field that is in the MIDDLE of a compound index
//   (the prefix must be matched for the index to be used efficiently)

db.customers.createIndex({ email: 1 }, { unique: true });
db.products.createIndex({ category: 1, price: -1 });