SQLite Forum

Percentage of total database

Percentage of total database

(1) By anonymous on 2020-05-14 15:34:46 [link] [source]


I have been analysing my database sqlite3 file. I deleted all entries and added 1, there are two primary keys, so by adding 1 entry the 'number of entries' in the analyze tool says 4. Then I added 12 more database entries (so 48 in total with the indexes) and analyzed again.

What I don't understand is that 'Percentage of total database', in both cases gives 10.5%. I would think that it would give 12 times as much in the case of adding 12 database entries.

What am I missing?

Thanks in advance

(2) By Larry Brasfield (LarryBrasfield) on 2020-05-14 17:07:16 in reply to 1 [source]

Maybe the page count stayed the same across your last "adding", and still represents the same fraction of space used, however that is accounted. The sqlite_analyzer is quite coarse-grained with respect to space usage.

(3) By ddevienne on 2020-05-14 18:05:36 in reply to 1 [link] [source]

SQLite DBs are fixed-sized page-based. Non-empty tables and indexes
consume at least 1 page each, and of course more to store all necessary
table or index entries.

SQLite stores row content inside pages, and fits as many rows as it can
per-page (possibly splitting the some rows into overflow pages). If when
you analyze your DB, the used% does not change, that just means all 12
rows fit on a single page (per table/index). Insert more rows, to allocate
more pages, to see that % change. Or VACUUM your DB, to get rid of unused
pages, and get back to a large used%.