Indexing practice questions

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?

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

Placing the range field created_at before the sort field priority violates the ESR rule, forcing MongoDB to perform an in-memory sorting stage after retrieving documents, which degrades performance and memory utilization under heavy application concurrency.

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

Placing the sort field priority before the equality fields status and category prevents the query optimizer from leveraging equality index bounds effectively, resulting in an inefficient index scan that must evaluate unnecessary key combinations.

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

Starting with the range field created_at breaks the ESR rule sequence entirely, forcing MongoDB to scan a much wider index range than necessary before filtering for equality matches and sorting results.

A
{ status: 1, category: 1, created_at: 1, priority: -1 }
Why it fails: Placing the range field created_at before the sort field priority violates the ESR rule, forcing MongoDB to perform an in-memory sorting stage after retrieving documents, which degrades performance and memory utilization under heavy application concurrency.
B
{ status: 1, category: 1, priority: -1, created_at: 1 }
Following the ESR rule, the equality fields status and category appear first, followed by the sort field priority, and finally the range field created_at. This structure allows the query engine to efficiently navigate index pointers and satisfy both sorting and range requirements simultaneously without extra sorting.
C
{ priority: -1, status: 1, category: 1, created_at: 1 }
Why it fails: Placing the sort field priority before the equality fields status and category prevents the query optimizer from leveraging equality index bounds effectively, resulting in an inefficient index scan that must evaluate unnecessary key combinations.
D
{ created_at: 1, status: 1, category: 1, priority: -1 }
Why it fails: Starting with the range field created_at breaks the ESR rule sequence entirely, forcing MongoDB to scan a much wider index range than necessary before filtering for equality matches and sorting results.

Question 2mediummultiple choice

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?

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

Standard indexes on array fields treat the entire array as a single atomic value rather than indexing individual elements. Consequently, queries filtering for specific items inside the array cannot leverage this index efficiently and will result in collection scans.

Trap 2: Create a hashed index on the 'tags' field.

Hashed indexes support exact-match queries for sharded cluster distribution but do not support range queries, sorting, or multi-element array matching. Attempting to create a hashed index on an array field results in an error during index creation.

Trap 3: Create a text index on the 'tags' field.

Text indexes are designed for linguistic text search capabilities such as stemming and stop words. Using a text index for exact tag categorization is inefficient and restricted to specific text search operator syntax.

A
Create a single-field standard index on the 'tags' field.
Why it fails: Standard indexes on array fields treat the entire array as a single atomic value rather than indexing individual elements. Consequently, queries filtering for specific items inside the array cannot leverage this index efficiently and will result in collection scans.
B
Create a multikey index on the 'tags' field.
MongoDB automatically creates a multikey index when you index a field that holds an array. This indexes each element of the array separately, enabling fast lookups and efficient query execution when filtering documents based on elements inside that array.
C
Create a hashed index on the 'tags' field.
Why it fails: Hashed indexes support exact-match queries for sharded cluster distribution but do not support range queries, sorting, or multi-element array matching. Attempting to create a hashed index on an array field results in an error during index creation.
D
Create a text index on the 'tags' field.
Why it fails: Text indexes are designed for linguistic text search capabilities such as stemming and stop words. Using a text index for exact tag categorization is inefficient and restricted to specific text search operator syntax.

Question 3mediummulti select

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?

Trap 1: You can create a unique index on a field even if duplicate values…

MongoDB will fail to create a unique index if the collection already contains documents with duplicate values in the target field. You must first clean the data by removing or modifying duplicates before the index creation command can succeed. This ensures that the uniqueness constraint is immediately enforced for all data.

Trap 2: Unique indexes cannot be used in conjunction with the…

Unique indexes are frequently combined with partial filter expressions to enforce uniqueness only on documents that meet specific criteria. For example, you can enforce unique emails only for active users. This combination is a powerful tool for managing data integrity in collections that store different types of documents or states.

Trap 3: A unique index can be created on a hashed index to ensure…

MongoDB does not support unique constraints on hashed indexes. Hashed indexes are primarily used for even data distribution in sharded clusters and do not support the uniqueness property. If you need a unique index, you must use a standard ascending or descending index type to ensure the values are truly distinct.

A
If a document is missing the 'email' field, the unique index stores a null value for that document.
When a field is missing from a document, MongoDB treats it as having a value of null within the index. Because the index is unique, only one document in the collection can omit the 'email' field. Subsequent documents missing the field will cause a duplicate key error during insertion or update operations.
B
You can create a unique index on a field even if duplicate values already exist in the collection.
Why it fails: MongoDB will fail to create a unique index if the collection already contains documents with duplicate values in the target field. You must first clean the data by removing or modifying duplicates before the index creation command can succeed. This ensures that the uniqueness constraint is immediately enforced for all data.
C
Unique indexes cannot be used in conjunction with the 'partialFilterExpression' option.
Why it fails: Unique indexes are frequently combined with partial filter expressions to enforce uniqueness only on documents that meet specific criteria. For example, you can enforce unique emails only for active users. This combination is a powerful tool for managing data integrity in collections that store different types of documents or states.
D
A unique index can be created on a hashed index to ensure uniqueness of the original value.
Why it fails: MongoDB does not support unique constraints on hashed indexes. Hashed indexes are primarily used for even data distribution in sharded clusters and do not support the uniqueness property. If you need a unique index, you must use a standard ascending or descending index type to ensure the values are truly distinct.
E
In a sharded collection, the unique index must contain the shard key as a prefix or be the shard key itself.
For sharded collections, MongoDB can only enforce uniqueness across shards if the unique index includes the shard key. This allows the mongos router to direct the uniqueness check to the specific shard responsible for that key range. Without this, the cluster would need to perform an expensive cross-shard check for every write.

