CCNA Indexing and Performance Questions

A covered query is one where all the fields in the query projection exist within the index itself. Because the database can retrieve all necessary data directly from the index tree without needing to fetch the actual document from disk, it drastically reduces I/O. This is one of the most effective ways to optimize read performance for high-frequency queries in large collections.

A 'COLLSCAN' indicates that the database is performing a full collection scan, reading every document to satisfy the query. The high number of docs examined relative to the returned documents suggests that the query is inefficient. In a production environment, this is a major performance bottleneck, as the database engine must load entire documents into memory to evaluate the filter, leading to high I/O and latency.

The index { category: 1, price: 1 } supports efficient filtering when the query has an equality predicate on category and a range predicate on price. MongoDB can use index bounds to seek to the correct category and scan only the relevant price range, minimizing the number of documents fetched. This makes the index scan efficient despite examining many index entries.

The $all operator is used to find documents where the field contains all the specified elements. When indexing an array field, MongoDB creates a multikey index. A simple index on 'tags' is sufficient here because the multikey index structure inherently supports finding documents that contain specific array elements, enabling the query engine to efficiently narrow down the result set without scanning all documents in the collection.

Multikey indexes are used when you need to index a field that contains an array. MongoDB creates an index entry for each element in the array, allowing you to efficiently query for documents containing specific array values. This is essential for applications managing tags, inventory lists, or user attributes stored as arrays, ensuring that queries filtering by these elements perform with high efficiency rather than scanning the entire collection.

A large discrepancy between 'keysExamined' and 'nReturned' suggests that the index is not very selective or is being used inefficiently. The engine is scanning many index entries that do not match the query criteria, which is a major performance drain. Investigating the selectivity of the index and potentially adding more fields to the compound index can help narrow down the search and improve efficiency.

Partial indexes allow you to index only a subset of documents that meet a specific filter condition. This reduces index size and the overhead of maintaining the index during writes, as only updates to documents matching the filter trigger an index update. This is highly effective for reducing memory usage when you only need to index documents with specific statuses, such as 'active' or 'pending', rather than the entire collection.

A compound index can provide both the equality match and the sort when the equality fields form the prefix and the sort field follows. With { tenantId: 1, createdAt: -1 }, the query's equality on tenantId and sort on createdAt should be served without an in-memory sort. A blocking SORT in explain therefore points to a mismatch such as collation, type, or query shape rather than a missing index.

To optimize queries with equality filters and descending sorts, the index must match the equality field first, followed by the sort field with the correct direction. Placing category first satisfies equality lookup, while setting price to negative one avoids in-memory sorting stages during execution.

A COLLSCAN stage indicates that the query engine scanned every document in the collection because no suitable index was found. Creating an index on the filtered field enables the query planner to select an IXSCAN stage, drastically reducing disk reads and improving throughput.

The index { a: 1, b: 1, c: 1 } cannot support the sort on c because b is a range predicate, which means the index order after the equality on a is determined by b, not c. To support both the filter and the sort, the index should be reordered so that the sort field c follows the equality field a, and the range field b comes last. The index { a: 1, c: 1, b: 1 } allows the equality on a to bound the prefix, then the sort on c to be satisfied by the index order, and finally the range on b to be applied as an index filter.

Partial indexes are designed to index only a subset of documents that meet a specific filter expression. They are highly efficient when queries only target a small, specific portion of a large collection. By reducing the size of the index in memory and on disk, partial indexes lower storage costs and decrease the impact on write operations while maintaining query performance for the intended subset of data.

Hashed indexes map the hash of the field value to the document, which is excellent for distributing data evenly across shards. They are ideal for fields with high cardinality that are primarily used for equality lookups. They do not support range-based queries, so they should not be used for fields where inequality filtering (like greater than or less than) is the primary query pattern.

A covered query is one whose filter and projection are both satisfied by the index, so no document fetch is needed. Including every projected field in the index, as with { email: 1, name: 1 }, lets the engine return results straight from index keys. Single-field, hashed, and text indexes cannot supply the projected name field, so they cannot cover this query.

