MongoDB: why doesn't sorting by multiple keys use an index?

Question

Question: I have a very large collection that is indexed by field ts: (timestamp)

> db.events.ensureIndex({'ts': -1})

I want to get last 5 entries. What surprises me is that the query doesn't use the index and is thus very slow:

> db.events.find().sort({'ts': -1, '_id': -1}).limit(5)

However, sorting just by ts or the other field uses index as it should:

> db.events.find().sort({'ts': -1}).limit(5)
> db.events.find().sort({'_id': -1}).limit(5)

Is this a bug in MongoDB, is this indeed a documented feature or am I doing something wrong?

Additional info:

> db.events.find().sort({'ts': -1, '_id': -1}).limit(5).explain()
{
    "cursor" : "BasicCursor",
    "nscanned" : 795609,
    "nscannedObjects" : 795609,
    "n" : 5,
    "scanAndOrder" : true,
    "millis" : 22866,
    "nYields" : 73,
    "nChunkSkips" : 0,
    "isMultiKey" : false,
    "indexOnly" : false,
    "indexBounds" : {

    }
}
> db.events.find().sort({'ts': -1}).limit(5).explain()
{
    "cursor" : "BtreeCursor ts_-1",
    "nscanned" : 5,
    "nscannedObjects" : 5,
    "n" : 5,
    "millis" : 0,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "isMultiKey" : false,
    "indexOnly" : false,
    "indexBounds" : {
            "ts" : [
                    [
                            {
                                    "$maxElement" : 1
                            },
                            {
                                    "$minElement" : 1
                            }
                    ]
            ]
    }
}

Stennie · Accepted Answer · 2012-08-22 12:23:21Z

5

It's worth having a read of the Indexing Strategies section of the Indexing Advice & FAQ wiki page.

There are a few considerations that you may be missing:

MongoDB only uses one index per query
the sort column used must be the last column in the index

So, for your example you should add a compound index on ts and _id:

db.events.ensureIndex({'ts':-1, '_id':-1});

.. and confirm with explain() that the sort is now using the expected index:

> db.events.find().sort({'ts': -1, '_id':-1}).limit(5).explain()
{
    "cursor" : "BtreeCursor ts_-1__id_-1",
    "nscanned" : 5,
    "nscannedObjects" : 5,
    "n" : 5,
    "millis" : 0,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "isMultiKey" : false,
    "indexOnly" : false,
    "indexBounds" : {
        "ts" : [
            [
                {
                    "$maxElement" : 1
                },
                {
                    "$minElement" : 1
                }
            ]
        ],
        "_id" : [
            [
                {
                    "$maxElement" : 1
                },
                {
                    "$minElement" : 1
                }
            ]
        ]
    }
}

answered Aug 22, 2012 at 12:23

Stennie

65.6k15 gold badges155 silver badges181 bronze badges

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

2 Comments

johndodo Over a year ago

This is indeed the answer - I guess relational DBs have spoiled me... :) Thanks!

user4951 Over a year ago

Why the sort column must be the last column in the index?

Collectives™ on Stack Overflow

MongoDB: why doesn't sorting by multiple keys use an index?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related