Hi,
I have a collection with around 5M documents. This collection contains the history of a certain balances together with the current balance like this:
{
_id: ObjectId('12345')
UserId: 1
BalanceChanged: "10"
BalanceCurrent: "10"
Date: 2022-11-18T01:00:00
...
},
{
_id: ObjectId('12346')
UserId: 1
BalanceChanged: 5
BalanceCurrent: "15"
Date: 2022-11-18T02:00:00
...
}
When I want the current balance of all users, i am facing performance issues:
- The data must be sorted first (desc) in order to get the most recent balance (takes around 2 seconds with a desc index on “Date”);
- The data must be grouped by “UserId” and get the first document (another 2 seconds);
- Replacing root in order to get the inner document of the group (another 2 seconds);
I was wondering if I could do this another way where I can get the current balance of all users in lets say a few milliseconds?
I am aware that I can create a inner collection like this:
{
_id: ObjectId('12345')
UserId: 1
BalanceCurrent: "15"
History: [
{
BalanceChanged: "10"
Date: 2022-11-18T01:00:00
},
{
BalanceChanged: 5
Date: 2022-11-18T02:00:00
...
},
]
}
But I’m worried about running into limits (max document size for instance). Some users already have 50K documents in less then 3 months.
Or is there another way to query this kind of data in a few milliseconds?
Thanks!