C100DBA Indexing and Performance — Timed 20 Questions

Question 1

Refer to the exhibit. Why is the query execution plan performing a FETCH stage after an IXSCAN, and how can it be optimized?

Accepted Answer

Change the index to {status: 1, type: 1, created: -1}.. The FETCH stage indicates that the index used (status, type) does not contain the 'created' field, forcing MongoDB to load documents from disk to sort them in memory. This is a common performance bottleneck. Adding the 'created' field to the index as the third key will allow an index-based sort, enabling the query to be covered by the index and eliminating the expensive blocking sort and fetch operations.

Answer

The index is missing the 'status' field, causing a scan.

Answer

The query is not using an index at all.

Answer

Remove the index to improve write throughput.

Question 2

Which TWO of the following statements regarding the TTL (Time-To-Live) index are correct?

Accepted Answer

Deletion of expired documents is not guaranteed to occur immediately.. TTL indexes are a specialized feature used to automatically remove documents from a collection after a specific duration. They are crucial for managing temporary data like sessions or logs. Understanding their constraints—such as not being compatible with capped collections and the approximate nature of the deletion process—is fundamental for ensuring data lifecycle management is handled correctly within the database.

Answer

They can be used on capped collections.

Answer

They can be created on any field type.

Answer

They are only available in the Community Edition.

Question 3

Which THREE factors significantly impact the performance of a MongoDB write operation?

Accepted Answer

The number of indexes on the collection.. Write performance is influenced by several architectural factors. Index overhead is primary, as every write requires updating all relevant indexes. Replication lag affects write concern acknowledgement, and disk I/O latency limits the throughput of the storage engine. Understanding how these factors interact allows developers and DBAs to tune write concerns and index counts to meet the specific throughput and latency requirements of their applications.

Answer

The amount of available RAM on the server.

Answer

The number of active read-only users.

Question 4

Refer to the exhibit. A query filters on field 'a'. Which index will the optimizer choose?

Accepted Answer

It will choose based on the optimizer's cost analysis.. MongoDB's query optimizer evaluates available indexes based on cost. When both {a: 1, b: 1} and {a: 1} are available, both can satisfy a query filtering on 'a'. However, the optimizer typically prefers the index that is most specific or smaller if the costs are identical. In many cases, the single-field index {a: 1} is preferred as it is smaller and more efficient for a single-field filter.

Answer

It will always use {a: 1, b: 1}.

Answer

It will always use {a: 1}.

Answer

It will perform a collection scan.

Question 5

What happens when a $sort operation cannot be satisfied by an index?

Accepted Answer

The query fails if the sorted data exceeds 100 MB.. If a query result set exceeds the 100 MB limit for an in-memory sort, MongoDB will throw an error and terminate the query. When sorting is required but no index can provide the order, MongoDB must perform a blocking sort in memory. This is a common performance trap that often leads to high memory consumption and application errors if the collection grows significantly larger than expected.

Answer

The database automatically creates a temporary index.

Answer

It defaults to a disk-based sort automatically.

Answer

The sort is performed by the client driver.

Question 6

A high-throughput collection in a MongoDB cluster experiences degraded write performance due to a heavily fragmented compound index. Which TWO administrative actions safely resolve index fragmentation without downtime? (Choose TWO)

Accepted Answer

Perform a rolling maintenance procedure by stepping down secondaries, rebuilding their indexes, and finally stepping down the primary.. Managing index fragmentation in production environments requires careful operational planning to avoid locking primary nodes or starving application traffic. Rebuilding indexes online or performing rolling primary-to-secondary maintenance ensures high availability while reclaiming wasted disk space and restoring optimal B-tree traversal speeds.

Answer

Execute the compact command on every individual collection containing fragmented indexes during peak operational hours.

Answer

Drop the fragmented index and recreate it using the background option in MongoDB versions prior to 4.2.

Answer

Rebuild indexes using the createIndexes command with unique naming strategies to replace the old structures.

Question 7

Which TWO statements are true regarding the use of compound indexes in MongoDB?

Accepted Answer

A compound index on {A, B} can be used for a query filtering on {A}.. Compound indexes are powerful but must be designed with the prefix rule in mind. An index on {a, b, c} can support queries on {a}, {a, b}, and {a, b, c}. However, it cannot support a query on {b, c} alone because the prefix 'a' is missing. Understanding these limitations is vital for index pruning and ensuring that the query optimizer chooses the most efficient path for data retrieval.

Answer

A compound index on {A, B} can be used for a query filtering only on {B}.

Answer

Adding more fields to a compound index always improves query performance.

Answer

Compound indexes are limited to a maximum of 5 fields per index.

Question 8

What is the primary benefit of a 'covered query' in MongoDB?

Accepted Answer

It eliminates the need to load the full document into memory.. A covered query occurs when all fields requested by a query are present in the index itself. Because the query engine can retrieve all the necessary data from the index tree without fetching the actual documents from the data files (the heap), it significantly reduces I/O operations. This is one of the most effective ways to optimize read-heavy workloads and minimize memory usage during query execution.

Answer

