Chapter 5 Sort Limit Skip in MQL and Aggregation

MongoDB University M001: MongoDB Basics
#1

Hi Team,

Can you please help me to understand the order of execution for the below cursor methods in MQL and aggregation,

Sort and Limit - Mongo DB by default will sort the documents and then limit irrespective of the order mentioned by the user in MQL. Can you please confirm if the same behaviour is being used for mongo aggregate pipeline stages $sort and $limit. There is another thumb rule that aggregation pipeline stages are to specified as an array and execution takes place based on the order mentioned by the user in the array. What happens if user mentions $limit pipeline stage prior to $sort. How does mongo db interpret this between MQL and aggregation pipeline?

Skip and Limit - How does mongo db interpret the order in MQL and aggregation pipeline stages?

#2

Hi @Laks,

I’ll just give you some thoughts. But wait for a better answer.

tl;dr

Aggregation won’t alter sort and limit. But instead follows the order in the pipeline.
I’ve only tried it with one field, both indexed and not indexed.

Also, see the docs.

MQL

First, let’s take sort and limit separately.

sort will either

  • Use an index to sort, if the sort key is indexed
  • Do block-sort if the sort key is not indexed.

The latter normally takes more time, and if you include .sort({key:val}).explain() at the end of your query, you can check whether is doing block sort or indexed sort. Block sort will appear with SORT on the query planner.

limit is simpler, and just restricts the stream coming out of a find query or match query.

But sort + limit is a bit different.

If you run:

db.zips.find().sort({"pop":-1}).limit(2).explain()
//or
db.zips.find().limit(2).sort({"pop":-1}).explain()

You get the same result, with or without indexes. But more important, the sort/limit order is irrelevant. Because we get the extreme values, sort is always done before limit.

Also take this line from the docs:

You can use sort() in conjunction with limit() to return the first (in terms of the sort order) k documents, where k is the specified limit.

The next paragraph in the docs is a bit confusing but it’s really repeating what I’ve quoted. As you said, limit and sort order don’t matter.

Aggregation

We can run the exact same queries on aggregation.

//you can include {$match:{}} if you like. Same output.
db.zips.aggregate([{$sort:{pop:-1}}, {$limit:2}])
// same result than find
db.zips.aggregate([{$limit:2}, {$sort:{pop:-1}}]
// different result

Aggregate is following the order in the pipeline, and not necessarily sorting in advance.

1 Like