a developer

MongoDB group

$group to group data by specified attribute

// prepare data
> db.company.insertOne({"name":"A","foundYear":1980,"numberOfEmployee":50})
{
        "acknowledged" : true,
        "insertedId" : ObjectId("5f96340713b68b37fd0ed626")
}
> db.company.insertOne({"name":"B","foundYear":1980,"numberOfEmployee":60})
{
        "acknowledged" : true,
        "insertedId" : ObjectId("5f96340f13b68b37fd0ed627")
}
> db.company.insertOne({"name":"C","foundYear":1980,"numberOfEmployee":70})
{
        "acknowledged" : true,
        "insertedId" : ObjectId("5f96341513b68b37fd0ed628")
}
> db.company.insertOne({"name":"D","foundYear":1981,"numberOfEmployee":50})
{
        "acknowledged" : true,
        "insertedId" : ObjectId("5f96341f13b68b37fd0ed629")
}
> db.company.insertOne({"name":"E","foundYear":1981,"numberOfEmployee":60})
{
        "acknowledged" : true,
        "insertedId" : ObjectId("5f96342713b68b37fd0ed62a")
}
> db.company.insertOne({"name":"F","foundYear":1981,"numberOfEmployee":70})
{
        "acknowledged" : true,
        "insertedId" : ObjectId("5f96342e13b68b37fd0ed62b")
}
> db.company.insertOne({"name":"G","foundYear":1980,"numberOfEmployee":70})
{
        "acknowledged" : true,
        "insertedId" : ObjectId("5f9634a013b68b37fd0ed62c")
}

// find data
> db.company.find().pretty()
{
        "_id" : ObjectId("5f96340713b68b37fd0ed626"),
        "name" : "A",
        "foundYear" : 1980,
        "numberOfEmployee" : 50
}
{
        "_id" : ObjectId("5f96340f13b68b37fd0ed627"),
        "name" : "B",
        "foundYear" : 1980,
        "numberOfEmployee" : 60
}
{
        "_id" : ObjectId("5f96341513b68b37fd0ed628"),
        "name" : "C",
        "foundYear" : 1980,
        "numberOfEmployee" : 70
}
{
        "_id" : ObjectId("5f96341f13b68b37fd0ed629"),
        "name" : "D",
        "foundYear" : 1981,
        "numberOfEmployee" : 50
}
{
        "_id" : ObjectId("5f96342713b68b37fd0ed62a"),
        "name" : "E",
        "foundYear" : 1981,
        "numberOfEmployee" : 60
}
{
        "_id" : ObjectId("5f96342e13b68b37fd0ed62b"),
        "name" : "F",
        "foundYear" : 1981,
        "numberOfEmployee" : 70
}
{
        "_id" : ObjectId("5f9634a013b68b37fd0ed62c"),
        "name" : "G",
        "foundYear" : 1980,
        "numberOfEmployee" : 70
}

// group by foundYear
> db.company.aggregate({$group:{_id:{foundYear:"$foundYear"},avg_numberOfEmployee:{$avg:"$numberOfEmployee"}}})
{ "_id" : { "foundYear" : 1981 }, "avg_numberOfEmployee" : 60 }
{ "_id" : { "foundYear" : 1980 }, "avg_numberOfEmployee" : 62.5 }

// group by foundYear, sort by numberOfEmployee
> db.company.aggregate([{$group:{_id:{foundYear:"$foundYear"},avg_numberOfEmployee:{$avg:"$numberOfEmployee"}}},{$sort:{avg_numberOfEmployee:-1}}])
{ "_id" : { "foundYear" : 1980 }, "avg_numberOfEmployee" : 62.5 }
{ "_id" : { "foundYear" : 1981 }, "avg_numberOfEmployee" : 60 }

List all name in group by using $group

// list all names by using $push
> db.company.aggregate([{$group: {_id:{foundYear:"$foundYear"},avgNumberOfEmployee:{$avg:"$numberOfEmployee"},names:{$push:"$name"}}},{$sort:{avgNumberOfEmployee:-1}}]).pretty() 
{ 
        "_id" : { 
                "foundYear" : 1980 
        }, 
        "avgNumberOfEmployee" : 62.5, 
        "names" : [ 
                "A", 
                "B", 
                "C", 
                "G" 
        ] 
} 
{ 
        "_id" : { 
                "foundYear" : 1981 
        }, 
        "avgNumberOfEmployee" : 60, 
        "names" : [ 
                "D", 
                "E", 
                "F" 
        ] 
}

it is a best practice to explicitly label values on which we group.

MongoDB accomulator

Accomulator calculate values from field values found in multiple documents.

// prepare data
> db.acc.insertOne({"name":"a","nums":[{"num":1},{"num":2},{"num":3}]})
{
    "acknowledged" : true,
    "insertedId" : ObjectId("5f8502a6651639530e9c2b8b")
}
> db.acc.insertOne({"name":"b","nums":[{"num":2},{"num":3},{"num":4}]})
{
    "acknowledged" : true,
    "insertedId" : ObjectId("5f8502b5651639530e9c2b8c")
}
> db.acc.insertOne({"name":"c","nums":[{"num":3},{"num":4},{"num":5}]})
{
    "acknowledged" : true,
    "insertedId" : ObjectId("5f8502c2651639530e9c2b8d")
}
> db.acc.insertOne({"name":"d","nums":[]})
{
    "acknowledged" : true,
    "insertedId" : ObjectId("5f8502d3651639530e9c2b8e")
}
> db.acc.insertOne({"name":"e"})
{
    "acknowledged" : true,
    "insertedId" : ObjectId("5f8502f3651639530e9c2b8f")
}

// data seems like 
> db.acc.find().pretty()
{
    "_id" : ObjectId("5f8502a6651639530e9c2b8b"),
    "name" : "a",
    "nums" : [
        {
            "num" : 1
        },
        {
            "num" : 2
        },
        {
            "num" : 3
        }
    ]
}
{
    "_id" : ObjectId("5f8502b5651639530e9c2b8c"),
    "name" : "b",
    "nums" : [
        {
            "num" : 2
        },
        {
            "num" : 3
        },
        {
            "num" : 4
        }
    ]
}
{
    "_id" : ObjectId("5f8502c2651639530e9c2b8d"),
    "name" : "c",
    "nums" : [
        {
            "num" : 3
        },
        {
            "num" : 4
        },
        {
            "num" : 5
        }
    ]
}
{ "_id" : ObjectId("5f8502d3651639530e9c2b8e"), "name" : "d", "nums" : [ ] }
{ "_id" : ObjectId("5f8502f3651639530e9c2b8f"), "name" : "e" }

$exists filter out empty nums