It forces the data to remain in the WiredTiger cache.

Answer

It automatically creates an index on all queried fields.

Answer

It reduces the amount of disk space consumed by the collection.

Question 9

Which THREE factors contribute to slow query performance in MongoDB?

Accepted Answer

The working set size exceeds the available RAM.. Performance issues in MongoDB are typically driven by inefficient access patterns, resource saturation, or architectural bottlenecks. When queries lack proper indexing, the database must perform collection scans, which are extremely slow on large datasets. Additionally, high concurrency, locked documents, or memory pressure (where the working set exceeds RAM) force the database to read from disk, leading to significant latency spikes that degrade the overall application performance.

Answer

Using a replica set for data redundancy.

Answer

The use of the WiredTiger storage engine.

Question 10

Refer to the exhibit. Why might this geospatial query be underperforming?

Accepted Answer

The $maxDistance is too large, causing too many documents to be evaluated.. Geospatial queries require specific indexing and data structures. If the index isn't used, the query might default to a less efficient operator. Furthermore, geospatial queries are computationally expensive. Without proper bounds or if the data set is very dense, the $near operator can trigger excessive calculations, leading to high latency. Ensuring the query matches the index precision and the $maxDistance parameter is optimized is essential for performance.

Answer

2dsphere indexes do not support $near queries.

Answer

Geospatial indexes require a compound index on all fields.

Answer

The coordinates are stored in the wrong order (latitude/longitude).

Question 11

A collection named events contains millions of documents with fields timestamp (Date), userId (String), and eventType (String). A new application query frequently filters on timestamp and userId and sorts by timestamp descending. The DBA creates an index { timestamp: 1, userId: 1 }. The query's performance is still poor because MongoDB must perform an in-memory sort. Which index should the DBA create instead to allow the query to use the index for both filtering and sorting?

Accepted Answer

{ timestamp: -1, userId: 1 }. The query filters on timestamp and userId and sorts by timestamp descending. An index with timestamp as the leading field in descending order and userId as the secondary field allows MongoDB to use the index for the timestamp range and userId equality while also providing the required descending sort order. This eliminates the in-memory sort and improves performance.

Answer

{ userId: -1, timestamp: 1 }

Answer

{ timestamp: 1, userId: -1 }

Answer

{ userId: 1, timestamp: -1 }

Question 12

A DBA runs db.orders.find({ status: "shipped", customerId: 42 }).explain("executionStats") and sees totalKeysExamined equal to nReturned, but the plan uses an index on { customerId: 1 } rather than the existing { status: 1, customerId: 1 }. The collection has high cardinality on status. Which statement best explains the planner's choice?

Accepted Answer

The planner chose the index that minimized totalKeysExamined and totalDocsExamined for this query shape, and the single-field index on customerId was more selective here.. The query planner selects the plan that examines the fewest keys and documents for the given predicates. When customerId is more selective than status, an index on customerId alone can locate the matching documents with minimal work, so the planner may prefer it over the compound index even though both are eligible. Selectivity and examined counts, not index type, drive the choice.

Answer

The planner always prefers single-field indexes over compound indexes when both are available.

Answer

The compound index was rejected because it lacks a sort component matching the query.

Answer

The compound index { status: 1, customerId: 1 } cannot be used because status is a string and customerId is a number.

Question 13

A DBA is investigating a slow query on the orders collection. The query is: db.orders.find( { status: "shipped", customerId: 12345 } ).sort( { orderDate: -1 } ). The collection has an index { customerId: 1, status: 1, orderDate: -1 }. The query planner chooses a collection scan instead of using the index. Which action would most likely allow the index to be used effectively?

Accepted Answer

Create a new index { customerId: 1, orderDate: -1, status: 1 }.. The query has equality filters on customerId and status and sorts by orderDate descending. The optimal compound index places equality fields first and the sort field next, so { customerId: 1, orderDate: -1, status: 1 } allows MongoDB to filter on customerId, use the index for the descending sort on orderDate, and apply the status filter as a residual check. This avoids an in-memory sort and improves performance.

Answer

Create a new index { orderDate: -1, customerId: 1, status: 1 }.

Answer

Create a new index { status: 1, customerId: 1, orderDate: -1 }.

Answer

Add a hint to force the use of the existing index.

Question 14

A collection named orders has a compound index { customerId: 1, orderDate: -1, total: 1 }. A DBA runs a query that filters on customerId and total, and sorts by orderDate descending. Which statement about the query's index usage is correct?

Accepted Answer

The index can be used for both the filter and the sort because customerId is an equality prefix, orderDate provides the sort order, and total can be applied as an in-memory filter.. The compound index { customerId: 1, orderDate: -1, total: 1 } supports this query because customerId is an equality predicate that bounds the leading key, orderDate matches the requested descending sort, and total can be evaluated as an index filter on the remaining key. This avoids a blocking sort and reduces document fetches, which is exactly the scenario compound indexes are designed to optimize.

Answer

The index cannot be used for the sort because total is specified in the filter and appears after orderDate in the index key pattern.

Answer

