MongoDB: Should I combine fields for performance in limited circumstances

mongodb

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.

Best Answer

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.

Related Question