Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a really simple MongoDB collection:

{
  "user": 1,
  "domain": "example.com",
  "apiAccess": true
}

There's an index on the "user" and "domain" fields. I always perform the same type of query on this collection and it only ever returns one result.

db.collection.find({"user" 1, "domain": "example.com"});

What I realised is because I always query on "user" and "domain" and because together they're unique I could just combine the field into one field. And I could make this the _id like so:

{
  "_id": "example.com_1",
  "apiAccess": true
}

This way I only query on one field and only need one index. I was thinking this would perform better due to these reasons however due to my limited understanding of MongoDB I'm unsure if this would be the case and I was wondering if someone could shed some light.

share|improve this question
1  
Which value is more selective? Does a typical user have a high number of unique domains, and are there more domains than users? Without more details it will be tough if violating 1NF this way will be worth it. Of course you could always test it... –  Aaron Bertrand Apr 23 '12 at 3:31
    
Hi Aaron, thanks for the questions, it got me thinking about how the two indexes could improve performance I really hadn't thought that through. DBs aren't usually my thing. Things are still in development stages but there'll be a higher number of domains than users, with each user having between 1 and 2000+ domains with an average per user of roughly 30 domain names. I do have plans to test this but I was more interested in the reasoning behind why each method might perform faster. And from what I understand with more domains than users I'm probably best keeping the two indexes? –  Sean Bannister Apr 23 '12 at 4:40

1 Answer 1

Without combining the fields in your data set you could just create a compound index using both. No changes to your data necessary. To do so, just create the index like so:

db.collection.ensureIndex({"user" : 1, "domain" : 1})

Docs are here:

http://www.mongodb.org/display/DOCS/Indexes#Indexes-CompoundKeys

Once you have created such a compound key it essentially makes an index on the leftmost element (user in my example above) redundant, and so an index on user (if it exists) could be removed.

Don't forget that the query optimizer only runs every ~1000 queries, so you will have to hint() the index to make sure it is used if you are testing it out.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.