Percentage of total database
(1) By anonymous on 2020-05-14 15:34:46 [link]
Hello, 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 [link]
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
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%.