MongoDB createIndex() when using populate() in my query-CodePudding

I have a query into my MongoDB database like this:

WineUniversal.find({"scoreTotal": {
      "$gte": 50
    }})
    .populate([{
        path: 'userWines',
        model: 'Wine',
        match: {$and: [{mode: {$nin: ['wishlist', 'cellar']}}, {scoreTotal: {$gte: 50}}] }
}])

Do I need to create an compound index for this query?

Do I just need a single-item index, and then do a separate index for the other collection I am populating?

CodePudding user response：

In MongoDB it is not possible to index across collections, so the most optimal solution would be to create different indexes for both collections you are joining.

PS: You could use explain to see the performance of your query and the indexes you add: https://www.mongodb.com/docs/manual/reference/command/explain/#mongodb-dbcommand-dbcmd.explain

CodePudding user response：

mongoose's "populate" method just executes an additional find query behind the scenes into the other collection. it's not all "magically" executed as one query.

Now with this knowledge is very easy to decide what would be optimal.

For the first query you just need to make sure "WineUniversal" has an index for scoreTotal field.

For the second "populated" query assuming you use _id as the population field (which is standard) this field is already indexed.

mongoose creates a condition similar to this:

const idsToPopulate = [...object id list from wine matches];
const match = {$and: [{mode: {$nin: ['wishlist', 'cellar']}}, {scoreTotal: {$gte: 50}}] };

match._id = {$in: idsToPopulate}
const userWinesToPopulate = collection.find(match);

So as long as you're using _id then creating additional indexes could help performance, but in a very very negligible way as the _id index does the heavy lifting as it is.

If you're using a different field to populate yes, you should make sure userWines collection has a compound index on that field.