> db.acc.aggregate({$match:{"nums":{$exists:true,$ne:[]}}})
{ "_id" : ObjectId("5f8502a6651639530e9c2b8b"), "name" : "a", "nums" : [ { "num" : 1 }, { "num" : 2 }, { "num" : 3 } ] }
{ "_id" : ObjectId("5f8502b5651639530e9c2b8c"), "name" : "b", "nums" : [ { "num" : 2 }, { "num" : 3 }, { "num" : 4 } ] }
{ "_id" : ObjectId("5f8502c2651639530e9c2b8d"), "name" : "c", "nums" : [ { "num" : 3 }, { "num" : 4 }, { "num" : 5 } ] }

$max to get max element

> db.acc.aggregate({$match:{"nums":{$exists:true,$ne:[]}}},{$project:{max:{$max:"$nums.num"}}})
{ "_id" : ObjectId("5f8502a6651639530e9c2b8b"), "max" : 3 }
{ "_id" : ObjectId("5f8502b5651639530e9c2b8c"), "max" : 4 }
{ "_id" : ObjectId("5f8502c2651639530e9c2b8d"), "max" : 5 }

$sum to get total number

> db.acc.aggregate({$match:{"nums":{$exists:true,$ne:[]}}},{$project:{max:{$sum:"$nums.num"}}})
{ "_id" : ObjectId("5f8502a6651639530e9c2b8b"), "max" : 6 }
{ "_id" : ObjectId("5f8502b5651639530e9c2b8c"), "max" : 9 }
{ "_id" : ObjectId("5f8502c2651639530e9c2b8d"), "max" : 12 }

MongoDB aggregate

$match: same as find

> db.users.aggregate([{$match:{name:"user164"}}])
{ "_id" : ObjectId("5f7c234602c41bd39db9af76"), "name" : "user164" }
{ "_id" : ObjectId("5f7c234c02c41bd39db9af77"), "name" : "user164" }
{ "_id" : ObjectId("5f7c23f702c41bd39db9af78"), "name" : "user164", "comments" : "abc" }

$project: reduce output to specific fields

> db.users.aggregate([{$match:{name:"user164"}},{$project:{_id:0,"name":1}}])
{ "name" : "user164" }
{ "name" : "user164" }
{ "name" : "user164" }

$limit: limit processing data size

> db.users.aggregate([{$match:{name:"user164"}},{$project:{_id:0,"name":1}},{$limit:2}])
{ "name" : "user164" }
{ "name" : "user164" }

$sort: sort by specific field

> db.users.aggregate([{$match:{age:{$gt:10}}},{$project:{_id:0,"name":1,age:1}},{$limit:5},{$sort:{age:1}}])
{ "name" : "user4", "age" : 70 }
{ "name" : "user3", "age" : 84 }
{ "name" : "user0", "age" : 113 }
{ "name" : "user2", "age" : 114 }
{ "name" : "user1", "age" : 115 }

$skip records

> db.users.aggregate([{$match:{age:{$gt:10}}},{$project:{_id:0,"name":1,age:1}},{$skip:5},{$limit:5},{$sort:{age:1}}])
{ "name" : "user10", "age" : 22 }
{ "name" : "user6", "age" : 28 }
{ "name" : "user7", "age" : 36 }
{ "name" : "user9", "age" : 63 }
{ "name" : "user5", "age" : 88 }

define new attribute when $project

