Sorting on Multiple fields mongo DB

Question

I have a query in mongo such that I want to give preference to the first field and then the second field.

Say I have to query such that

db.col.find({category: A}).sort({updated: -1, rating: -1}).limit(10).explain() 

So I created the following index

db.col.ensureIndex({category: 1, rating: -1, updated: -1}) 

It worked just fined scanning as many objects as needed, i.e. 10.

But now I need to query

db.col.find({category: { $ne: A}}).sort({updated: -1, rating: -1}).limit(10) 

So I created the following index:

 db.col.ensureIndex({rating: -1, updated: -1}) 

but this leads to scanning of the whole document and when I create

 db.col.ensureIndex({ updated: -1 ,rating: -1}) 

It scans less number of documents:

I just want to ask to be clear about sorting on multiple fields and what is the order to be preserved when doing so. By reading the MongoDB documents, it's clear that the field on which we need to perform sorting should be the last field. So that is the case I assumed in my $ne query above. Am I doing anything wrong?

Answer 1

The MongoDB query optimizer works by trying different plans to determine which approach works best for a given query. The winning plan for that query pattern is then cached for the next ~1,000 queries or until you do an explain().

To understand which query plans were considered, you should use explain(1), eg:

db.col.find({category:'A'}).sort({updated: -1}).explain(1) 

The allPlans detail will show all plans that were compared.

If you run a query which is not very selective (for example, if many records match your criteria of {category: { $ne:'A'}}), it may be faster for MongoDB to find results using a BasicCursor (table scan) rather than matching against an index.

The order of fields in the query generally does not make a difference for the index selection (there are a few exceptions with range queries). The order of fields in a sort does affect the index selection. If your sort() criteria does not match the index order, the result data has to be re-sorted after the index is used (you should see scanAndOrder:true in the explain output if this happens).

It's also worth noting that MongoDB will only use one index per query (with the exception of $ors).

So if you are trying to optimize the query:

db.col.find({category:'A'}).sort({updated: -1, rating: -1}) 

You will want to include all three fields in the index:

db.col.ensureIndex({category: 1, updated: -1, rating: -1}) 

FYI, if you want to force a particular query to use an index (generally not needed or recommended), there is a hint() option you can try.