Optimization: more idb queries could use indicies or key ranges

[turbocrime]

A frequent pattern is to query an entire table, or cursor-scan an entire table, then filter the result in-memory to obtain a subset of items. Often this involves parsing of item json, conversion to/from binary, and/or regex operations on every table item.

This is slow. This has been discussed before:

• Stream large idb queries, avoid querying entire tables #312
• Avoid streaming entire IDB tables #486

Though not as featured as SQL, idb does provide query features beyond 'give me the whole table' that should be used.

asset types

Commonly, in-memory filtering is used to select a set of asset types - delegation assets, for example. Two approaches:

• By table index. Note tables and asset tables could add a field identifying asset type of the item, and add an index for this field. Then queries conditional on this field could be made directly and handled by the database engine.

• By key range. Tables keyed by bech32 asset id could be queried by key range, since bech32 strings for each type would be lexically ordered together.

spend height

Commonly, notes are filtered by spend height - either detecting unspent notes, or in comparison to a specific height.

Note tables could add an index by spend height.

considerations

Be aware that idb has some awkward requirements around compound queries https://stackoverflow.com/questions/23806635/searching-for-compound-indexes-in-indexeddb

Any idb change requires a migration.

[TalDerei]

some early design decisions like stringifying idb contents were proving to be less than desirable towards implementing #1996, but was still able to yield some speed benefits.

[turbocrime]

more considerations

bigint

idb does not support bigint indicies, but, bigints could be stringified and left-padded with zeroes to achieve lexical ordering equivalent to numeric ordering.

some desirable indicies such as height which are nominally bigint will effectively never exceed float64 values, so could be converted to js number for indexing purposes.

byte strings

idb fully supports storage and indexing of byte strings