Question 4hardmultiple choice

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?

Trap 1: A sparse index on { status: 1, creation_date: 1 } to exclude…

Sparse indexes only exclude documents that are missing the indexed field entirely. Since all documents likely have a 'status' field (even if it is 'processed'), a sparse index would still include all 10 million documents. This does not provide the performance benefits of a partial index which filters based on specific field values.

Trap 2: A compound index on { status: 1, creation_date: 1 } covering all…

While a full compound index would support the query, it would be 100 times larger than necessary and incur write penalties for every document inserted or updated. In high-volume systems, maintaining a massive index where 99% of the entries are rarely queried leads to unnecessary memory pressure and slower disk I/O operations.

Trap 3: A TTL index on 'creation_date' that automatically deletes documents…

TTL (Time-To-Live) indexes are used for expiring data based on time, not based on field values like 'status'. While you could theoretically use TTL to delete old data, it does not solve the problem of efficiently querying 'pending' documents within a large active set. It also risks losing data that might still be needed.

A
A sparse index on { status: 1, creation_date: 1 } to exclude documents missing the status field.
Why it fails: Sparse indexes only exclude documents that are missing the indexed field entirely. Since all documents likely have a 'status' field (even if it is 'processed'), a sparse index would still include all 10 million documents. This does not provide the performance benefits of a partial index which filters based on specific field values.
B
A compound index on { status: 1, creation_date: 1 } covering all documents in the collection.
Why it fails: While a full compound index would support the query, it would be 100 times larger than necessary and incur write penalties for every document inserted or updated. In high-volume systems, maintaining a massive index where 99% of the entries are rarely queried leads to unnecessary memory pressure and slower disk I/O operations.
C
A partial index on { creation_date: 1 } with a partialFilterExpression of { status: 'pending' }.
A partial index only includes documents where 'status' is 'pending'. This results in a very small, highly efficient index that speeds up queries for pending items while ignoring the millions of 'processed' documents. It minimizes storage requirements and reduces the frequency of index updates during standard 'processed' document write operations.
D
A TTL index on 'creation_date' that automatically deletes documents once they are 'processed'.
Why it fails: TTL (Time-To-Live) indexes are used for expiring data based on time, not based on field values like 'status'. While you could theoretically use TTL to delete old data, it does not solve the problem of efficiently querying 'pending' documents within a large active set. It also risks losing data that might still be needed.

Question 5mediummultiple choice

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?

Exhibit

{
  "queryPlanner": {
    "winningPlan": {
      "stage": "FETCH",
      "inputStage": {
        "stage": "IXSCAN",
        "keyPattern": { "userId": 1, "timestamp": -1 },
        "indexName": "userId_1_timestamp_-1",
        "direction": "forward"
      }
    }
  },
  "executionStats": {
    "nReturned": 50,
    "totalKeysExamined": 50,
    "totalDocsExamined": 50
  }
}

Trap 1: The query is a 'covered query' because totalDocsExamined is equal…

A query is only 'covered' if totalDocsExamined is 0, meaning MongoDB retrieved all necessary data from the index itself without fetching the actual documents. In this exhibit, totalDocsExamined is 50, which means the database had to access the disk or cache to retrieve the full document bodies after scanning the index.

Trap 2: The index is inefficient because it had to examine 50 documents to…

Examining 50 documents to return 50 results is actually perfectly efficient. Inefficiency occurs when 'totalDocsExamined' is significantly higher than 'nReturned', indicating that the index was not specific enough and the engine had to filter out many documents manually after fetching them from storage. This 1:1 ratio is optimal.

Trap 3: MongoDB performed a COLLSCAN because the winning plan stage is…

The exhibit clearly shows an 'IXSCAN' (Index Scan) as the input stage to the 'FETCH' stage. A 'COLLSCAN' would mean no index was used. The 'FETCH' stage simply means that after finding the relevant index entries, MongoDB went to the collection to retrieve the actual document data requested by the query.

A
The query is a 'covered query' because totalDocsExamined is equal to nReturned.
Why it fails: A query is only 'covered' if totalDocsExamined is 0, meaning MongoDB retrieved all necessary data from the index itself without fetching the actual documents. In this exhibit, totalDocsExamined is 50, which means the database had to access the disk or cache to retrieve the full document bodies after scanning the index.
B
The index is inefficient because it had to examine 50 documents to return 50 results.
Why it fails: Examining 50 documents to return 50 results is actually perfectly efficient. Inefficiency occurs when 'totalDocsExamined' is significantly higher than 'nReturned', indicating that the index was not specific enough and the engine had to filter out many documents manually after fetching them from storage. This 1:1 ratio is optimal.
C
The query used the 'userId_1_timestamp_-1' index effectively with no wasted work.
The output shows that 'totalKeysExamined' and 'totalDocsExamined' are both equal to 'nReturned'. This demonstrates that every index entry scanned and every document fetched was a match for the query. There was no 'scanning' of irrelevant data, making this a highly performant query that utilizes the index correctly.
D
MongoDB performed a COLLSCAN because the winning plan stage is FETCH.
Why it fails: The exhibit clearly shows an 'IXSCAN' (Index Scan) as the input stage to the 'FETCH' stage. A 'COLLSCAN' would mean no index was used. The 'FETCH' stage simply means that after finding the relevant index entries, MongoDB went to the collection to retrieve the actual document data requested by the query.

Question 6mediummulti select

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?

