C100DEV Indexing Practice Test 3 — 15 Questions

Question 1

When creating an index on a large collection in a production environment, what is the primary advantage of using the 'background' option (for versions prior to 4.2) or the standard build process in 4.2+?

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

Accepted Answer

It prevents the index build from blocking read and write operations on the collection.. In modern MongoDB (4.2+), all index builds use an optimized process that only holds an exclusive lock at the beginning and end of the operation, allowing the database to remain available for reads and writes. This prevents the index creation from blocking application traffic, which is essential for maintaining high availability and a good user experience during maintenance.

Answer

The index build completes much faster than a foreground build.

Answer

The index will take up significantly less space on the disk.

Answer

It automatically creates the index on all secondaries in a single transaction.

Question 2

You are optimizing a query: db.orders.find({ status: 'urgent', amount: { $gt: 500 } }).sort({ order_date: -1 }). According to the ESR (Equality, Sort, Range) rule, what is the ideal index for this query?

Accepted Answer

{ status: 1, order_date: -1, amount: 1 }. The ESR rule is a best practice for ordering fields in a compound index to maximize efficiency. Equality matches should come first to narrow the result set, followed by the Sort field to allow the index to provide the requested order, and finally the Range filter. This order minimizes the number of index keys scanned and avoids in-memory sorts.

Answer

{ amount: 1, order_date: -1, status: 1 }

Answer

{ order_date: -1, status: 1, amount: 1 }

Answer

{ status: 1, amount: 1, order_date: -1 }

Question 3

A developer wants to implement full-text search on a collection of articles. They create a text index on the 'content' field. Which of the following is a significant limitation of 'text' indexes in MongoDB?

Accepted Answer

A collection can have at most one 'text' index.. A collection in MongoDB can have at most one text index. However, this single index can cover multiple fields by creating a compound text index. This limitation means developers must carefully plan which fields need to be searchable via text commands and consolidate them into a single index definition for that collection.

Answer

Text indexes do not support case-insensitive searches.

Answer

Text indexes cannot be used in a sharded cluster.

Answer

You cannot use 'text' indexes with the $match operator in aggregation.

Question 4

You are storing GPS coordinates in a 'locations' collection using GeoJSON format. You need to perform queries that find points within a specific polygon. Which index type must be created on the location field?

Accepted Answer

A '2dsphere' index to support GeoJSON and spherical geometry.. For querying data on a sphere (like the Earth), MongoDB provides the '2dsphere' index. This index type supports GeoJSON objects and calculates distances using spherical geometry. It is required for advanced geospatial queries such as $geoWithin or $nearSphere when working with real-world coordinates and complex shapes like polygons.

Answer

A '2d' index, as it is the standard for all geospatial data.

Answer

A standard ascending index, as GeoJSON is just a nested document.

Answer

A 'geo' index, which automatically chooses between 2d and 2dsphere.

Question 5

You are optimizing a collection containing user logs. Queries frequently filter by 'category' and sort by 'timestamp' in descending order. Which index configuration best supports these queries while maintaining efficiency?

Accepted Answer

{ category: 1, timestamp: -1 }. The ESR rule suggests placing Equality fields before Sort fields. By indexing { category: 1, timestamp: -1 }, MongoDB can filter by category and then traverse the index in the order specified for sorting. This eliminates the need for an in-memory sort stage, which is crucial for performance as the result set grows. Efficient index usage directly reduces memory consumption and prevents expensive blocking operations on the primary node.

Answer

{ category: 1 }

Answer

{ timestamp: -1, category: 1 }

Answer

{ timestamp: -1 }

Question 6

You need to ensure that values in the 'email' field are unique across all documents in a collection, but documents where the 'email' field is missing should not violate this constraint. How should you create this index?

Accepted Answer

Create a unique partial index with a filter expression requiring email to exist. Partial indexes allow you to restrict the indexing operation to a subset of documents that meet a specified filter expression. By combining a unique constraint with a partial filter expression requiring the field to exist, you prevent duplicate values for present fields while allowing multiple documents to omit the field entirely.

Answer

Create a standard unique index on the email field

Answer

Create a sparse index on the email field without uniqueness

Answer

Create a wildcard index on the email field

Question 7

Which index type should be used to support efficient queries on a field containing geographical coordinates in MongoDB?

Accepted Answer

2dsphere index. The 2dsphere index is specifically designed to support queries on geospatial data using spherical geometry. This index type is essential for location-based applications that perform proximity searches or geometry intersections. By structuring data this way, MongoDB can optimize calculations for distances between points on a sphere, which is crucial for modern mapping and delivery applications that require high precision and performance over large datasets.

Answer

Text index

Answer

Hashed index

Answer

Compound index

Question 8

Which TWO of the following statements regarding the ESR rule for index creation are true?

