We have learnt the basics of multi-key indexes in MongoDB. Lets look at an example to demonstrate the multi-key indexing on arrays.
foo: database name
name: collection name
Insert a document
1
2
3
4
5
6
7
8
| MongoDB shell version: 2.6.1
connecting to: test
> use foo
switched to db foo
> db.name.insert({a: 1, b: 2, c: 3});
WriteResult({ "nInserted" : 1 }) |
MongoDB shell version: 2.6.1
connecting to: test
> use foo
switched to db foo
> db.name.insert({a: 1, b: 2, c: 3});
WriteResult({ "nInserted" : 1 })
Here we insert {a: 1, b: 2, c: 3} into “name” collection.
Multi-key Indexes and Arrays: MongoDB
[youtube https://www.youtube.com/watch?v=VGHSmjVmnzs]
Basic Cursor
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| > db.name.find({a: 1, b: 2})
{ "_id" : ObjectId("53d8982b79142c385cddc607"), "a" : 1, "b" : 2, "c" : 3 }
> db.name.find({a: 1, b: 2}).explain()
{
"cursor" : "BasicCursor",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"nscannedObjectsAllPlans" : 1,
"nscannedAllPlans" : 1,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"server" : "Satish-PC:27017",
"filterSet" : false
} |
> db.name.find({a: 1, b: 2})
{ "_id" : ObjectId("53d8982b79142c385cddc607"), "a" : 1, "b" : 2, "c" : 3 }
> db.name.find({a: 1, b: 2}).explain()
{ "cursor" : "BasicCursor", "isMultiKey" : false, "n" : 1, "nscannedObjects" : 1, "nscanned" : 1, "nscannedObjectsAllPlans" : 1, "nscannedAllPlans" : 1, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 0, "server" : "Satish-PC:27017", "filterSet" : false
}
We find() the document using fields “a” and “b” and the query/command returns a basic cursor, as we do not have indexing on them.
Related Read: index creation: MongoDB
Lets create index on a and b
1
2
3
4
5
6
7
| > db.name.ensureIndex({a: 1, b: 1});
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
} |
> db.name.ensureIndex({a: 1, b: 1});
{ "createdCollectionAutomatically" : false, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1
}
Previous there was only 1 index i.e., on “_id” Now there are 2 indexes – “_id” and “{a: 1, b: 1}”
Btree Cursor with multi-key as false
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
| > db.name.find({a: 1, b: 2}).explain()
{
"cursor" : "BtreeCursor a_1_b_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"nscannedObjectsAllPlans" : 1,
"nscannedAllPlans" : 1,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"a" : [
[
1,
1
]
],
"b" : [
[
2,
2
]
]
},
"server" : "Satish-PC:27017",
"filterSet" : false
} |
> db.name.find({a: 1, b: 2}).explain()
{ "cursor" : "BtreeCursor a_1_b_1", "isMultiKey" : false, "n" : 1, "nscannedObjects" : 1, "nscanned" : 1, "nscannedObjectsAllPlans" : 1, "nscannedAllPlans" : 1, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 0, "indexBounds" : { "a" : [ [ 1, 1 ] ], "b" : [ [ 2, 2 ] ] }, "server" : "Satish-PC:27017", "filterSet" : false
}
After creating the index on “a” and “b”, chain explain() method on the same command, and it shows you that, now it returns a Btree Cursor.
Lets insert another document
1
2
| > db.name.insert({a: [0, 1, 2], b: 2, c: 3});
WriteResult({ "nInserted" : 1 }) |
> db.name.insert({a: [0, 1, 2], b: 2, c: 3});
WriteResult({ "nInserted" : 1 })
Lets insert an array as value to field “a” and scalar values to “b” and “c”.
Btree Cursor with Multi-key true
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
| > db.name.find({a: 1, b: 2})
{ "_id" : ObjectId("53d8982b79142c385cddc607"),
"a" : 1, "b" : 2, "c" : 3 }
{ "_id" : ObjectId("53d8986f79142c385cddc608"),
"a" : [ 0, 1, 2 ], "b" : 2, "c": 3 }
> db.name.find({a: 1, b: 2}).explain()
{
"cursor" : "BtreeCursor a_1_b_1",
"isMultiKey" : true,
"n" : 2,
"nscannedObjects" : 2,
"nscanned" : 2,
"nscannedObjectsAllPlans" : 2,
"nscannedAllPlans" : 2,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"a" : [
[
1,
1
]
],
"b" : [
[
2,
2
]
]
},
"server" : "Satish-PC:27017",
"filterSet" : false
} |
> db.name.find({a: 1, b: 2})
{ "_id" : ObjectId("53d8982b79142c385cddc607"), "a" : 1, "b" : 2, "c" : 3 }
{ "_id" : ObjectId("53d8986f79142c385cddc608"), "a" : [ 0, 1, 2 ], "b" : 2, "c": 3 }
> db.name.find({a: 1, b: 2}).explain()
{ "cursor" : "BtreeCursor a_1_b_1", "isMultiKey" : true, "n" : 2, "nscannedObjects" : 2, "nscanned" : 2, "nscannedObjectsAllPlans" : 2, "nscannedAllPlans" : 2, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 0, "indexBounds" : { "a" : [ [ 1, 1 ] ], "b" : [ [ 2, 2 ] ] }, "server" : "Satish-PC:27017", "filterSet" : false
}
Now append explain() method to our command, it shows us that it returns a Btree Cursor and multi-key as true. MongoDB engine need to match every element of the array present in field “a” with the scalar value of field “b”. Hence it uses Multi-Key indexing.
Multi-Key Condition in MongoDB
1
2
3
4
5
6
7
8
9
| > db.name.insert({a: [0, 1, 2], b: [3, 4], c: 3});
WriteResult({
"nInserted" : 0,
"writeError" : {
"code" : 10088,
"errmsg" : "insertDocument :: caused by :: 10088 cannot
index parallel arrays [b] [a]"
}
}) |
> db.name.insert({a: [0, 1, 2], b: [3, 4], c: 3});
WriteResult({ "nInserted" : 0, "writeError" : { "code" : 10088, "errmsg" : "insertDocument :: caused by :: 10088 cannot index parallel arrays [b] [a]" }
})
It’s difficult to match every combination of the array elements present inside both “a” and “b” fields. If both keys/indexes has its value as an array, then it gets complicated. Thus, mongoDB doesn’t allow both keys to be arrays. Either one of them must be a scalar value.
