Tag: embed

Multi-key Index: MongoDB

Lets learn about multikey indexes and how they are efficient in MongoDB.

multi key index 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]

YouTube Link: https://www.youtube.com/watch?v=9C1x-xrN_qQ [Watch the Video In Full Screen.]



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.