Unit 4: Indexing and Aggregation Framework - Subjective Questions

Definition: A single-field index is an index created on one document field. In MongoDB, it can be created using db.collection.createIndex({ fieldName: 1 }), where 1 represents ascending order and -1 represents descending order.

How it works:

• The database maintains an ordered structure containing field values and references to matching documents.
• During a query, the database can locate matching values in the index instead of scanning every document.
• It can improve equality queries, range queries, sorting, and some prefix-based operations.

Example:
javascript
db.students.createIndex({ studentId: 1 })
db.students.find({ studentId: "ST101" })

Useful situations:

• Queries frequently filter on one field.
• A field has high selectivity, such as an identifier or email address.
• Applications frequently sort or perform range searches using the same field.

An index improves read performance but consumes storage and adds maintenance work when documents are inserted, updated, or deleted.

Definition: A compound index contains indexed keys from two or more fields. For example:
javascript
db.employees.createIndex({ department: 1, salary: -1 })

This index first organizes documents by department in ascending order and then by salary in descending order within each department.

Effect of field order:

• The index efficiently supports queries using the leading field, department.
• It can support queries using both department and salary.
• It generally cannot efficiently support a query that uses only salary, because salary is not the leading indexed field.
• The order also influences sorting and range-query performance.

Examples:
javascript
db.employees.find({ department: "Sales" })
db.employees.find({ department: "Sales", salary: { $gt: 50000 } })

A good field order depends on equality predicates, sorting requirements, and range predicates. Fields commonly used for equality filtering are often placed before fields used for ranges or sorting.

Single indexes:

• Contain one indexed field.
• Are simple to create and maintain.
• Work well for queries focused on one field.
• Usually cannot provide efficient filtering for combinations of multiple fields.

Compound indexes:

• Contain multiple indexed fields in a defined order.
• Support queries involving the index prefix and combinations of indexed fields.
• Can support filtering and sorting together.
• Require careful selection of field order.

Comparison:

• Query coverage: A single index is suitable for one main predicate, while a compound index can support related multi-field predicates.
• Storage: A compound index generally consumes more storage than a single-field index.
• Sorting: Compound indexes can efficiently support sorting that follows their key pattern.
• Maintenance: Both types add write overhead, but compound indexes may require more index updates.
• Design: Too many single indexes can duplicate data and increase overhead; a well-designed compound index may replace several less useful indexes.

Index selection should be based on actual query patterns and measured using explain().

Definition: A text index is a specialized index designed to search words and phrases in string fields. It indexes terms rather than treating the entire string as one exact value.

Main operations:

• Text is tokenized into individual terms.
• Common stop words may be ignored depending on the database and language configuration.
• Stemming or language-specific processing may group related word forms.
• Matching documents can be assigned relevance scores.

Example:
javascript
db.articles.createIndex({ title: "text", body: "text" })
db.articles.find({ search: "distributed databases" } })

Important considerations:

• Text indexes are different from ordinary equality or range indexes.
• Search behavior depends on language, case sensitivity, stemming, and punctuation rules.
• A text index can cover one or multiple text fields.
• Relevance scores can be used to order results.

Text indexes are useful for document search, article retrieval, and product descriptions, but advanced search requirements may require a dedicated search engine.

Regular single-field index:

• Indexes complete field values.
• Is effective for exact matches, ranges, and sorting.
• For example, it can efficiently find { status: "active" }.
• It does not normally split a sentence into searchable terms.

Text index:

• Indexes individual terms appearing in text.
• Supports word-based searches and relevance ranking.
• Can search across one or more text fields.
• Handles text-specific processing such as tokenization and language rules.

Example: Suppose a document contains "NoSQL databases improve scalability". A regular index can efficiently match the complete string or a suitable prefix in some systems, but it cannot generally provide efficient word-based search for the term scalability inside the sentence. A text index is designed for this purpose.

