0

I have a Mongo collection with 2 fields, both are dates. I also have the following indexes:

{date1: 1}
{date2: 1}

If I perform the following query it's fast and does an IXSCAN using the date2 index backwards as expected:

db.getCollection('foo').find({ date1: { '$gte': new Date() } }).sort({date2:-1}).limit(50)

However if I reverse the sort order the query becomes very slow and instead uses the date1 index and then sorts the result from the find part of the query.

I also tried adding an index {date1: 1, date2:1} which I would expect to be used for both fetching and sorting however this make no difference.

Why is this? I would expect it to use the index for date2 in order to do the limit first in the same way regardless of sort order and I would also expect the compound index to be used.

Rick
  • 3,240
  • 2
  • 29
  • 53
  • Are you using the `{date1: 1}` index in any other query? (1) The index on `{date1: 1, date2:1}` will not work in your case; see this in documentation about [Sort and Non-prefix Subset of an Index](https://docs.mongodb.com/manual/tutorial/sort-results-with-indexes/index.html#sort-and-non-prefix-subset-of-an-index). (2) Why `skip(0).limit(50)`? This is same as `limit(50)`. (3) If you have two indexes on two fields, one of the indexes is redundant, unless used in other queries. – prasad_ Apr 04 '20 at 09:42
  • Yes, the indices are being used separately in other queries unfortunately. The `skip(0)` was just because I lifted this from app code where it does pagination, I'll remove it from the question. – Rick Apr 05 '20 at 10:24
  • Since you have many queries, using the same date fields in different combinations (I am guessing this last part), I think you have to analyze all these together rather than individually. Compound indexes are useful to fulfill the needs of multiple queries. You have to generate _query plans_ using the `explain()` on all these queries and the verify the usage of the indexes by the queries. What is best in your case is difficult to mention without know details about other queries, amount of data being queried, query criteria (other fields used along with the date fields), etc. – prasad_ Apr 06 '20 at 01:17
  • Does the index on `{ date2: 1, date1: 1 }` help? What happens with other queries using these fields? What is the MongoDB version? – prasad_ Apr 06 '20 at 01:17

0 Answers0