Trap 1: TTL indexes can be created on compound fields to expire documents…

TTL indexes are restricted to a single field; they cannot be compound indexes. If you need to expire documents based on complex logic or multiple fields, you must use a single date field that is calculated at write time or manage the deletion process manually through an application-level worker or script.

Trap 2: A TTL index will automatically remove documents even if the indexed…

For a TTL index to function, the indexed field must contain a BSON Date type or an array of BSON Date types. If the field contains other data types, such as strings or timestamps, the TTL thread will ignore those documents and they will never be automatically deleted, potentially leading to unexpected storage growth.

Trap 3: TTL indexes can only be created on capped collections to ensure…

TTL indexes cannot be used on capped collections. Capped collections have their own fixed-size management system where the oldest documents are removed to make room for new ones. TTL indexes are designed for standard collections where individual documents need to be deleted based on a specific time attribute rather than insertion order.

A
TTL indexes can be created on compound fields to expire documents based on multiple dates.
Why it fails: TTL indexes are restricted to a single field; they cannot be compound indexes. If you need to expire documents based on complex logic or multiple fields, you must use a single date field that is calculated at write time or manage the deletion process manually through an application-level worker or script.
B
The background thread that removes expired documents runs every 60 seconds by default.
MongoDB uses a background task to find and delete expired documents, typically running once per minute. This means there can be a slight delay between a document reaching its expiration time and its actual removal from the collection. Applications should not rely on millisecond-precision timing for TTL deletions due to this frequency.
C
If the indexed field is an array of dates, the document expires when the earliest date is reached.
When a TTL index is applied to an array field containing multiple date values, MongoDB uses the earliest date in the array to determine the expiration time. This allows for flexible expiration logic where a document might have multiple triggers for deletion, with the first one taking precedence for the cleanup process.
D
A TTL index will automatically remove documents even if the indexed field is not a Date type.
Why it fails: For a TTL index to function, the indexed field must contain a BSON Date type or an array of BSON Date types. If the field contains other data types, such as strings or timestamps, the TTL thread will ignore those documents and they will never be automatically deleted, potentially leading to unexpected storage growth.
E
TTL indexes can only be created on capped collections to ensure fixed storage size.
Why it fails: TTL indexes cannot be used on capped collections. Capped collections have their own fixed-size management system where the oldest documents are removed to make room for new ones. TTL indexes are designed for standard collections where individual documents need to be deleted based on a specific time attribute rather than insertion order.

Question 7hardmulti select

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?

Trap 1: { last_name: 1, first_name: 1 }

This sort pattern does not match the index direction or its inverse. Because 'first_name' is stored in descending order for every 'last_name' in the index, a request for 'first_name' in ascending order would require the database to reverse the order of entries in memory, which is inefficient for large result sets.

Trap 2: { first_name: -1, last_name: 1 }

This sort pattern changes the order of the fields compared to the index. In a compound index, the order of fields is fixed. You cannot sort by the second field in the index before the first field unless the first field is provided as an equality match in the query's filter criteria.

Trap 3: { last_name: -1, first_name: -1 }

This pattern does not match the index or its inverse. Similar to the { 1, 1 } case, the mismatch in direction for the second field ('first_name') means the index cannot be used to provide the requested order. The database would need to perform a blocking sort operation to arrange the data correctly.

A
{ last_name: 1, first_name: 1 }
Why it fails: This sort pattern does not match the index direction or its inverse. Because 'first_name' is stored in descending order for every 'last_name' in the index, a request for 'first_name' in ascending order would require the database to reverse the order of entries in memory, which is inefficient for large result sets.
B
{ last_name: 1, first_name: -1 }
This pattern is an exact match for the index definition. Since the index stores documents sorted by 'last_name' ascending and then 'first_name' descending, the database can simply traverse the index entries in their stored order to return the documents, avoiding any expensive in-memory sorting operations during query execution.
C
{ last_name: -1, first_name: 1 }
This pattern is the exact inverse of the index definition. MongoDB can traverse any index in reverse order as efficiently as forward order. By reading the { last_name: 1, first_name: -1 } index backwards, it naturally produces a result set ordered by last_name descending and first_name ascending.
D
{ first_name: -1, last_name: 1 }
Why it fails: This sort pattern changes the order of the fields compared to the index. In a compound index, the order of fields is fixed. You cannot sort by the second field in the index before the first field unless the first field is provided as an equality match in the query's filter criteria.
E
{ last_name: -1, first_name: -1 }
Why it fails: This pattern does not match the index or its inverse. Similar to the { 1, 1 } case, the mismatch in direction for the second field ('first_name') means the index cannot be used to provide the requested order. The database would need to perform a blocking sort operation to arrange the data correctly.

Question 8hardmulti select

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?

Trap 1: Wildcard indexes support '2dsphere' and 'text' search capabilities…

Wildcard indexes are standard B-tree indexes and do not incorporate specialized logic for geospatial or full-text search. If you need those capabilities, you must create dedicated '2dsphere' or 'text' indexes. Wildcard indexes are strictly for equality and range matches on dynamic field paths.

Trap 2: Wildcard indexes are more performant than compound indexes for…

Standard compound indexes are generally more performant than wildcard indexes because they are optimized for specific, known query paths. Wildcard indexes carry more metadata and are designed for flexibility, not for outperforming a precisely tuned compound index that matches an application's primary query patterns.

