My local PC testing on a full database shows the following performance improvements in the balance query (transactions.js)...
The most common case, get the balance for 1 owner key.. This query runs every time a checkout or pricing occurs. When I run the balance query for 1 public key the new query runs in 32ms and the old query takes 1.7 sec..
If, however, I run the balance query on all rows the new query runs in 5 seconds and the old query runs in 8.3 seconds .. So, admin queries should be almost 2x faster.
I optimized by adding two columns: last_trx_event and last_pay_event in the blockchain_trx and account_pay tables. The migration will create these columns, create INSERT triggers to keep them up-to-date, and finally (and lastly) populate existing records with the data.
The migration runs on my machine where nothing else is running in less than 2 seconds.
I did not add the same design pattern to the account_pay table. Multiple attempts to pay for a domain should be unusual so I don't think that it will be worth it. So this pattern will remain unchanged:
join account_pay ap on ap.id = (
select id from account_pay
where account_id = a.id
order by id desc limit 1
)
After the migration, it is possible to optimize any SQL with the following join as follows:
- join blockchain_trx_event le on le.id = (
- select max(le.id)
- from blockchain_trx lt
- join blockchain_trx_event le on le.blockchain_trx_id = lt.id
- where lt.account_id = a.id
- )
+ join blockchain_trx_event le on le.id = t.last_trx_event
And:
- join account_pay_event ae on ae.id = (
- select max(le.id)
- from account_pay_event le
- where le.account_pay_id = ap.id
- )
+ join account_pay_event ae on ae.id = ap.last_pay_event
My local PC testing on a full database shows the following performance improvements in the balance query (
transactions.js)...The most common case, get the balance for 1 owner key.. This query runs every time a checkout or pricing occurs. When I run the balance query for 1 public key the new query runs in 32ms and the old query takes 1.7 sec..
If, however, I run the balance query on all rows the new query runs in 5 seconds and the old query runs in 8.3 seconds .. So, admin queries should be almost 2x faster.
I optimized by adding two columns: last_trx_event and last_pay_event in the blockchain_trx and account_pay tables. The migration will create these columns, create INSERT triggers to keep them up-to-date, and finally (and lastly) populate existing records with the data.
The migration runs on my machine where nothing else is running in less than 2 seconds.
I did not add the same design pattern to the account_pay table. Multiple attempts to pay for a domain should be unusual so I don't think that it will be worth it. So this pattern will remain unchanged:
After the migration, it is possible to optimize any SQL with the following join as follows:
And: