CODEWITHSUNDEEP
performancemongodb
Alan
Alan

Reputation: 1134

_id field vs indexed date field to obtain latest documents

I need to be able to find the most recently added documents. I have decided to include a date field that contains the date they were added, this field is indexed. I am however, a bit curious about which query is superior, performance wise. Say I wnated to obtain the first 50 documents, which query would run fastest?

db.collection.find({}).sort({_id : 1}).limit(50);

db.collection.find({date : {$lt : new Date() }}).sort({_id : 1}).limit(50);

db.collection.find({}).sort({date : 1}).limit(50);

Upvotes: 0

Views: 28

Answers (1)

Allen Chou
Allen Chou

Reputation: 1237

You can use explain to compare the performance of the three query.

Given the three query and based on the description you offer, the second one actually dose a date field query before sort, the first and third one will directly scan the index in order(_id field index, date field index). Which will be fastest between the first and third, you can find by yourself with the explain.

Besides, if you need to find the most recently added documents, then the order in sort should be like _id: -1, i.e in descend order.

A real case would be:

db.collection.find().sort({"_id": -1}).limit(50).explain()
{
    // BtreeCursor is an indicator that your query actually use the index
    "cursor" : "BtreeCursor _id_ reverse",
    "isMultiKey" : false,
    "n" : 50,
    "nscannedObjects" : 50,
    "nscanned" : 50,
    "nscannedObjectsAllPlans" : 50,
    "nscannedAllPlans" : 50,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    // time used to execute the query
    "millis" : 5,
    "indexBounds" : {
        "_id" : [
            [
                {
                    "$maxElement" : 1
                },
                {
                    "$minElement" : 1
                }
            ]
        ]
    },
    "server" : "....",
    "filterSet" : false
}

Upvotes: 1

Related Questions