add index to history_transaction

[WhoSoup]

continuing #82

you're right about the index.

I used the test query

EXPLAIN QUERY PLAN SELECT batch.history_id, batch.entry_hash, batch.height, batch.timestamp, batch.executed, tx.tx_index, tx.action_type, tx.from_address, tx.from_asset, tx.from_amount, tx.outputs, tx.to_asset, tx.to_amount FROM pn_history_lookup lookup, pn_history_txbatch batch, pn_history_transaction tx WHERE lookup.address = 'foobar' AND lookup.entry_hash = tx.entry_hash AND lookup.tx_index = tx.tx_index AND batch.entry_hash = tx.entry_hash ORDER BY batch.history_id ASC

to see what it was doing, and got:

SCAN TABLE... not good. The performance for this query was: Result: 0 rows returned in 18996ms

So I added an index for tx_index, and ran the EXPLAIN again:

The performance for this query was: Result: 0 rows returned in 1ms

[Emyrk]

Ahh indexing that column has a huge improvement? Interesting.

[VeenaGondkar]

Without the index

    SELECT batch.history_id, batch.entry_hash, batch.height, batch.timestamp, batch.executed, tx.tx_index, tx.action_type,
           tx.from_address, tx.from_asset, tx.from_amount, tx.outputs, tx.to_asset,
           tx.to_amount FROM pn_history_lookup lookup, pn_history_txbatch batch, pn_history_transaction tx
    WHERE lookup.address = X'f7beb98ac89640a31a2587b3df8ce855321b47fbea352c9a49d21181ed026be7' AND
          lookup.entry_hash = tx.entry_hash AND lookup.tx_index = tx.tx_index AND
          batch.entry_hash = tx.entry_hash ORDER BY batch.history_id ASC
[2019-11-21 14:22:18] 55 rows retrieved starting from 1 in 2 s 380 ms (execution: 593 ms, fetching: 1 s 787 ms)

With the index:

[2019-11-21 14:23:00] 55 rows retrieved starting from 1 in 4 s 333 ms (execution: 4 s 286 ms, fetching: 47 ms)

The index is making the query for get txs much slower. The fetch time is quicker, but the execution is longer.