Do I need composite indices if each attribute is indexed in mongodb collection?

Question

Suppose I have a collection in a mongo database with the following documents

{
    "name" : "abc",
    "email": "abc@xyz.com",
    "phone" : "+91 1234567890"
}

The collection has a lot of objects (a million or so), and my application, apart from regularly adding objects to this collection, does a few different types of finds on this data.

One method does a find with all the three attributes (name, email and phone), so I can make a composite index for those three fields to make sure this find works effiently.

db.mycollection.ensureIndex({name:1,email:1,phone:1})

Now, I also have methods in my application which fetch all the objects with the same name (bad example, I know). So I need an index for the name field.

db.mycollection.ensureIndex({name:1})

Gradually, my application grows to a point where I have to index the other fields.

Now, my question. If I have each of the attributes indexed individually, does it still make sense to maintain composite indices for all three attributes (or 2 of the attributes)?

Obviously, this is a bad example... If I were making a collection to store multiple contact info for a person, I'd use arrays. But, this question is purely about the indexes.

It all depends on the nature of your queries really ... having too many indexes is poor for performance (if there are lots of inserts). With the composite index, you can't do a search only on `phone` for example. Make sure you read this section: http://docs.mongodb.org/manual/core/indexes/#compound-indexes — WiredPrairie, Jun 26 '13 at 21:02

score 0 · Answer 1 · answered Jun 26 '13 at 11:24

0

It depends on your queries.

If you are doing a query such as:

db.mycollection.find({"name": "abc", email: "abc@xyz.com", phone: "+91 1234567890"});

then a composite index would be the most efficient.

answered Jun 26 '13 at 11:24

Alex

37,502
51
204
332

Yes, I am doing that query. That is obvious. My question is.. if I am also doing a query where I am searching by 2 attributes or only one attribute, I need to add indexes for those finds too. So do I still need to maintain composite indexes? Or do I even need the individual indexes? – Munim Jun 26 '13 at 12:10

score 0 · Accepted Answer · answered Jul 03 '13 at 05:46

Just to answer my own question for sake of completion:
Compound indexes don't mean that each of the individual attributes are indexed, only the first attribute in the compound index can be used alone in a find with efficiency. The idea is to strike a balance and optimize queries, as too many indexes increase disk storage and insertion time.

Do I need composite indices if each attribute is indexed in mongodb collection?

2 Answers2