A
Wildcard indexes cannot be used to enforce a unique constraint.
Because a wildcard index covers multiple fields with potentially different names and values, it cannot be used to enforce uniqueness across the collection. Uniqueness requires a standard index on a specific, named field to ensure that the database can consistently check for duplicate entries during write operations.
B
They cannot be used as a shard key for a collection.
A shard key must be a standard, non-wildcard index to ensure that the balancer and router can predictably determine the location of documents. Wildcard indexes are too dynamic and unpredictable to serve as the foundation for data distribution across a sharded cluster in MongoDB.
C
Wildcard indexes support '2dsphere' and 'text' search capabilities naturally.
Why it fails: Wildcard indexes are standard B-tree indexes and do not incorporate specialized logic for geospatial or full-text search. If you need those capabilities, you must create dedicated '2dsphere' or 'text' indexes. Wildcard indexes are strictly for equality and range matches on dynamic field paths.
D
A wildcard index can be created on the entire document using '$**'.
Using the '$**' syntax, you can create a wildcard index that covers every field in every document in the collection (except _id). This is useful for collections where almost every field is a potential query filter, though it comes with significant storage and write performance costs.
E
Wildcard indexes are more performant than compound indexes for fixed query patterns.
Why it fails: Standard compound indexes are generally more performant than wildcard indexes because they are optimized for specific, known query paths. Wildcard indexes carry more metadata and are designed for flexibility, not for outperforming a precisely tuned compound index that matches an application's primary query patterns.

Question 9hardmulti select

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

Trap 1: Wildcard indexes can be used to index all fields in a document,…

While wildcard indexes target fields within documents, they have specific behaviors with arrays. They do not automatically index every element of an array if the array is deeply nested or configured differently. Furthermore, they are generally discouraged for workloads where field names are known and fixed in advance.

Trap 2: Wildcard indexes are functionally identical to creating individual…

Wildcard indexes are not the same as manual indexes. They have limitations on query coverage and do not support certain features like unique constraints. Relying on them as a substitute for explicit indexes can lead to sub-optimal execution plans and increased resource utilization during write-heavy operations in production.

Trap 3: Wildcard indexes support unique constraints on the indexed fields.

Unique indexes cannot be wildcard indexes. MongoDB strictly prohibits the use of the unique property when creating a wildcard index because the engine cannot guarantee uniqueness across an unpredictable set of dynamic keys. Attempting to create one with the unique option will result in a command error.

A
Wildcard indexes can be used to index all fields in a document, including those inside nested arrays.
Why it fails: While wildcard indexes target fields within documents, they have specific behaviors with arrays. They do not automatically index every element of an array if the array is deeply nested or configured differently. Furthermore, they are generally discouraged for workloads where field names are known and fixed in advance.
B
Wildcard indexes support compound index definitions where the wildcard is a secondary field.
MongoDB allows compound wildcard indexes where a specific field is defined first, followed by the wildcard pattern. This is highly useful for queries that always filter by a static field like 'tenantId' while allowing dynamic searching across a variable set of attributes stored within the document schema.
C
Wildcard indexes are functionally identical to creating individual indexes on every single field in a document.
Why it fails: Wildcard indexes are not the same as manual indexes. They have limitations on query coverage and do not support certain features like unique constraints. Relying on them as a substitute for explicit indexes can lead to sub-optimal execution plans and increased resource utilization during write-heavy operations in production.
D
Wildcard indexes can be used to improve performance for queries where the field names are unknown.
This is the primary use case for wildcard indexes. When your application schema evolves dynamically or contains unstructured data where you cannot predict the field names, wildcard indexes enable the query engine to find documents efficiently without requiring manual schema updates for every newly added field.
E
Wildcard indexes support unique constraints on the indexed fields.
Why it fails: Unique indexes cannot be wildcard indexes. MongoDB strictly prohibits the use of the unique property when creating a wildcard index because the engine cannot guarantee uniqueness across an unpredictable set of dynamic keys. Attempting to create one with the unique option will result in a command error.

Question 10mediummultiple choice

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

Exhibit

db.users.find({age: {$gt: 25}, status: 'active'}).sort({name: 1})

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

Starting with the range field 'age' is inefficient. It forces the database to scan a wide range of documents based on age before it can even check the status or sort by name, leading to a much higher number of index keys scanned during the query execution process.

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

By placing the range field 'age' before the sort field 'name', this index fails to support the sort operation optimally. The database will likely need to perform a blocking in-memory sort after retrieving the data, which significantly increases memory usage and slows down the overall query response time.

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

Starting the index with the sort field 'name' is inefficient because it ignores the equality filter on 'status'. The engine must scan the index to find names, even if the status is not 'active', resulting in unnecessary scanning and failing to leverage the most selective field in the query.

A
{age: 1, status: 1, name: 1}
Why it fails: Starting with the range field 'age' is inefficient. It forces the database to scan a wide range of documents based on age before it can even check the status or sort by name, leading to a much higher number of index keys scanned during the query execution process.
B
{status: 1, name: 1, age: 1}
This index follows the ESR rule perfectly. 'status' is the equality field, 'name' is the sort field, and 'age' is the range field. This ordering allows the engine to isolate relevant documents immediately, sort them efficiently, and then apply the range filter, providing optimal query performance for the workload.
C
{status: 1, age: 1, name: 1}
Why it fails: By placing the range field 'age' before the sort field 'name', this index fails to support the sort operation optimally. The database will likely need to perform a blocking in-memory sort after retrieving the data, which significantly increases memory usage and slows down the overall query response time.
D
{name: 1, status: 1, age: 1}
Why it fails: Starting the index with the sort field 'name' is inefficient because it ignores the equality filter on 'status'. The engine must scan the index to find names, even if the status is not 'active', resulting in unnecessary scanning and failing to leverage the most selective field in the query.

