What are the tradeoffs of creating a new compound index versus adding to an existing?

Question

I'm querying by fields a,b, and c, and have this index:

{a: 1, b: 1, c: 1}

I'm adding a new query on a, sorted by d desc. Should I change the index to:

{a: 1, b: 1, c: 1, d: -1}

Or should I add a second index:

{a: 1, d: -1}

Answer 1

In this case, changing the index doesn't work (see http://docs.mongodb.org/manual/tutorial/sort-results-with-indexes/#sort-and-non-prefix-subset-of-an-index).

In the general case of querying non-sequential compound index fields, while creating indexes always takes up more memory, it will perform better:

"However, the index would not be as efficient in supporting the query as would be an index on only item and stock." http://docs.mongodb.org/manual/core/index-compound/