Therefore, regular indexes are appropriate for structured predicates, while text indexes are appropriate for natural-language term retrieval.

Performance improvement: An index provides an organized access path to documents. Instead of examining every document, the database can locate candidate records through the index and fetch only relevant documents.

Important concepts:

• Selectivity: An index is more useful when a predicate eliminates many documents. An identifier usually has high selectivity, whereas a Boolean field with two values may have low selectivity.
• Index traversal: The database navigates the index structure to locate matching keys.
• Document examination: After locating index entries, the database may fetch and examine the referenced documents.
• Sorting: A suitable index can avoid an expensive in-memory sort.

Write trade-off:

• Inserts must add entries to every applicable index.
• Updates to indexed fields may require index deletion and reinsertion.
• Deletes must remove index entries.
• Indexes consume memory and disk space.

Thus, indexes improve read performance but should be created selectively. The best design balances frequent query patterns against write workload and resource costs.

The explain() method reveals how the database plans and executes a query. A typical command is:
javascript
db.orders.find({ customerId: "C101" }).explain("executionStats")

Important execution concepts:

• Collection scan (COLLSCAN): The database examines documents throughout the collection because no suitable index is used.
• Index scan (IXSCAN): The database traverses an index to find matching key entries.
• Fetch: Matching index entries are used to retrieve complete documents when the index does not contain all required fields.
• Documents examined: The number of documents inspected by the execution engine.
• Keys examined: The number of index keys inspected.
• Documents returned: The number of results produced.
• Execution time: The measured time for the selected execution plan.

A plan is usually more efficient when it examines substantially fewer documents than the collection size and returns results with an appropriate index. explain() should be evaluated with realistic data and representative queries.

Definition: A covered query is answered entirely from an index without fetching the complete documents from the collection.

Conditions:

• Every field required by the query must be present in the index.
• Every field returned in the projection must also be present in the index.
• The query must not require an unavailable field or document content.
• In MongoDB, the default _id field may need to be excluded from the projection unless it is included in the index.

Example:
javascript
db.users.createIndex({ email: 1, name: 1 })
db.users.find(
{ email: "ana@example.com" },
{ _id: 0, name: 1 }
)

The query and projection can be satisfied by the index alone. This reduces document reads, disk access, and memory use.

Limitations:

• Indexes require additional storage and write maintenance.
• A covered query may become uncovered when its projection changes.
• It is useful only when the indexed fields contain all required information.
• Coverage must be verified with explain() rather than assumed.

A practical evaluation compares the work performed by the query with the number of results returned and the size of the collection.

Let:

• be the number of documents examined.
• be the number of index keys examined.
• be the number of documents returned.
• be the total number of documents in the collection.

Useful measures include:

Interpretation:

• A low document examination ratio indicates that the query examines relatively few documents per result.
• A low collection scan fraction indicates that the query avoids scanning most of the collection.
• A high key examination ratio may indicate low selectivity or a poorly ordered index.
• A plan using IXSCAN is not automatically efficient; the examined and returned counts must also be considered.

The evaluation should include execution time, sort stages, realistic data volume, and read frequency. Indexes should be retained only when their measurable read benefit justifies storage and write costs.

An aggregation pipeline processes documents through a sequence of stages. Each stage receives documents from the previous stage, transforms or filters them, and passes the resulting stream to the next stage.

Typical stages:

• $match filters documents.
• $project selects or computes fields.
• $group combines documents according to a grouping key.
• $sort orders results.
• $limit restricts the number of results.
• $unwind expands array elements into separate documents.
• $lookup combines related data from another collection.

Example:
javascript
db.sales.aggregate([
{ $match: { year: 2025 } },
{ product", total: { amount" } } },
{ $sort: { total: -1 } }
])

Stage order matters because it affects both meaning and performance. A selective $match placed early reduces the number of documents processed by later stages. However, a stage must occur after any fields it depends on have been created or preserved.

$match: Filters documents using conditions similar to a query filter.
javascript
{ gt: 1000 } } }

