C100DEV Indexing Practice Test 4 — 15 Questions

Question 1

An application queries a users collection with db.users.find({ email: "a@b.com" }). The collection holds about 2 million documents, and the email field is unique for every user. A developer proposes creating an index { email: 1, country: 1 } to speed up the query. What is the most accurate assessment of this proposal?

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

Accepted Answer

It works, but it is wasteful; a single-field index { email: 1 } on this unique field would give the same lookup efficiency with a smaller index.. Because email is the leading field of the compound index, the planner can use it and seek directly to the single matching entry, so the query is still fast. But the trailing country key is never touched by this query, and email is already unique, so it adds no selectivity. A single-field index on email achieves the same lookup with less storage and lower write amplification.

Answer

It is invalid unless the index is declared as unique to match the field's uniqueness.

Answer

It is optimal because compound indexes always outperform single-field indexes on the leading field.

Answer

It fails because the query does not include the country field, so the compound index cannot be used.

Question 2

A logistics application stores shipment records in a collection where each document has a numeric field `weight_kg`. Queries frequently filter on `weight_kg` with a range (e.g., between 50 and 200) and sort by `weight_kg` ascending. You create a standard ascending index on `weight_kg`. Which statement best describes the index's behavior for these queries?

Accepted Answer

The index can efficiently serve both the range filter and the ascending sort, avoiding an in-memory sort.. A single-field ascending index on a numeric field stores keys in sorted order. When a query filters on a range of that field and sorts ascending on the same field, MongoDB can scan the index range and return results in the requested order. This avoids an expensive blocking sort and leverages the index for both filtering and sorting, making it the optimal choice for such query patterns.

Answer

The index cannot be used for range queries on numeric fields because B-tree indexes are designed for equality matches only.

Answer

The index can support the range filter but cannot provide sorted results, so a blocking sort will be required.

Answer

The index will be used only if the range filter is selective enough; otherwise, MongoDB ignores it and performs a collection scan.

Question 3

A logistics company stores shipment events in a collection where each document contains a `history` array of subdocuments with fields `status`, `timestamp`, and `location`. Queries frequently filter on `history.status` and sort by `history.timestamp`. An index is created on `history.status`. What is the most accurate statement about this index?

Accepted Answer

The index is multikey; each element of the `history` array produces a separate index key, and querying `history.status` can use the index.. When an indexed field contains an array, MongoDB builds a multikey index and creates an index key for each array element. This allows queries that filter on `history.status` to use the index efficiently. The index is not restricted to the first element, it is not sparse by default, and creating it is fully supported.

Answer

The index is sparse by default because array fields often contain missing values, so it excludes documents where `history.status` is absent.

Answer

The index stores one key per document, using the first element of the `history` array, so only queries matching the first event can use it.

Answer

The index cannot be created because MongoDB does not allow indexing a field that contains an array of subdocuments.

Question 4

A financial application has a collection `transactions` with fields `accountId`, `type`, `amount`, and `createdAt`. A query filters on `accountId` and `type`, sorts descending by `createdAt`, and projects only `_id` and `amount`. A developer creates the index `{ accountId: 1, type: 1, createdAt: -1 }`. Which statement best describes whether this index can support the query as a covered query?

Accepted Answer

It cannot be covered because the index lacks the `amount` field, so MongoDB must fetch documents to return `amount`.. A covered query requires that all fields used in the filter, sort, and projection exist in the index. Here the projected field `amount` is not part of the compound index, so MongoDB must fetch documents to return it. Neither excluding `_id` nor using `explain()` changes that; the index would need to include `amount`.

Answer

It can be covered if the query uses `explain()` with the `covered` option, which forces MongoDB to answer from the index.

Answer

It can be covered only if the projection explicitly excludes `_id`, because the index does not contain the `_id` field.

Answer

It is automatically covered because `_id` is always included in every index, so all projected fields are present.

Question 5

A logistics application stores shipment records in a collection named `shipments`. Each document includes a `status` field (e.g., "in_transit", "delivered") and a `delivery_date` field. The development team frequently runs a query that filters on `status` and sorts by `delivery_date` in ascending order. They want to create a compound index to optimize this query. Which index should they create?

Accepted Answer

{ status: 1, delivery_date: 1 }. For a query that filters on one field and sorts on another, the index should list the equality-filtered field first, followed by the sort field. This allows MongoDB to use the index for both the filter and the sort, avoiding an expensive in-memory sort. The index { status: 1, delivery_date: 1 } matches this pattern and is optimal.

Answer

{ status: 1 }

Answer

{ delivery_date: 1, status: 1 }

Answer

{ status: 1, delivery_date: -1 }

Question 6

A retail application queries an `orders` collection with a filter on `status` and `customerId`, and a sort on `orderDate`. The query shape is: `db.orders.find({ status: "shipped", customerId: 12345 }).sort({ orderDate: -1 })`. The collection has no indexes other than the default `_id` index. Which index should be created to best support this query according to the ESR guideline?

