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 }) |
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
} |
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
} |
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
} |
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 }) |
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
} |
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]"
}
}) |
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.
