Compound Indexes

// 1) The ESR rule
// EQUALITY columns first.
// SORT  columns next, in the order the query sorts by.
// RANGE columns last ($gt, $gte, $lt, $lte, $in with > 1 value).
// Example query:
//   db.orders.find({ customer_id: 42, status: 'paid' })
//            .sort({ created_at: -1 })
//            .limit(20)
// Right index:
db.orders.createIndex({ customer_id: 1, status: 1, created_at: -1 });

// 2) Index prefix usage
// The above index ALSO serves:
//   { customer_id: 42 }
//   { customer_id: 42, status: 'paid' }
// but NOT:
//   { status: 'paid' }                 (status is NOT a prefix)
//   { created_at: ... }                (created_at is NOT a prefix)

// 3) Sort direction is reversible whole-index
// The index { customer_id: 1, status: 1, created_at: -1 } serves:
//   .sort({ customer_id: 1, status: 1, created_at: -1 })
//   .sort({ customer_id: -1, status: -1, created_at: 1 })
// It does NOT serve mixed directions like
//   .sort({ customer_id: 1, status: 1, created_at: 1 })

// 4) Covered queries — the index has every field returned
// Add total_cents to the index. Now the query can read from index ONLY.
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 (in explain.executionStats)

// 5) When a range field is in the middle, the index splits
db.orders.createIndex({ customer_id: 1, total_cents: 1, status: 1 });
// For a query: WHERE customer_id=? AND total_cents > 5000 AND status='paid'
// the planner uses ONLY the (customer_id, total_cents) prefix; status comes
// after a range so it cannot be a clean index seek.
// Fix by ordering: { customer_id: 1, status: 1, total_cents: 1 }

// 6) Indexing arrays — multikey + compound
db.products.createIndex({ tags: 1, price_cents: -1 });
db.products.find({ tags: 'sale' }).sort({ price_cents: -1 });
// multikey: at most ONE field per index can be an array.

// 7) Partial compound indexes
db.orders.createIndex(
  { customer_id: 1, created_at: -1 },
  { partialFilterExpression: { status: { $in: ['new', 'paid'] } } }
);
// Half the size; useful when the working set is a subset.

// 8) Inspect what the planner chooses
db.orders.find({ customer_id: 42, status: 'paid' })
         .sort({ created_at: -1 }).limit(20)
         .explain('executionStats');
// Look for: stage 'IXSCAN', not 'COLLSCAN'; totalKeysExamined close to nReturned.

// 9) Limit the number of indexes per collection (more = slower writes)
db.orders.aggregate([{ $indexStats: {} }]);
// Drop indexes with accesses.ops = 0:
db.orders.dropIndex('old_idx_1');

// 10) Common anti-patterns
// - One single-field index per WHERE clause + a separate sort index
//   (a single compound index following ESR usually does both)
// - Multi-array compound indexes (forbidden)
// - Indexes that include the entire document for 'covered queries' on huge docs
//   (the index itself grows huge; not worth it)

// Compound indexes follow the leftmost-prefix rule.
db.products.createIndex({ category: 1, price: -1 });