I'm using the following aggregate and wanted to see what would be the recommended indexes for this:
{
"$match" :
{
user : ObjectId(user_id),
type : 'item'
}
},
{ "$group" : {
_id : "$user",
size : { "$sum" : "$size" }
}
}
Would it be too much or necessary to do a compound index on user and type, then another compound index on _id and size?
You can't create an index that works the way you've described it. There's not a way to "index" one of the outputs of a pipeline step when running an aggregation.
There are a few aggregation operators that can work with MongoDB collection indexes.
Currently, they include: $match, $sort, $limit, and $skip.
As $match is one of them, the performance of the aggregation query you've written may benefit by declaring a compound index on the two fields, especially as you've correctly placed the $match at the beginning of the pipeline:
db.theCollection.ensureIndex({user: 1, type: 1})
The $group step in the pipeline will take the results from the $match and ideally, be relatively quick. :)
Although compound index on user and type can be helpful, index on _id and size will be absolutely unnecessary. You have to put index on the fields you are planning to make search on. Here you are searching on fields user and type and then you are grouping what you have found on these other fields.
Do not forget to check your indexes are performing with explain.
