Hinting an index

mongoDb hint() method is used to override MongoDB’s default index selection and query optimization process.

Let’s say, below students collection has indexes on “first_name”, “last_name” and “first_name”, “last_name”.

> db.students.getIndexes()
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "school.students"
        },
        {
                "v" : 1,
                "key" : {
                        "last_name" : 1
                },
                "name" : "last_name_1",
                "ns" : "school.students"
        },
        {
                "v" : 1,
                "key" : {
                        "first_name" : 1
                },
                "name" : "first_name_1",
                "ns" : "school.students"
        },
        {
                "v" : 1,
                "key" : {
                        "first_name" : 1,
                        "last_name" : 1
                },
                "name" : "first_name_1_last_name_1",
                "ns" : "school.students"
        }
]
>

>

>

> db.students.find({"first_name": "first_name1"}).explain()
{
        "cursor" : "BtreeCursor first_name_1",
        "isMultiKey" : false,
        "n" : 1,
        "nscannedObjects" : 1,
        "nscanned" : 1,
        "nscannedObjectsAllPlans" : 2,
        "nscannedAllPlans" : 2,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 1,
        "nChunkSkips" : 0,
        "millis" : 285,
        "indexBounds" : {
                "first_name" : [
                        [
                                "first_name1",
                                "first_name1"
                        ]
                ]
        },
        "server" : "RENT-MIS-LT3016:27017",
        "filterSet" : false
}

As you observe, mongoDB uses the index “first_name”. If you want to force mongoDB to use index on “last_name”, use hint command like below.

> db.students.find({"first_name": "first_name1"}).hint({"last_name":1}).explain()
{
        "cursor" : "BtreeCursor last_name_1",
        "isMultiKey" : false,
        "n" : 1,
        "nscannedObjects" : 10000000,
        "nscanned" : 10000000,
        "nscannedObjectsAllPlans" : 10000000,
        "nscannedAllPlans" : 10000000,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 78147,
        "nChunkSkips" : 0,
        "millis" : 32239,
        "indexBounds" : {
                "last_name" : [
                        [
                                {
                                        "$minElement" : 1
                                },
                                {
                                        "$maxElement" : 1
                                }
                        ]
                ]
        },
        "server" : "RENT-MIS-LT3016:27017",
        "filterSet" : false
}

You can observe above query takes more time than the original one, it is because we are searching on “first_name” using the index on “last_name”.

If you want to use default index, then use {natural:1} as hint parameter.

> db.students.find({"first_name": "first_name1"}).hint({$natural:1}).explain()
{
        "cursor" : "BasicCursor",
        "isMultiKey" : false,
        "n" : 1,
        "nscannedObjects" : 10000000,
        "nscanned" : 10000000,
        "nscannedObjectsAllPlans" : 10000000,
        "nscannedAllPlans" : 10000000,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 78125,
        "nChunkSkips" : 0,
        "millis" : 5591,
        "server" : "RENT-MIS-LT3016:27017",
        "filterSet" : false
}

Prevoius                                                 Next                                                 Home