It is commonly placed early to reduce processing volume.

$project: Controls the fields in the output and can compute new fields.
javascript
{ multiply: ["$amount", 0.18] } } }

It can include, exclude, rename, or calculate fields.

$group: Combines documents according to an _id expression and calculates accumulations.
javascript
{ $group: {
_id: "$customerId",
totalSpent: { amount" },
averageOrder: { amount" }
} }

Together, these stages can filter relevant records, shape the required data, and calculate summaries. Correct field references and accumulator expressions are essential for accurate results.

$sort: Orders documents according to one or more fields. It is useful for ranking products by sales or ordering reports by date.
javascript
{ $sort: { totalSales: -1 } }

$limit: Restricts the pipeline output to a specified number of documents. It is useful for retrieving the top 10 products.
javascript
{ $limit: 10 }

$unwind: Converts each element of an array into a separate document. It is useful for analyzing individual items in an order.
javascript
{ items" }

$lookup: Performs a left outer join-like operation with another collection. It is useful for attaching customer details to order records.
javascript
{
$lookup: {
from: "customers",
localField: "customerId",
foreignField: "_id",
as: "customer"
}
}

These stages should be ordered carefully to avoid unnecessary sorting, expansion, or joining of irrelevant documents.

A suitable pipeline first calculates the value of each line item and then groups the results by product.

db.orders.aggregate([
  {
    $project: {
      productId: 1,
      lineTotal: { $multiply: ["$quantity", "$unitPrice"] }
    }
  },
  {
    $group: {
      _id: "$productId",
      totalSales: { $sum: "$lineTotal" },
      averageOrderValue: { $avg: "$lineTotal" },
      orderCount: { $sum: 1 }
    }
  },
  { $sort: { totalSales: -1 } }
])

Explanation:

• $project derives lineTotal using .
• $group creates one result for every productId.
• $sum calculates total sales.
• $avg calculates the average value of the input line totals.
• $sum: 1 counts the contributing orders or documents.
• $sort ranks products from highest to lowest sales.

If the collection contains multiple line items per order, an additional grouping stage may be required to calculate the average value per complete order rather than per line item.

A monthly revenue report can be created by filtering valid transactions, extracting the year and month, grouping by that time period, and sorting chronologically.

db.transactions.aggregate([
  { $match: { status: "paid" } },
  {
    $group: {
      _id: {
        year: { $year: "$transactionDate" },
        month: { $month: "$transactionDate" }
      },
      revenue: { $sum: "$amount" },
      transactionCount: { $sum: 1 }
    }
  },
  { $sort: { "_id.year": 1, "_id.month": 1 } },
  {
    $project: {
      _id: 0,
      year: "$_id.year",
      month: "$_id.month",
      revenue: 1,
      transactionCount: 1
    }
  }
])

Explanation:

• $match excludes unpaid or cancelled transactions.
• $year and $month derive the reporting period from the date.
• $group calculates revenue and transaction count.
• $sort produces chronological results.
• $project formats the final report.

Timezone handling should be defined explicitly when transactions are recorded across multiple regions.

Indexes can reduce the amount of data entering an aggregation pipeline and can sometimes provide ordered input to later stages.

Index use by $match:

• An initial $match stage can use a suitable index, especially when it appears at the beginning of the pipeline.
• Filtering early reduces the number of documents processed by grouping, sorting, and joining stages.

Index use by $sort:

• A $sort stage may use an index when its order matches an available index and preceding stages preserve the required ordering.
• If the database cannot use an index, it may perform an in-memory or disk-backed sort.

Example index:
javascript
db.orders.createIndex({ status: 1, orderDate: -1 })

This may support a pipeline beginning with a match on status followed by sorting on orderDate.

Indexes generally cannot eliminate the computational cost of $group, and stages such as $project or $unwind may change the document shape. Pipeline optimization should therefore be verified with explain() and realistic data.

Although indexes improve selected reads, creating too many indexes can reduce overall system efficiency.

Negative effects:

• Storage usage: Each index consumes disk space and increases backup size.
• Write latency: Inserts, updates, and deletes must maintain all relevant index entries.
• Memory pressure: Frequently used index pages compete for memory with document data and other workloads.
• Build cost: Creating or rebuilding indexes can consume CPU, memory, and I/O resources.
• Redundancy: Multiple indexes may support overlapping query patterns without providing meaningful additional benefit.

For example, separate indexes on { department: 1 } and { department: 1, salary: 1 } may be redundant for some workloads because the compound index has department as its prefix. However, redundancy depends on sorting, projections, and actual query plans.

A sound indexing process identifies frequent and important queries, examines their plans, measures write impact, and removes unused or duplicate indexes after validation.

Compound-index design should reflect how a query filters and orders data.

Equality predicates: These match exact values, such as { region: "East" }. Equality fields are commonly placed early because they narrow the index range.

Range predicates: These use operators such as $gt, $lt, or $in in suitable contexts. A range field may limit how effectively later index fields can be used for filtering.

Sorting predicates: These require index key order compatible with the requested sort direction.

A commonly used guideline is:

• Equality fields first.
• Sort fields next when sorting is important.
• Range fields after them, depending on the query and database optimizer.

This is only a guideline. A query that performs a highly selective range scan may justify a different order, and a required sort may be more important than theoretical selectivity. Multikey fields, collations, projections, and data distribution also affect the result. The proposed index should be tested with explain() against representative workloads.

When a document field contains an array, indexing and aggregation must account for multiple values within one document.

Multikey indexes:

• A normal index on an array field becomes a multikey index in systems such as MongoDB.
• The index can support queries that match one or more array elements.
• A single document may contribute multiple index keys.
• Multikey indexes can have restrictions, especially when indexing multiple array paths.

Example:
javascript
db.products.createIndex({ tags: 1 })
db.products.find({ tags: "database" })

$unwind: This stage expands an array into separate pipeline documents.
javascript
{ tags" }

If a document has three tags, it can produce three pipeline records. This enables grouping or counting individual array elements.

Care is required because $unwind can multiply the number of records and therefore increase processing cost. Filtering before $unwind, when possible, can reduce unnecessary work.

Assume each order has an items array containing objects with productId and quantity. The pipeline is:

db.orders.aggregate([
  { $unwind: "$items" },
  {
    $group: {
      _id: "$items.productId",
      quantitySold: { $sum: "$items.quantity" }
    }
  },
  { $sort: { quantitySold: -1 } },
  { $limit: 3 },
  {
    $project: {
      _id: 0,
      productId: "$_id",
      quantitySold: 1
    }
  }
])

Stage explanation:

• $unwind creates one pipeline document for each ordered item.
• $group combines all occurrences of each product and sums quantities.
• $sort places the products with the greatest quantities first.
• $limit selects the top three products.
• $project formats the final output.

The order of $sort and $limit is important. Applying $limit before sorting would return three arbitrary products rather than the three best-selling products.

$lookup combines documents from one collection with matching documents in another collection. For example, an order can be associated with its customer record using customerId.

{
  $lookup: {
    from: "customers",
    localField: "customerId",
    foreignField: "_id",
    as: "customer"
  }
}

Benefits:

• Allows reporting across related collections.
• Avoids duplicating every customer attribute in every order.
• Supports dashboards such as customer purchase summaries.

Limitations and costs:

• The result is usually an array, even when one matching customer is expected.
• Large joins can consume CPU and memory.
• Missing or nonselective join keys can make the operation expensive.
• Frequently accessed read models may be faster when relevant data is denormalized.

Performance practices:

• Filter orders with $match before $lookup.
• Ensure the foreign join field is indexed where supported.
• Use a pipeline form of $lookup to filter and project only needed customer fields.
• Measure the complete pipeline using explain().