CODEWITHSUNDEEP
databasemongodb
jastor_007
jastor_007

Reputation: 451

Why Multi Query in MongoDB does not return data in sorted order?

It is from MongoDB: The Definitive Guide. At first we index the users collection by {"age": 1, "username": 1}. It is said that, age fields are ordered strictly in ascending and, within each age, username's are also in ascending order.

QUERY 1: db.user.find({"age": 21}).sort({"username": -1})
In query 1, MongoDB starts with the last match for {"age": 21} and traverse the index in reverse order. That way, we get username's in descending order. It means that, MongoDB selects documents linearly

QUERY 2: db.users.find({"age": {"$gte": 21, "$lte": 30}}).sort({"username" : 1})
But now, in query 2, it says that index does not return usernames in sorted order, so MongoDB has to sort the results in memory before returning them. Therefore in order to get usernames in ordered manner, we have to set index in reverse order i.e. {"username": 1, "age": 1}. Now here, if MongoDB selects ages linearly, then usernames must be in ascending order. But that's not the case here, Why is that?.

Upvotes: 1

Views: 210

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521629

I agree with the first part of your question, and the index {"age": 1, "username": 1} should be implemented by a B-tree which first splits on age (ascending), and then splits on username (also ascending).

I agree with what you said on the first query:

db.user.find({"age": 21}).sort({"username": -1})

After restricting to a single age value, beyond that all splits should only be taking place on username, and they should be sorted ascending. This means that Mongo can directly read the index entries in their original order to satisfy the sorting portion of the query pipeline. That being said, I see no reason why Mongo could also not use the index in either direction, either forward or backward, so I would also expect the following query to have the same execution plan:

db.user.find({"age": 21}).sort({"username": 1})

Regarding your second query:

db.users.find({"age": {"$gte": 21, "$lte": 30}}).sort({"username" : 1})

The problem here is that the index can't cover both the filter and sorting portions of the query pipeline. It does cover the filter on age, but it only restricts to a range of age values within the B-tree. Within that age range, the username values cannot be assumed to be sorted. As a result, Mongo will have to do an in-memory sort.

But the critical thing to keep in mind for the second query is that the in-memory sort is not nearly as costly as you might think. Mongo only has to sort the index entries, not the actual BSON documents. Typically, Mongo might implement the sort using a divide and conquer algorithm like merge sort, which runs quickly. Once the index entries have been sorted, a seek would be required to lookup the fields for each index entry in the original user collection.

Upvotes: 2

Related Questions