Does a sort have a computation overhead when the sorted field is the same as the queried field ?

[Simon T.]

Hi guys,

I got a performance question for you. I'll explain the short version of the question first and then a more detailed version of it.

Let say your document looks like this:

{

  min: 0

  max: 3

}

If I index the "max" field, that means that I've created a sorted index table of the documents, right ? So if my query looks like:

db.myCollection.find( { "max" : { $gt : 2 } } ).sort( { "max" : 1 } ).limit(1)

Is there any overhead of sorting the collection in the query, or since the index table is already sorted on the "max" field it is a "free" sort ?

If it is a free sort, does that mean that the cursor it returns will always be sorted ? Or you still need to specify you want your cursor to be sorted ?

Thank you !

Simon

[Thomas Boyd]

In your example, yes, you will get the sort for "free".  MongoDB can use the single index both to restrict the results returned and also to order those results.

As to the second part of your question: provided MongoDB determines that it needs to use your index to restrict the result set, it will return those results in a sorted order, even if you do not provide an explicit sort.   However, you should not rely on the decision to use the index, and should always specify a sort order if it is required by your application. As you have pointed out -- there is no additional cost if you are already using the index.

To see if your query is using an index for sorting or whether it has to perform an in-memory sort of the results, look at the scanAndOrder field from the explain() output.  For example, the output below indicates that the query did not have to order the results after accessing the data (i.e. it leveraged the index for sorting):

> db.stuff.find().sort({_id: 1}).explain();

{

"cursor" : "BtreeCursor _id_",

"isMultiKey" : false,

"n" : 51428,

"nscannedObjects" : 51428,

"nscanned" : 51428,

"nscannedObjectsAllPlans" : 51428,

"nscannedAllPlans" : 51428,

"scanAndOrder" : false,

"indexOnly" : false,

"nYields" : 0,

"nChunkSkips" : 0,

"millis" : 464,

        "indexBounds" : { "_id" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ] }, 

        "server" : "myserver:27017" }

[Simon T.]

Thanks Thomas !