MongoDB Index and Natural Sort Optimization

Question

What's the fastest method to sort by reverse insertion order on a capped-collection ('rf' has been sparse-indexed)

db.log.find({ rf : 'o-5556457634'}).sort({ '$natural' : -1 }).explain();
{
"cursor" : "ReverseCappedCursor",
"nscanned" : 1654468,
"nscannedObjects" : 1654468,
"n" : 4,
"millis" : 2932,
"nYields" : 5,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {

}
}

seems like 'natural' bypass the use of the indexed ('rf') field, significantly slowing the query. Is this an expected expected behaviour? Shouldn't the 'natural' sort be computed after the find/index?

without the 'natural' sort:

db.log.find({ rf : 'o-5556457634'}).explain();
{
"cursor" : "BtreeCursor rf_1",
"nscanned" : 4,
"nscannedObjects" : 4,
"n" : 4,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
    "rf" : [
        [
            "o-5556457634",
            "o-5556457634"
        ]
    ]
}

Hint does force the engine to use the 'rf' index but the result bypass the (reverse) 'natural' sort

db.log.find({ rf : 'o-5556457634'}).sort({ '$natural' : -1 }).hint({rf :1}).explain();
{
"cursor" : "BtreeCursor rf_1",
"nscanned" : 4,
"nscannedObjects" : 4,
"n" : 4,
"scanAndOrder" : true,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
    "rf" : [
        [
            "o-5556457634",
            "o-5556457634"
        ]
    ]
}
}

mephisto123 · Accepted Answer · 2015-06-20 12:14:57Z

2

Faced the same problem but found a solution. You can create index on the fields you're mentioning in find filter with addition of the "_id":-1 field and then use sort({"_id":-1}). Helped me.

answered Jun 20, 2015 at 12:14

mephisto123

1,4481 gold badge16 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ian Mercer · Accepted Answer · 2012-04-20 02:32:26Z

1

It looks like the query optimizer is doing the wrong thing when you add the sort.

Can you try adding .hint({rf :1}) to the query to see what happens?

answered Apr 20, 2012 at 2:32

Ian Mercer

39.4k8 gold badges103 silver badges139 bronze badges

3 Comments

Lokus Pokus Over a year ago

Just updated the question with the hint-explain. It does force the engine to use the 'rf' index but the result bypass the (reverse) 'natural' sort.

Lokus Pokus Over a year ago

Yes, with the same results (reverse natural sort being ignored). Ideally I would like to avoid 'hint' to keep the query as simple/intuitive as possible, as the 'find' query will be dynamic. Trying to understand if it's expected for a 'natural' sort to throw the index-usage out of the window.

Lokus Pokus Over a year ago

Apparently there is no mechanism to use natural sort AND indexes. Pretty odd, but seems like there is no built-in method to accomplish a basic task like this.

Collectives™ on Stack Overflow

MongoDB Index and Natural Sort Optimization

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related