C100DBA Indexing and Performance Practice Test 3 — 15 Questions

Question 1

A sharded cluster experiences poor query performance because a targeted query targeting a specific shard key still results in a scatter-gather operation across all shards. What is the root cause?

Accepted Answer

The query predicate completely omitted the shard key or its valid prefix fields.. To target a specific shard directly, a query must include the exact shard key or a valid prefix of the shard key in its filter predicate. Omitting the shard key forces the mongos router to query every shard in the cluster to assemble the final result set.

Answer

The chunk migration threshold was set too low, causing frequent balancing cycles across shards.

Answer

The collection was created as unhashed, preventing the mongos router from hashing query inputs.

Answer

The balancer process was paused, leaving uneven document distribution across cluster shards.

Question 2

Refer to the exhibit. The explain output shows a SORT stage wrapping an IXSCAN stage. What is the performance implication of this execution plan?

Accepted Answer

The query engine performed an in-memory sort because the index did not support the requested sort order.. When a SORT stage wraps an IXSCAN stage, it means the index used for filtering did not satisfy the sort requirement. The query engine had to load the matching documents into memory to sort them, risking memory limit exceptions on large result sets.

Answer

The query is fully optimized because it leverages an index scan for filtering records.

Answer

The query executed a full collection scan across all cluster nodes.

Answer

The storage engine automatically converted the query into a covered query.

Question 3

An application frequently queries a large collection by 'user_id' and 'status', sorting the results by 'timestamp'. Which index provides the most efficient execution plan?

Accepted Answer

{ user_id: 1, status: 1, timestamp: 1 }. To optimize this query, the ESR (Equality, Sort, Range) rule must be applied. The 'user_id' and 'status' fields are equality filters, while 'timestamp' is used for sorting. An index of {user_id: 1, status: 1, timestamp: 1} allows MongoDB to satisfy the query filters and provide the sort order directly from the index, avoiding a blocking sort operation in memory which is critical for performance on large collections.

Answer

{ timestamp: 1, user_id: 1, status: 1 }

Answer

{ user_id: 1, timestamp: 1, status: 1 }

Answer

{ user_id: 1, status: 1 }

Question 4

Refer to the exhibit. What does this output indicate about the query performance?

Accepted Answer

The query requires an index to avoid a full collection scan.. A 'COLLSCAN' indicates that the database is performing a full collection scan, reading every document to satisfy the query. The high number of docs examined relative to the returned documents suggests that the query is inefficient. In a production environment, this is a major performance bottleneck, as the database engine must load entire documents into memory to evaluate the filter, leading to high I/O and latency.

Answer

The query is fully covered by an index.

Answer

The query is performing well because nReturned equals totalDocsExamined.

Answer

The index is being used, but it is not selective enough.

Question 5

Which situation is best suited for a Multikey Index?

Accepted Answer

Querying documents based on specific elements inside an array field.. Multikey indexes are used when you need to index a field that contains an array. MongoDB creates an index entry for each element in the array, allowing you to efficiently query for documents containing specific array values. This is essential for applications managing tags, inventory lists, or user attributes stored as arrays, ensuring that queries filtering by these elements perform with high efficiency rather than scanning the entire collection.

Answer

Indexing a collection where every document contains a simple numeric ID.

Answer

Improving the performance of large joins across multiple collections.

Answer

Enforcing unique constraints on non-array fields.

Question 6

When evaluating index performance, what is the impact of a 'partial index'?

Accepted Answer

It allows for faster writes by indexing fewer documents.. Partial indexes allow you to index only a subset of documents that meet a specific filter condition. This reduces index size and the overhead of maintaining the index during writes, as only updates to documents matching the filter trigger an index update. This is highly effective for reducing memory usage when you only need to index documents with specific statuses, such as 'active' or 'pending', rather than the entire collection.

Answer

It forces all documents in the collection to be indexed.

Answer

It automatically makes all queries return faster.

Answer

