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?

Answer 1

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().