Hi Richard,
On 8/10/2011 12:51 AM, Richard Hipp wrote:
Hi All,
I have been trying to see how we can make one of our databases more space
efficient. I am now looking at seeing if we have the right indexes and if
there is a way to save space by removing indexes.
Have you run the sqlite3_analyzer.exe utility (available from
http://www.sqlite.org/download.html) on your database to see how efficiently
the data is being stored and which tables are taking up the most space? Can
you share the output of sqlite3_analyzer.exe with us?
I have run sqlite_analyzer (and I use it all the time to figure out what
to optimize next!). The specific database in question has about 80
tables - let me see if I can extract out a small part to compare. This
is from one of the analyses.
*** Table NCX and all its indices *******************************
Percentage of total database.......... 15.4%
Number of entries..................... 2128132
Bytes of storage consumed............. 173074432
Bytes of payload...................... 139889514 80.8%
Average payload per entry............. 65.73
Average unused bytes per entry........ 9.03
Average fanout........................ 89.00
Fragmentation......................... 3.6%
Maximum payload per entry............. 1083
Entries that use overflow............. 9 0.0%
Index pages used...................... 1691
Primary pages used.................... 167318
Overflow pages used................... 9
Total pages used...................... 169018
Unused bytes on index pages........... 207792 12.0%
Unused bytes on primary pages......... 18999559 11.1%
Unused bytes on overflow pages........ 850 9.2%
Unused bytes on all pages............. 19208201 11.1%
*** Table NCX w/o any indices ***********************************
Percentage of total database.......... 13.8%
Number of entries..................... 1064066
Bytes of storage consumed............. 155700224
Bytes of payload...................... 128184788 82.3%
Average payload per entry............. 120.47
Average unused bytes per entry........ 15.91
Average fanout........................ 89.00
Fragmentation......................... 3.3%
Maximum payload per entry............. 1083
Entries that use overflow............. 9 0.0%
Index pages used...................... 1691
Primary pages used.................... 150351
Overflow pages used................... 9
Total pages used...................... 152051
Unused bytes on index pages........... 207792 12.0%
Unused bytes on primary pages......... 16725875 10.9%
Unused bytes on overflow pages........ 850 9.2%
Unused bytes on all pages............. 16934517 10.9%
*** Indices of table NCX ****************************************
Percentage of total database.......... 1.5%
Number of entries..................... 1064066
Bytes of storage consumed............. 17374208
Bytes of payload...................... 11704726 67.4%
Average payload per entry............. 11.00
Average unused bytes per entry........ 2.14
Fragmentation......................... 5.9%
Maximum payload per entry............. 11
Entries that use overflow............. 0 0.0%
Primary pages used.................... 16967
Overflow pages used................... 0
Total pages used...................... 16967
Unused bytes on primary pages......... 2273684 13.1%
Unused bytes on overflow pages........ 0
Unused bytes on all pages............. 2273684 13.1%
The table and its indexes are as follows:
CREATE TABLE ncx (zid INTEGER PRIMARY KEY, str1 TEXT, str2 TEXT);
CREATE INDEX nc_qiid on newconns(zid);
We have many table that have zid (unique) and all will usually have an
index on zid and (zid,...) for other queries.
I'm not sure if there is enough information here?
Best Regards,
Mohit.
8/10/2011 | 10:41 PM.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