// update embedded object to one user
> db.users.updateOne({"name":"user1956"},{$set:{"aa":{"bb":"cc"}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

// show aa.bb by element by $project
> db.users.aggregate([{$match:{age:{$gt:10}}},{$project:{_id:0,"name":1,age:1,"name2":"$name","bb":"$aa.bb"}},{$skip:5}])
{ "name" : "user1864", "age" : 11, "name2" : "user1864" }
{ "name" : "user1907", "age" : 11, "name2" : "user1907" }
{ "name" : "user1929", "age" : 11, "name2" : "user1929" }
{ "name" : "user1956", "age" : 11, "name2" : "user1956", "bb" : "cc" }

$unwind

// prepare data
> db.unwinddata.insertOne({"k1":"v1","k2":"v2","k3":["e1","e2","e3"]})
{
    "acknowledged" : true,
    "insertedId" : ObjectId("5f833438651639530e9c2b82")
}
> db.unwinddata.updateOne({},{$set:{"k4":{"k5":["e4","e5","e6"]}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.unwinddata.find().pretty()
{
    "_id" : ObjectId("5f833438651639530e9c2b82"),
    "k1" : "v1",
    "k2" : "v2",
    "k3" : [
        "e1",
        "e2",
        "e3"
    ],
    "k4" : {
        "k5" : [
            "e4",
            "e5",
            "e6"
        ]
    }
}

// match data in array
> db.unwinddata.aggregate({$match:{"k4.k5":"e4"}}, {$project:{_id:0}}).pretty()
{
    "k1" : "v1",
    "k2" : "v2",
    "k3" : [
        "e1",
        "e2",
        "e3"
    ],
    "k4" : {
        "k5" : [
            "e4",
            "e5",
            "e6"
        ]
    }
}

// unwind k3 array
> db.unwinddata.aggregate({$match:{"k4.k5":"e4"}}, {$unwind: "$k3"}, {$project:{_id:0}}).pretty()
{
    "k1" : "v1",
    "k2" : "v2",
    "k3" : "e1",
    "k4" : {
        "k5" : [
            "e4",
            "e5",
            "e6"
        ]
    }
}
{
    "k1" : "v1",
    "k2" : "v2",
    "k3" : "e2",
    "k4" : {
        "k5" : [
            "e4",
            "e5",
            "e6"
        ]
    }
}
{
    "k1" : "v1",
    "k2" : "v2",
    "k3" : "e3",
    "k4" : {
        "k5" : [
            "e4",
            "e5",
            "e6"
        ]
    }
}

// project attribute from embedded object
> db.unwinddata.aggregate({$match:{"k4.k5":"e4"}}, {$project:{_id:0,k1:1,k2:2,k3:3,k4:1,kk:"$k4.k5"}}).pretty()
{
    "k1" : "v1",
    "k2" : "v2",
    "k3" : [
        "e1",
        "e2",
        "e3"
    ],
    "k4" : {
        "k5" : [
            "e4",
            "e5",
            "e6"
        ]
    },
    "kk" : [
        "e4",
        "e5",
        "e6"
    ]
}

// match as early as possible => match after unwind
> db.unwinddata.aggregate({$match:{"k4.k5":"e4"}}, {$unwind: "$k3"}, {$match:{"k3":"e3"}}, {$project:{_id:0}}).pretty()
{
    "k1" : "v1",
    "k2" : "v2",
    "k3" : "e3",
    "k4" : {
        "k5" : [
            "e4",
            "e5",
            "e6"
        ]
    }
}

$filter an array

// prepare data
> db.unwinddata.updateOne({}, {$set:{"k6":[{"kv":{"num":62}},{"kv":{"num":64}}]}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

> db.unwinddata.find().pretty()
{
    "_id" : ObjectId("5f84074ba576e2ec23a7ec0a"),
    "k1" : "v1",
    "k2" : "v2",
    "k3" : [
        "e1",
        "e2",
        "e3"
    ],
    "k4" : {
        "k5" : [
            "e4",
            "e5",
            "e6"
        ]
    },
    "k6" : [
        {
            "kv" : {
                "num" : 62
            }
        },
        {
            "kv" : {
                "num" : 64
            }
        }
    ]
}

> db.unwinddata.aggregate({$project:{k6:1,kk:{$filter:{input:"$k6",as:"kvkvkv",cond:{$gte:["$$kvkvkv.kv.num",63]}}}}}).pretty()
{
    "_id" : ObjectId("5f84074ba576e2ec23a7ec0a"),
    "k6" : [
        {
            "kv" : {
                "num" : 62
            }
        },
        {
            "kv" : {
                "num" : 64
            }
        }
    ],
    "kk" : [
        {
            "kv" : {
                "num" : 64
            }
        }
    ]
}

Use $arrayElemAt to select specified index of an array

// $arrayElemAt with -1 select the first value
> db.unwinddata.aggregate({$project:{k1:1,k2:1,k3:1,k4:1,k6:1,kk:{$arrayElemAt:["$k6",-1]}}}).pretty()
{
    "_id" : ObjectId("5f833438651639530e9c2b82"),
    "k1" : "v1",
    "k2" : "v2",
    "k3" : [
        "e1",
        "e2",
        "e3"
    ],
    "k4" : {
        "k5" : [
            "e4",
            "e5",
            "e6"
        ]
    },
    "k6" : [
        {
            "kv" : {
                "num" : 62
            }
        },
        {
            "kv" : {
                "num" : 64
            }
        }
    ],
    "kk" : {
        "kv" : {
            "num" : 64
        }
    }
}

$slice to get range of data.

> db.unwinddata.aggregate({$project:{_id:0,k6:1,kk:{$slice:["$k6.kv.num",1,2]}}}).pretty()
{
    "k6" : [
        {
            "kv" : {
                "num" : 62
            }
        },
        {
            "kv" : {
                "num" : 64
            }
        }
    ],
    "kk" : [
        64
    ]
}

Get array size by $size

> db.unwinddata.aggregate({$project:{_id:0,k6:1,kk:{$size:"$k6"}}}).pretty()
{
    "k6" : [
        {
            "kv" : {
                "num" : 62
            }
        },
        {
            "kv" : {
                "num" : 64
            }
        }
    ],
    "kk" : 2
}

MongoDB Special indexes

Capped Collections

// Create capped collection (100000 bytes and max 10 entries)
> db.createCollection("ring_buffer",{"capped":true,"size":100000,"max":10})
{ "ok" : 1 }

// insert more than 10 messages
> db.ring_buffer.insert({"message":1})
WriteResult({ "nInserted" : 1 })
> db.ring_buffer.insert({"message":2})
WriteResult({ "nInserted" : 1 })
> db.ring_buffer.insert({"message":3})
WriteResult({ "nInserted" : 1 })
> db.ring_buffer.insert({"message":4})
WriteResult({ "nInserted" : 1 })
> db.ring_buffer.insert({"message":5})
WriteResult({ "nInserted" : 1 })
> db.ring_buffer.insert({"message":6})
WriteResult({ "nInserted" : 1 })
> db.ring_buffer.insert({"message":7})
WriteResult({ "nInserted" : 1 })
> db.ring_buffer.insert({"message":8})
WriteResult({ "nInserted" : 1 })
> db.ring_buffer.insert({"message":9})
WriteResult({ "nInserted" : 1 })
> db.ring_buffer.insert({"message":10})
WriteResult({ "nInserted" : 1 })
> db.ring_buffer.insert({"message":11})
WriteResult({ "nInserted" : 1 })

// list collection, only rest 10 entries
> db.ring_buffer.find()
{ "_id" : ObjectId("5f7d7b1b4db79b5cc8c54240"), "message" : 2 }
{ "_id" : ObjectId("5f7d7b1d4db79b5cc8c54241"), "message" : 3 }
{ "_id" : ObjectId("5f7d7b204db79b5cc8c54242"), "message" : 4 }
{ "_id" : ObjectId("5f7d7b234db79b5cc8c54243"), "message" : 5 }
{ "_id" : ObjectId("5f7d7b264db79b5cc8c54244"), "message" : 6 }
{ "_id" : ObjectId("5f7d7b274db79b5cc8c54245"), "message" : 7 }
{ "_id" : ObjectId("5f7d7b2a4db79b5cc8c54246"), "message" : 8 }
{ "_id" : ObjectId("5f7d7b2c4db79b5cc8c54247"), "message" : 9 }
{ "_id" : ObjectId("5f7d7b2e4db79b5cc8c54248"), "message" : 10 }
{ "_id" : ObjectId("5f7d7b304db79b5cc8c54249"), "message" : 11 }

Time-To-Live Indexes

Time-To-Live index need config in Date data type.

After “expireAfterSeconds”, the data will be deleted

// create index for Date type with “expireAfterSeconds” attributes
> db.users.createIndex({"date":1},{"expireAfterSeconds":5})
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 1,
    "numIndexesAfter" : 2,
    "ok" : 1
}

// update users with date
> db.users.updateOne({"name":"user164"},{"$set":{"date":new Date()}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

// after 5 seconds, the records will be deleted

MongoDB Index Administration

Index information is stored in system.indexes
Commands: createIndex, createIndexes, dropIndex, dropIndexes
db.collectionName.getIndexes()

> db.users.getIndexes()
[ { "v" : 2, "key" : { "_id" : 1 }, "name" : "_id_" } ]

Key can be used for hinting

MongoDB unique index

Unique index

> db.users.createIndex({"name":1},{"unique":true})
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 4,
    "numIndexesAfter" : 5,
    "ok" : 1
}
> db.users.insertOne({"name":"user164"})
WriteError({
    "index" : 0,
    "code" : 11000,
    "errmsg" : "E11000 duplicate key error collection: test.users index: name_1 dup key: { name: \"user164\" }",
    "op" : {
        "_id" : ObjectId("5f7c208d02c41bd39db9af73"),
        "name" : "user164"
    }
}) :
WriteError({
    "index" : 0,
    "code" : 11000,
    "errmsg" : "E11000 duplicate key error collection: test.users index: name_1 dup key: { name: \"user164\" }",
    "op" : {
        "_id" : ObjectId("5f7c208d02c41bd39db9af73"),
        "name" : "user164"
    }
})
WriteError@src/mongo/shell/bulk_api.js:458:48
mergeBatchResults@src/mongo/shell/bulk_api.js:855:49
executeBatch@src/mongo/shell/bulk_api.js:919:13
Bulk/this.execute@src/mongo/shell/bulk_api.js:1163:21
DBCollection.prototype.insertOne@src/mongo/shell/crud_api.js:264:9
@(shell):1:1
>

partialFilterExpression

MongoDB treat null as the same value in index, can use partialFilterExpression to filter only specific field exist

// re-create unique index with partialFilterExpression
> db.users.dropIndex({"name":1})
{ "nIndexesWas" : 5, "ok" : 1 }
> db.users.createIndex({"name":1},{"unique":true,"partialFilterExpression":{"comments":{$exists: true}}})
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 4,
    "numIndexesAfter" : 5,
    "ok" : 1
}

// insert duplicated name will success
> db.users.insertOne({"name":"user164"})

{
    "acknowledged" : true,
    "insertedId" : ObjectId("5f7c234602c41bd39db9af76")
}
> db.users.insertOne({"name":"user164"})
{
    "acknowledged" : true,
    "insertedId" : ObjectId("5f7c234c02c41bd39db9af77")
}

// however, if insert user with comments field, unique index works
> db.users.insertOne({"name":"user164","comments":"abc"})
{
    "acknowledged" : true,
    "insertedId" : ObjectId("5f7c23f702c41bd39db9af78")
}
> db.users.insertOne({"name":"user164","comments":"abcdef"})
WriteError({
    "index" : 0,
    "code" : 11000,
    "errmsg" : "E11000 duplicate key error collection: test.users index: name_1 dup key: { name: \"user164\" }",
    "op" : {
        "_id" : ObjectId("5f7c23fa02c41bd39db9af79"),
        "name" : "user164",
        "comments" : "abcdef"
    }
}) :
WriteError({
    "index" : 0,
    "code" : 11000,
    "errmsg" : "E11000 duplicate key error collection: test.users index: name_1 dup key: { name: \"user164\" }",
    "op" : {
        "_id" : ObjectId("5f7c23fa02c41bd39db9af79"),
        "name" : "user164",
        "comments" : "abcdef"
    }
})
WriteError@src/mongo/shell/bulk_api.js:458:48
mergeBatchResults@src/mongo/shell/bulk_api.js:855:49
executeBatch@src/mongo/shell/bulk_api.js:919:13
Bulk/this.execute@src/mongo/shell/bulk_api.js:1163:21
DBCollection.prototype.insertOne@src/mongo/shell/crud_api.js:264:9
@(shell):1:1
>

Compound unique indexes

// create compound unique index
> db.tickets.createIndex({"name":1,"location":1},{"unique":true})
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 1,
    "numIndexesAfter" : 2,
    "ok" : 1
}

// insert the first record
> db.tickets.insertOne({"name":"T1","location":"Taipei"})
{
    "acknowledged" : true,
    "insertedId" : ObjectId("5f7c408f4db79b5cc8c5423d")
}

// insert the second record fail
> db.tickets.insertOne({"name":"T1","location":"Taipei"})
WriteError({
    "index" : 0,
    "code" : 11000,
    "errmsg" : "E11000 duplicate key error collection: test.tickets index: name_1_location_1 dup key: { name: \"T1\", location: \"Taipei\" }",
    "op" : {
        "_id" : ObjectId("5f7c40904db79b5cc8c5423e"),
        "name" : "T1",
        "location" : "Taipei"
    }
}) :
WriteError({
    "index" : 0,
    "code" : 11000,
    "errmsg" : "E11000 duplicate key error collection: test.tickets index: name_1_location_1 dup key: { name: \"T1\", location: \"Taipei\" }",
    "op" : {
        "_id" : ObjectId("5f7c40904db79b5cc8c5423e"),
        "name" : "T1",
        "location" : "Taipei"
    }
})
WriteError@src/mongo/shell/bulk_api.js:458:48
mergeBatchResults@src/mongo/shell/bulk_api.js:855:49
executeBatch@src/mongo/shell/bulk_api.js:919:13
Bulk/this.execute@src/mongo/shell/bulk_api.js:1163:21
DBCollection.prototype.insertOne@src/mongo/shell/crud_api.js:264:9
@(shell):1:1
>

MongoDB Compound Index

At first, schema in mongodb is as follows

> db.users.find()
{ "_id" : ObjectId("5f6f162c432d41b06263640d"), "name" : "user12", "i" : 12, "age" : 27, "createdAt" : ISODate("2020-09-26T10:21:32.660Z") }
{ "_id" : ObjectId("5f6f162c432d41b06263640e"), "name" : "user13", "i" : 13, "age" : 67, "createdAt" : ISODate("2020-09-26T10:21:32.661Z") }

Check execution status.

In this case, the query is scaning colleciton.

We insert million of user records, so mongodb need examine million documents to find matched ones.

> db.users.find().explain("executionStats")
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.users",
                "indexFilterSet" : false,
                "parsedQuery" : {


                },
                "winningPlan" : {
                        // this query scan collection!!
                        "stage" : "COLLSCAN",
                        "direction" : "forward"
                },
                "rejectedPlans" : [ ]
        },
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 1380558,
                "executionTimeMillis" : 433,
                "totalKeysExamined" : 0,
                // all documents need to be examined
                "totalDocsExamined" : 1380558,
                "executionStages" : {
                        "stage" : "COLLSCAN",
                        "nReturned" : 1380558,
                        "executionTimeMillisEstimate" : 27,
                        "works" : 1380560,
                        "advanced" : 1380558,
                        "needTime" : 1,
                        "needYield" : 0,
                        "saveState" : 1380,
                        "restoreState" : 1380,
                        "isEOF" : 1,
                        "direction" : "forward",
                        "docsExamined" : 1380558
                }
        },
        "serverInfo" : {
                "host" : "c38be4173edc",
                "port" : 27017,
                "version" : "4.4.1",
                "gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
        },
        "ok" : 1
}

Find user by name, it's still a collection scan query, performance is still bad

> db.users.find({"name":"user164"})
{ "_id" : ObjectId("5f6f162c432d41b0626364a5"), "name" : "user164", "i" : 164, "age" : 74, "createdAt" : ISODate("2020-09-26T10:21:32.743Z") }
{ "_id" : ObjectId("5f6f16f640ac430dab030a88"), "name" : "user164", "i" : 164, "age" : 92, "createdAt" : ISODate("2020-09-26T10:24:54.869Z") }
{ "_id" : ObjectId("5f6f178608c93b291de083db"), "name" : "user164", "i" : 164, "age" : 29, "createdAt" : ISODate("2020-09-26T10:27:18.322Z") }

> db.users.find({"name":"user164"}).explain("executionStats")
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.users",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "name" : {
                                "$eq" : "user164"
                        }
                },
                "winningPlan" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                                "name" : {
                                        "$eq" : "user164"
                                }
                        },
                        "direction" : "forward"
                },
                "rejectedPlans" : [ ]
        },
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 3,
                "executionTimeMillis" : 685,
                "totalKeysExamined" : 0,
                // without index, find by name still need examined all documents
                "totalDocsExamined" : 1380558,
                "executionStages" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                                "name" : {
                                        "$eq" : "user164"
                                }
                        },
                        "nReturned" : 3,
                        "executionTimeMillisEstimate" : 68,
                        "works" : 1380560,
                        "advanced" : 3,
                        "needTime" : 1380556,
                        "needYield" : 0,
                        "saveState" : 1380,
                        "restoreState" : 1380,
                        "isEOF" : 1,
                        "direction" : "forward",
                        "docsExamined" : 1380558
                }
        },
        "serverInfo" : {
                "host" : "c38be4173edc",
                "port" : 27017,
                "version" : "4.4.1",
                "gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
        },
        "ok" : 1
}

