Search code examples
mongodbcompound-index

MongoDB: Compound Indexes on a "Where"-Select

I have the following MongoDB schema:

  mandate: {
    type: mongo.Schema.Types.ObjectId,
    ref: 'Mandate',
    required: true
  },
  observer: {
    type: mongo.Schema.Types.ObjectId,
    ref: 'Observer',
    required: true
  },
  value: {
    type: Number,
    required: true
  },
  observedAt: {
    type: Date,
    required: true
  }

This collections holds an huge amount of data.

I ask for data in the following way: give me all data observerd between 2015 and 2016 where observer = "a" and mandate = "b"

Is there a best practise approach for a compound index over the 3 fields (observer, mandate, observedAt)?

For today, i do this like this:

schema.index({
  mandate: 1,
  observer: 1,
  observedAt: -1
});

It this the right way?

Solution

  • If you will always query with all of these 3 fields, like in your example, then yes, it's good. Also because you used the observedAt as the last index, you can also sort by that field and it will still use an index for the sorting stage.

    But if your query can change using less or more fields so it may not be the best option. read more about it here, look especially at the examples

    https://docs.mongodb.com/manual/core/index-compound/