Indexing and Performance practice questions

The index is missing the 'status' field, causing a scan.

Why it fails: The execution plan shows that the index {status: 1, type: 1} is being successfully used in an IXSCAN stage. The problem is not the absence of the equality fields, but the lack of the sorting field within the index structure to avoid memory-intensive operations.

The query is not using an index at all.

Why it fails: The execution plan clearly displays an IXSCAN stage, which confirms that an index is currently being utilized. The bottleneck identified is the combination of the index and the sort operation, not a lack of an index. Therefore, index optimization is the correct path forward.

Remove the index to improve write throughput.

Why it fails: Removing an index will force the database to perform a collection scan, which is the most expensive operation in MongoDB. This would drastically degrade read performance. The goal is to optimize the read path while balancing the write cost, not to eliminate indexes entirely.

They can be used on capped collections.

Why it fails: TTL indexes are incompatible with capped collections because capped collections have strict fixed-size limitations and internal document lifecycle management that conflicts with the background deletion mechanism of the TTL index. Attempting to create a TTL index on a capped collection will result in an error during execution.

They can be created on any field type.

Why it fails: TTL indexes must be created on a field that contains BSON date values or an array of BSON date values. If the field is not a date type or does not contain dates, the TTL index will not function as expected, as it cannot calculate the expiration time.

They are only available in the Community Edition.

Why it fails: TTL indexes are a core feature of MongoDB and are available in all editions, including both the Community and Enterprise versions. They are a fundamental part of the database's document management capabilities and are not restricted to any specific licensing tier or deployment model.

The amount of available RAM on the server.

Why it fails: While RAM is crucial for read performance through the WiredTiger cache, its impact on write performance is indirect. Writes are primarily constrained by disk I/O and index overhead. While sufficient RAM helps with caching, it does not directly speed up the persistence process of a single write operation.

The number of active read-only users.

Why it fails: Read-only users do not directly compete with write operations for resources in a way that significantly impacts performance, provided there is enough CPU and memory. MongoDB's concurrency control allows for multiple readers, and write operations are prioritized at the document level, making read volume less impactful.

It will always use {a: 1, b: 1}.

Why it fails: The optimizer does not blindly pick the compound index. It evaluates the performance cost of both. If both indexes provide an equally efficient way to filter on 'a', the optimizer will generally prefer the smaller index to save memory and I/O resources during the traversal.

It will always use {a: 1}.

Why it fails: While likely, the optimizer's choice is not guaranteed to be 'always' the single-field index. Internal cost-based estimation (involving cache state and index size) dictates the choice. Under specific circumstances, it might select the compound index, especially if other query components favor it.

It will perform a collection scan.

Why it fails: A collection scan is the most expensive operation and is only chosen if no suitable indexes exist. Since both provided indexes can effectively filter on field 'a', the optimizer will certainly select one of them rather than defaulting to a full collection scan of the entire dataset.

The database automatically creates a temporary index.

Why it fails: MongoDB does not dynamically create indexes to support queries at runtime. Index creation is a deliberate administrative action. Relying on the database to automatically optimize its own index structure during a query would lead to unpredictable performance and resource exhaustion in production environments.

It defaults to a disk-based sort automatically.

Why it fails: MongoDB does not automatically spill to disk for sorting operations. If the memory limit for the sort is exceeded, the operation is simply aborted with an error. Developers must ensure that their queries use indexes to satisfy sort orders or use aggregation pipelines with 'allowDiskUse' if necessary.

The sort is performed by the client driver.

Why it fails: Sorting is a server-side operation performed by the MongoDB engine. The client driver simply receives the final result set after the server has processed the query, including any sorting requirements. The server is responsible for the computational complexity of the sort operation, not the client.

Execute the compact command on every individual collection containing fragmented indexes during peak operational hours.

Why it fails: Running the compact command requires an exclusive database lock and consumes significant CPU and disk input-output resources. Executing this maintenance task during peak hours will block incoming application reads and writes, leading to severe timeouts and application service outages.

Drop the fragmented index and recreate it using the background option in MongoDB versions prior to 4.2.

Why it fails: The background index build option was deprecated in modern server releases and removed entirely in version 4.4. Relying on legacy command arguments on modern database deployments causes syntax errors and fails to execute the required administrative maintenance procedure.

Rebuild indexes using the createIndexes command with unique naming strategies to replace the old structures.

Why it fails: Creating duplicate indexes with alternate names fails to clean up the existing fragmented B-tree structures. This approach merely consumes additional disk space and increases memory pressure on the WiredTiger cache without resolving the underlying performance degradation.

A compound index on {A, B} can be used for a query filtering only on {B}.

Why it fails: The prefix rule dictates that MongoDB can only use an index if the query includes the leftmost fields of that index. Since field B is not the start of the index structure, a filter on B alone will not trigger the use of an index on {A, B}.