create index for name

> db.users.createIndex({"name":1})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}

> db.users.find({"name":"user164"}).explain("executionStats")
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.users",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "name" : {
                                "$eq" : "user164"
                        }
                },
                "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "name" : 1
                                },
                                "indexName" : "name_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                        "name" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "name" : [
                                                "[\"user164\", \"user164\"]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 3,
                "executionTimeMillis" : 1,
                "totalKeysExamined" : 3,
                "totalDocsExamined" : 3,
                "executionStages" : {
                        "stage" : "FETCH",
                        "nReturned" : 3,
                        "executionTimeMillisEstimate" : 0,
                        "works" : 4,
                        "advanced" : 3,
                        "needTime" : 0,
                        "needYield" : 0,
                        "saveState" : 0,
                        "restoreState" : 0,
                        "isEOF" : 1,
                        "docsExamined" : 3,
                        "alreadyHasObj" : 0,
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "nReturned" : 3,
                                "executionTimeMillisEstimate" : 0,
                                "works" : 4,
                                "advanced" : 3,
                                "needTime" : 0,
                                "needYield" : 0,
                                "saveState" : 0,
                                "restoreState" : 0,
                                "isEOF" : 1,
                                "keyPattern" : {
                                        "name" : 1
                                },
                                "indexName" : "name_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                        "name" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "name" : [
                                                "[\"user164\", \"user164\"]"
                                        ]
                                },
                                "keysExamined" : 3,
                                "seeks" : 1,
                                "dupsTested" : 0,
                                "dupsDropped" : 0
                        }
                }
        },
        "serverInfo" : {
                "host" : "c38be4173edc",
                "port" : 27017,
                "version" : "4.4.1",
                "gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
        },
        "ok" : 1
} 