Question 11mediummulti select

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

Trap 1: They support range queries.

Hashed indexes map values to a hash code, which essentially randomizes the storage order relative to the original values. Because the logical order is lost, range queries like greater-than or less-than are impossible to execute, requiring a full collection scan if attempted on a field with a hashed index.

Trap 2: They can be used as a compound index.

Hashed indexes cannot be part of a compound index. You can only create a hashed index on a single field. If you need to index multiple fields together, you must use a standard B-tree index, as the hashing algorithm does not support multi-field key combinations in a single index structure.

A
They support range queries.
Why it fails: Hashed indexes map values to a hash code, which essentially randomizes the storage order relative to the original values. Because the logical order is lost, range queries like greater-than or less-than are impossible to execute, requiring a full collection scan if attempted on a field with a hashed index.
B
They are ideal for shard keys to ensure uniform distribution.
Using a hashed index for a shard key ensures that documents are distributed evenly across the shards. By hashing the shard key value, MongoDB prevents hotspots that occur with monotonically increasing values, which would otherwise lead to data skew and uneven performance distribution across the database cluster's nodes.
C
They support equality matches.
Hashed indexes are highly optimized for exact-match queries. When searching for a specific value, the database hashes the query value and performs a direct lookup in the index, which is extremely fast and efficient, making them an excellent choice for fields frequently queried using exact equality operators.
D
They can be used as a compound index.
Why it fails: Hashed indexes cannot be part of a compound index. You can only create a hashed index on a single field. If you need to index multiple fields together, you must use a standard B-tree index, as the hashing algorithm does not support multi-field key combinations in a single index structure.
E
They truncate floating point numbers to integers.
When hashing floating-point values, MongoDB converts them to a common format (truncating to integers) before hashing to ensure that different representations of the same numeric value result in the same hash, maintaining consistency across the index and preventing unexpected behavior during exact-match queries for numeric data types.

Question 12mediummultiple choice

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

Exhibit

db.products.createIndex({category: 1, price: -1})

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

This query does not include the prefix field 'category'. An index can only be used if the query starts with the index prefix. Without filtering by category, the database must perform a full collection scan because the index cannot be utilized effectively for a sort on price alone.

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

The query filters by 'price' first, which is the second field in the index. The database cannot use this index because it doesn't start with the 'category' field. An index must always be used starting from the leftmost field to be effective, rendering this specific index useless for this filter.

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

The query filters by 'tags', a field that is not part of this index at all. Therefore, the index provides no assistance for this query. The query engine will be forced to perform a full collection scan to find documents matching the 'tags' criteria, leading to poor query performance.

A
db.products.find({}).sort({price: 1})
Why it fails: This query does not include the prefix field 'category'. An index can only be used if the query starts with the index prefix. Without filtering by category, the database must perform a full collection scan because the index cannot be utilized effectively for a sort on price alone.
B
db.products.find({category: 'electronics'}).sort({price: 1})
This query matches the index prefix 'category' and performs a sort on 'price'. Since the index supports both ascending and descending sorts, it can efficiently serve this request by traversing the pre-sorted index structure, avoiding a blocking sort operation and significantly reducing the time required to return the results.
C
db.products.find({price: 100}).sort({category: 1})
Why it fails: The query filters by 'price' first, which is the second field in the index. The database cannot use this index because it doesn't start with the 'category' field. An index must always be used starting from the leftmost field to be effective, rendering this specific index useless for this filter.
D
db.products.find({tags: 'sale'}).sort({category: 1})
Why it fails: The query filters by 'tags', a field that is not part of this index at all. Therefore, the index provides no assistance for this query. The query engine will be forced to perform a full collection scan to find documents matching the 'tags' criteria, leading to poor query performance.

Question 13mediummultiple choice

An application frequently queries a collection of user profiles using an equality condition on the status field followed by a range condition on the created_at timestamp field. Which compound index structure provides optimal ESR (Equality, Sort, Range) rule compliance?

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

Putting the range field created_at first forces MongoDB to scan a much wider index range before applying the equality filter on status, violating the ESR rule and severely degrading query performance on large datasets.

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

While it respects the ESR order, descending sort directions are unnecessary unless the query specifically requires a descending sort or matching descending range, otherwise it needlessly complicates the index definition for simple equality filters.

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

This reverses the required ESR order by putting the range field first, which prevents MongoDB from leveraging the equality field effectively to prune the initial key search space during query execution.

A
db.users.createIndex({ created_at: 1, status: 1 })
Why it fails: Putting the range field created_at first forces MongoDB to scan a much wider index range before applying the equality filter on status, violating the ESR rule and severely degrading query performance on large datasets.
B
db.users.createIndex({ status: 1, created_at: 1 })
This follows the ESR rule perfectly by placing the equality field status first to narrow the document set, followed by the range field created_at, allowing the storage engine to efficiently locate and scan matching documents.
C
db.users.createIndex({ status: -1, created_at: -1 })
Why it fails: While it respects the ESR order, descending sort directions are unnecessary unless the query specifically requires a descending sort or matching descending range, otherwise it needlessly complicates the index definition for simple equality filters.
D
db.users.createIndex({ created_at: -1, status: 1 })
Why it fails: This reverses the required ESR order by putting the range field first, which prevents MongoDB from leveraging the equality field effectively to prune the initial key search space during query execution.

Question 14mediummultiple choice

An application frequently queries a collection of user profiles using an equality condition on the 'status' field, followed by a range condition on the 'age' field, and then sorts the resulting documents by 'joinDate' in descending order. Which single index structure follows the ESR rule most effectively?

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

