I have an application in which I am using MongoDB. I have 1.1M documents in a single collection and I am trying to do some aggregations, my document structure looks like this:
My aggregation query looks like this:
db.spinHistory.explain().aggregate([
{
"$match": {
"gameRef": "6047a10c58ed573e490b8f54"
}
},
{
"$project": {
"platformRef": 1,
"gameRef": 1,
"currency": 1,
"win": 1,
"bet": 1,
"bonusWin": "$data.bonusWin",
"_id": 0
}
},
{
"$group": {
"_id": {
"platformRef": "$platformRef",
"gameRef": "$gameRef",
"currency": "$currency"
},
"bet": {
"$sum": "$bet"
},
"win": {
"$sum": "$win"
},
"bonus": {
"$sum": "$data.bonusWin"
},
"count": {
"$sum": 1
}
}
},
{
"$project": {
"platformRef": "$_id.platformRef",
"gameRef": "$_id.gameRef",
"currency": "$_id.currency",
"bet": 1,
"win": 1,
"bonus": 1,
"count": 1
}
}
])
This query takes 5 seconds to execute (in 1.1M documents). I wonder if there is any way to optimize it?
I have this index set up:
{
"v": 2,
"key": {
"gameRef": 1,
"platformRef": 1,
"currency": 1,
"bet": 1,
"win": 1,
"data.bonusWin": 1
},
"name": "idx_spin_history_main_fields",
"background": false
}
and the explain plan gives me this:
{
"stages": [
{
"$cursor": {
"queryPlanner": {
"plannerVersion": 1,
"namespace": "oak9e_rgs_temp.spinHistory",
"indexFilterSet": false,
"parsedQuery": {
"gameRef": {
"$eq": "6047a10c58ed573e490b8f54"
}
},
"queryHash": "27C08187",
"planCacheKey": "E204EC8C",
"winningPlan": {
"stage": "PROJECTION_DEFAULT",
"transformBy": {
"bet": true,
"platformRef": true,
"win": true,
"currency": true,
"gameRef": true,
"bonusWin": "$data.bonusWin",
"_id": false
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"gameRef": 1,
"platformRef": 1,
"currency": 1,
"bet": 1,
"win": 1,
"data.bonusWin": 1
},
"indexName": "idx_spin_history_main_fields",
"isMultiKey": false,
"multiKeyPaths": {
"gameRef": [],
"platformRef": [],
"currency": [],
"bet": [],
"win": [],
"data.bonusWin": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"gameRef": [
"[\"6047a10c58ed573e490b8f54\", \"6047a10c58ed573e490b8f54\"]"
],
"platformRef": [
"[MinKey, MaxKey]"
],
"currency": [
"[MinKey, MaxKey]"
],
"bet": [
"[MinKey, MaxKey]"
],
"win": [
"[MinKey, MaxKey]"
],
"data.bonusWin": [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans": []
}
}
},
{
"$group": {
"_id": {
"platformRef": "$platformRef",
"gameRef": "$gameRef",
"currency": "$currency"
},
"bet": {
"$sum": "$bet"
},
"win": {
"$sum": "$win"
},
"bonus": {
"$sum": "$data.bonusWin"
},
"count": {
"$sum": {
"$const": 1
}
}
}
},
{
"$project": {
"_id": true,
"bet": true,
"bonus": true,
"count": true,
"win": true,
"platformRef": "$_id.platformRef",
"gameRef": "$_id.gameRef",
"currency": "$_id.currency"
}
}
],
"serverInfo": {
"host": "DESKTOP-V3NTFPM",
"port": 27017,
"version": "4.4.3",
"gitVersion": "913d6b62acfbb344dde1b116f4161360acd8fd13"
},
"ok": 1
}
Please can you let me know if there is anything I can do to make this work faster?