C100DBA Indexing and Performance Practice Test 2 — 15 Questions

Question 1

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?

Choose an answer to begin — your selection is scored in the full session.

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 2

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 3

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 4

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 5

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.

Question 6

Refer to the exhibit. A developer attempts to run a query using both 'tags' and 'location' in a single $and operation. What is the expected behavior regarding index usage?

Accepted Answer

The optimizer may choose an index intersection plan.. MongoDB can utilize multiple indexes for a single query through an 'index intersection' execution plan. However, the query optimizer decides whether to perform an intersection or use just one index based on cost. For complex queries involving geo-spatial data and arrays, intersection might be less efficient than a single compound index, and the optimizer may choose a collection scan if intersection costs are deemed too high.

Answer

It will always use both indexes simultaneously.

Answer

It will fail because compound indexes are required.

Answer

It will ignore all indexes and scan the collection.

Question 7

Refer to the exhibit. Which index is most effective for this query?

Accepted Answer

{ tags: 1 }. The $all operator is used to find documents where the field contains all the specified elements. When indexing an array field, MongoDB creates a multikey index. A simple index on 'tags' is sufficient here because the multikey index structure inherently supports finding documents that contain specific array elements, enabling the query engine to efficiently narrow down the result set without scanning all documents in the collection.

Answer

{ tags: 'hashed' }

Answer

{ tags: 1, _id: 1 }

Answer

No index is required for array fields.

Question 8

What is the primary benefit of using a Covered Query in MongoDB?

Accepted Answer

It avoids reading the full document from disk.. A covered query is one where all the fields in the query projection exist within the index itself. Because the database can retrieve all necessary data directly from the index tree without needing to fetch the actual document from disk, it drastically reduces I/O. This is one of the most effective ways to optimize read performance for high-frequency queries in large collections.

Answer

It forces the data to stay in the WiredTiger cache.

Answer

It automatically compresses all returned data.

Answer

It eliminates the need for any index on the collection.

Question 9

Why should you avoid creating too many indexes on a single collection?

Accepted Answer

It increases the cost of write operations.. Every index adds overhead to every write operation (insert, update, delete) because the database must maintain the index structure alongside the collection. Furthermore, indexes consume significant memory, and if the working set (the data and indexes most frequently accessed) exceeds available RAM, the database will experience frequent page faults from disk, leading to severe performance degradation across all types of operations.

Answer

It prevents the use of sharding.

Answer

It automatically disables the WiredTiger cache.

Answer

It forces queries to use a collection scan.

Question 10

When evaluating the performance of a 'hint' in MongoDB, what should you keep in mind?

Accepted Answer

Hints prevent the optimizer from adapting.. Using a hint forces the optimizer to use a specific index, ignoring its own cost-based selection. While this can sometimes be useful for troubleshooting or very specific cases where the optimizer makes a poor choice, it is generally discouraged because it prevents the database from adapting to data changes. As the data distribution evolves, a hinted index might become suboptimal, leading to degraded performance compared to a dynamic plan.

Answer

Hints are always faster than the optimizer's choice.

Answer

Hints should be used for all production queries.

Answer

Hints are only available for aggregation queries.

Question 11

Which TWO of the following scenarios are best handled by a Hashed Index?

Accepted Answer

Sharding a collection on a high-cardinality key.. Hashed indexes map the hash of the field value to the document, which is excellent for distributing data evenly across shards. They are ideal for fields with high cardinality that are primarily used for equality lookups. They do not support range-based queries, so they should not be used for fields where inequality filtering (like greater than or less than) is the primary query pattern.

Answer

Range-based queries on numeric fields.

Answer

Sorting results by the indexed field.

Answer

Text search on long strings.

Question 12

What is the consequence of having a high number of 'keysExamined' compared to 'nReturned' in a query?

Accepted Answer

The query is inefficiently scanning the index.. A large discrepancy between 'keysExamined' and 'nReturned' suggests that the index is not very selective or is being used inefficiently. The engine is scanning many index entries that do not match the query criteria, which is a major performance drain. Investigating the selectivity of the index and potentially adding more fields to the compound index can help narrow down the search and improve efficiency.

Answer

The index is too small and needs to be rebuilt.

Answer

The database is performing a cache miss.

Answer

The query will be automatically optimized.

Question 13

An e-commerce application frequently runs a query filtering by category and sorting by price descending. Which index structure provides the optimal performance for this access pattern?

Accepted Answer

{ category: 1, price: -1 }. To optimize queries with equality filters and descending sorts, the index must match the equality field first, followed by the sort field with the correct direction. Placing category first satisfies equality lookup, while setting price to negative one avoids in-memory sorting stages during execution.

Answer

{ price: -1, category: 1 }

Answer

{ category: 1, price: 1 }

Answer

{ price: 1, category: -1 }

Question 14

An operations engineer notices that a frequently executed aggregation pipeline fails with a 'Exceeded memory limit for $sort' error. Which index configuration best resolves this memory constraint?

Accepted Answer

Create a compound index matching the query's match filter fields followed by the sort fields in exact order.. MongoDB restricts in-memory operations like sorting to a fixed buffer size. When a query cannot satisfy its sort requirements from an index, it loads documents into memory. Providing a compound index containing the query's equality, range, and sort fields allows the execution engine to stream sorted results directly.

Answer

Create a wildcard index across all collection fields to capture unpredictable aggregation pipeline projections.

Answer

Increase the global cluster parameter maxInMemorySortBytes beyond the default allocation limit.

Answer

Enable allowDiskUse on every query connection string globally across the application configuration.

Question 15

Refer to the exhibit. An application queries active user documents frequently, but explainPlan output reveals a COLLSCAN stage. What is the most appropriate remediation step?

Accepted Answer

Create a single-field index on the status field using db.collection.createIndex({ status: 1 }).. A COLLSCAN stage indicates that the query engine scanned every document in the collection because no suitable index was found. Creating an index on the filtered field enables the query planner to select an IXSCAN stage, drastically reducing disk reads and improving throughput.

Answer

Modify the application connection string to include readConcern level linearizable.

Answer

Restart the primary database node to clear the query plan cache and force re-evaluation.

Answer

Increase the WiredTiger cache size configuration parameter to cache the entire collection in RAM.