Placing the range field 'age' before the sort field 'joinDate' violates the ESR rule, forcing MongoDB to perform an in-memory sort phase for the query results since index order does not match the requested sort order.

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

Putting the sort field 'joinDate' first means the initial query filter on 'status' cannot use the prefix of the index effectively if range scans intervene, significantly reducing index selectivity and traversal performance.

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

Starting with the range field 'age' prevents subsequent fields from serving as an efficient index prefix for equality lookups, degrading query performance down to a nearly full collection scan in worst cases.

A
{ status: 1, age: 1, joinDate: -1 }
Why it fails: Placing the range field 'age' before the sort field 'joinDate' violates the ESR rule, forcing MongoDB to perform an in-memory sort phase for the query results since index order does not match the requested sort order.
B
{ joinDate: -1, status: 1, age: 1 }
Why it fails: Putting the sort field 'joinDate' first means the initial query filter on 'status' cannot use the prefix of the index effectively if range scans intervene, significantly reducing index selectivity and traversal performance.
C
{ status: 1, joinDate: -1, age: 1 }
This index strictly follows the Equality, Sort, Range rule. 'status' handles the exact match equality predicate, 'joinDate' matches the requested sort order directly, and 'age' handles the final range scan.
D
{ age: 1, status: 1, joinDate: -1 }
Why it fails: Starting with the range field 'age' prevents subsequent fields from serving as an efficient index prefix for equality lookups, degrading query performance down to a nearly full collection scan in worst cases.

Question 15mediummultiple choice

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?

Trap 1: { price: -1, category: 1 }

Placing the sort field first violates the essential Equality, Sort, Range design principle, forcing MongoDB to evaluate category as a range or secondary predicate after evaluating the price sort, which heavily degrades performance and prevents optimal index utilization for equality matching.

Trap 2: { category: -1, price: -1 }

Setting the category field to descending order does not match the equality predicate requirements. While equality matches function independently of direction, mismatched directional definitions on subsequent keys can prevent optimal index traversal efficiency and complicate query planning.

Trap 3: { price: 1, category: -1 }

Reversing both the field order and the sort direction completely breaks the query optimization pattern, causing the query planner to reject the index for index-based sorting and requiring an inefficient blocking sort execution stage in memory.

A
{ price: -1, category: 1 }
Why it fails: Placing the sort field first violates the essential Equality, Sort, Range design principle, forcing MongoDB to evaluate category as a range or secondary predicate after evaluating the price sort, which heavily degrades performance and prevents optimal index utilization for equality matching.
B
{ category: 1, price: 1 }
Following the Equality, Sort, Range rule places the equality field category first with ascending order, followed by the price field with descending order, perfectly matching the query pattern and eliminating any need for an expensive in-memory sort operation during execution.
C
{ category: -1, price: -1 }
Why it fails: Setting the category field to descending order does not match the equality predicate requirements. While equality matches function independently of direction, mismatched directional definitions on subsequent keys can prevent optimal index traversal efficiency and complicate query planning.
D
{ price: 1, category: -1 }
Why it fails: Reversing both the field order and the sort direction completely breaks the query optimization pattern, causing the query planner to reject the index for index-based sorting and requiring an inefficient blocking sort execution stage in memory.

Question 16hardmultiple choice

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?

Trap 1: Text index

Text indexes are specifically engineered for parsing and searching string content using linguistic rules and stemmers. They do not support efficient multi-element array matching queries and cannot index arbitrary nested numeric or boolean array elements reliably.

Trap 2: Wildcard index

Wildcard indexes automatically index all fields in documents or specific subpaths, but they create extensive index overhead and do not solve the fundamental Cartesian explosion problem inherent in indexing multiple unrestricted arrays simultaneously.

Trap 3: Hashed index

Hashed indexes compute a hash of the field value to support efficient sharding and exact-match queries. They do not support range queries, sorting operations, or multi-element array matching because arrays cannot be hashed directly for multikey traversal.

A
Text index
Why it fails: Text indexes are specifically engineered for parsing and searching string content using linguistic rules and stemmers. They do not support efficient multi-element array matching queries and cannot index arbitrary nested numeric or boolean array elements reliably.
B
Wildcard index
Why it fails: Wildcard indexes automatically index all fields in documents or specific subpaths, but they create extensive index overhead and do not solve the fundamental Cartesian explosion problem inherent in indexing multiple unrestricted arrays simultaneously.
C
Compound multikey index
Compound multikey indexes can index fields within arrays, but MongoDB enforces strict rules restricting documents from having more than one path indexed as an array to prevent uncontrollable Cartesian product expansion of index entries.
D
Hashed index
Why it fails: Hashed indexes compute a hash of the field value to support efficient sharding and exact-match queries. They do not support range queries, sorting operations, or multi-element array matching because arrays cannot be hashed directly for multikey traversal.

Question 17mediummultiple choice

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?

Trap 1: Create a new compound index on `{ price: 1, category: 1 }` to…

A compound index on { price: 1, category: 1 } would first order by price, then category. For a range query on price, the index can efficiently find the matching price range, but because the range is not an equality, the index cannot guarantee that the results are sorted by category within that range. The sort on category would require an in-memory sort, which can be costly. This index would not support the sort as effectively as the existing index, and creating it would add overhead without addressing the sort requirement.

Trap 2: Use a covered query by projecting only the `category` and `price`…

