C100DBA practice question 22 of 25

Consider the following example document: { "_id": Objectld("5360c0a0a655a60674680bbe"), "user" "login": "irOn" "description": "Made of steel" "date"…

Full set: 130 questions
Consider the following example document: { "_id": Objectld("5360c0a0a655a60674680bbe"), "user" "login": "irOn" "description": "Made of steel" "date": ISODate("2014-04-30T09:16:45.836Z"), } > and index creation command: db.users.createlndex( { "user.login": 1, "user.date": -1 }, "mylndex" ) When performing the following query: db.users.find( { "user.login": /Air.*/ }, { "user":1, "_id":0 > ).sort( { "user.date":1 > ) which of the following statements correctly describe how MongoDB will handle the query? Check all that apply.
Answer options
A: As an optimized sort query (scanAndOrder = false) using "mylndex" because we are sorting on an indexed field
B: As an indexed query using "mylndex" because field "user.login" is indexed Correct answer
C: MongoDB will need to do a table/collection scan to find matching documents
D: None of the above
E: As a covered query using "mylndex" because we are filtering out "_id" and only returning "user.login"
Correct Answer:
B. As an indexed query using "mylndex" because field "user.login" is indexed
Explanation

The query will use the mylndex because the filter { "user.login": /Air./ } is a prefix-based regular expression on the first field of the compound index. This allows MongoDB to perform an index scan, which is more efficient than a collection scan. However, the index cannot be used to satisfy the sort order { "user.date": 1 } because the query's sort direction (ascending) is the opposite of the index's sort direction (descending) for that field, and the filter on the prefix field is a range scan (regex), not an equality match. Therefore, MongoDB will perform an in-memory sort after retrieving the documents using the index.

Why Incorrect

A. The sort is not optimized (scanAndOrder=true) because the sort order in the query (1) is the reverse of the index order (-1) for the user.date field.
C. A collection scan is avoided because the index on user.login can be used to satisfy the filter condition.
D. This is incorrect because option B is a correct statement describing the query's execution.
E. This is not a covered query because the projection { "user": 1 } returns the entire user subdocument, which includes fields not present in the index (e.g., description).

References

1. MongoDB Manual, "Use Indexes to Sort Query Results". Section: "Sort with a Different Order". It states: "However, if the query specifies a different sort direction for the keys, MongoDB cannot use the index for the sort."

2. MongoDB Manual, "Compound Indexes". Section: "Sort Order". It explains that for range scans on a prefix key, subsequent keys must match the index sort order to be used for sorting.

3. MongoDB Manual, "Covered Query". It specifies that for a query to be covered, all the fields in the query's filter and projection must be part of the index.

Scroll to Top