Adding more fields to a compound index always improves query performance.

Why it fails: Over-indexing can significantly degrade write performance. Every write operation must update all associated indexes, consuming CPU and I/O. Furthermore, indexes consume RAM; having too many large, rarely used indexes can lead to memory pressure and cache thrashing, which negatively impacts the overall throughput of the database cluster.

Compound indexes are limited to a maximum of 5 fields per index.

Why it fails: MongoDB allows compound indexes to contain up to 32 fields. While it is technically possible to create very wide indexes, it is rarely recommended due to the storage overhead and the performance impact on write operations, as every update must modify every single index field defined in the collection.

It forces the data to remain in the WiredTiger cache.

Why it fails: While covered queries are efficient, they do not explicitly force data into the cache. Instead, they allow the query to avoid loading the document from the cache or disk entirely, because the index provides all the required information needed to satisfy the result set during the query execution phase.

It automatically creates an index on all queried fields.

Why it fails: Covered queries do not automatically create indexes. The developer must manually design an index that includes all fields used in the projection and the filter. If the index does not contain every field requested, the query will not be covered and will require a fetch operation, losing the benefit.

It reduces the amount of disk space consumed by the collection.

Why it fails: Covered queries do not impact the size of the collection on disk. In fact, adding indexes to support covered queries actually increases storage usage because the indexes themselves occupy disk space. The benefit is exclusively related to the speed of query execution by avoiding full document access during reads.

Using a replica set for data redundancy.

Why it fails: Replica sets are a core architectural feature for high availability and do not negatively impact query performance. In fact, replica sets can improve performance by allowing read operations to be offloaded to secondary nodes, which distributes the load away from the primary, effectively scaling read throughput across multiple servers.

The use of the WiredTiger storage engine.

Why it fails: WiredTiger is the default, high-performance storage engine in MongoDB and is highly optimized for concurrency and storage efficiency. It is designed to maximize performance through document-level locking and compression, making it a critical component of the database's performance, not a cause of slow query behavior.

2dsphere indexes do not support $near queries.

Why it fails: 2dsphere indexes are specifically designed to support geospatial queries, including $near and $geoWithin. The problem is not index type incompatibility, but rather the complexity of the query or the underlying data distribution, which can make geospatial search operations computationally expensive even when the correct index is present.

Geospatial indexes require a compound index on all fields.

Why it fails: Geospatial indexes are designed to work on specific coordinate fields and do not require compound indexing with other data fields to function. The performance issue in geospatial queries is almost always related to the radius of the query or the density of the documents within the queried area.

The coordinates are stored in the wrong order (latitude/longitude).

Why it fails: While coordinate order is critical (GeoJSON uses longitude/latitude), an error here would typically result in incorrect results or a query error rather than underperformance. Performance issues in this context are usually tied to the volume of data being scanned to find points within the specified distance threshold.

{ userId: -1, timestamp: 1 }

Why it fails: This index starts with userId, so timestamp is not the primary sort key. For the given query, which filters on timestamp and sorts by timestamp descending, the index cannot provide the required sort order directly. MongoDB would still need to sort the results in memory after using the index for userId filtering, which does not resolve the performance problem described.

{ timestamp: 1, userId: -1 }

Why it fails: The index order on timestamp is ascending, but the query requests a descending sort. MongoDB can traverse an ascending index in reverse to satisfy a descending sort on the leading field, but only if the query does not also require a range filter on that field that prevents the reverse scan from being efficient. More importantly, the secondary field userId is descending, which does not match the equality filter’s lack of order requirement, but the primary issue is that the sort direction on the leading field is opposite to the query’s requested order, potentially forcing an in-memory sort.

{ userId: 1, timestamp: -1 }

Why it fails: This index leads with userId, so it cannot efficiently filter on timestamp range conditions unless userId is an equality predicate. Even if userId is also an equality filter, the sort on timestamp descending is satisfied only if the equality fields precede the sort field, but the query also filters on timestamp. This index does not provide a single contiguous scan for the timestamp range and does not guarantee the sort order for the given query pattern.

The planner always prefers single-field indexes over compound indexes when both are available.

Why it fails: The planner has no inherent preference for single-field indexes. It evaluates candidate plans through trial execution and plan-ranking, and compound indexes frequently win because they can satisfy multiple predicates and sorts. The observed choice reflects selectivity and work examined for this query, not a blanket rule favoring single-field indexes.

The compound index was rejected because it lacks a sort component matching the query.

Why it fails: A compound index does not need to match a sort to be eligible; the query has no sort clause here. The planner still considers the compound index for the equality predicates on status and customerId. Rejection due to missing sort support would only be relevant if a sort existed, which it does not in this find.

