Indexing & Aggregation

Concept#

Indexing: B-Tree Traversal vs Collection Scans#

Without an index, MongoDB must run a COLLSCAN (Collection Scan), reading every document in a collection sequentially to check for matches. This grows linearly with collection size: O(n).

An index is a separate data structure (B-tree) that stores a sorted pointer map of a field's values. MongoDB can search this tree in O(log n) log time to find the exact document location:

// Find user Ada by email address
db.users.find({ email: 'ada@example.com' }).explain('executionStats');

• Single-Field Index: Index on a single attribute.

  db.users.createIndex({ email: 1 }); // 1 = ascending, -1 = descending

• Compound Index: Index on multiple fields. The order of fields matters (Equality, Sort, Range - ESR rule).

  db.users.createIndex({ status: 1, age: -1 });

The Aggregation Pipeline#

The aggregation framework executes multi-stage data transformations. Documents flow through a pipeline of stages (each stage starting with $):

db.orders.aggregate([
  { $match: { status: "completed" } }, // Stage 1: Filter
  { $group: { _id: "$customerId", totalSpent: { $sum: "$price" } } }, // Stage 2: Group & Sum
  { $sort: { totalSpent: -1 } } // Stage 3: Sort descending
]);

Common Mistakes#

1. Indexing fields with low cardinality#

Creating an index on fields with few distinct values (like gender or status) yields low selectivity. The database engine may skip the index entirely and run a COLLSCAN.

2. Violating the prefix order in compound indices#

If you create a compound index { status: 1, created: -1 }, queries filtering on created alone cannot use this index. Compound index lookups require prefix matching (you must filter on the first field status for the index to assist).

Best Practices#

• Rule of ESR: Place fields in compound indices in this order: Equality filters first, then Sort fields, then Range queries.
• Index Covered Queries: Ensure your query requests only the fields contained within the index (and excludes _id). MongoDB can return the result from the index itself without loading documents from memory.
• Explain Analysis: Always verify index utilization using .explain('executionStats'). Confirm that totalDocsExamined matches nReturned.