mongodb not using indexes when sorting?

Question

i have a collection with these indexes:

> db.message.getIndexKeys()
[
    {
        "_id" : 1
    },
    {
        "msgid" : 1
    },
    {
        "keywords" : 1,
        "msgid" : 1
    }
]

and a query like

db.message.find({'keywords': {'$all': ['apple', 'banana']}}).limit(30).explain()

works fine with index

{
    "cursor" : "BtreeCursor keywords_1_msgid_1",    
    "nscanned" : 96,
    "nscannedObjects" : 96,
    ...
}

but when sorting with msgid:

db.message.find({'keywords': {'$all': ['apple', 'banana']}})
    .sort({msgid:-1})
    .limit(30).explain()

mongodb do not use indexes any more:

{
"cursor" : "BtreeCursor msgid_1 reverse",
"nscanned" : 1784455,
"nscannedObjects" : 1784455,
...
}

any solutions?

Answer 1

Mongo actually is using an index (which you can tell by seeing BtreeCursor in the explain), just not the compound one.

It's important to keep in mind that direction matters when you have a compound index.

Try: db.ensureIndex({ keywords: 1, msg_id: -1 })

Mongo chooses to use msg_id index in reverse in your example because its faster to retrieve the results in sorted order and then match in O(n) time than to match the results and then sort in O(nlogn) time.

Answer 2

It is using an index -- the index on msgid. MongoDB chooses an index to use for a query by trying all possible indexes, and using whichever one finishes first. This result is cached for 1,000 queries, or until a certain number of modifications to the collection are made (data changes, new indexes, etc).

You can see all the query plans tried by passing true to explain().

For more details, see http://www.mongodb.org/display/DOCS/Query+Optimizer.