C100DBA Indexing and Performance Practice Test 1 — 15 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.