The index can be used for the sort but not for the filter, because a non-equality field (total) breaks the index prefix used for filtering.

Answer

The index cannot be used at all because the query does not include orderDate in its filter predicate.

Question 15

A DBA is optimizing a collection that supports a high volume of read queries. Several queries filter on a field named category and sort by a field named createdAt descending. The DBA wants to create an index that allows these queries to be covered. Which two conditions must be met for a query to be covered by an index? (Choose two.)

Accepted Answer

All fields in the query filter must be part of the index key.. A covered query requires that all fields used in the query filter and all fields returned in the results are present in the index. Additionally, the projection must exclude _id unless _id is part of the index. The index must contain category and createdAt, and the query must project only those fields, making the index sufficient to answer the query without fetching documents.

Answer

The index must be a unique index.

Answer

The query must not include any projection that excludes the _id field.

Answer

The query must use the $natural hint to force index usage.

Question 16

A DBA is investigating why a query on a collection with a compound index { a: 1, b: 1, c: 1 } is not using the index for a sort on { b: 1, a: 1 }. The query filters on a and c. Which explanation is correct?

Accepted Answer

The index cannot support the sort because the sort keys { b: 1, a: 1 } do not match the index order; the index is ordered by a, then b, then c.. The compound index { a: 1, b: 1, c: 1 } is ordered by a, then b, then c. The query filters on a and c, with a likely an equality predicate. For the sort { b: 1, a: 1 }, the effective sort after fixing a is on b, which matches the next index key. However, the sort specification includes a after b, which is redundant because a is constant. The index can support the sort on b, but the sort on a is unnecessary. The correct explanation is that the index cannot support the sort as specified because the sort keys do not form a prefix of the index keys in the correct order, but in practice, the sort on a is redundant and the index can still be used for the b sort. The DBA should recognize that the index can support the sort on b, but the sort on a is unnecessary. The correct answer is that the index can support the sort because the equality on a makes the sort on a redundant, and the sort on b matches the next index key.

Answer

The index cannot support the sort because the filter on c is not an equality predicate and breaks the index prefix.

Answer

The index can support the sort because the filter on a provides an equality bound, and the sort on b is the next key in the index.

Answer

The index can support the sort because the sort keys are a subset of the index keys, and MongoDB can reorder the sort keys to match the index.

Question 17

A DBA is designing indexes for a collection that supports a reporting workload. The queries frequently filter on region and status, and sort by createdAt descending. The DBA creates a compound index { region: 1, status: 1, createdAt: -1 }. Which two statements about this index are correct? (Choose two.)

Accepted Answer

The index can support queries that filter on region and status and sort by createdAt descending without a blocking sort.. The compound index { region: 1, status: 1, createdAt: -1 } is optimized for queries that have equality predicates on region and status and sort by createdAt. It supports the descending sort directly, and it can also support the ascending sort by scanning the index in reverse. It does not efficiently support queries that omit the leading field region, nor does it support sorts that do not align with the index order after equality predicates.

Answer

The index can support queries that filter on region and sort by createdAt descending, but only if status is not part of the filter.

Answer

The index can also support queries that filter only on status and sort by createdAt descending, using the index efficiently.

Answer

The index can support queries that filter on status and sort by region ascending, because status is an equality predicate.

Question 18

An application frequently queries a large collection using two fields: 'category' (equality) and 'timestamp' (range). Which index strategy provides the most efficient execution plan?

Accepted Answer

{ category: 1, timestamp: 1 }. The ESR (Equality, Sort, Range) rule dictates that equality fields should come first in a compound index, followed by sort fields, and finally range fields. By placing 'category' first, MongoDB eliminates most irrelevant documents immediately. The 'timestamp' field follows, allowing the engine to leverage the index for range filtering. This minimizes memory usage and prevents costly full collection scans, which is critical for maintaining performance as data volume scales.

Answer

{ timestamp: 1, category: 1 }

Answer

{ category: 1 }

Answer

{ timestamp: 1 }

Question 19

Which property of a field makes it a poor candidate for an index?

Accepted Answer

The field has very low cardinality.. Low cardinality fields, such as those with only two or three unique values, provide little benefit when indexed. MongoDB's query optimizer often skips these indexes because a collection scan is usually faster than traversing the B-tree for a large percentage of the dataset. Identifying high-cardinality fields is essential for building effective indexes that genuinely speed up data retrieval rather than just adding overhead to write operations.

Answer

The field is frequently used in filter criteria.

Answer

The field contains unique identifiers like UUIDs.

Answer

The field is a numeric timestamp.

Question 20

When would you choose to create a Partial Index instead of a standard index?

Accepted Answer

When only a subset of data is frequently queried.. Partial indexes are designed to index only a subset of documents that meet a specific filter expression. They are highly efficient when queries only target a small, specific portion of a large collection. By reducing the size of the index in memory and on disk, partial indexes lower storage costs and decrease the impact on write operations while maintaining query performance for the intended subset of data.

Answer

When you need to ensure the index covers all documents.

Answer

To improve performance for all possible queries.

Answer

To automatically shard the collection data.