C100DEV Indexing Practice Test 2 — 15 Questions

Question 1

A collection contains large documents with deeply nested arrays. You want to create an index on a field inside these arrays to support queries matching multiple elements simultaneously. Which index type provides this capability while preventing Cartesian explosions in index size?

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

Accepted Answer

Compound multikey index. A multikey index indexes array values, but indexing multiple nested arrays simultaneously can create index bloat through Cartesian explosions. Newer MongoDB versions support partial and specialized indexing strategies, but standard multikey rules apply unless managed carefully with specific compound array limits.

Answer

Text index

Answer

Wildcard index

Answer

Hashed index

Question 2

A collection `inventory` has an index `{ category: 1, price: 1 }`. A developer runs `db.inventory.find({ price: { $gt: 100 } }).sort({ category: 1 }).explain('executionStats')` and notices a high number of documents examined. They want to reduce the number of examined documents while keeping the same index. Which index usage strategy should they apply?

Accepted Answer

Add a hint to force the query to use the existing index and rely on the index bounds to limit examined documents.. The existing compound index { category: 1, price: 1 } is designed to support queries that filter on category and sort by price. However, for a query that filters on price with a range and sorts on category, the index can still be used effectively because it is sorted by category first. The query can scan the index in category order and apply the price range filter within each category, which reduces the number of documents examined compared to a collection scan. Forcing the use of this index with a hint ensures the query planner chooses it, leveraging the index's order to handle the sort and filter efficiently. Creating additional indexes or attempting a covered query would not provide the same benefits in this context.

Answer

Create a new compound index on `{ price: 1, category: 1 }` to better support the range filter and sort.

Answer

Use a covered query by projecting only the `category` and `price` fields and ensuring the index covers the query.

Answer

Create a separate index on `{ price: 1 }` and drop the compound index.

Question 3

A social media application has a `posts` collection with a field `tags` that is an array of strings. You create an index on `{ tags: 1 }`. A query retrieves posts that contain both "mongodb" and "database" in the `tags` array. Which statement accurately describes how MongoDB uses this index for the query?

Accepted Answer

The index can be used to satisfy the query, and MongoDB will automatically intersect the index entries for the two tag values.. A multikey index on an array field creates index entries for each element. For a query that requires multiple tags, MongoDB can use the index to find documents containing each tag and then intersect the results. This allows efficient retrieval without a full collection scan, though the planner may choose index intersection or a compound index if available. The key is that the multikey index supports queries on individual array elements.

Answer

The index stores a single entry for the entire array, so it cannot help with queries that match individual elements.

Answer

The index cannot be used because it is multikey and queries with multiple array element conditions are unsupported.

Answer

The index will only be used if the query uses the $all operator; otherwise, it performs a collection scan.

Question 4

A development team is designing an index strategy for a MongoDB collection that stores sensor readings. Each document has a `sensor_id` field, a `timestamp` field, and a `value` field. The team needs to support queries that filter on `sensor_id`, filter on a range of `timestamp`, and project only `sensor_id` and `timestamp`. They also want to minimize the number of indexes and ensure queries are covered. Which TWO of the following statements are true regarding the creation of a compound index to achieve these goals? (Choose two.)

Accepted Answer

A compound index on { sensor_id: 1, timestamp: 1 } can support queries that filter on sensor_id and sort by timestamp.. The compound index { sensor_id: 1, timestamp: 1 } supports queries that filter on sensor_id and sort or range on timestamp, and it can cover queries that only need sensor_id and timestamp. It follows the ESR rule for equality, sort, and range. Including the value field is unnecessary for covering the described query, and reversing the index order would be less efficient for the given filter patterns.

Answer

A compound index on { timestamp: 1, sensor_id: 1 } is more efficient for queries that filter on sensor_id and range on timestamp than { sensor_id: 1, timestamp: 1 }.

Answer

To cover a query that filters on sensor_id and projects sensor_id and timestamp, the index must include the value field.

Answer

To support a query that filters on a range of timestamp and sorts by sensor_id, the index { sensor_id: 1, timestamp: 1 } is optimal.

Question 5

You have a collection `orders` with a compound index on `{ customer_id: 1, order_date: -1, status: 1 }`. A query filters on `customer_id` and `status`, and sorts by `order_date` descending. Which statement best describes the index's ability to support this query efficiently?

Accepted Answer

