3.11 — Quick Revision: Database Optimization

Your cheat sheet for indexing, explain() analysis, aggregation pipeline stages, and MongoDB operators. Bookmark this page for quick reference during development and interview prep.

---

< Interview Questions | README >

---

Index Types

Index Type	Syntax	Use Case
Single-field	{ email: 1 }	Query/sort on one field
Compound	{ status: 1, date: -1 }	Multi-field queries (prefix rule)
Unique	{ email: 1 }, { unique: true }	Enforce uniqueness
Text	{ name: "text", desc: "text" }	Full-text search
TTL	{ createdAt: 1 }, { expireAfterSeconds: 3600 }	Auto-delete after time
Sparse	{ phone: 1 }, { sparse: true }	Skip docs missing the field
Partial	{ price: 1 }, { partialFilterExpression: {...} }	Index only matching docs
Wildcard	{ "$**": 1 }	Dynamic/flexible schemas

---

Index Commands

// Create
db.col.createIndex({ field: 1 });
db.col.createIndex({ a: 1, b: -1 }, { unique: true });

// List
db.col.getIndexes();

// Usage stats
db.col.aggregate([{ $indexStats: {} }]);

// Drop
db.col.dropIndex("field_1");
db.col.dropIndexes();              // Drop all except _id

// Mongoose
schema.index({ field: 1 });
await Model.createIndexes();

---

ESR Rule (Compound Index Field Order)

E — Equality    (exact match fields first)
S — Sort        (sort fields second)
R — Range       ($gte, $lte, $in fields last)

// Query: status = "active", sort by date, price >= 50
// Index: { status: 1, date: -1, price: 1 }
//           E            S          R

---

explain() Output Key Fields

Field	What It Tells You	Good Value
stage	Scan type	IXSCAN (not COLLSCAN)
nReturned	Docs returned	Expected count
totalKeysExamined	Index entries scanned	Close to nReturned
totalDocsExamined	Docs read from disk	Close to nReturned (0 for covered)
executionTimeMillis	Query time	As low as possible

db.col.find({...}).explain("executionStats");

---

Aggregation Pipeline Stages

Stage	Purpose	Example
$match	Filter documents	{ $match: { status: "active" } }
$group	Group + accumulate	{ $group: { _id: "$field", total: { $sum: "$amt" } } }
$project	Reshape / select fields	{ $project: { name: 1, total: 1, _id: 0 } }
$addFields	Add fields (keep existing)	{ $addFields: { tax: { $multiply: ["$total", 0.18] } } }
$sort	Order results	{ $sort: { total: -1 } }
$limit	Limit count	{ $limit: 10 }
$skip	Skip N docs	{ $skip: 20 }
$unwind	Flatten arrays	{ $unwind: "$tags" }
$lookup	Join collections	{ $lookup: { from, localField, foreignField, as } }
$count	Count docs	{ $count: "total" }
$facet	Parallel pipelines	{ $facet: { results: [...], count: [...] } }
$bucket	Manual bucketing	{ $bucket: { groupBy, boundaries, default, output } }
$bucketAuto	Auto bucketing	{ $bucketAuto: { groupBy, buckets } }
$graphLookup	Recursive traversal	{ $graphLookup: { from, startWith, connectFromField, connectToField, as } }
$merge	Upsert to collection	{ $merge: { into, on, whenMatched, whenNotMatched } }
$out	Replace collection	{ $out: "collectionName" }

---

Accumulators ($group)

Accumulator	Purpose	Example
$sum	Sum / count	{ $sum: "$amount" } or { $sum: 1 }
$avg	Average	{ $avg: "$price" }
$min	Minimum	{ $min: "$date" }
$max	Maximum	{ $max: "$score" }
$push	Collect into array	{ $push: "$name" }
$addToSet	Collect unique values	{ $addToSet: "$tag" }
$first	First in group	{ $first: "$name" }
$last	Last in group	{ $last: "$name" }
$count	Count in group	{ $count: {} }

---

Comparison Operators

Operator	Meaning	Example
$eq	Equal	{ age: { $eq: 25 } }
$ne	Not equal	{ status: { $ne: "deleted" } }
$gt	Greater than	{ price: { $gt: 50 } }
$gte	Greater or equal	{ age: { $gte: 18 } }
$lt	Less than	{ stock: { $lt: 10 } }
$lte	Less or equal	{ rating: { $lte: 3 } }
$in	In array	{ status: { $in: ["a", "b"] } }
$nin	Not in array	{ role: { $nin: ["banned"] } }

---

Logical Operators

Operator	Syntax
$and	{ $and: [ {cond1}, {cond2} ] }
$or	{ $or: [ {cond1}, {cond2} ] }
$not	{ field: { $not: { $gt: 100 } } }
$nor	{ $nor: [ {cond1}, {cond2} ] }

Note: Implicit $and is the default when specifying multiple conditions on the same query.

---

Array Operators

Query

Operator	Purpose	Example
$elemMatch	Element matches all conditions	{ scores: { $elemMatch: { $gte: 80, $lte: 90 } } }
$all	Array has all values	{ tags: { $all: ["a", "b"] } }
$size	Exact array length	{ items: { $size: 3 } }

Update

Operator	Purpose	Example
$push	Add element	{ $push: { tags: "new" } }
$addToSet	Add if unique	{ $addToSet: { tags: "new" } }
$pull	Remove matching	{ $pull: { tags: "old" } }
$pop	Remove first/last	{ $pop: { tags: 1 } } (1=last, -1=first)

---

Update Operators

Operator	Purpose	Example
$set	Set field value	{ $set: { name: "Alice" } }
$unset	Remove field	{ $unset: { temp: "" } }
$inc	Increment	{ $inc: { count: 1 } }
$mul	Multiply	{ $mul: { price: 0.9 } }
$rename	Rename field	{ $rename: { "old": "new" } }
$min	Set if less	{ $min: { low: 50 } }
$max	Set if greater	{ $max: { high: 99 } }
$currentDate	Set current date	{ $currentDate: { updatedAt: true } }

---

Element and Regex Operators

Operator	Purpose	Example
$exists	Field exists	{ phone: { $exists: true } }
$type	Field BSON type	{ age: { $type: "number" } }
$regex	Pattern match	{ name: { $regex: /^alice/i } }

---

Performance Checklist

• $match is the first stage in aggregation pipelines
• Compound indexes follow ESR order
• explain("executionStats") shows IXSCAN, not COLLSCAN
• totalDocsExamined is close to nReturned
• No unused indexes wasting RAM (check $indexStats)
• Index total size fits in available RAM
• allowDiskUse: true set for large aggregations
• Auto-indexing disabled in production (autoIndex: false)
• TTL indexes used for session/token cleanup
• $lookup has indexes on foreignField