Sort by age after find by name.

Can see stage: SORT, it means mongodb need sort in memory, which may occupy much memory

> db.users.find({"name":"user164"}).sort({"age":1}).explain("executionStats")
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.users",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "name" : {
                "$eq" : "user164"
            }
        },
        "winningPlan" : {
            "stage" : "SORT",
            "sortPattern" : {
                "age" : 1
            },
            "memLimit" : 104857600,
            "type" : "simple",
            "inputStage" : {
                "stage" : "FETCH",
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "name" : 1
                    },
                    "indexName" : "name_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "name" : [ ]
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "name" : [
                            "[\"user164\", \"user164\"]"
                        ]
                    }
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 3,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 3,
        "totalDocsExamined" : 3,
        "executionStages" : {
            "stage" : "SORT",
            "nReturned" : 3,
            "executionTimeMillisEstimate" : 0,
            "works" : 8,
            "advanced" : 3,
            "needTime" : 4,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "sortPattern" : {
                "age" : 1
            },
            "memLimit" : 104857600,
            "type" : "simple",
            "totalDataSizeSorted" : 297,
            "usedDisk" : false,
            "inputStage" : {
                "stage" : "FETCH",
                "nReturned" : 3,
                "executionTimeMillisEstimate" : 0,
                "works" : 4,
                "advanced" : 3,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "docsExamined" : 3,
                "alreadyHasObj" : 0,
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "nReturned" : 3,
                    "executionTimeMillisEstimate" : 0,
                    "works" : 4,
                    "advanced" : 3,
                    "needTime" : 0,
                    "needYield" : 0,
                    "saveState" : 0,
                    "restoreState" : 0,
                    "isEOF" : 1,
                    "keyPattern" : {
                        "name" : 1
                    },
                    "indexName" : "name_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "name" : [ ]
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "name" : [
                            "[\"user164\", \"user164\"]"
                        ]
                    },
                    "keysExamined" : 3,
                    "seeks" : 1,
                    "dupsTested" : 0,
                    "dupsDropped" : 0
                }
            }
        }
    },
    "serverInfo" : {
        "host" : "c38be4173edc",
        "port" : 27017,
        "version" : "4.4.1",
        "gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
    },
    "ok" : 1
}

To avoid in-memory sort, need create index for “name” and “age".

> db.users.createIndex({"name":1,"age":1})
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 2,
    "numIndexesAfter" : 3,
    "ok" : 1
}

> db.users.find({"name":"user164"}).sort({"age":1}).explain("executionStats")
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.users",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "name" : {
                "$eq" : "user164"
            }
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "name" : 1,
                    "age" : 1
                },
                "indexName" : "name_1_age_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "name" : [ ],
                    "age" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "name" : [
                        "[\"user164\", \"user164\"]"
                    ],
                    "age" : [
                        "[MinKey, MaxKey]"
                    ]
                }
            }
        },
        "rejectedPlans" : [
            {
                "stage" : "SORT",
                "sortPattern" : {
                    "age" : 1
                },
                "memLimit" : 104857600,
                "type" : "simple",
                "inputStage" : {
                    "stage" : "FETCH",
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "name" : 1
                        },
                        "indexName" : "name_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "name" : [ ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "name" : [
                                "[\"user164\", \"user164\"]"
                            ]
                        }
                    }
                }
            }
        ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 3,
        "executionTimeMillis" : 1,
        "totalKeysExamined" : 3,
        "totalDocsExamined" : 3,
        "executionStages" : {
            "stage" : "FETCH",
            "nReturned" : 3,
            "executionTimeMillisEstimate" : 0,
            "works" : 5,
            "advanced" : 3,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "docsExamined" : 3,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 3,
                "executionTimeMillisEstimate" : 0,
                "works" : 4,
                "advanced" : 3,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "keyPattern" : {
                    "name" : 1,
                    "age" : 1
                },
                "indexName" : "name_1_age_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "name" : [ ],
                    "age" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "name" : [
                        "[\"user164\", \"user164\"]"
                    ],
                    "age" : [
                        "[MinKey, MaxKey]"
                    ]
                },
                "keysExamined" : 3,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "c38be4173edc",
        "port" : 27017,
        "version" : "4.4.1",
        "gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
    },
    "ok" : 1
}