The index can be used for the filter on customer_id and status, and it can provide the descending sort on order_date without a blocking sort.. The compound index is ordered by customer_id, then order_date descending, then status. For a query with equality on customer_id and status, and a sort on order_date descending, the index can be used efficiently. The equality on customer_id allows the index to be scanned in order_date descending order for that customer, and the status equality is applied as a filter. This avoids a blocking sort, making the index suitable for the query.

Answer

The index cannot support the sort because the sort field order_date is not the first field in the index.

Answer

The index will be used only if the query includes an equality condition on order_date; otherwise, it performs a collection scan.

Answer

The index can support the filter but will require an in-memory sort because the sort direction does not match the index direction for order_date.

Question 6

A financial application runs a query that filters on `accountId` equality and `balance` range, then sorts by `lastUpdated` descending. The developer creates the index { accountId: 1, balance: 1, lastUpdated: -1 }. During testing, the planner selects this index but the plan shows a SORT stage. Which change should the developer make to eliminate the blocking sort?

Accepted Answer

Reorder the index to { accountId: 1, lastUpdated: -1, balance: 1 }.. The index follows equality, then range, then sort, but the sort field must precede the range field to avoid a blocking sort. Reordering to `{ accountId: 1, lastUpdated: -1, balance: 1 }` keeps the equality prefix, places the sort field in the correct direction, and leaves the range field last, allowing an ordered index scan with no SORT stage.

Answer

Add a second single-field index on `lastUpdated` and rely on index intersection.

Answer

Change the sort direction to ascending so it matches the existing index.

Answer

Create a hashed index on `accountId` and a separate index on `lastUpdated`.

Question 7

A developer is designing an index for a collection 'inventory' that will be sharded. The shard key is { warehouseId: 1, itemId: 1 }. They want to create an index that supports queries that filter on warehouseId and sort by quantity. Which index should they create to optimize these queries while being compatible with the sharded cluster?

Accepted Answer

Create a compound index { warehouseId: 1, quantity: 1 }.. In a sharded cluster, indexes must include the shard key as a prefix to be effective for routing queries. The shard key is { warehouseId: 1, itemId: 1 }, so an index that starts with warehouseId is compatible. The index { warehouseId: 1, quantity: 1 } supports filtering on warehouseId and sorting on quantity, as the equality on warehouseId makes the quantity values contiguous and sorted. This optimizes the queries.

Answer

Create a compound index { quantity: 1, warehouseId: 1 }.

Answer

Create a single-field index on quantity.

Answer

Create a compound index { warehouseId: 1, itemId: 1, quantity: 1 }.

Question 8

A developer is designing indexes for a MongoDB collection that stores product data. The collection has a field `tags` that is an array of strings, and a field `price` that is a number. The developer needs to support queries that filter on both `tags` and `price`, and also queries that sort by `price`. Which TWO of the following statements are true regarding the creation of a compound index on `{ tags: 1, price: 1 }`? (Choose two.)

Accepted Answer

The index can be used to efficiently filter on `tags` and `price` together.. A compound index on `{ tags: 1, price: 1 }` where `tags` is an array becomes a multikey index. This means it creates index entries for each element in the `tags` array, allowing efficient filtering on both `tags` and `price` together. However, it cannot efficiently support sorting by `price` when filtering on `tags` because the multikey nature disrupts the global sort order. It also cannot efficiently filter on `price` alone, as the index prefix is `tags`. The correct statements are that it can filter on both fields and that it is a multikey index.

Answer

The index can be used to efficiently sort by `price` for queries that filter on `tags`.

Answer

The index can be used to efficiently sort by `tags` for queries that filter on `price`.

Answer

The index can be used to efficiently filter on `price` alone.

Question 9

A MongoDB collection stores event logs with a 'timestamp' field and a 'level' field. The collection is very large, and most queries filter on level: 'ERROR' and sort by timestamp descending. However, only about 5% of documents have level 'ERROR'. You want to create an index that minimizes index size and improves write performance while still supporting these queries. Which index should you create?

Accepted Answer

A partial index on { timestamp: -1 } with filter expression { level: 'ERROR' }. The scenario requires an index that supports queries filtering on level: 'ERROR' and sorting by timestamp descending, while minimizing index size and write overhead. A partial index with a filter on level: 'ERROR' includes only the small subset of matching documents, greatly reducing size and maintenance cost. Indexing timestamp descending supports the sort. The other options either include all documents or use inappropriate index types.

Answer

A sparse index on { level: 1, timestamp: -1 }

Answer

A TTL index on { timestamp: 1 } with expireAfterSeconds set to 0

Answer

A compound index on { level: 1, timestamp: -1 }

Question 10

