CODEWITHSUNDEEP
mysqlindexing
Josh
Josh

Reputation: 3288

How would indexes improve aggregate lookup speeds in MySQL?

I'm building a virtual currency system, and as it is situated in real money, accurate retention of data is a very key goal. I am building my currency so that user wallets do not have a fixed value (an 'amount'), rather, they are the net of all transactions to and from that wallet -- sort of like bitcoin.

So, if I'm calculating the amount of a wallet, my MySQL query looks like this.

SELECT (
    SELECT IFNULL(SUM(`tx`.`amount`), 0)
    FROM `transactions` AS `tx`
    WHERE `tx`.`to_wallet_id` = 5
) - (
    SELECT IFNULL(SUM(`tx`.`amount`), 0)
    FROM `transactions` AS `tx`
    WHERE `tx`.`from_wallet_id` = 5
) AS `net`

This query builds a net value using the aggregate data of a SUM() for all transactions towards a wallet subtracted by all transactions from a wallet. The final value represents how much money the wallet currently contains.

In short, I want to know how I can optimize my table so that these queries are very fast and scale as well possible.

I would assume I should index [from_wallet_id, amount] and [to_wallet_id, amount], but I'm very uncertain.

Upvotes: 1

Views: 58

Answers (1)

Pavel Zimogorov
Pavel Zimogorov

Reputation: 1442

I would suggest to do the following:

  • Make amount field is not nullable. And put 0 as default value.
  • Create indexes according to you idea ([from_wallet_id, amount] and [to_wallet_id, amount]). At allows to run queries which retrieves all necessary data from indexes.

If it doesn't help, you can think about the following options:

  • Divide the transaction table on 2 part: in_transaction and out_transaction
  • Keep aggregate values in the separate table and update its after any changes.

Upvotes: 1

Related Questions