Advise me: Mutual credit caching strategies

Mutual credit accounting is very easy because you just store all the transactions in one table with

• a payer
• a payee
• a quantity

, and then add up all the transactions per user to get the balances. However once a mutual credit system gets larger, it becomes expensive to add up all these transactions. So a caching strategy is needed.

The easiest and most obvious caching is on the member balances. But there are two other main needs:

1. the transaction histories per user look better with a running balance figure, as we are accustomed to on our bank statements. So every time you want to see a running balance (as opposed to the present balance), you need to refer to, or recalculate the whole transaction history per user.

In order to calculate the highest spenders, or the most frequent earners, or the average transaction size, the whole transaction history needs to be parsed, or updated after every transaction. This is also expensive.

2. There is one more consideration that, at some point you may want to just cache all transactions before, say, 2008 to save adding them all up ever again.

There is a trade off between the quantity of cached objects to manage, and the processing power needed to do all the recalculating. I'm not looking for ultimate efficiency, but to set the software off in the right direction. So which of these seems like the right direction to you?

1. Have 2 kinds of caches. A transaction cache which stores all the entries in the users' statements, which is one entry per user per transaction, showing the running balance. And a system wide cache. The latter need not be completely refreshed, but can be incremented and checked occasionally.
2. To cache everything before 2008, and calculate everything dynamically from there. This would mean moving all the old transactions into an 'archive' table and creating a table with all the stats you can derive from payer, payee and quantity.
3. If applying a combination of the above, which should be primary?

Part of my unknowing is about the processing power involved and the likely limits of a general purpose machine running a large network. While users might be doing, say, 2 transactions a day, some system accounts might be trading with all users frequently. How many thousand transaction objects can be safely held in memory while they are added up? Perhaps MySQL can add them up more efficiently than any caching mechanism, without loading the whole table into memory?

Perhaps I have a year or two before this even needs thinking about?