Need help creating an index for a complex MongoDB query-CodePudding

I need to create an index for the following query:

await Activity.find({
   $and: [
      {
         lastUpdated: {
            $gte: new Date(new Date().getTime() - 7 * 24 * 60 * 60 * 1000),
         },
      },
      {
         "followers._user": _user,
      },
      {
         global: true,
      }
   ]
})
.collation({
   locale: "en_US",
   numericOrdering: true,
})
.sort({
   lastUpdated: -1
})
.skip(
   length
)
.limit(
   10
)

I have the below index in place currently but the query does not use it.

ActivitiesSchema.index(
  { "followers._user": 1, global: 1, lastUpdated: -1 },
  {
    collation: {
      locale: "en_US",
      numericOrdering: true,
    },
  }
);

If I am missing any information or need to provide more detail let me know and I would be happy to provide it.

CodePudding user response：

Change index to:

{ lastUpdated: -1, "followers._user": 1, global: 1 }

NB: it may affect other queries that rely on existing index

https://docs.mongodb.com/manual/tutorial/sort-results-with-indexes/#sort-and-index-prefix reads:

If the sort keys correspond to the index keys or an index prefix, MongoDB can use the index to sort the query results. A prefix of a compound index is a subset that consists of one or more keys at the start of the index key pattern.

Since you are sorting by "lastUpdated", the index should start from it.

NB 2: With this change Mongodb can use, but it is not guaranteed. There are many other factors like selectivity and cardinality, e.g. global: true, implies extremely low cardinality to benefit from an index on this field. On the other hand if the user doesn't follow much and the total activity is massive, it might be cheaper to filter by "followers._user" index and do in-memory sort. It's up to the query planner to decide which index to use.