MongoDB M101N Final Exam Question 5

Question 5:

Suppose your have a collection fubar with the following indexes created:

[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "ns" : "test.fubar",
        "name" : "_id_"
    },
    {
        "v" : 1,
        "key" : {
            "a" : 1,
            "b" : 1
        },
        "ns" : "test.fubar",
        "name" : "a_1_b_1"
    },
    {
        "v" : 1,
        "key" : {
            "a" : 1,
            "c" : 1
        },
        "ns" : "test.fubar",
        "name" : "a_1_c_1"
    },
    {
        "v" : 1,
        "key" : {
            "c" : 1
        },
        "ns" : "test.fubar",
        "name" : "c_1"
    },
    {
        "v" : 1,
        "key" : {
            "a" : 1,
            "b" : 1,
            "c" : -1
        },
        "ns" : "test.fubar",
        "name" : "a_1_b_1_c_-1"
    }
]


Now suppose you want to run the following query against the collection.

db.fubar.find({'a':{'$lt':10000}, 'b':{'$gt': 5000}}, {'a':1, 'c':1}).sort({'c':-1})

Which of the following indexes could be used by MongoDB to assist in answering the query? Check all that apply.

• _id_
• a_1_b_1
• a_1_c_1
• c_1
• a_1_b_1_c_-1

Solution: 

 The question itself is tricky- as mongodb uses a single best matching index at a time to evaluate a query - here we have to select all those indexes that could be considered by mongodb to evaluate given query

We can manually check each index with hint on above query. For instance,

db.fubar.find({'a':{'$lt':10000}, 'b':{'$gt': 5000}}, {'a':1, 'c':1}).sort({'c':-1}).hint('a_1_b_1').explain()

If you analyze exexution stats using above query, you will notice mongo db has used index for find operation (see winning plan of query)

Apply same query for different indexes in question, to check if mongo db considers that index for executing given query or not

Note : If you do not find anything in winning plan section of execution stats then it means that mongo db could not consider that index for that particular query. To confirm, check "b_-1_a_1_c_1" index with above query (Winning plan is empty, which means mongo db could not use this index to execute above query)

1. c_1 could be used for sorting
2. a_1_b_1 can be used for find operation
3. a_1_b_1_c_-1 could also be used for find operation
4. a_1_c_1 could also be used for find operation

_id_ could not be considered by mongo db