signum-node icon indicating copy to clipboard operation
signum-node copied to clipboard

Add new index to speed up collection of transactions by type, subtype

Open frankTheTank72 opened this issue 3 years ago • 0 comments

We should add a new index to the DB:

ALTER TABLE transaction ADD INDEX transaction_type_subtype_idx (type, subtype`) USING BTREE;

This gives a massive speed up from around 30sec to 0.14 sec for this query :

SELECT id FROM transaction WHERE (transaction.subtype = 8 AND transaction.type = 2) ORDER BY transaction.height DESC;

frankTheTank72 avatar Dec 04 '22 14:12 frankTheTank72