It is only compatible with unique indexes.

Question 7

A MongoDB DBA observes that a query with filter { status: "active", created_at: { $gte: ISODate("2024-01-01") } } and sort { created_at: -1 } is performing a collection scan. The collection has an index { status: 1, created_at: -1 }. Which of the following best explains why the index is not being used efficiently?

Accepted Answer

The index { status: 1, created_at: -1 } should support both the filter and sort; the collection scan indicates a different issue such as index not being built or query planner choosing a different plan.. The compound index { status: 1, created_at: -1 } is designed to support queries that filter on status with equality and sort on created_at. The index prefix (status) is an equality match, allowing the index to be used for both filtering and sorting. A collection scan indicates that the index might not exist, be invalid, or the query planner chose a different plan due to factors like low selectivity or stale statistics.

Answer

The index prefix is not equality on the leading field, so the sort cannot be satisfied by the index.

Answer

The index is a partial index that excludes documents with status "active", so it cannot be used.

Answer

The index does not include the sort field as the first field, so the sort cannot use the index.

Question 8

A DBA is designing indexes for a collection that stores user activity logs. The collection has fields: user_id, action, timestamp, and metadata (an embedded document). The most common queries are: (1) find all actions for a given user_id sorted by timestamp descending; (2) find all users who performed a specific action within a time range. Which TWO indexes would best support these queries? (Choose two.)

Accepted Answer

{ user_id: 1, timestamp: -1 }. The two indexes that best support the queries are { user_id: 1, timestamp: -1 } for query (1) and { action: 1, timestamp: 1 } for query (2). The first provides an equality match on user_id and a sort on timestamp, while the second provides an equality match on action and a range on timestamp. These indexes align with the equality-sort and equality-range patterns, ensuring efficient index usage.

Answer

{ metadata: 1 }

Answer

{ user_id: 1, action: 1, timestamp: -1 }

Answer

{ timestamp: 1 }

Question 9

A collection 'events' has a compound index { tenantId: 1, createdAt: -1 }. A developer runs db.events.find({ tenantId: "acme", createdAt: { $gte: ISODate("2024-01-01") } }).sort({ createdAt: -1 }).limit(50). The explain output shows the index is used but the query still performs a large in-memory sort. Which action resolves the sort performance issue?

Accepted Answer

Ensure the index is { tenantId: 1, createdAt: -1 } and verify the query does not use a collation or type mismatch that prevents index-provided sort.. A compound index can provide both the equality match and the sort when the equality fields form the prefix and the sort field follows. With { tenantId: 1, createdAt: -1 }, the query's equality on tenantId and sort on createdAt should be served without an in-memory sort. A blocking SORT in explain therefore points to a mismatch such as collation, type, or query shape rather than a missing index.

Answer

Add a sort key to the index: { tenantId: 1, createdAt: -1, _id: 1 }.

Answer

Reverse the compound index to { createdAt: -1, tenantId: 1 }.

Answer

Create a separate single-field index on createdAt.

Question 10

A DBA needs to optimize a query that filters on a field with high cardinality, such as email, and also sorts on a low cardinality field, such as country. The query is { email: "user@example.com" } with sort { country: 1 }. Which index would best support this query?

Accepted Answer

{ email: 1, country: 1 }. The compound index { email: 1, country: 1 } is optimal because it allows an equality match on email, which is highly selective, and then provides sorted results by country. The index order matches the query pattern: equality first, then sort. This avoids an in-memory sort and minimizes the number of documents examined.

Answer

{ email: 1 }

Answer

{ country: 1 }

Answer

{ country: 1, email: 1 }

Question 11

A DBA is troubleshooting a slow query on a large collection. Which two techniques directly reduce the number of documents the query must examine for an equality-plus-range predicate? (Choose two.)

Accepted Answer

