MongoDB, Web Development

MongoDB Single Key Index or Compound Index

Just got a question on the MongoDB user group as which type of index over multiple fields would be best and why. The user basically had three fields:

  • Type
  • CompanyID
  • Date

And was wondering what the difference would be to using a compound index on all three fields and using a single key index per field. The main difference would be that MongoDB only supports ONE index per query so the query optimiser would only be able to use one of your three indexes rendering the other 2 indexes useless and dead weight in the DB slowing inserts and using valuable indexing space.

As such with that in mind it is of course best to use a compound index to cover all three fields rather than a index on each field.

Also note that queries will be parsed and optimised better if you index in the order you query so for example:

find({ type: '', companyID: {} }).sort({ Date: -1 })

Would mean a version of an optimal index would be of:

ensureIndex({ type: 1, companyId: 1, Date: 1 })

Hope this helps,

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s