CODEWITHSUNDEEP
mongodbmongodb-querymongodb-indexes
u3l
u3l

Reputation: 3412

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

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?

Upvotes: 2

Views: 3452

Answers (2)

Christian P
Christian P

Reputation: 12240

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.

Upvotes: 5

Muhammad Ali
Muhammad Ali

Reputation: 2014

You and use $exist,, When is true, $exists matches the documents that contain the field, including documents where the field value is null. If is false, the query returns only the documents that do not contain the field.

$exist

the query will be 

db.inventory.find( { "key.a": { $exists: true, 1 },"key.b": { $exists: true, 1 }  } )

Upvotes: 0

Related Questions