CODEWITHSUNDEEP
mongodbmongooseindexing
Craig Howell
Craig Howell

Reputation: 1194

How to create an index for a complex MongoDB query

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,
    },
  }
);

What can I try to solve this?

Upvotes: 0

Views: 544

Answers (1)

Alex Blex
Alex Blex

Reputation: 37128

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.

Upvotes: 1

Related Questions