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?
OR
Do I just need a single-item index, and then do a separate index for the other collection I am populating?
2
Answers
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.explainmongoose
‘s "populate
" method just executes an additionalfind
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: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.