2

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?

u3l
  • 3,342
  • 4
  • 34
  • 51
  • can show us your indexes? – Zarathustra Jul 13 '14 at 08:23
  • Updated my question with the actual array (didn't think it was necessary earlier) – u3l Jul 13 '14 at 08:34
  • It's worth noting that you can drop your first index (`a_1_b_1`) since it is a proper subset of the second index (same keys and order) which can be used to satisfy the same queries. Duplicate indexes are unnecessary overhead. – Stennie Jul 14 '14 at 01:02
  • Yup, this was just more of a theoretical question of how MongoDB works though. I realize this would make writes slower etc – u3l Jul 14 '14 at 03:49

2 Answers2

5

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.

Christian P
  • 12,032
  • 6
  • 60
  • 71
  • 2
    Yup, I realize the redundancy... I was just curious that **IF** both were present, what would be chosen (which you answered). – u3l Jul 13 '14 at 09:03
0

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 }  } )
Muhammad Ali
  • 1,992
  • 1
  • 13
  • 20
  • I think you may have misunderstood my question (or I your answer)... I want to know which index will be used... the one with the first key or the one with the second key, in the situation where either would be possible in a `find` or `sort` command being executed. – u3l Jul 13 '14 at 07:39
  • The `key` is a field within the array of indexes a collection has. Like when I say `db.collection.ensureIndex({a:1,b:1})` – u3l Jul 13 '14 at 07:46
  • the key is array or json document.. of collection? – Muhammad Ali Jul 13 '14 at 07:46
  • it is a part of the output of `db.collection.getIndexes()` – u3l Jul 13 '14 at 07:48