mongodb compound index and single index performance

Question

Consider the following scenario:

100% of the time my query will include a in the query, and sometimes also b.

90% the query will be:

{a:"somevalue"}

and 10% it will be

{a:"somevalue",b:"somevalue"}

What would be the downside to satisfy this with a compound index only (if any)?, like so:

{
    "v" : 1,
    "key" : {
            "a" : 1,
            "b" : 1
    },
    "name" : "a_1_b_1",
    "ns" : "foo.bar"

}

Or would i benefit from adding a second index satisfying only queries on a

{
    "v" : 1,
    "key" : {
            "a" : 1,
            "b" : 1
    },
    "name" : "a_1_b_1",
    "ns" : "foo.bar"
},
{
    "v" : 1,
    "key" : {
            "a" : 1
    },
    "name" : "a_1",
    "ns" : "foo.bar"
}

score 2 · Accepted Answer · answered Sep 28 '14 at 13:42

The manual has this to say;

If you have a collection that has a compound index on { a: 1, b: 1 }, as well as an index that consists of the prefix of that index, i.e. { a: 1 }, assuming none of the index has a sparse or unique constraints, then you can drop the { a: 1 } index.

MongoDB will be able to use the compound index in all of situations that it would have used the { a: 1 } index.

In other words, you'll most likely get as good or better performance using a single index, since MongoDB does not have to cache two indexes in memory or update two separate indexes on every insert.

mongodb compound index and single index performance

1 Answers1