/ Artifact Content
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Artifact a031aaf37b185e4fa540223cb516d3bccec7eeac:


An SQLite (version 1.0) database was used in a large military application
where the database contained 105 tables and indices.  The following is
a breakdown on the sizes of keys and data within these tables and indices:

Entries:      967089
Size:         45896104
Avg Size:     48
Key Size:     11112265
Avg Key Size: 12
Max Key Size: 99

    0..8            263    0%
    9..12          5560    0%
   13..16         71394    7%
   17..24        180717   26%
   25..32        215442   48%
   33..40        151118   64%
   41..48         77479   72%
   49..56         13983   74%
   57..64         14481   75%
   65..80         41342   79%
   81..96        127098   92%
   97..112        38054   96%
  113..128        14197   98%
  129..144         8208   99%
  145..160         3326   99%
  161..176         1242   99%
  177..192          604   99%
  193..208          222   99%
  209..224          213   99%
  225..240          132   99%
  241..256           58   99%
  257..288          515   99%
  289..320           64   99%
  321..352           39   99%
  353..384           44   99%
  385..416           25   99%
  417..448           24   99%
  449..480           26   99%
  481..512           27   99%
  513..1024         470   99%
 1025..2048         396   99%
 2049..4096         187   99%
 4097..8192          78   99%
 8193..16384         35   99%
16385..32768         17   99%
32769..65536          6   99%
65537..65541          3  100%

If the indices are omitted, the statistics for the 49 tables
become the following:

Entries:      451103
Size:         30930282
Avg Size:     69
Key Size:     1804412
Avg Key Size: 4
Max Key Size: 4

    0..24            89    0%
   25..32          9417    2%
   33..40        119162   28%
   41..48         68710   43%
   49..56          9539   45%
   57..64         12435   48%
   65..80         38650   57%
   81..96        126877   85%
   97..112        38030   93%
  113..128        14183   96%
  129..144         7668   98%
  145..160         3302   99%
  161..176         1238   99%
  177..192          597   99%
  193..208          217   99%
  209..224          211   99%
  225..240          130   99%
  241..256           57   99%
  257..288          100   99%
  289..320           62   99%
  321..352           34   99%
  353..384           43   99%
  385..416           24   99%
  417..448           24   99%
  449..480           25   99%
  481..512           27   99%
  513..1024         153   99%
 1025..2048          92   99%
 2049..4096           7  100%

The 56 indices have these statistics:

Entries:      512422
Size:         14879828
Avg Size:     30
Key Size:     9253204
Avg Key Size: 19
Max Key Size: 99

    0..8            246    0%
    9..12          5486    1%
   13..16         70717   14%
   17..24        178246   49%
   25..32        205722   89%
   33..40         31951   96%
   41..48          8768   97%
   49..56          4444   98%
   57..64          2046   99%
   65..80          2691   99%
   81..96           202   99%
   97..112           11   99%
  113..144          527   99%
  145..160           20   99%
  161..288          406   99%
  289..1024         316   99%
 1025..2048         304   99%
 2049..4096         180   99%
 4097..8192          78   99%
 8193..16384         35   99%
16385..32768         17   99%
32769..65536          6   99%
65537..65541          3  100%