Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.8k views
in Technique[技术] by (71.8m points)

mongodb - Sorting on Multiple fields mongo DB

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...