CODEWITHSUNDEEP
mongodb
user1578872
user1578872

Reputation: 9088

Mongo indexes on Arrays

I have a Mongo document as below. Here, projects will be like the first project will be on array index 0, second project on array index 1, third project on array index 2,....

{
  "_id": "234463456453643563456",
  "name": "Mike",
  "empId": "10",
  "managerId": "8",
  "projects" : [ "123", "456", "789"]
}

I have index on projects field and I would like to search employees whose first project is 123, second project is 456, ...

I have index as follows,

createIndex({"projects":1});

When I search like the below, it takes time.

{ "$or" : [ { "projects.2" : "123"} , { "projects.4" : "456"} , { "projects.3" : "789"} , { "projects.1" : "5467"} , { "projects.0" : "9768"}]}

But, the same query works very well after creating indexes like, it is really fast.

createIndex({"projects.0":1});
createIndex({"projects.1":1});
createIndex({"projects.2":1});
createIndex({"projects.3":1});
createIndex({"projects.4":1});
createIndex({"projects.5":1});

It saves 3 secs for each query after creating the index.

How does it work internally? Why is there this much difference?

Upvotes: 0

Views: 300

Answers (1)

Akrion
Akrion

Reputation: 18525

It really is easy to see how it works if you use the explain command. Here is a quick summary:

My test collection:

{
    "_id" : ObjectId("5b7fa665844b5ebfad064b1c"),
    "arr" : [ "123", "456", "789"]
},
{
    "_id" : ObjectId("5b80f96e9fceda195ba853af"),
    "arr" : [ "ABC", "DEF", "FOO"]
}

My index: createIndex({"arr":1})

Now if I do a search as find({arr: '123'}).explain() we would see from the explain result:

...
"indexName" : "_arr_",
"isMultiKey" : true,
"multiKeyPaths" : {
    "arr" : [ 
        "arr"
    ]
},
...

That the correct index was used. Now what about find({arr.0: '123'}).explain()?

...
"winningPlan" : {
    "stage" : "COLLSCAN",
    "filter" : {
        "arr.0" : {
            "$eq" : "123"
        }
    },
    "direction" : "forward"
},
...

Well now we get a lovely full COLLSCAN as the "winning" plan :).

Next createIndex({"arr.0":1}); and then find({arr.0: '123'}).explain() gives us:

...
"winningPlan" : {
    "stage" : "FETCH",
    "inputStage" : {
        "stage" : "IXSCAN",
        "keyPattern" : {
            "arr.0" : 1
        },
        "indexName" : "_arr.0_",
        "isMultiKey" : false,
        "multiKeyPaths" : {
            "arr.0" : []
        },
        "isUnique" : false,
        "isSparse" : false,
        "isPartial" : false,
        "indexVersion" : 2,
        "direction" : "forward",
        "indexBounds" : {
            "arr.0" : [ 
                "[\"123\", \"123\"]"
            ]
        }
    }
}
...

So as you can see the multiKey index on the arr is not enough if you are going to search by array index since:

MongoDB creates an index key for each element in the array. These multikey indexes support efficient queries against array fields

So this is exactly what happens when you do the initial index ... mongo finds the document which contains the 123 but in your case you want to get to the specific array index query for which you need a single index as shown by the explain.

Hope this helps you.

Upvotes: 1

Related Questions