Which index would be used if there are multiple indexes containing the same fields?

Question

Take, for example, a find() that involves a field a and b, in that order. For example,

db.collection.find({'a':{'$lt':10},'b':{'$lt':5}})

I have two keys in my array of indexes for the collection:

[
{
    "v" : 1,
    "key" : {
        "a" : 1,
        "b" : 1
    },
    "ns" : "x.test",
    "name" : "a_1_b_1"
},
{
    "v" : 1,
    "key" : {
        "a" : 1,
        "b" : 1,
        "c" : 1
    },
    "ns" : "x.test",
    "name" : "a_1_b_1_c_1"
}
]

Is it guaranteed that mongo will use the first key since it more accurately matches the query, or does it randomly choose any of the two keys because they will both work?

Answer 1

MongoDB has a query optimizer which selects the indexes that are most efficient. From the docs:

The MongoDB query optimizer processes queries and chooses the most efficient query plan for a query given the available indexes.

So it's not strictly guaranteed (but I expect that the smaller index will yield results faster than the bigger compound index). You can also use hint operator to force the query optimizer to use the specified index.

db.collection.find({'a':{'$lt':10},'b':{'$lt':5}}).hint({a:1, b:1});

However, those two indexes in your example are redundant. That's because the compound index supports queries on any prefix of index fields.

The following index:

db.collection.ensureIndex({a: 1, b: 1, c: 1});

Can support queries that include a, a and b and a and b and c, but not only b or c, or only b and c.