A covered query requires that all fields in the query filter and projection are part of the index, and that no fields need to be fetched from the documents. In this scenario, the query filters on price with a range condition and sorts on category. The index { category: 1, price: 1 } cannot cover the query because the filter on price is not an equality and the index does not include all fields needed. Additionally, the query does not specify a projection, so it returns all fields, making it impossible to be covered.

Trap 3: Create a separate index on `{ price: 1 }` and drop the compound…

Creating a separate index on price alone would allow the query to use that index for the range filter, but it would not support the sort on category. The sort would then require an in-memory sort, which can be inefficient for large result sets. Additionally, dropping the compound index would remove support for queries that filter on category and sort by price, which the compound index currently handles well. This approach does not leverage the existing index and may not reduce examined documents for the given query.

A
Add a hint to force the query to use the existing index and rely on the index bounds to limit examined documents.
The query filters on price with a range condition and sorts on category. The index { category: 1, price: 1 } can support the sort on category and then apply the range filter on price within each category, because the index is sorted first by category and then by price. By hinting the index, MongoDB can use it to perform an index scan that respects the sort order and applies the price range efficiently, reducing examined documents compared to a collection scan. This leverages the existing index's structure to handle both the sort and the range filter.
B
Create a new compound index on `{ price: 1, category: 1 }` to better support the range filter and sort.
Why it fails: A compound index on { price: 1, category: 1 } would first order by price, then category. For a range query on price, the index can efficiently find the matching price range, but because the range is not an equality, the index cannot guarantee that the results are sorted by category within that range. The sort on category would require an in-memory sort, which can be costly. This index would not support the sort as effectively as the existing index, and creating it would add overhead without addressing the sort requirement.
C
Use a covered query by projecting only the `category` and `price` fields and ensuring the index covers the query.
Why it fails: A covered query requires that all fields in the query filter and projection are part of the index, and that no fields need to be fetched from the documents. In this scenario, the query filters on price with a range condition and sorts on category. The index { category: 1, price: 1 } cannot cover the query because the filter on price is not an equality and the index does not include all fields needed. Additionally, the query does not specify a projection, so it returns all fields, making it impossible to be covered.
D
Create a separate index on `{ price: 1 }` and drop the compound index.
Why it fails: Creating a separate index on price alone would allow the query to use that index for the range filter, but it would not support the sort on category. The sort would then require an in-memory sort, which can be inefficient for large result sets. Additionally, dropping the compound index would remove support for queries that filter on category and sort by price, which the compound index currently handles well. This approach does not leverage the existing index and may not reduce examined documents for the given query.

Question 18mediummultiple choice

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?

Trap 1: The index stores a single entry for the entire array, so it cannot…

A multikey index stores separate index entries for each element of an array. This allows efficient lookups for individual array elements. The statement that it stores a single entry for the entire array is incorrect; that would be the case for a non-multikey index on an array, which MongoDB does not create by default—it automatically creates a multikey index when indexing an array field.

Trap 2: The index cannot be used because it is multikey and queries with…

MongoDB supports multikey indexes and can use them for queries that match multiple array elements. While there are limitations (e.g., a compound index can have at most one array field), a single-field multikey index can be used for queries with multiple conditions on the same array. The statement that such queries are unsupported is incorrect.

Trap 3: The index will only be used if the query uses the $all operator;…

MongoDB can use a multikey index for queries that match array elements using various operators, including implicit equality on multiple elements (e.g., { tags: "mongodb", tags: "database" } is not valid syntax; typically $all or $and is used). However, the index is not limited to $all; it can be used with $and conditions or other operators. The claim that only $all triggers index use is false.

A
The index stores a single entry for the entire array, so it cannot help with queries that match individual elements.
Why it fails: A multikey index stores separate index entries for each element of an array. This allows efficient lookups for individual array elements. The statement that it stores a single entry for the entire array is incorrect; that would be the case for a non-multikey index on an array, which MongoDB does not create by default—it automatically creates a multikey index when indexing an array field.
B
The index cannot be used because it is multikey and queries with multiple array element conditions are unsupported.
Why it fails: MongoDB supports multikey indexes and can use them for queries that match multiple array elements. While there are limitations (e.g., a compound index can have at most one array field), a single-field multikey index can be used for queries with multiple conditions on the same array. The statement that such queries are unsupported is incorrect.
C
The index will only be used if the query uses the $all operator; otherwise, it performs a collection scan.
Why it fails: MongoDB can use a multikey index for queries that match array elements using various operators, including implicit equality on multiple elements (e.g., { tags: "mongodb", tags: "database" } is not valid syntax; typically $all or $and is used). However, the index is not limited to $all; it can be used with $and conditions or other operators. The claim that only $all triggers index use is false.
D
The index can be used to satisfy the query, and MongoDB will automatically intersect the index entries for the two tag values.
For a multikey index on an array field, MongoDB creates separate index entries for each array element. When a query matches multiple elements (e.g., tags containing both "mongodb" and "database"), the query planner can use index intersection or a compound multikey index if available. For a single-field multikey index, MongoDB can still use the index to find documents matching each tag and then intersect the results, though performance may vary.

Question 19hardmulti select

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.)

Trap 1: A compound index on { timestamp: 1, sensor_id: 1 } is more…

For queries that filter on sensor_id and range on timestamp, the index { sensor_id: 1, timestamp: 1 } is more efficient because it allows an equality match on sensor_id first, then a range scan on timestamp. The reverse order would require scanning all timestamps for each sensor_id, which is less efficient. So this statement is false.

Trap 2: To cover a query that filters on sensor_id and projects sensor_id…

A covered query requires that all fields in the query filter and projection are included in the index. The projection includes sensor_id and timestamp, and the filter is on sensor_id. The value field is not needed for this query, so including it would unnecessarily increase index size and write overhead. Thus, this statement is false.