Accepted Answer

{ status: 1, customerId: 1, orderDate: -1 }. The ESR guideline orders compound index fields as Equality, Sort, Range. The query has equality predicates on `status` and `customerId` and a sort on `orderDate`. Placing both equality fields first and the sort field last, as in `{ status: 1, customerId: 1, orderDate: -1 }`, lets the index serve the equality matches and provide sorted output without an in-memory sort.

Answer

{ status: 1, orderDate: -1, customerId: 1 }

Answer

{ customerId: 1, orderDate: -1, status: 1 }

Answer

{ orderDate: -1, status: 1, customerId: 1 }

Question 7

A developer is working with a collection that stores user profiles. The collection has an index on the `email` field. The developer runs a query that uses a regular expression to find all users whose email ends with "@example.com". The query is: `db.users.find({ email: /@example\.com$/ })`. What is the most likely behavior of this query with respect to the index?

Accepted Answer

The query will perform a collection scan because the regular expression is not anchored at the beginning.. MongoDB can use an index for regular expression queries only when the regex is anchored at the start of the string (e.g., /^prefix/). A regex that matches a suffix or an arbitrary substring cannot leverage the index's sorted order, so the query performs a full collection scan. The given query, which looks for emails ending with "@example.com", is not left-anchored and therefore cannot use the index efficiently.

Answer

The query will use the index but will still need to filter the results in memory.

Answer

The query will use the index efficiently because regular expressions can always leverage indexes.

Answer

The query will use the index only if the field is a covered index.

Question 8

A developer is working with a MongoDB collection that stores user profiles. The collection has a unique index on `email` and a compound index on `{ lastName: 1, firstName: 1 }`. The developer needs to enforce that no two documents have the same combination of `tenantId` and `username`. Which TWO of the following statements are correct regarding the creation and behavior of a unique compound index for this requirement? (Choose two.)

Accepted Answer

Creating a unique compound index on `{ tenantId: 1, username: 1 }` enforces uniqueness across the combination of both fields, not on each field individually.. A unique compound index on `{ tenantId: 1, username: 1 }` enforces uniqueness on the combination of both fields, not on either field alone. If existing duplicates are present, the index build will fail until they are resolved or excluded via a partial filter expression. The index is not sparse by default, does not enforce prefix uniqueness, and is created with `createIndex`, not `collMod`.

Answer

The unique compound index will also enforce uniqueness on `tenantId` alone, because the first field of a compound index is treated as a separate unique key.

Answer

Creating the unique compound index requires the `collMod` command to be run first on the collection to enable unique constraints.

Answer

A unique compound index automatically becomes a sparse index, so documents missing either `tenantId` or `username` are excluded from the uniqueness constraint.

Question 9

A developer maintains a collection of sensor readings. Queries almost always filter on `status` and then sort by `timestamp` descending. The current index is { status: 1, timestamp: 1 }. The developer observes that the query planner selects a COLLSCAN even though the index covers both fields. Which index change should be made to allow an efficient index scan for this query?

Accepted Answer

Create a new index { status: 1, timestamp: -1 } and drop the existing index.. The query combines an equality predicate on `status` with a descending sort on `timestamp`. Following equality-sort-range ordering, the equality field belongs first and the sort field must match the query direction, giving `{ status: 1, timestamp: -1 }`. That lets the planner walk the index in the requested order without a blocking sort, which is why the reversed or split alternatives fail.

Answer

Add a separate index { timestamp: -1 } and keep the existing compound index.

Answer

Create a new index { timestamp: -1, status: 1 } and drop the existing index.

Answer

Create a hashed index on `status` combined with `timestamp` to improve selectivity.

Question 10

An IoT platform stores sensor readings in a collection `readings` with fields `deviceId`, `timestamp`, and `value`. The collection is large and grows continuously. Queries typically retrieve the most recent readings for a specific device, filtering by `deviceId` and sorting by `timestamp` descending. The operations team wants to minimize index size while still supporting these queries efficiently. Which index strategy best meets this requirement?

Accepted Answer

Create a compound index on { deviceId: 1, timestamp: -1 } only, which supports the equality filter and the descending sort without an additional index.. The query pattern filters by `deviceId` and sorts by `timestamp` descending. A compound index on `{ deviceId: 1, timestamp: -1 }` directly supports both the equality match and the sort, allowing MongoDB to return results in order without an in-memory sort. This single index is sufficient, minimizing index size and avoiding unnecessary additional indexes.

Answer

Create a hashed index on { deviceId: 1 } combined with a single-field index on { timestamp: -1 } to distribute data and support sorting.

Answer

Create a compound index on { deviceId: 1, timestamp: -1 } and a separate single-field index on { timestamp: -1 } to handle global sorts.

Answer

Create a single-field index on { deviceId: 1 } only, because the sort on `timestamp` can always be performed in memory efficiently.

Question 11

