[bind10-dev] Adding another index to the records SQLite3 table

[Mukund Sivaraman]

Hi all

This is a request for comments on whether or not we should add a new
index to the "records" SQLite3 table.  See bug #1756.

The index specifically is:
CREATE INDEX records_byrname_and_rdtype ON records (rname, rdtype);

This is intended to make zone iteration faster. But it has the
following negative effects:

32 seconds for insert without index of 299957 records
-rw-rw-r--   1 muks muks 95246336 Sep 20 15:36 tmp2.sqlite3-without-index

7 seconds for creating index after inserting records
-rw-rw-r--   1 muks muks 114114560 Sep 20 15:39 tmp2.sqlite3-with-index-after
~20% increase in file size

41 seconds for insert with index of 299957 records
-rw-rw-r--   1 muks muks 114114560 Sep 20 15:44 tmp2.sqlite3-with-index-before
~20% increase in file size
~28% increase in insert time 

Basically, it increases file size by about 20% and decreases insert
performance by about 28%.


Kind regards,

Mukund