CODEWITHSUNDEEP
mongodb
Nick Caruso
Nick Caruso

Reputation: 507

What's the best Mongo index strategy that includes a date range

I have the following schema: 

{
  a: string;
  b: date;
  c: number;
}

My query is 

find({
  a: 'some value',
  b: {
    $gte: new Date('some date')
  }
})
.sort({ 
 c: -1
});

I have an index that is: 

{ a: 1, b: 1, c: 1 }

But it's not using this index.

I have several other indexes, and when analyzing my explain(), it shows it's employing multiple other indexes to accomplish my query.

I believe since my "b" query is a date range, that's not considered an equality condition, so maybe that index won't work?

Should I have two indexes: { a: 1, c: 1} and separately { b: 1 }

Upvotes: 0

Views: 482

Answers (1)

klhr
klhr

Reputation: 3380

Dates tend to be much more selective than other fields, so when you have an index that looks like {dateField: 1, otherField: 1}, the selectivity of the dateField means that otherField will be useless unless you have multiple items that share the same date.

Depending on what your data distribution actually looks like, you might consider {otherField: 1, dateField: 1} (which means that mongo can go through in sorted order to check whether the docs match your date query). In general, putting your sort field before any fields used in a range query is a good idea.

Mlab's indexing docs are the best resource I've seen on index usage, and they recommend:

A good rule of thumb for queries with sort is to order the indexed fields in this order:

  • First, the field(s) on which you will query for exact values
  • Second, one small $in array
  • Third, the field(s) on which you will sort in the same order and specification as the sort itself (sorting on multiple fields)
  • Finally, the field(s) on which you will query for a range of values in the order of most selective to least selective (see range operators below)

Upvotes: 1

Related Questions