Create a compound index with the equality field first and the range field second.. Reducing documents examined comes from narrowing the index scan. A compound index ordered equality-then-range lets the engine seek to the exact key range, and a partial index filtered on the equality predicate stores only relevant entries, shrinking the index. Both directly cut keys and documents examined. Read concern, hint to _id, and page fill settings do not change the access path work for this predicate.

Answer

Add a hint forcing the query to use the _id index.

Answer

Set the query's read concern to 'majority' to reduce examined documents.

Answer

Increase the index's fill factor using collMod to leave more space on pages.

Question 12

A DBA needs a query that returns only the fields name and email from a 'users' collection, filtered by an equality on email, to avoid fetching full documents from disk. Which index design supports this efficiently?

Accepted Answer

A compound index { email: 1, name: 1 } so the query can be covered by the index.. A covered query is one whose filter and projection are both satisfied by the index, so no document fetch is needed. Including every projected field in the index, as with { email: 1, name: 1 }, lets the engine return results straight from index keys. Single-field, hashed, and text indexes cannot supply the projected name field, so they cannot cover this query.

Answer

A single-field index on email, relying on the query's projection to fetch name from the document.

Answer

A text index on name and email to cover both fields.

Answer

A hashed index on email to speed equality matching.

Question 13

A collection 'logs' receives continuous inserts and is queried by both timestamp ranges and a rarely used severity field. A DBA creates six indexes to cover every query variant, and now insert throughput has dropped sharply while index sizes dominate the working set. Which action best restores insert throughput while preserving the important query paths?

Accepted Answer

Drop redundant indexes and consolidate overlapping ones into compound indexes that serve multiple query shapes.. Every index adds maintenance work to each insert, update, and delete, so a collection with many overlapping indexes suffers write amplification and cache pressure. Auditing index usage, dropping unused or redundant indexes, and consolidating overlapping prefixes into compound indexes reduces per-write maintenance while keeping the timestamp range and severity access paths covered. Write concern and cache tuning do not remove index maintenance cost.

Answer

Convert all indexes to hashed indexes to reduce index size.

Answer

Set the collection's write concern to w:0 to avoid index maintenance waits.

Answer

Increase the WiredTiger cache size so more indexes stay resident.

Question 14

A DBA notices that a query on the products collection uses an index scan but returns only a small fraction of documents. The index is { category: 1, price: 1 }. The query filters on category and price with a range condition on price. Which statement best describes why the index might still be efficient despite scanning many index entries?

Accepted Answer

The index scan is efficient because it can use the index bounds to limit the scan to the relevant category and price range, reducing the number of documents fetched.. The index { category: 1, price: 1 } supports efficient filtering when the query has an equality predicate on category and a range predicate on price. MongoDB can use index bounds to seek to the correct category and scan only the relevant price range, minimizing the number of documents fetched. This makes the index scan efficient despite examining many index entries.

Answer

The index scan is efficient because MongoDB automatically converts it to a collection scan when the selectivity is low.

Answer

The index scan is efficient because it avoids fetching documents entirely due to the index being covered.

Answer

The index scan is efficient because it uses the index only for sorting and not for filtering.

Question 15

A support team reports that a MongoDB 6.0 replica set is experiencing slow queries on a collection where almost every document has a unique value for the field status. A developer suggests creating a hashed index on status to speed up equality lookups. Which outcome should the DBA expect?

Accepted Answer

A hashed index will not improve equality query performance and is primarily intended for even data distribution in sharded clusters.. Hashed indexes are optimized for even distribution of values, which is critical for sharding but not for improving equality query performance on a single replica set. A standard B-tree index on a high-cardinality field like status already provides efficient equality lookups and supports range queries. Therefore, a hashed index would not be the right choice to speed up these queries; it is better suited for shard keys that need uniform distribution.

Answer

Hashed indexes provide no benefit for equality queries on high-cardinality fields and should not be used.

Answer

A hashed index will improve equality query performance but will prevent the use of range queries on status.

Answer

Hashed indexes are ideal for equality lookups and will outperform a standard B-tree index for this query pattern.