Wallet DB Index Issues Summary

[neurosis69]

This summary is referencing the main build as of ce533879e25a536b6414307c4e6d850c1da06d4f

There are a couple of ways to get these issues fixed, so I decided to create an issue, giving a comprehensive overview, instead of just creating a PR. (There is already an old/stale PR #9297 addressing some of these problems.)

How did I check the indexes? After downloading the beta wallet 1.3, I synced in wallet mode. Then I just compared the wallet dbs schema with the definition from the repo code.

(table names in alphabetical order)

• action_queue

  • Issue: No Indexes were created.
  • Reason: All three indexes use index names already used before.
  • Fix: Try to establish a naming convention, like always prefix the table_name, e.g.CREATE INDEX action_store_queue_name ... ...
  • Missing Indexes: https://github.com/Chia-Network/chia-blockchain/blob/5d2770cbddf8434b76725cb70acb1755a0754837/chia/wallet/wallet_action_store.py#L40 https://github.com/Chia-Network/chia-blockchain/blob/5d2770cbddf8434b76725cb70acb1755a0754837/chia/wallet/wallet_action_store.py#L42 https://github.com/Chia-Network/chia-blockchain/blob/5d2770cbddf8434b76725cb70acb1755a0754837/chia/wallet/wallet_action_store.py#L44

• derivation_paths

  • Issue: There is no unique constraint on the PK column and several indexes are missing or wrongly created (imho)

  • Reason: Typos, reused index names, mixed up indexed columns

  • Primary Key definition fixed with #10273

    Instead of https://github.com/Chia-Network/chia-blockchain/blob/5d2770cbddf8434b76725cb70acb1755a0754837/chia/wallet/wallet_puzzle_store.py#L43

    it has to be " puzzle_hash text PRIMARY KEY,"

    Sadly this typo doesn't lead to an error, it looks like the PK definition is just ignored. This means, there is no unique index on puzzle_hash column right now.

  • Index on puzzle_hash column This index would be redundant if the primary key was defined correctly. https://github.com/Chia-Network/chia-blockchain/blob/5d2770cbddf8434b76725cb70acb1755a0754837/chia/wallet/wallet_puzzle_store.py#L54

    Right now at least it helps accessing the table on puzzle_hash columns using an index, leaving the missing uniqueness unsolved. This index should be dropped when the PK problem is solved.

  • Index wallet_type Not created due to naming conflicts with index on coin_record table, to fix choose a different index name https://github.com/Chia-Network/chia-blockchain/blob/5d2770cbddf8434b76725cb70acb1755a0754837/chia/wallet/wallet_puzzle_store.py#L58

  • Index wallet_id Not created due to naming conflicts with index on coin_record table, to fix choose a different index name https://github.com/Chia-Network/chia-blockchain/blob/5d2770cbddf8434b76725cb70acb1755a0754837/chia/wallet/wallet_puzzle_store.py#L60

  • Index used This index was created but referencing column wallet_type. According to the name of the index, I'm pretty sure the intent was to index the column used. https://github.com/Chia-Network/chia-blockchain/blob/5d2770cbddf8434b76725cb70acb1755a0754837/chia/wallet/wallet_puzzle_store.py#L62

  • Index hardened Not sure how and what the table derivation_paths is actually used for, but as all other columns are indexed and the column hardened is used on many queries. Maybe there is also a need to index hardened?

• key_val_store

  • Issue: Index key was not created
  • Reason: The indexes name was already used
  • Fix: Just remove the Index DDL as there is an unique index already automatically created on PK column (sqlite_autoindex_key_val_store_1) https://github.com/Chia-Network/chia-blockchain/blob/5d2770cbddf8434b76725cb70acb1755a0754837/chia/wallet/key_val_store.py#L25

• trade_records

  • Issue: Redundant index
  • Reason: Primary Key columns are automatically indexed by unique index (sqlite_autoindex_trade_records_1)
  • Fix: Remove the Index DDL and drop the index in existing wallet dbs https://github.com/Chia-Network/chia-blockchain/blob/5d2770cbddf8434b76725cb70acb1755a0754837/chia/wallet/trading/trade_store.py#L99

• transaction_record

  • Issue: Redundant and missing index
  • Reason: Column is indexed 2 times and index name as already used
  • Fix: Drop/Change index and rename missing index
  • Index tx_created_index Both of the following indexes are created on the same column. Either choose the correct column or drop one of them. https://github.com/Chia-Network/chia-blockchain/blob/5d2770cbddf8434b76725cb70acb1755a0754837/chia/wallet/wallet_transaction_store.py#L57 https://github.com/Chia-Network/chia-blockchain/blob/5d2770cbddf8434b76725cb70acb1755a0754837/chia/wallet/wallet_transaction_store.py#L65
  • Index wallet_id Rename this index as the name wallet_id was already used for index in table users_wallets https://github.com/Chia-Network/chia-blockchain/blob/5d2770cbddf8434b76725cb70acb1755a0754837/chia/wallet/wallet_transaction_store.py#L74

[arvidn]

these are some really good finds. I'll work on fixing this as well as attempting to prevent these mistakes to be made in the future

[AmineKhaldi]

@neurosis69 thank you very much for the time and effort you spent collecting and reporting these. Looking forward to your next contribution!

[emlowe]

Closing - you may add new comments and reopen with additional details as needed

I think we got everything here.