0

I have a collection named user with three properties

  • name
  • email
  • zipcode

In my code I do single and compound queries like so:

db.user.find({'email': <some_email>, 'zipcode': <some_zipcode>})
db.user.find({'email': <some_email>)

My question is if I add a compound index on email and zipcode, do I also need to add a single index on email? I am not sure if the compound index covers both cases. Thanks.

i_trope
  • 1,554
  • 2
  • 22
  • 42
  • 2
    I'm sure this has been asked before, but it is also covered in the documentation. In addition to supporting queries that match on all the index fields, compound indexes can support queries that match on the prefix of the index fields: http://docs.mongodb.org/manual/core/index-compound/#compound-index-prefix. So you do not need the single index on email since it is covered by a prefix of the compound index. You can confirm index usage with [explain()](http://docs.mongodb.org/manual/reference/explain-results/). – Stennie Sep 18 '15 at 14:59
  • Thanks, this is what I was looking for. If you put this in an answer, I will accept it. – i_trope Sep 18 '15 at 15:01

2 Answers2

0

Compound index does not covers both the cases. If single index is not added on email, then I have record like this which has same email:

{'email':'gg@gg.com', 'zipcode':'ABC123'}
{'email':'gg@gg.com', 'zipcode':'ABC124'}

Having a single index on email will prevent above record in collection

Gaurav
  • 1,549
  • 2
  • 15
  • 31
  • 1
    You may have misread the question. The single index on email was a non-unique index that is a prefix of the compound index. In this case, a compound index will cover the same queries as the single index. Your example would apply if the email address needed to have a [unique index](http://docs.mongodb.org/manual/core/index-unique/). – Stennie Sep 18 '15 at 15:06
0

Yes, a compound index can be used for both queries. However, it depends on how you establish your compound index; realize that the field order in the index is important.

If you want to do both of these queries:

db.user.find({'email': <some_email>, 'zipcode': <some_zipcode>})
db.user.find({'email': <some_email>)

Then it is important that when you establish your index that you create your compound index with the email field first.

For example:

db.users.createIndex({
    "email" : 1,
    "stock" : 1
})

This will allow db.user.find({'email': <some_email>) to take advantage of the compound index because the email field is an index prefix; in other words, the field is at the beginning of the indexed fields.

Read about Compound Index Prefixes to gain a better understanding of this concept.

Also, see MongoDB's Compound Indexes for more information on compound indexes in general.

whyceewhite
  • 6,317
  • 7
  • 43
  • 51