mongodb index (reverse) optimization

Question

I have a mongodb collection, "features", having 3 fields: name, active, weight. I will sort features by weight descending:

db.features.find({active:true},{name:1, weight:1}).sort({weight:-1})

for optimization, i create index for it:

db.features.ensureIndex({'active': 1, 'weight': -1})

I can see it works well when using explain() in query.

However, when i query it by weight ascending, i suppose the index i just created will not work and i need to create another index on weight ascending. Query:

db.features.find({active:true},{name:1, weight:1}).sort({weight:1}).explain()

when i use explain() to show how index working, i find it prints out:

"cursor" : "BtreeCursor active_1_weight_-1 reverse",

does the index reverse mean the query is optimized by the index?

generally, do i need to create 2 index like ascending on weight and descending on weight if i will sort it by weight ascending in some case and descending in other cases?

ann · Accepted Answer · 2013-02-16 15:13:16Z

I know I'm late but I would like to add a little more detail. When you use explain() and it outputs cursor: BtreeCursor, it doesn't always guarantee that only the index is used to satisfy your query. You also have to check the "indexOnly" option in the results of explain(). If indexOnly is outputted as true it means that your query was satisfied using the index only and the documents in the collection was not referred to at all. This is called 'covered index query' http://docs.mongodb.org/manual/applications/indexes/

But if the results of explain are cursor: BtreeCursor and indexOnly:false, it means that in addition to using the index, the collection was also referred to. In you case, for the query:

    db.features.find({active:true},{name:1, weight:1}).sort({weight:1}).explain()

Mongo would have used the index 'active': 1, 'weight': -1 to satisfy the initial part of the query i.e. db.features.find({active:true}) and would have done the sort without using the index. So to know exactly, you have to look at the indexOnly result within explain().

Mark Hillick · Accepted Answer · 2012-08-23 08:40:20Z

9

As you can see from this document, when explain() outputs BtreeCursor, it means that an index was used. When an index is used, indexBounds will be set to indicate the key bounds for scanning in the index. However, if the putput showed BasicCursor, it indicates a table scan style operation.

So based on what you've said, from the explain() results, you can see that you're using a BTree Cursor on the index named active_1_weight_-1 and the reverse means that you're iterating over the index in reverse order.

So no, you don't need to create separate indexes.

answered Aug 23, 2012 at 8:40

Mark Hillick

6,9831 gold badge21 silver badges23 bronze badges

Comments

user3208003 · Accepted Answer · 2014-10-02 02:51:02Z

this is very confusing. in mongodb class there is an example see below.

notice BtreeCursor reverse is used ONLY for the purpose of sorting in skip and limit command

NOT for the purpose of locating the record.

the lesson is if nscan =40k and n=10 means btree index is not used in locating record.

so when u see btreecursor index reverse does not necessay mean index get used to locating the reocrd.

Suppose you have a collection called tweets whose documents contain information about thecreated_at time of the tweet and the user's followers_count at the time they issued the tweet. What can you infer from the following explain output? db.tweets.find({"user.followers_count":{$gt:1000}}).sort({"created_at" : 1 }).limit(10).skip(5000).explain() { "cursor" : "BtreeCursor created_at_-1 reverse", "isMultiKey" : false, "n" : 10, "nscannedObjects" : 46462, "nscanned" : 46462, "nscannedObjectsAllPlans" : 49763, "nscannedAllPlans" : 49763, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 205, "indexBounds" : { "created_at" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ] }, "server" : "localhost.localdomain:27017" } This query performs a collection scan. yes The query uses an index to determine the order in which to return result documents. yes The query uses an index to determine which documents match. no The query returns 46462 documents no

Adam Comerford · Accepted Answer · 2012-08-23 08:34:09Z

1

Assuming you are using 2.0+ then reverse traversal is not more costly to MongoDB, so for this case you don't need to create separate indexes for the forward/reverse sort. You can confirm by creating it and using hint() if you wish (the optimizer will cache the current index for a while, so will not automatically select the other index).

answered Aug 23, 2012 at 8:34

Adam Comerford

21.7k4 gold badges68 silver badges87 bronze badges

Collectives™ on Stack Overflow

mongodb index (reverse) optimization

4 Answers 4

Comments

Comments

notice BtreeCursor reverse is used ONLY for the purpose of sorting in skip and limit command

NOT for the purpose of locating the record.

the lesson is if nscan =40k and n=10 means btree index is not used in locating record.

so when u see btreecursor index reverse does not necessay mean index get used to locating the reocrd.

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

notice BtreeCursor reverse is used ONLY for the purpose of sorting in skip and limit command

NOT for the purpose of locating the record.

the lesson is if nscan =40k and n=10 means btree index is not used in locating record.

so when u see btreecursor index reverse does not necessay mean index get used to locating the reocrd.

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related