Trap 3: To support a query that filters on a range of timestamp and sorts…

For a query that range-filters on timestamp and sorts by sensor_id, the optimal index would have timestamp as the leading field for the range, but sorting on sensor_id would not be efficient because the index order is sensor_id then timestamp. The index { sensor_id: 1, timestamp: 1 } would not support sorting by sensor_id when filtering on a timestamp range. This statement is false.

A
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 }.
Why it fails: For queries that filter on sensor_id and range on timestamp, the index { sensor_id: 1, timestamp: 1 } is more efficient because it allows an equality match on sensor_id first, then a range scan on timestamp. The reverse order would require scanning all timestamps for each sensor_id, which is less efficient. So this statement is false.
B
To cover a query that filters on sensor_id and projects sensor_id and timestamp, the index must include the value field.
Why it fails: A covered query requires that all fields in the query filter and projection are included in the index. The projection includes sensor_id and timestamp, and the filter is on sensor_id. The value field is not needed for this query, so including it would unnecessarily increase index size and write overhead. Thus, this statement is false.
C
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.
Why it fails: For a query that range-filters on timestamp and sorts by sensor_id, the optimal index would have timestamp as the leading field for the range, but sorting on sensor_id would not be efficient because the index order is sensor_id then timestamp. The index { sensor_id: 1, timestamp: 1 } would not support sorting by sensor_id when filtering on a timestamp range. This statement is false.
D
A compound index on { sensor_id: 1, timestamp: 1 } can support queries that filter on sensor_id and sort by timestamp.
This compound index places the equality field (sensor_id) first and the sort/range field (timestamp) second. It can efficiently handle queries that filter on sensor_id and sort or range-filter on timestamp. This follows the ESR rule and is a valid statement for supporting such queries while minimizing index count.
E
A compound index on { sensor_id: 1, timestamp: 1 } can be used to cover a query that filters on sensor_id and projects sensor_id and timestamp, provided the query does not include other fields.
If the query only filters on sensor_id and returns only sensor_id and timestamp, then the index { sensor_id: 1, timestamp: 1 } contains all the required fields. MongoDB can satisfy the query entirely from the index without fetching documents, making it a covered query. This is true and helps minimize index count.

Question 20hardmultiple choice

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?

Trap 1: The index cannot support the sort because the sort field order_date…

In a compound index, the sort can be supported if the query includes equality conditions on all fields that precede the sort field in the index. Here, customer_id and status are equality conditions, but status comes after order_date in the index. However, the index order is customer_id, order_date, status. For the sort on order_date to be efficient, the query must have equality on customer_id. The status equality is after order_date, so it does not need to be before the sort field. Actually, the ESR rule: equality on customer_id, sort on order_date, range on status? But status is equality, so it can be placed after the sort field. The index can still support the sort because the prefix customer_id is equality, and order_date is next. The status equality is applied as a filter after the index scan. So the sort is supported.

Trap 2: The index will be used only if the query includes an equality…

The index can be used for queries that filter on customer_id and status and sort on order_date. An equality condition on order_date is not required; in fact, the query sorts on order_date, so it is not an equality. The index is designed to support such patterns. The claim that it requires equality on order_date is false and would defeat the purpose of the index for sorting.

Trap 3: The index can support the filter but will require an in-memory sort…

The index has order_date descending (-1). The query sorts by order_date descending. The sort direction matches the index direction, so no in-memory sort is needed. If the query sorted ascending, a blocking sort would be required unless the index could be traversed in reverse. MongoDB can traverse an index in reverse order, so even an ascending sort could use a descending index by scanning backwards. Thus, the statement about direction mismatch causing an in-memory sort is incorrect.

A
The index cannot support the sort because the sort field order_date is not the first field in the index.
Why it fails: In a compound index, the sort can be supported if the query includes equality conditions on all fields that precede the sort field in the index. Here, customer_id and status are equality conditions, but status comes after order_date in the index. However, the index order is customer_id, order_date, status. For the sort on order_date to be efficient, the query must have equality on customer_id. The status equality is after order_date, so it does not need to be before the sort field. Actually, the ESR rule: equality on customer_id, sort on order_date, range on status? But status is equality, so it can be placed after the sort field. The index can still support the sort because the prefix customer_id is equality, and order_date is next. The status equality is applied as a filter after the index scan. So the sort is supported.
B
The index will be used only if the query includes an equality condition on order_date; otherwise, it performs a collection scan.
Why it fails: The index can be used for queries that filter on customer_id and status and sort on order_date. An equality condition on order_date is not required; in fact, the query sorts on order_date, so it is not an equality. The index is designed to support such patterns. The claim that it requires equality on order_date is false and would defeat the purpose of the index for sorting.
C
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.
Why it fails: The index has order_date descending (-1). The query sorts by order_date descending. The sort direction matches the index direction, so no in-memory sort is needed. If the query sorted ascending, a blocking sort would be required unless the index could be traversed in reverse. MongoDB can traverse an index in reverse order, so even an ascending sort could use a descending index by scanning backwards. Thus, the statement about direction mismatch causing an in-memory sort is incorrect.
D
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 ascending, then order_date descending, then status ascending. For a query with equality on customer_id and status, and sort on order_date descending, the index can be used. The equality on customer_id and status allows the index to be scanned in the order of order_date descending for that customer_id, because within each customer_id, the index is sorted by order_date descending. The status equality can be applied as a filter, but it does not disrupt the sort order. Thus, no blocking sort is needed.