mongodb - Subdocument index in mongo

Question

What exactly happens when I call ensureIndex(data) when typical data looks like data:{name: "A",age:"B", job : "C"} ? Will it create a compound index over these three fields or will it create only one index applicable when anything from data is requested or something altogether different ?

question from:https://stackoverflow.com/questions/16769705/subdocument-index-in-mongo

Answer 1

You can do either :

> db.collection.ensureIndex({"data.name": 1,"data.age":1, "data.job" : 1})
> db.collection.ensureIndex({"data": 1})

This is discussed in the documentation under indexes-on-embedded-fields and indexes on sub documents

The important section of the sub document section is 'When performing equality matches on subdocuments, field order matters and the subdocuments must match exactly.'

This means that the 2 indexes are the same for simple queries .

However, as the sub-document example shows, you can get some interesting results (that you might not expect) if you just index the whole sub-document as opposed to a specific field and then do a comparison operator (like $gte) - if you index a specific sub field you get a less flexible, but potentially more useful index.

It really all depends on your use case.

Anyway, once you have created the index you can check what's created with :

> db.collection.getIndexes()
[
{
    "v" : 1,
    "key" : {
        "_id" : 1
    },
    "ns" : "test.collection",
    "name" : "_id_"
},
{
    "v" : 1,
    "key" : {
        "data.name" : 1,
        "data.age" : 1,
        "data.job" : 1
    },
    "ns" : "test.collection",
    "name" : "data.name_1_data.age_1_data.job_1"
}

]

As you can see from the output it created a new key called data.name_1_data.age_1_data.job_1 (the _id_ index is always created).

If you want to test your new index then you can do :

> db.collection.insert({data:{name: "A",age:"B", job : "C"}})
> db.collection.insert({data:{name: "A1",age:"B", job : "C"}})
> db.collection.find({"data.name" : "A"}).explain()
{
    "cursor" : "BtreeCursor data.name_1_data.age_1_data.job_1",
     .... more stuff

The main thing is that you can see that your new index was used (BtreeCursor data.name_1_data.age_1_data.job_1 in the cursor field is what indicates this is the case). If you see "cursor" : "BasicCursor", then your index was not used.

For more detailed information look here.