C100DEV Indexing — 15 Questions

Question 1

An application frequently queries a collection using equality conditions on 'status' and 'category', followed by a range filter on 'created_at', and sorts the final results by 'priority' descending. How should you design a compound index for optimal performance according to the ESR rule?

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

Accepted Answer

{ status: 1, category: 1, priority: -1, created_at: 1 }. The Equality, Sort, and Range rule dictates that fields with equality matches must come first, followed by the sort fields, and finally the range filter fields. This precise positioning prevents unnecessary in-memory sorts and allows MongoDB to use index bounds directly for range scans, maximizing query efficiency and minimizing traversal overhead in high-throughput production environments.

Answer

{ status: 1, category: 1, created_at: 1, priority: -1 }

Answer

{ priority: -1, status: 1, category: 1, created_at: 1 }

Answer

{ created_at: 1, status: 1, category: 1, priority: -1 }

Question 2

An application queries an array field named 'tags' containing strings for product categorization. Queries frequently search for documents matching multiple tags simultaneously. Which index strategy optimizes this query workload?

Accepted Answer

Create a multikey index on the 'tags' field.. Creating a multikey index on the tags array allows MongoDB to index every element of the array individually. When queries search for multiple elements, the query planner can efficiently intersect or utilize the multikey entries to narrow down candidate documents without performing full collection scans. This is the foundational pattern for querying any array field effectively in MongoDB applications.

Answer

Create a single-field standard index on the 'tags' field.

Answer

Create a hashed index on the 'tags' field.

Answer

Create a text index on the 'tags' field.

Question 3

A developer needs to ensure that the 'email' field in a 'users' collection is unique across all documents. Which TWO statements accurately describe the behavior or requirements of unique indexes in MongoDB?

Accepted Answer

If a document is missing the 'email' field, the unique index stores a null value for that document.. Unique indexes ensure that the indexed fields do not store duplicate values, which is critical for data integrity. They handle null values by treating them as a single unique value, meaning only one document can have a null 'email' unless a sparse or partial index is used. Understanding these behaviors prevents common errors when dealing with optional fields and data migration.

Answer

You can create a unique index on a field even if duplicate values already exist in the collection.

Answer

Unique indexes cannot be used in conjunction with the 'partialFilterExpression' option.

Answer

A unique index can be created on a hashed index to ensure uniqueness of the original value.

Question 4

You have a collection with 10 million documents. Most documents have a 'status' of 'processed', but about 1% have a 'status' of 'pending'. You frequently query for 'pending' documents sorted by 'creation_date'. Which indexing strategy provides the best balance between write performance and query efficiency?

Accepted Answer

A partial index on { creation_date: 1 } with a partialFilterExpression of { status: 'pending' }.. Partial indexes allow you to index only a subset of documents that meet a specific filter expression. In this scenario, indexing only 'pending' documents significantly reduces the index size and the overhead of updating the index during writes for 'processed' documents. This targeted approach is more efficient than a full index when a small fraction of data is frequently accessed.

Answer

A sparse index on { status: 1, creation_date: 1 } to exclude documents missing the status field.

Answer

A compound index on { status: 1, creation_date: 1 } covering all documents in the collection.

Answer

A TTL index on 'creation_date' that automatically deletes documents once they are 'processed'.

Question 5

Refer to the exhibit. Based on the explain output provided for a query on the 'logs' collection, which of the following is true about the query performance?

Accepted Answer

The query used the 'userId_1_timestamp_-1' index effectively with no wasted work.. The explain output shows an ideal scenario where the number of keys examined and documents examined exactly match the number of documents returned. This indicates that the index is highly selective and perfectly matches the query's filtering criteria. A 'totalKeysExamined' to 'nReturned' ratio of 1:1 is the gold standard for index efficiency in MongoDB.

Answer

The query is a 'covered query' because totalDocsExamined is equal to nReturned.

Answer

The index is inefficient because it had to examine 50 documents to return 50 results.

Answer

MongoDB performed a COLLSCAN because the winning plan stage is FETCH.

Question 6

You are implementing a session management system using MongoDB. Which TWO of the following are true regarding the use of TTL (Time-To-Live) indexes?

Accepted Answer

The background thread that removes expired documents runs every 60 seconds by default.. TTL indexes provide a way to automatically remove documents from a collection after a certain amount of time. They are managed by a background thread that runs periodically. Understanding the background nature of the deletion and the restriction on compound indexes is vital for designing reliable cleanup mechanisms and ensuring that the database remains performant under load.

Answer

TTL indexes can be created on compound fields to expire documents based on multiple dates.

Answer

A TTL index will automatically remove documents even if the indexed field is not a Date type.

Answer

TTL indexes can only be created on capped collections to ensure fixed storage size.

Question 7

You have an index defined as { last_name: 1, first_name: -1 }. Which TWO of the following sort patterns can be efficiently satisfied by this index?

Accepted Answer

{ last_name: 1, first_name: -1 }. When sorting on multiple fields in a compound index, MongoDB can use the index if the sort directions match the index exactly or are the exact inverse of the index. For an index on { A: 1, B: -1 }, it supports { A: 1, B: -1 } and { A: -1, B: 1 }. Other combinations like { A: 1, B: 1 } would require an in-memory sort.

Answer

{ last_name: 1, first_name: 1 }

Answer

{ first_name: -1, last_name: 1 }

Answer

{ last_name: -1, first_name: -1 }

Question 8