The compound index { status: 1, customerId: 1 } cannot be used because status is a string and customerId is a number.

Why it fails: Mixed BSON types across different fields in a compound index are entirely supported. Each field is indexed in its own BSON type order, and the planner can use the compound index with equality on both status and customerId. Type mixing across distinct fields is not a disqualifier, so this does not explain the planner's decision.

Create a new index { orderDate: -1, customerId: 1, status: 1 }.

Why it fails: This index leads with orderDate, the sort field, but the query also has equality filters on customerId and status. Leading with the sort field can be useful if the sort field is also used in a range predicate, but here it is only used for sorting. Because the equality fields come after orderDate, the index cannot efficiently filter on customerId and status before sorting; it would likely scan many orderDate values and then filter, which is less efficient than placing equality fields first.

Create a new index { status: 1, customerId: 1, orderDate: -1 }.

Why it fails: Reordering the fields to put status first does not align with the query’s equality predicates and sort. The query has equality filters on both status and customerId, so either order of these equality fields in the index is acceptable for filtering. However, the sort field orderDate must come after all equality fields to support the sort. This index still has orderDate last, so it would not enable an index-supported sort any better than the existing index, and it may not be chosen due to similar cost.

Add a hint to force the use of the existing index.

Why it fails: Forcing the use of the existing index with a hint may not improve performance if the index cannot efficiently support the query’s sort. The existing index { customerId: 1, status: 1, orderDate: -1 } has status before orderDate, so it cannot provide the required sort order on orderDate alone after filtering on both equality fields. The query planner likely rejected it because it would require an in-memory sort, and hinting it would not eliminate that sort, potentially making performance worse.

The index cannot be used for the sort because total is specified in the filter and appears after orderDate in the index key pattern.

Why it fails: The position of total in the filter does not disqualify the index from satisfying the sort. MongoDB can still scan the index in orderDate order and apply total as a residual index filter. The rule that matters is whether the sort keys form a prefix of the index after accounting for equality predicates; here they do, so the sort is index-supported even though total is also filtered.

The index can be used for the sort but not for the filter, because a non-equality field (total) breaks the index prefix used for filtering.

Why it fails: This mischaracterizes how the optimizer builds bounds. Equality on customerId establishes a tight prefix, and the index can still evaluate total as an index filter on the remaining keys. The filter on total does not need to be the leading field to benefit; the index still restricts documents before fetching them. Claiming the filter is unsupported is incorrect for this key pattern.

The index cannot be used at all because the query does not include orderDate in its filter predicate.

Why it fails: A field does not need to appear in the filter to be useful in an index. orderDate is present in the index key pattern and is used to satisfy the sort direction. MongoDB can exploit index order to return sorted results without a blocking sort when the sort keys align with the index, even if the sort field is not part of the filter. The index remains usable.

The index must be a unique index.

Why it fails: Uniqueness is not required for an index to cover a query. A non-unique index can still provide all the data needed for a covered query as long as it contains all filter and projection fields. The covered query optimization depends on the index containing the necessary fields, not on the index being unique. Requiring uniqueness would unnecessarily restrict index design.

The query must not include any projection that excludes the _id field.

Why it fails: A covered query can exclude _id from the projection. In fact, to achieve a covered query, the projection must explicitly exclude _id, because the index does not store the _id field unless it is part of the index key. Including _id in the projection would require fetching the document. Therefore, the statement that the query must not exclude _id is incorrect; the opposite is required.

The query must use the $natural hint to force index usage.

Why it fails: The $natural hint forces a collection scan in natural order and explicitly prevents index usage. It cannot be used to achieve a covered query; in fact, it would guarantee the query is not covered. Covered queries rely on the query planner selecting an appropriate index, and hints can be used to force a specific index, but $natural is the opposite of what is needed here.

The index cannot support the sort because the filter on c is not an equality predicate and breaks the index prefix.

Why it fails: The filter on c is not specified as an equality predicate in the stem; it says the query filters on a and c. If c is not an equality predicate, it could be a range or other condition. However, the sort is on b and a. The index prefix for sorting is determined by equality predicates on leading fields. The filter on a is equality, so a is fixed. The filter on c, if it is not equality, would be applied after the sort keys in the index, but it does not break the sort support for b. The statement that the filter on c breaks the index prefix is incorrect; c appears after b in the index, so it does not interfere with the sort on b. The real issue is the sort order of b and a.

The index can support the sort because the filter on a provides an equality bound, and the sort on b is the next key in the index.

