Does Mongo sorting _id in descending order take advantage of the automatic index or?

Question

I'm trying to figure out if sorting by the _id field in descending order takes advantage of the index created automatically by the system. I tried to use explain() to figure it out but I'm not sure. Should I create an additional index on _id in descending order to get the data back faster?

> db.foo.insert({ name: 'foo' });
> db.foo.insert({ name: 'bar' });
> db.foo.find();
{ "_id" : ObjectId("5142d30ca4a8b347cb678c1a"), "name" : "foo" }
{ "_id" : ObjectId("5142d310a4a8b347cb678c1b"), "name" : "bar" }
> db.foo.find().sort({ _id: -1 });
{ "_id" : ObjectId("5142d310a4a8b347cb678c1b"), "name" : "bar" }
{ "_id" : ObjectId("5142d30ca4a8b347cb678c1a"), "name" : "foo" }
> db.foo.find().sort({ _id: -1 }).explain();
{
    "cursor" : "BtreeCursor _id_ reverse",
    "isMultiKey" : false,
    "n" : 2,
    "nscannedObjects" : 2,
    "nscanned" : 2,
    "nscannedObjectsAllPlans" : 2,
    "nscannedAllPlans" : 2,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "_id" : [
            [
                {
                    "$maxElement" : 1
                },
                {
                    "$minElement" : 1
                }
            ]
        ]
    },
    "server" : "localhost:27017"
}

Sammaye Sammaye · Accepted Answer · 2013-03-15T08:48:18

The explain does give a good indicator as to whether it is able to use the default _id index in reverse because of the very first line under the property cursor: BtreeCursor _id_ reverse.

The BtreeCursor shows that it is a cursor which is using an index, while _id_ reverse shows that it is using the _id_ index in reverse.

From all intents and purposes it should be using the _id index in reverse correctly.

As an additional, MongoDB does not yet have index intersectioning ( https://jira.mongodb.org/browse/SERVER-3071 ) which means that it does still use (for most queries) a single index for both the condition and the sort so if you wish to use a find condition you will find that you will probably require to include the _id in future indexes as well.

Does Mongo sorting _id in descending order take advantage of the automatic index or?

1 Answers