Sort by age

> db.users.find({"age":{"$gte":10,"$lte":20}}).sort({"age":1}).explain("executionStats")
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.users",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "age" : {
                        "$lte" : 20
                    }
                },
                {
                    "age" : {
                        "$gte" : 10
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "SORT",
            "sortPattern" : {
                "age" : 1
            },
            "memLimit" : 104857600,
            "type" : "simple",
            "inputStage" : {
                "stage" : "COLLSCAN",
                "filter" : {
                    "$and" : [
                        {
                            "age" : {
                                "$lte" : 20
                            }
                        },
                        {
                            "age" : {
                                "$gte" : 10
                            }
                        }
                    ]
                },
                "direction" : "forward"
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 126350,
        "executionTimeMillis" : 1020,
        "totalKeysExamined" : 0,
        "totalDocsExamined" : 1380558,
        "executionStages" : {
            "stage" : "SORT",
            "nReturned" : 126350,
            "executionTimeMillisEstimate" : 146,
            "works" : 1506911,
            "advanced" : 126350,
            "needTime" : 1380560,
            "needYield" : 0,
            "saveState" : 1507,
            "restoreState" : 1507,
            "isEOF" : 1,
            "sortPattern" : {
                "age" : 1
            },
            "memLimit" : 104857600,
            "type" : "simple",
            "totalDataSizeSorted" : 12857285,
            "usedDisk" : false,
            "inputStage" : {
                "stage" : "COLLSCAN",
                "filter" : {
                    "$and" : [
                        {
                            "age" : {
                                "$lte" : 20
                            }
                        },
                        {
                            "age" : {
                                "$gte" : 10
                            }
                        }
                    ]
                },
                "nReturned" : 126350,
                "executionTimeMillisEstimate" : 90,
                "works" : 1380560,
                "advanced" : 126350,
                "needTime" : 1254209,
                "needYield" : 0,
                "saveState" : 1507,
                "restoreState" : 1507,
                "isEOF" : 1,
                "direction" : "forward",
                "docsExamined" : 1380558
            }
        }
    },
    "serverInfo" : {
        "host" : "c38be4173edc",
        "port" : 27017,
        "version" : "4.4.1",
        "gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
    },
    "ok" : 1
}

Create index for age and sort by age

> db.users.createIndex({"age":1})
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 3,
    "numIndexesAfter" : 4,
    "ok" : 1
}

> db.users.find({"age":{"$gte":10,"$lte":20}}).sort({"age":1}).explain("executionStats")
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.users",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "age" : {
                        "$lte" : 20
                    }
                },
                {
                    "age" : {
                        "$gte" : 10
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "age" : 1
                },
                "indexName" : "age_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "age" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "age" : [
                        "[10.0, 20.0]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 126350,
        "executionTimeMillis" : 243,
        "totalKeysExamined" : 126350,
        "totalDocsExamined" : 126350,
        "executionStages" : {
            "stage" : "FETCH",
            "nReturned" : 126350,
            "executionTimeMillisEstimate" : 35,
            "works" : 126351,
            "advanced" : 126350,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 126,
            "restoreState" : 126,
            "isEOF" : 1,
            "docsExamined" : 126350,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 126350,
                "executionTimeMillisEstimate" : 13,
                "works" : 126351,
                "advanced" : 126350,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 126,
                "restoreState" : 126,
                "isEOF" : 1,
                "keyPattern" : {
                    "age" : 1
                },
                "indexName" : "age_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "age" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "age" : [
                        "[10.0, 20.0]"
                    ]
                },
                "keysExamined" : 126350,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "c38be4173edc",
        "port" : 27017,
        "version" : "4.4.1",
        "gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
    },
    "ok" : 1
}

Sort by age and I, MongoDB need sort in memory because no index for both age and I

> db.users.find({"age":{"$gte":10,"$lte":20}}).sort({"age":1,"i":-1}).explain("executionStats")
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.users",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "age" : {
                        "$lte" : 20
                    }
                },
                {
                    "age" : {
                        "$gte" : 10
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "SORT",
            "sortPattern" : {
                "age" : 1,
                "i" : -1
            },
            "memLimit" : 104857600,
            "type" : "simple",
            "inputStage" : {
                "stage" : "FETCH",
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "age" : 1
                    },
                    "indexName" : "age_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "age" : [ ]
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "age" : [
                            "[10.0, 20.0]"
                        ]
                    }
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 126350,
        "executionTimeMillis" : 491,
        "totalKeysExamined" : 126350,
        "totalDocsExamined" : 126350,
        "executionStages" : {
            "stage" : "SORT",
            "nReturned" : 126350,
            "executionTimeMillisEstimate" : 167,
            "works" : 252702,
            "advanced" : 126350,
            "needTime" : 126351,
            "needYield" : 0,
            "saveState" : 253,
            "restoreState" : 253,
            "isEOF" : 1,
            "sortPattern" : {
                "age" : 1,
                "i" : -1
            },
            "memLimit" : 104857600,
            "type" : "simple",
            "totalDataSizeSorted" : 12857285,
            "usedDisk" : false,
            "inputStage" : {
                "stage" : "FETCH",
                "nReturned" : 126350,
                "executionTimeMillisEstimate" : 63,
                "works" : 126351,
                "advanced" : 126350,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 253,
                "restoreState" : 253,
                "isEOF" : 1,
                "docsExamined" : 126350,
                "alreadyHasObj" : 0,
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "nReturned" : 126350,
                    "executionTimeMillisEstimate" : 29,
                    "works" : 126351,
                    "advanced" : 126350,
                    "needTime" : 0,
                    "needYield" : 0,
                    "saveState" : 253,
                    "restoreState" : 253,
                    "isEOF" : 1,
                    "keyPattern" : {
                        "age" : 1
                    },
                    "indexName" : "age_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "age" : [ ]
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "age" : [
                            "[10.0, 20.0]"
                        ]
                    },
                    "keysExamined" : 126350,
                    "seeks" : 1,
                    "dupsTested" : 0,
                    "dupsDropped" : 0
                }
            }
        }
    },
    "serverInfo" : {
        "host" : "c38be4173edc",
        "port" : 27017,
        "version" : "4.4.1",
        "gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
    },
    "ok" : 1
}

Create index for age and I then sort again

