CODEWITHSUNDEEP
mongodb
Pawan
Pawan

Reputation: 32321

Is Compound Index on a collection is always better than having single Indexes

I have come across this particular line with respect to indexes in mongdb .

Each additional index on a collections does impose some overhead when performing inserts or updates that involve changing index entries.

which means that with respect to my understanding , if there are more number of indexes on a collection it will degrade performance during insertion or Updation .

So is it always true that compound indexes are always better than single indexes ??

For example if i have a collection named stocks

and a compound index is present on it as shown below 

db.stocks.ensureIndex({"symbol":1,"date":1,"type": 1,"price":1},{"unique" : false})

and the above is better than the individual indexes shown below .

db.stocks.ensureIndex({"symbol" : 1}, {"unique" : false})
db.stocks.ensureIndex({"date" : 1}, {"unique" : false})
db.stocks.ensureIndex({"type" : 1}, {"unique" : false})
db.stocks.ensureIndex({"price" : 1}, {"unique" : false})

Please let me know if i am correct or not ??

Upvotes: 2

Views: 396

Answers (2)

i3arnon
i3arnon

Reputation: 116538

No. As of MongoDB v2.6 with its support for Index Intersection it really depends on how you query your data because:

  1. Compound indexes are usually bigger than single indexes, and so take more time to update.
  2. Compound indexes only support all the index fields or a prefix while single ones support every subset of fields in any order.

If you always query using all the index fields then it's better to use a compound Index, otherwise I would rely on index intersection and the freedom it brings.

Upvotes: 0

Sammaye
Sammaye

Reputation: 43884

I depends upon your queries, currently MongoDB can only use one index per query and index intersectioning is still on the road map ( https://jira.mongodb.org/browse/SERVER-3071 )however that being said compound indexes work via prefixes. As an example if you were to:

db.c.ensureIndex({s:1,d:1});

That index would work for a query with s and d or only s but not using only d. So compound indexes are not always the best method.

You have also got to consider:

Each additional index on a collections does impose some overhead when performing inserts or updates that involve changing index entries.

depends on the size and type of indexes your updating, not just how many indexes you have. For example updating an index containing large text areas will be very slow.

This also applies to updating a large compound index compared to two smaller indexes for example.

Upvotes: 1

Related Questions