Remove O(m) DB queries

[majecty]

When querying UTXOs using Asset type, aggregating UTXOs by account, and creating a snapshot, the Indexer scans all the UTXOs that have a specific account or a specific asset type.

When a user opens a CodeChain web wallet, wallet requests the total amount of assets that the user has to the Indexer. Indexer sends a query that scans all the UTXOs that the user has. If the number of UTXOs that the user has is m, the upper bound of the query is O(m).

How to solve

Make APIs return sub results that read k rows in the DB. The Web wallet or Hub should request the maximum [n/k] number of API calls to get all the results. The upper bound of each query is O(k*log m). Then there won't be a single slow query that consumes all the resources(CPU, Memory, disk I/O) in the DB.

[majecty]

@foriequal0 says that we need to de-normalize a user's all asset amounts. The indexer calculates the assets' amounts whenever a user requests that information. Since a user's total asset amount is fundamental information in the wallet, it is a query that is used very often. This means that calculating it every time is a burden.

IMO it makes sense. We may use a view for the table or create another table that saves the total amount of assets a user has.