> db.users.createIndex({"age":1,"i":1})
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 4,
    "numIndexesAfter" : 5,
    "ok" : 1
}
> db.users.find({"age":{"$gte":10,"$lte":20}}).sort({"age":1,"i":-1}).explain("executionStats")
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.users",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "age" : {
                        "$lte" : 20
                    }
                },
                {
                    "age" : {
                        "$gte" : 10
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "SORT",
            "sortPattern" : {
                "age" : 1,
                "i" : -1
            },
            "memLimit" : 104857600,
            "type" : "simple",
            "inputStage" : {
                "stage" : "FETCH",
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "age" : 1
                    },
                    "indexName" : "age_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "age" : [ ]
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "age" : [
                            "[10.0, 20.0]"
                        ]
                    }
                }
            }
        },
        "rejectedPlans" : [
            {
                "stage" : "FETCH",
                "inputStage" : {
                    "stage" : "SORT",
                    "sortPattern" : {
                        "age" : 1,
                        "i" : -1
                    },
                    "memLimit" : 104857600,
                    "type" : "default",
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "age" : 1,
                            "i" : 1
                        },
                        "indexName" : "age_1_i_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "age" : [ ],
                            "i" : [ ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "age" : [
                                "[10.0, 20.0]"
                            ],
                            "i" : [
                                "[MinKey, MaxKey]"
                            ]
                        }
                    }
                }
            }
        ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 126350,
        "executionTimeMillis" : 939,
        "totalKeysExamined" : 126350,
        "totalDocsExamined" : 126350,
        "executionStages" : {
            "stage" : "SORT",
            "nReturned" : 126350,
            "executionTimeMillisEstimate" : 188,
            "works" : 252702,
            "advanced" : 126350,
            "needTime" : 126351,
            "needYield" : 0,
            "saveState" : 380,
            "restoreState" : 380,
            "isEOF" : 1,
            "sortPattern" : {
                "age" : 1,
                "i" : -1
            },
            "memLimit" : 104857600,
            "type" : "simple",
            "totalDataSizeSorted" : 12857285,
            "usedDisk" : false,
            "inputStage" : {
                "stage" : "FETCH",
                "nReturned" : 126350,
                "executionTimeMillisEstimate" : 47,
                "works" : 126351,
                "advanced" : 126350,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 380,
                "restoreState" : 380,
                "isEOF" : 1,
                "docsExamined" : 126350,
                "alreadyHasObj" : 0,
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "nReturned" : 126350,
                    "executionTimeMillisEstimate" : 26,
                    "works" : 126351,
                    "advanced" : 126350,
                    "needTime" : 0,
                    "needYield" : 0,
                    "saveState" : 380,
                    "restoreState" : 380,
                    "isEOF" : 1,
                    "keyPattern" : {
                        "age" : 1
                    },
                    "indexName" : "age_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "age" : [ ]
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "age" : [
                            "[10.0, 20.0]"
                        ]
                    },
                    "keysExamined" : 126350,
                    "seeks" : 1,
                    "dupsTested" : 0,
                    "dupsDropped" : 0
                }
            }
        }
    },
    "serverInfo" : {
        "host" : "c38be4173edc",
        "port" : 27017,
        "version" : "4.4.1",
        "gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
    },
    "ok" : 1
}

However, sort by I and age will need sort in memory again.

Need create another index for I and age.

> db.users.find({"age":{"$gte":10,"$lte":20}}).sort({"i":-1,"age":1}).explain("executionStats")
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.users",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "age" : {
                        "$lte" : 20
                    }
                },
                {
                    "age" : {
                        "$gte" : 10
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "SORT",
            "sortPattern" : {
                "i" : -1,
                "age" : 1
            },
            "memLimit" : 104857600,
            "type" : "simple",
            "inputStage" : {
                "stage" : "FETCH",
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "age" : 1
                    },
                    "indexName" : "age_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "age" : [ ]
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "age" : [
                            "[10.0, 20.0]"
                        ]
                    }
                }
            }
        },
        "rejectedPlans" : [
            {
                "stage" : "FETCH",
                "inputStage" : {
                    "stage" : "SORT",
                    "sortPattern" : {
                        "i" : -1,
                        "age" : 1
                    },
                    "memLimit" : 104857600,
                    "type" : "default",
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "age" : 1,
                            "i" : 1
                        },
                        "indexName" : "age_1_i_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "age" : [ ],
                            "i" : [ ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "age" : [
                                "[10.0, 20.0]"
                            ],
                            "i" : [
                                "[MinKey, MaxKey]"
                            ]
                        }
                    }
                }
            }
        ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 126350,
        "executionTimeMillis" : 976,
        "totalKeysExamined" : 126350,
        "totalDocsExamined" : 126350,
        "executionStages" : {
            "stage" : "SORT",
            "nReturned" : 126350,
            "executionTimeMillisEstimate" : 225,
            "works" : 252702,
            "advanced" : 126350,
            "needTime" : 126351,
            "needYield" : 0,
            "saveState" : 380,
            "restoreState" : 380,
            "isEOF" : 1,
            "sortPattern" : {
                "i" : -1,
                "age" : 1
            },
            "memLimit" : 104857600,
            "type" : "simple",
            "totalDataSizeSorted" : 12857285,
            "usedDisk" : false,
            "inputStage" : {
                "stage" : "FETCH",
                "nReturned" : 126350,
                "executionTimeMillisEstimate" : 68,
                "works" : 126351,
                "advanced" : 126350,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 380,
                "restoreState" : 380,
                "isEOF" : 1,
                "docsExamined" : 126350,
                "alreadyHasObj" : 0,
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "nReturned" : 126350,
                    "executionTimeMillisEstimate" : 18,
                    "works" : 126351,
                    "advanced" : 126350,
                    "needTime" : 0,
                    "needYield" : 0,
                    "saveState" : 380,
                    "restoreState" : 380,
                    "isEOF" : 1,
                    "keyPattern" : {
                        "age" : 1
                    },
                    "indexName" : "age_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "age" : [ ]
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "age" : [
                            "[10.0, 20.0]"
                        ]
                    },
                    "keysExamined" : 126350,
                    "seeks" : 1,
                    "dupsTested" : 0,
                    "dupsDropped" : 0
                }
            }
        }
    },
    "serverInfo" : {
        "host" : "c38be4173edc",
        "port" : 27017,
        "version" : "4.4.1",
        "gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
    },
    "ok" : 1
}

Three kinds of filters:

equality filter (ex: “name” = “abc”)
multivalue filter (ex. “grade”>5000)
sort component of common query pattern (ex. sort({“age”}))

Tricks:

Keys for equality filters should appear first.
Keys used for sorting should appear before multivalue fields.
Keys for multivalue filters should appear last. (ex. grade > 5000)

Covered query

Means query only look for fields that are included in the index.

Need avoid query _id field unless it’s part of index.

totalDocsExamined will be 0

