I did some tests with compression back when writing fts. I don't recall if it was the fts1 time-frame or fts2 (which is mostly the same as fts3). It didn't end up integrated into fts because zlib isn't always available to SQLite.
Metadata is sorted and the deltas are encoded, so it's already compressed quite a bit. Running it through zlib probably only gains a few percent on the compression side, and the metadata is read and rewritten somewhat often so any CPU cost is harder to bear. Your results for the contents table pretty much match what I recall. For that table, compression is helpful without much downside. I seem to recall that zlib's costs are mostly on the compression side, so for databases with relatively low update rates, it might be reasonable to amp up the compression ratio. Gains can really depend on the amount of data you are working with relative to the size of your page cache. -scott On Sun, Nov 1, 2009 at 9:53 AM, Alexey Pechnikov <[email protected]> wrote: > Hello! > > See some tests here: > http://geomapx.blogspot.com/2009/11/tests-of-zlib-patched-fts3.html > > The quick results: > 1. The metadata compression is not useful. I think the metadata realization > is nice. > 2. The documents compression decreasing speed of count(*) selects. I think > it's error in FTS3 virtual table realisation or in my compression code. > 3. The database size by compressing a documents is decreased about 3x factor > on my test docset. > 4. The speed of selecting documents snippets is de-facto independent of the > documents compression. > > The patch for the only documents compression is added to > http://geomapx.blogspot.com/2009/11/fts3-compression.html > > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