Why it fails: While the filter on a does provide an equality bound, the sort is on { b: 1, a: 1 }, which includes a after b. Since a is already fixed by the equality predicate, the sort on a is irrelevant, and the effective sort is on b. The index order after a is b, then c, so the sort on b can be supported. However, the presence of c in the index does not prevent the sort on b from being supported. The statement is partially correct but misses that the sort on a is redundant. The index can indeed support the sort on b, but the question asks for the correct explanation, and the index can support the sort because the effective sort keys are a prefix of the index after the equality bound. This option is not entirely wrong, but it does not address the full sort specification. The correct answer is that the index cannot support the sort as specified because the sort keys do not match the index order, but in practice, the sort on a is redundant and the index can still be used for the b sort. The question is tricky because the sort includes a field that is already fixed. The DBA should recognize that the index can support the sort on b, but the sort on a is unnecessary. The correct explanation is that the index can support the sort because the equality on a makes the sort on a redundant, and the sort on b matches the next index key.

The index can support the sort because the sort keys are a subset of the index keys, and MongoDB can reorder the sort keys to match the index.

Why it fails: MongoDB cannot reorder sort keys arbitrarily. The sort specification { b: 1, a: 1 } requires that results be sorted first by b, then by a. The index { a: 1, b: 1, c: 1 } is ordered by a, then b, then c. Even though a is an equality predicate, the index order after a is b, then c. The sort on b can be satisfied, but the sort on a is redundant. MongoDB does not reorder sort keys to match the index; it can only use the index if the sort keys form a prefix of the index keys in the same order. The statement that MongoDB can reorder sort keys is false.

The index can support queries that filter on region and sort by createdAt descending, but only if status is not part of the filter.

Why it fails: If the query filters on region but not status, the index can still be used for the region equality and the sort on createdAt, because status is not constrained. However, the index keys after region are status and createdAt. Without an equality on status, the index cannot directly use createdAt for sorting because status varies. The sort would require a blocking sort unless status is also equality-constrained. So this statement is incorrect.

The index can also support queries that filter only on status and sort by createdAt descending, using the index efficiently.

Why it fails: Because region is the leading field and is not constrained by an equality predicate, the index cannot efficiently locate documents by status alone. MongoDB would have to scan the entire index or perform a collection scan, making the index ineffective for this query pattern. The index is designed for queries that include region as an equality predicate.

The index can support queries that filter on status and sort by region ascending, because status is an equality predicate.

Why it fails: The index is ordered by region first, then status, then createdAt. A query filtering on status alone cannot use the index efficiently because region is not constrained. Additionally, sorting by region ascending after an equality on status would require the index to be ordered by region, which it is, but the filter on status does not provide a tight bound on the leading key region. The index would not be used effectively for this query.

{ timestamp: 1, category: 1 }

Why it fails: Placing the range field before the equality field forces the database to scan a larger portion of the index. This violates the ESR rule, preventing the query optimizer from effectively pruning the search space, which leads to higher disk I/O and increased latency during execution.

{ category: 1 }

Why it fails: An index on only the equality field helps narrow down the category, but the database must still perform a secondary scan on the entire result set to filter by timestamp. This results in an in-memory fetch and filter operation, which is significantly slower than using a compound index.

{ timestamp: 1 }

Why it fails: A single-field index on timestamp does not help with the equality filter on category. The engine would be forced to perform a full collection scan or a large index scan, filtering the documents after retrieval, which is highly inefficient for queries filtering on specific categories.

The field is frequently used in filter criteria.

Why it fails: Fields used in filter criteria are ideal candidates for indexing. By indexing these fields, MongoDB can quickly locate specific documents without scanning the entire collection, which directly improves query performance and reduces the overall load on the database engine during data retrieval tasks.

The field contains unique identifiers like UUIDs.

Why it fails: Fields with unique values are high-cardinality and are perfect candidates for indexing. An index on a unique field allows the database to locate a single document in logarithmic time, which is highly efficient for lookup-heavy workloads and essential for enforcing uniqueness constraints in the schema.

The field is a numeric timestamp.

Why it fails: Numeric timestamps are typically high-cardinality and are excellent candidates for indexing. They are frequently used for time-series range queries. Indexing a timestamp allows for efficient retrieval of recent data and helps optimize operations like sorting by time, which is a common requirement in many applications.

When you need to ensure the index covers all documents.

Why it fails: A standard index is required if the query needs to access all documents in the collection. A partial index specifically ignores documents that do not match the filter criteria, so it would fail to satisfy queries that require a full scan or search across the entire collection.

To improve performance for all possible queries.

Why it fails: Partial indexes are narrow in scope and only optimize queries that include the specific filter expression defined for the index. They do not provide broad performance benefits for every possible query, as they ignore documents not matching the criteria, potentially leading to slow performance for non-targeted queries.

To automatically shard the collection data.

Why it fails: Partial indexes and sharding serve different purposes. Sharding is a horizontal scaling mechanism that partitions data across multiple nodes, whereas a partial index is a local storage optimization technique. Using a partial index does not trigger or manage the sharding process within a MongoDB cluster configuration.