I’ve been looking at some of my MongoDB queries and trying to optimise them. I had one query that did a few ‘in’ statement and then sorted on a field called CreatedTime in descending order to give me the latest documents first.

My index looked something like:

{ “Field1” : 1, “Field2” : 1, “Field3” : 1, “CreatedTime” -1 }

With this index I got the following information from the Explain method:

“nscanned” : 8120, “nscannedObjects” : 4364, “n” : 121, “scanAndOrder” : true, “millis” : 62

I couldn’t really work out why it was scanning my entire index, and also half the documents in the collection. Playing around with the query I found that it was the sorting that was causing it. After removing my sort I get the following result:

“nscanned” : 128, “nscannedObjects” : 126, “n” : 121, “millis” : 2

Much better, although unfortunately in the incorrect order. If I was using a capped collection I could use reverse natural order, but I’m not. I thought that as I had CreatedTime indexed descending, doing a sort on that field descending would be fine, but just for fun I thought I’d put the field as the first field specified in my index to see if that would alter how MongoDB stores and uses the index:

{ “CreatedTime” -1, “Field1” : 1, “Field2” : 1, “Field3” : 1 }

Now running the query gives me the following:

“nscanned” : 256, “nscannedObjects” : 121, “n” : 121, “millis” : 2

That’s more like it, and you see the scanAndOrder attribute is no longer there. I’m limiting the query to 121 results, and the number of documents hit is 121, which is good as that means the query itself only used the index, and the exact documents were only accessed to get the fields I’m returning.

The query is scanning 256 index entries, presumably that how many it had to look at to match 121 documents based on my other query criteria.