MongoDB restricts in-memory operations like sorting to a fixed buffer size. When a query cannot satisfy its sort requirements from an index, it loads documents into memory. Providing a compound index containing the query's equality, range, and sort fields allows the execution engine to stream sorted results directly.

The two indexes that best support the queries are { user_id: 1, timestamp: -1 } for query (1) and { action: 1, timestamp: 1 } for query (2). The first provides an equality match on user_id and a sort on timestamp, while the second provides an equality match on action and a range on timestamp. These indexes align with the equality-sort and equality-range patterns, ensuring efficient index usage.

Using a hint forces the optimizer to use a specific index, ignoring its own cost-based selection. While this can sometimes be useful for troubleshooting or very specific cases where the optimizer makes a poor choice, it is generally discouraged because it prevents the database from adapting to data changes. As the data distribution evolves, a hinted index might become suboptimal, leading to degraded performance compared to a dynamic plan.

Every index adds maintenance work to each insert, update, and delete, so a collection with many overlapping indexes suffers write amplification and cache pressure. Auditing index usage, dropping unused or redundant indexes, and consolidating overlapping prefixes into compound indexes reduces per-write maintenance while keeping the timestamp range and severity access paths covered. Write concern and cache tuning do not remove index maintenance cost.

Hashed indexes are optimized for even distribution of values, which is critical for sharding but not for improving equality query performance on a single replica set. A standard B-tree index on a high-cardinality field like status already provides efficient equality lookups and supports range queries. Therefore, a hashed index would not be the right choice to speed up these queries; it is better suited for shard keys that need uniform distribution.

When a SORT stage wraps an IXSCAN stage, it means the index used for filtering did not satisfy the sort requirement. The query engine had to load the matching documents into memory to sort them, risking memory limit exceptions on large result sets.

To target a specific shard directly, a query must include the exact shard key or a valid prefix of the shard key in its filter predicate. Omitting the shard key forces the mongos router to query every shard in the cluster to assemble the final result set.

Every index adds overhead to every write operation (insert, update, delete) because the database must maintain the index structure alongside the collection. Furthermore, indexes consume significant memory, and if the working set (the data and indexes most frequently accessed) exceeds available RAM, the database will experience frequent page faults from disk, leading to severe performance degradation across all types of operations.

The query filters on active with equality and sorts by score descending. The optimal index places the equality field first and the sort field second in the matching direction: { active: 1, score: -1 }. This allows MongoDB to use the index for the filter and to provide the descending sort order directly, avoiding an in-memory sort and improving performance.

Low cardinality fields, such as those with only two or three unique values, provide little benefit when indexed. MongoDB's query optimizer often skips these indexes because a collection scan is usually faster than traversing the B-tree for a large percentage of the dataset. Identifying high-cardinality fields is essential for building effective indexes that genuinely speed up data retrieval rather than just adding overhead to write operations.

MongoDB can utilize multiple indexes for a single query through an 'index intersection' execution plan. However, the query optimizer decides whether to perform an intersection or use just one index based on cost. For complex queries involving geo-spatial data and arrays, intersection might be less efficient than a single compound index, and the optimizer may choose a collection scan if intersection costs are deemed too high.

The compound index { email: 1, country: 1 } is optimal because it allows an equality match on email, which is highly selective, and then provides sorted results by country. The index order matches the query pattern: equality first, then sort. This avoids an in-memory sort and minimizes the number of documents examined.

To optimize this query, the ESR (Equality, Sort, Range) rule must be applied. The 'user_id' and 'status' fields are equality filters, while 'timestamp' is used for sorting. An index of {user_id: 1, status: 1, timestamp: 1} allows MongoDB to satisfy the query filters and provide the sort order directly from the index, avoiding a blocking sort operation in memory which is critical for performance on large collections.

The compound index { status: 1, created_at: -1 } is designed to support queries that filter on status with equality and sort on created_at. The index prefix (status) is an equality match, allowing the index to be used for both filtering and sorting. A collection scan indicates that the index might not exist, be invalid, or the query planner chose a different plan due to factors like low selectivity or stale statistics.