A developer wants to use a Wildcard Index to handle highly dynamic metadata fields. Which THREE of the following are limitations or characteristics of Wildcard Indexes?

Accepted Answer

Wildcard indexes cannot be used to enforce a unique constraint.. Wildcard indexes provide a flexible way to index unknown or unpredictable fields within a document or subdocument. However, they do not support several specialized indexing features such as TTL, GeoSpatial, or Hashed types. Understanding these limitations is crucial when deciding between a wildcard index and a set of specific, well-defined compound indexes.

Answer

Wildcard indexes support '2dsphere' and 'text' search capabilities naturally.

Answer

Wildcard indexes are more performant than compound indexes for fixed query patterns.

Question 9

Which TWO of the following statements regarding wildcard indexes in MongoDB are correct?

Accepted Answer

Wildcard indexes support compound index definitions where the wildcard is a secondary field.. Wildcard indexes are powerful tools for indexing arbitrary fields in documents with unpredictable structures, but they come with trade-offs. They cannot support every query type and have specific restrictions compared to standard single-field or compound indexes. Understanding these limitations is critical for database administrators to avoid performance degradation and ensure that queries are actually utilizing the indexes as expected during application development cycles.

Answer

Wildcard indexes can be used to index all fields in a document, including those inside nested arrays.

Answer

Wildcard indexes are functionally identical to creating individual indexes on every single field in a document.

Answer

Wildcard indexes support unique constraints on the indexed fields.

Question 10

Refer to the exhibit. Which compound index would be most efficient for this query?

Accepted Answer

{status: 1, name: 1, age: 1}. According to the ESR rule, the equality field 'status' should come first, followed by the sort field 'name', and finally the range field 'age'. This structure allows MongoDB to jump directly to the 'active' users, sort them by 'name', and then apply the 'age' filter. This minimizes the amount of data examined, ensuring the query engine performs as few disk operations as possible for the result set.

Answer

{age: 1, status: 1, name: 1}

Answer

{status: 1, age: 1, name: 1}

Answer

{name: 1, status: 1, age: 1}

Question 11

Which THREE of the following are valid limitations or behaviors of hashed indexes?

Accepted Answer

They are ideal for shard keys to ensure uniform distribution.. Hashed indexes provide high-speed equality lookups by hashing the field value, but they have specific constraints compared to B-tree indexes. They do not support range queries because the hashing function destroys the ordering of values. Additionally, they cannot be compound indexes and are primarily intended for sharding keys where uniform distribution is required to prevent hotspotting and ensure balanced data placement across the cluster nodes.

Answer

They support range queries.

Answer

They can be used as a compound index.

Question 12

Refer to the exhibit. Which of the following queries can be served efficiently by this index?

Accepted Answer

db.products.find({category: 'electronics'}).sort({price: 1}). This compound index supports queries that filter by 'category' and sort by 'price', or filter by 'category' alone. Because the index is ordered, it can handle both ascending and descending sorts for the fields in the sequence as long as the prefix is matched. This allows the index to support a variety of common filtering and sorting patterns, significantly improving the performance of product retrieval operations by narrowing the search space effectively.

Answer

db.products.find({}).sort({price: 1})

Answer

db.products.find({price: 100}).sort({category: 1})

Answer

db.products.find({tags: 'sale'}).sort({category: 1})

Question 13

An application frequently queries a collection using equality conditions on 'status' and 'category', followed by a range filter on 'created_at', and sorts the final results by 'priority' descending. How should you design a compound index for optimal performance according to the ESR rule?

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

Accepted Answer

db.users.createIndex({ status: 1, created_at: 1 }). The ESR rule dictates that equality fields should be placed first in a compound index, followed by sort fields, and range fields last. Placing equality fields first reduces the candidate document set immediately. Range fields like created_at come last because they stop efficient traversal for subsequent index keys.

Answer

db.users.createIndex({ created_at: 1, status: 1 })

Answer

db.users.createIndex({ status: -1, created_at: -1 })

Answer

db.users.createIndex({ created_at: -1, status: 1 })

Question 14

An application frequently queries a collection using equality conditions on 'status' and 'category', followed by a range filter on 'created_at', and sorts the final results by 'priority' descending. How should you design a compound index for optimal performance according to the ESR rule?

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

Accepted Answer

{ status: 1, joinDate: -1, age: 1 }. The Equality, Sort, Range (ESR) rule dictates placing equality fields first, sort fields second, and range fields last to maximize index traversal efficiency. By placing 'status' first for exact matches, 'joinDate' second to satisfy the sort without an in-memory stage, and 'age' last for the range condition, MongoDB can efficiently traverse the B-tree and return sorted results immediately.

Answer

{ status: 1, age: 1, joinDate: -1 }

Answer

{ joinDate: -1, status: 1, age: 1 }

Answer

{ age: 1, status: 1, joinDate: -1 }

Question 15

An e-commerce application frequently queries a collection by filtering on category and sorting by price descending. You need to create an index that efficiently supports both the equality query and the sort operation without requiring an in-memory sort stage. How should you define the index key pattern?

Accepted Answer

{ category: 1, price: 1 }. To support both an equality match and a descending sort efficiently, the index fields must follow the Equality, Sort, Range order. The equality field comes first, followed by the sort field with a negative one value to indicate descending sort order, allowing MongoDB to traverse the B-tree directly in the requested sort direction.

Answer

{ price: -1, category: 1 }

Answer

{ category: -1, price: -1 }

Answer

{ price: 1, category: -1 }