An application writes documents to a `metrics` collection where each document contains an embedded array `samples` of subdocuments with fields `value` and `unit`. A query filters on `samples.unit` and `samples.value` together, expecting both conditions to match the same array element. The developer creates { "samples.unit": 1, "samples.value": 1 }. Which behavior should the developer expect from this index?

Accepted Answer

The index is valid and can be used, but it may return documents where `unit` and `value` match different array elements unless the filter is written carefully.. Compound multikey indexes are allowed when the indexed paths belong to the same array, so the index can be created and used to reduce the candidate set. The critical limitation is that the index does not preserve element pairing: a document can match `samples.unit` and `samples.value` from different elements. Using `$elemMatch` restores the same-element requirement during document filtering.

Answer

The index is automatically converted into a wildcard index covering all subdocument fields.

Answer

The index enforces that `unit` and `value` come from the same element, returning only matching pairs.

Answer

The index cannot be created because compound multikey indexes are disallowed on array fields.

Question 12

A collection 'orders' has a compound index { status: 1, customerId: 1, orderDate: -1 }. A developer runs a query: db.orders.find({ customerId: 'C123', status: 'shipped' }).sort({ orderDate: -1 }). Which statement about how MongoDB uses this index for the query is correct?

Accepted Answer

The index can be used for the equality filter on status and customerId, and it can also provide the sort on orderDate in descending order without an in-memory sort.. The compound index begins with status and customerId, both of which have equality conditions in the query. Since those fields are fixed, the index's remaining field orderDate is effectively ordered by its index direction. The query sorts by orderDate descending, matching the index direction, so MongoDB can use the index to satisfy both the filter and the sort without an in-memory sort stage.

Answer

The index cannot be used for this query because the query includes a sort on a field that is not the first field of the index.

Answer

The index can be used for the equality filter on status and customerId, but the sort on orderDate will require an in-memory sort because the index stores orderDate in ascending order only.

Answer

The index can be used for both the equality filter on status and customerId, but it cannot provide the sort order on orderDate because the sort field is not contiguous with the equality fields.

Question 13

A team stores user profiles in a `profiles` collection. About 90% of documents have a `deletedAt` field set to null, and only 10% have an actual timestamp indicating soft deletion. Queries that list active users filter on `deletedAt: null` and sort by `createdAt`. The team wants an index that stays small and avoids maintaining entries for deleted documents. Which index definition best matches this requirement?

Accepted Answer

db.profiles.createIndex({ createdAt: 1 }, { partialFilterExpression: { deletedAt: null } }). A partial index limits entries to documents matching its filter expression. Filtering on `deletedAt: null` stores only active profiles, so the index is far smaller than a full compound or sparse index and still supports the equality-plus-sort query. Sparse indexes are unsuitable because the field exists with a null value rather than being absent.

Answer

db.profiles.createIndex({ createdAt: 1 }, { expireAfterSeconds: 0 })

Answer

db.profiles.createIndex({ deletedAt: 1 }, { sparse: true })

Answer

db.profiles.createIndex({ deletedAt: 1, createdAt: 1 })

Question 14

You are the lead developer for a high-traffic e-commerce application using MongoDB 6.0. A query on the `orders` collection that filters on `customerId` and sorts by `orderDate` in descending order is performing poorly. You decide to create an index to improve its performance. Which of the following indexes would be most efficient for this query?

Accepted Answer

db.orders.createIndex({ customerId: 1, orderDate: -1 }). The most efficient index for a query with an equality filter and a sort is one that follows the Equality, Sort, Range (ESR) rule: equality fields first, then sort fields. The index must also match the sort direction. Here, the query filters on `customerId` (equality) and sorts by `orderDate` descending. Therefore, an index on `{ customerId: 1, orderDate: -1 }` allows MongoDB to use the index for both filtering and sorting, avoiding an expensive in-memory sort and ensuring optimal performance.

Answer

db.orders.createIndex({ customerId: 1, orderDate: 1 })

Answer

db.orders.createIndex({ orderDate: -1, customerId: 1 })

Answer

db.orders.createIndex({ orderDate: 1, customerId: 1 })

Question 15

A developer is working with a collection 'products' that has an index { category: 1, price: 1 }. They want to understand how MongoDB can use this index for queries that include a sort on price. Which TWO of the following statements are true regarding the use of this index for sorting? (Choose two.)

Accepted Answer

The index can support a sort on price descending if the query includes an equality condition on category, by scanning the index in reverse.. The index { category: 1, price: 1 } can support sorting on price when the query includes an equality condition on category, because then the index's price entries are contiguous and sorted. It can also support a descending sort by scanning the index in reverse. Without an equality condition on category, the index cannot provide a global sort on price because the leading field category varies.

Answer

The index can support a sort on price descending even if the query does not include any condition on category, because the index stores price in descending order when traversed in reverse.

Answer

The index can support a sort on price ascending if the query includes a range condition on category, such as { category: { $gt: 'A' } }.

Answer

The index can support a sort on price ascending even if the query does not include any condition on category.