> db.users.find({"name":"user16"},{"_id":0,"name":1}).explain("executionStats")
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.users",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "name" : {
                "$eq" : "user16"
            }
        },
        "winningPlan" : {
            "stage" : "PROJECTION_COVERED",
            "transformBy" : {
                "_id" : 0,
                "name" : 1
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "name" : 1
                },
                "indexName" : "name_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "name" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "name" : [
                        "[\"user16\", \"user16\"]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 1,
        "totalDocsExamined" : 0,
        "executionStages" : {
            "stage" : "PROJECTION_COVERED",
            "nReturned" : 1,
            "executionTimeMillisEstimate" : 0,
            "works" : 2,
            "advanced" : 1,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "transformBy" : {
                "_id" : 0,
                "name" : 1
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 1,
                "executionTimeMillisEstimate" : 0,
                "works" : 2,
                "advanced" : 1,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "keyPattern" : {
                    "name" : 1
                },
                "indexName" : "name_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "name" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "name" : [
                        "[\"user16\", \"user16\"]"
                    ]
                },
                "keysExamined" : 1,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "12c0a2efb72c",
        "port" : 27017,
        "version" : "4.4.1",
        "gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
    },
    "ok" : 1
}

Implicit Indexes

An index of {“a”:1,”b”:1}

A query of {“a”:”avalue”} can use this index as well because of prefix is covered.

// Without name index, MongoDB need examine all documents
> db.users.find({"name":"user16"}).explain("executionStats")
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.users",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "name" : {
                "$eq" : "user16"
            }
        },
        "winningPlan" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "name" : {
                    "$eq" : "user16"
                }
            },
            "direction" : "forward"
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 34,
        "totalKeysExamined" : 0,
        "totalDocsExamined" : 100003,
        "executionStages" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "name" : {
                    "$eq" : "user16"
                }
            },
            "nReturned" : 1,
            "executionTimeMillisEstimate" : 2,
            "works" : 100005,
            "advanced" : 1,
            "needTime" : 100003,
            "needYield" : 0,
            "saveState" : 100,
            "restoreState" : 100,
            "isEOF" : 1,
            "direction" : "forward",
            "docsExamined" : 100003
        }
    },
    "serverInfo" : {
        "host" : "12c0a2efb72c",
        "port" : 27017,
        "version" : "4.4.1",
        "gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
    },
    "ok" : 1
}

// create index for name and age, then query by name, MongoDB also choose name and age as the index
> db.users.createIndex({"name":1,"age":1})
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 2,
    "numIndexesAfter" : 3,
    "ok" : 1
}
> db.users.find({"name":"user16"}).explain("executionStats")
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.users",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "name" : {
                "$eq" : "user16"
            }
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "name" : 1,
                    "age" : 1
                },
                "indexName" : "name_1_age_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "name" : [ ],
                    "age" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "name" : [
                        "[\"user16\", \"user16\"]"
                    ],
                    "age" : [
                        "[MinKey, MaxKey]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 1,
        "totalKeysExamined" : 1,
        "totalDocsExamined" : 1,
        "executionStages" : {
            "stage" : "FETCH",
            "nReturned" : 1,
            "executionTimeMillisEstimate" : 0,
            "works" : 2,
            "advanced" : 1,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "docsExamined" : 1,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 1,
                "executionTimeMillisEstimate" : 0,
                "works" : 2,
                "advanced" : 1,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "keyPattern" : {
                    "name" : 1,
                    "age" : 1
                },
                "indexName" : "name_1_age_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "name" : [ ],
                    "age" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "name" : [
                        "[\"user16\", \"user16\"]"
                    ],
                    "age" : [
                        "[MinKey, MaxKey]"
                    ]
                },
                "keysExamined" : 1,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "12c0a2efb72c",
        "port" : 27017,
        "version" : "4.4.1",
        "gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
    },
    "ok" : 1
}

// however, it won’t benefit query by age
> db.users.find({"age":16}).explain("executionStats")
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.users",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "age" : {
                "$eq" : 16
            }
        },
        "winningPlan" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "age" : {
                    "$eq" : 16
                }
            },
            "direction" : "forward"
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 826,
        "executionTimeMillis" : 37,
        "totalKeysExamined" : 0,
        "totalDocsExamined" : 100003,
        "executionStages" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "age" : {
                    "$eq" : 16
                }
            },
            "nReturned" : 826,
            "executionTimeMillisEstimate" : 2,
            "works" : 100005,
            "advanced" : 826,
            "needTime" : 99178,
            "needYield" : 0,
            "saveState" : 100,
            "restoreState" : 100,
            "isEOF" : 1,
            "direction" : "forward",
            "docsExamined" : 100003
        }
    },
    "serverInfo" : {
        "host" : "12c0a2efb72c",
        "port" : 27017,
        "version" : "4.4.1",
        "gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
    },
    "ok" : 1
}

Index embedded docs

Data

{ "username" : "sid", "loc" : { "ip" : "1.2.3.4", "city" : "Springfield", "state" : "NY" } }

Create index

> db..createIndex({"loc.city" : 1})

db.users.find({"loc" : {"ip" : "123.456.789.000", "city" : "Shelbyville", "state" : "NY"}}})).

Can not

db.users.find({"loc.city" : "Shelbyville"}).

// prepare data
> db.users.updateOne({"name":"user16"},{"$set":{"comments":{"text":"aaaa","date":new Date()}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.updateOne({"name":"user14"},{"$set":{"comments":{"text":"aaaa","date":new Date()}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.updateOne({"name":"user15"},{"$set":{"comments":{"text":"aaaa","date":new Date()}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

// create index for comments.text
> db.users.createIndex({"comments.text":1})
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 3,
    "numIndexesAfter" : 4,
    "ok" : 1
}

// query by comments.text
> db.users.find({"comments.text":"aaaa"})
{ "_id" : ObjectId("5f73048f0895feaa44434196"), "i" : 14, "name" : "user14", "age" : 27, "created" : ISODate("2020-09-29T09:55:27.798Z"), "comments" : { "text" : "aaaa", "date" : ISODate("2020-10-06T06:37:36.023Z") } }
{ "_id" : ObjectId("5f73048f0895feaa44434197"), "i" : 15, "name" : "user15", "age" : 4, "created" : ISODate("2020-09-29T09:55:27.799Z"), "comments" : { "text" : "aaaa", "date" : ISODate("2020-10-06T06:37:43.407Z") } }
{ "_id" : ObjectId("5f73048f0895feaa44434198"), "i" : 16, "name" : "user16", "age" : 77, "created" : ISODate("2020-09-29T09:55:27.799Z"), "comments" : { "text" : "aaaa", "date" : ISODate("2020-10-06T06:37:18.400Z") } }
>

訂閱：文章 (Atom)