Indices are really helpful on databases that hold many thousands of rows and are searched frequently. On the other hand, indices are harmful on tables which are frequently written to because not only the actual entry needs to be updated but also the index.
So especially in the particular case of the attributes tables, the index is of more harm than use because we write updates all the time and never read it. Removing the index on the attributes table reduces the disk io footprint of the zigbee db by 30%.
As the other tables are mostly sitting still all the time, the index doesn't really do much harm either in terms of disk io, but it increases the database size, while not providing much benefit, as those entries are also only read once at startup and the tables are reasonably small (a few hundreds).
Indices are really helpful on databases that hold many thousands of rows and are searched frequently. On the other hand, indices are harmful on tables which are frequently written to because not only the actual entry needs to be updated but also the index.
So especially in the particular case of the attributes tables, the index is of more harm than use because we write updates all the time and never read it. Removing the index on the attributes table reduces the disk io footprint of the zigbee db by 30%.
As the other tables are mostly sitting still all the time, the index doesn't really do much harm either in terms of disk io, but it increases the database size, while not providing much benefit, as those entries are also only read once at startup and the tables are reasonably small (a few hundreds).