Lets learn about multikey indexes and how they are efficient in MongoDB.
database name: daily
collections: gadgets, users
Insert documents into gadgets collection
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | > use daily
switched to db daily
> db.gadgets.insert({_id: 1, gadget: "Nexus 5"});
WriteResult({ "nInserted" : 1 })
> db.gadgets.insert({_id: 2, gadget: "iPhone"});
WriteResult({ "nInserted" : 1 })
> db.gadgets.insert({_id: 3, gadget: "iPad"});
WriteResult({ "nInserted" : 1 })
> db.gadgets.insert({_id: 4, gadget: "Nexus 7"});
WriteResult({ "nInserted" : 1 })
> db.gadgets.find()
{ "_id" : 1, "gadget" : "Nexus 5" }
{ "_id" : 2, "gadget" : "iPhone" }
{ "_id" : 3, "gadget" : "iPad" }
{ "_id" : 4, "gadget" : "Nexus 7" } |
Here we have 4 documents with “_id” as 1, 2, 3, 4.
Insert documents into users collection
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | > db.users.insert({name: "Satish",
products: [db.gadgets.find()[0]._id,
db.gadgets.find()[3]._id]});
WriteResult({ "nInserted" : 1 })
> db.users.insert({name: "Kiran",
products: [db.gadgets.find()[0]._id,
db.gadgets.find()[3]._id,
db.gadgets.find()[2]._id]});
WriteResult({ "nInserted" : 1 })
> db.users.insert({name: "Jyothi", products: [1, 2]});
WriteResult({ "nInserted" : 1 })
> db.users.find().pretty()
{
"_id" : ObjectId("53c7a30efd7d3c9597ca2593"),
"name" : "Satish",
"products" : [
1,
4
]
}
{
"_id" : ObjectId("53c7a337fd7d3c9597ca2594"),
"name" : "Kiran",
"products" : [
1,
4,
3
]
}
{
"_id" : ObjectId("53c7a34efd7d3c9597ca2595"),
"name" : "Jyothi",
"products" : [
1,
2
]
} |
Here we insert documents into “users” collection and embed the “_id” value of “gadgets” collection as array elements of “products” key.
Multi-key Index: MongoDB
[youtube https://www.youtube.com/watch?v=9C1x-xrN_qQ]
Command to fetch documents with array element 1 and 4
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | > db.users.find({products: {$all: [1, 4]}}).pretty();
{
"_id" : ObjectId("53c7a30efd7d3c9597ca2593"),
"name" : "Satish",
"products" : [
1,
4
]
}
{
"_id" : ObjectId("53c7a337fd7d3c9597ca2594"),
"name" : "Kiran",
"products" : [
1,
4,
3
]
} |
Both these documents have array values 1 and 4 in them.
Related Read: Comparison Operators: MongoDB
system.indexes content
1 2 3 4 5 6 7 8 | > show collections
gadgets
system.indexes
users
> db.system.indexes.find()
{ "v" : 1, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "daily.gadgets" }
{ "v" : 1, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "daily.users" } |
Here we have only 1 key and it’s on “_id”.
Basic Cursor
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | > db.users.find({products: {$all: [1, 4]}}).explain();
{
"cursor" : "BasicCursor",
"isMultiKey" : false,
"n" : 2,
"nscannedObjects" : 3,
"nscanned" : 3,
"nscannedObjectsAllPlans" : 3,
"nscannedAllPlans" : 3,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"server" : "Satish-PC:27017",
"filterSet" : false
} |
If we chain explain() method to our command, we can know some details about the command and also evaluate about its efficiency. The above command doesn’t have multi-key enabled and it’s a Basic Cursor.
Lets create index on products field
1 2 3 4 5 6 7 | > db.users.ensureIndex({"products": 1});
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
} |
This creates index on field “products”.
Now the system.indexes content
1 2 3 4 5 6 | > db.system.indexes.find()
{ "v" : 1, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "daily.gadgets" }
{ "v" : 1, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "daily.users" }
{ "v" : 1, "key" : { "products" : 1 },
"name" : "products_1",
"ns" : "daily.users" } |
So now we have 2 keys, “_id” and “products”.
BTree Cursor
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | > db.users.find({products: {$all: [1, 4]}}).explain();
{
"cursor" : "BtreeCursor products_1",
"isMultiKey" : true,
"n" : 2,
"nscannedObjects" : 3,
"nscanned" : 3,
"nscannedObjectsAllPlans" : 3,
"nscannedAllPlans" : 8,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"products" : [
[
1,
1
]
]
},
"server" : "Satish-PC:27017",
"filterSet" : false
} |
Now if we run the same command chained with explain() method, we could see that its a Btree Cursor and multi-key index is true.
Note: Btree Cursors are faster and efficient than Basic Cursors.