You have a collection of user activity logs where queries frequently filter by `userId` and sort by `timestamp` in descending order. Which single index definition provides optimal support for this query pattern without requiring an in-memory sort?

Accepted Answer

{ userId: 1, timestamp: -1 }. To avoid an in-memory sort, the index direction must match the query's sort direction for the sorted fields. Since `userId` is filtered via equality and `timestamp` is sorted descending, the index `{ userId: 1, timestamp: -1 }` allows MongoDB to traverse the index naturally in the required order, ensuring optimal query performance and preventing sorting memory limit errors during execution.

Answer

{ userId: 1, timestamp: 1 }

Answer

{ timestamp: -1, userId: 1 }

Answer

{ userId: -1, timestamp: -1 }

Question 11

An e-commerce application frequently executes a query filtering by 'category' and 'brand', and then sorts the results by 'price' in ascending order. You created a compound index on { category: 1, brand: 1, price: 1 }. Which statement best describes how MongoDB utilizes this index for a query that filters only on 'brand' and 'price'?

Accepted Answer

MongoDB will perform a collection scan as the query lacks the 'category' prefix required by the index.. Compound indexes in MongoDB utilize a prefix-based matching system. To use an index for filtering or sorting, the query must include the first field of the index or a sequence of fields starting from the first. Since 'category' is the first field and is missing from the query filter, MongoDB cannot use this specific index for efficient lookups, highlighting the importance of field ordering in index design.

Answer

The query engine will perform an index scan because 'brand' and 'price' are part of the index.

Answer

The index will be used only for the sort operation on 'price' but not for the filter on 'brand'.

Answer

MongoDB will use the index to find 'brand' but will still need to fetch documents to verify 'price'.

Question 12

You are designing a schema for a social media platform where users have an array of 'tags' and an array of 'mentions'. You attempt to create an index on { tags: 1, mentions: 1 }. What is the most likely outcome of this operation?

Accepted Answer

The index build fails with an error stating that a compound index cannot include more than one array.. MongoDB restricts multikey indexes such that a single compound index cannot cover more than one array field. This limitation prevents the index from growing exponentially in size, which would happen if it had to create a cartesian product of every element in both arrays. Developers must choose which array is more critical for query performance or use alternative indexing strategies.

Answer

The index is created successfully and supports queries that filter by both tags and mentions.

Answer

The index is created but only the first element of each array is indexed to save space.

Answer

The index is created but it will only be used if the query provides a specific index hint.

Question 13

Your application executes the query: db.users.find({ age: { $gt: 30 } }, { age: 1, _id: 0 }). You have an index on { age: 1 }. How does MongoDB process this query?

Accepted Answer

This is a covered query, and MongoDB retrieves all data from the index alone.. A covered query occurs when the index contains all the fields required by both the query filter and the projection. Because the query only requests the 'age' field and explicitly excludes '_id', MongoDB can fulfill the entire request by looking only at the index. This avoids the need to fetch documents from the heap, significantly improving performance.

Answer

MongoDB performs an index scan followed by a fetch to get the 'age' values.

Answer

MongoDB must fetch the documents because projections are not supported in covered queries.

Answer

The query cannot be covered because the _id field is always required from the document.

Question 14

You are considering using a hashed index for a 'product_id' field to distribute data across shards. Which of the following is a known limitation of hashed indexes in MongoDB?

Accepted Answer

Hashed indexes do not support range-based queries or sort operations.. Hashed indexes compute a hash of the value of a field and use that hash to create the index. While this is great for equality matches and even distribution in sharding, it destroys the natural ordering of the data. Consequently, hashed indexes cannot support range-based queries (like greater than or less than) or sorting operations.

Answer

Hashed indexes do not support equality matches, only range queries.

Answer

Hashed indexes can only be created on fields that contain unique values.

Answer

Hashed indexes are only available in the MongoDB Enterprise Edition.

Question 15

Refer to the exhibit. If a query is executed as db.collection.find({ "metadata.type": "book" }), how will MongoDB use the created index?

Accepted Answer

MongoDB will perform a collection scan because the index is on the whole object.. Indexing a whole subdocument (the 'metadata' field) creates an index on the literal BSON object. This index is only useful for exact matches of the entire subdocument, including field order. It cannot be used to look up individual fields within that subdocument using dot notation. To index internal fields, you must use the dot notation in the index definition itself.

Answer

The index will be used to quickly find all documents where 'type' is 'book'.

Answer

The index will be used, but performance will be slower than a dot-notation index.

Answer

The index build will fail because metadata is a nested document.