Accepted Answer

Equality fields should be placed before sort fields.. The ESR rule stands for Equality, Sort, and Range, providing a standard guideline for ordering fields in a compound index to maximize performance. By placing equality fields first, the query engine narrows down the search space immediately. Sorting fields come next to satisfy the query's order requirements without blocking, and range fields are placed last to allow the index to effectively filter remaining documents using the index prefix.

Answer

Equality fields should be placed after range fields.

Answer

The ESR rule is only applicable to single-field indexes.

Answer

Range fields should always be placed at the beginning of an index.

Question 9

What is the primary consequence of having too many indexes on a collection that experiences a high volume of write operations?

Accepted Answer

Decreased performance of write operations.. Every index on a collection must be updated whenever a document is inserted, updated, or deleted. In write-heavy workloads, this results in significant write amplification. Each write operation triggers multiple index update tasks, consuming CPU and I/O resources. Maintaining excessive indexes leads to performance degradation as the system struggles to keep all indexes synchronized with the base data, directly impacting latency and throughput for application-level database interactions.

Answer

Increased disk space usage for data storage.

Answer

Increased memory usage for query execution.

Answer

Improved speed for data deletion operations.

Question 10

Which property of a Multikey Index is true when indexing an array field?

Accepted Answer

It creates an index entry for each element in the array.. A Multikey Index is automatically created when an index is placed on a field containing an array. The index creates an entry for each element in the array, effectively mapping the index key to the parent document. This allows queries to efficiently find documents that contain specific values within their arrays. However, it also means that a single document can have multiple entries in the index, which is a key technical distinction.

Answer

A document can only have one index entry in a multikey index.

Answer

Multikey indexes cannot index array fields.

Answer

Multikey indexes are created manually by the user.

Question 11

What is the primary function of the explain() method in MongoDB?

Accepted Answer

To provide diagnostic information about query execution.. The explain() method provides detailed information about how a query is executed, including which index was used, the number of documents scanned, and the time taken for the operation. It is the essential diagnostic tool for database administrators to identify performance bottlenecks, verify that queries are using the intended indexes, and optimize slow-running queries by examining the execution plan and resource usage statistics provided by the database engine.

Answer

To automatically optimize the query for faster execution.

Answer

To delete inefficient indexes from the collection.

Answer

To force the query to use a specific index.

Question 12

What is the primary benefit of using a partial index in MongoDB?

Accepted Answer

It reduces the index size and resource usage.. Partial indexes allow you to index only a subset of documents that meet a specific filter expression. This reduces the index size, resulting in lower memory and storage consumption. By ignoring unnecessary documents, the database avoids wasting resources on indexing data that is never or rarely queried. This optimization is particularly beneficial for large collections where only a small percentage of data is active or frequently accessed, leading to significant performance gains.

Answer

It forces all documents to be indexed.

Answer

It prevents duplicates across the entire collection.

Answer

It is required for all compound indexes.

Question 13

When should a TTL (Time-To-Live) index be used in a MongoDB collection?

Accepted Answer

To manage temporary or ephemeral data.. TTL indexes are designed to automatically remove documents from a collection after a certain period of time or at a specific time. This is ideal for ephemeral data like session logs, cached items, or temporary alerts. By relying on the database engine to handle the deletion, developers avoid writing custom cleanup scripts, ensuring that data is purged consistently without impacting application logic or manual overhead on the system.

Answer

To keep data indefinitely.

Answer

To speed up high-frequency write operations.

Answer

To ensure unique values in a field.

Question 14

What is the primary technical limitation when creating a covered query?

Accepted Answer

All projected fields must be contained in the index.. A covered query occurs when all fields requested in the query are present in the index. This allows MongoDB to return the result set directly from the index without having to load the actual documents from disk. The primary limitation is that you must include only the indexed fields in your projection. If even one field is not in the index, the database must perform an expensive 'fetch' operation to retrieve the full document from storage.

Answer

The query must include the _id field.

Answer

The collection must be small.

Answer

Covered queries only work with single-field indexes.

Question 15

A support team frequently runs a query that filters on status and sorts by created_at, both fields in the same collection. An existing index { status: 1 } is in place, but the query still performs an in-memory sort and gets killed once result sets grow past the 100 MB sort limit. Which index should you create to let the server satisfy both the filter and the sort without a blocking sort stage?

Accepted Answer

{ status: 1, created_at: 1 }. An index that lists the equality predicate field first and the sort field second stores entries for each status value in created_at order. The server can then read the matching range sequentially and emit results already sorted, which removes the blocking SORT stage and its 100 MB memory ceiling. Reversing the field order or flipping the sort direction prevents the index from supplying the required order.

Answer

{ created_at: 1, status: 1 }

Answer

{ status: 1, created_at: -1 }

Answer

{ created_at: 1 }