Hi :)

I've been playing with making an FTS5 index on an email corpus (about 80k emails), and I was kind of surprised about the size of the generated index. Here's some files:

 @tanelorn dbtests % ls -l total*.db
 -rw-r--r--  1 cowboy  staff  11120689152 Aug  5 16:45 total_complete.db
 -rw-r--r--  1 cowboy  staff   4610375680 Aug  5 17:44 total_content.db
 -rw-r--r--  1 cowboy  staff   1976729600 Aug  5 17:44 total_fts.db
 -rw-r--r--  1 cowboy  staff     27185152 Aug  5 17:45 total_without_fts.db

total_without_fts.db is the starting database. It's just the metadata without an FTS index on the body, and doesn't contain the actual body content. It's a good representation of what we have at the moment.

total_fts.db is after creating the FTS index with content set to "", detail=none and tokenize='porter unicode61'. The content (the mail body) is stored in the separate database 'total_content.db'

total_complete.db is the default 'detail' setting and the 'body' of the email stored in the same database (it's basically everything all together).

In our setup, the email content is typically stored on a different server to the database (and URLs are generated for clients to fetch stuff directly), so usually we'd only have the equivalent of 'total_without_fts.db' stored. I'm kind of surprised that the size balloons to ~70x the original when adding the FTS index (or almost half the size of the content). Am I being naive or does that mean I'm doing something wrong.

Is there a best-practice for getting FTS functionality (really, I only want the inverted index features) and keeping the size down ?

I would have thought the index would be smaller than this. You could try running:

INSERT INTO ft(ft) VALUES('optimize');

where "ft" is the name of your fts5 table. Then run a VACUUM and see if the db shrinks any.

There might be something funny going on though. Years ago I was using the Enron email corpus for testing. In that case with default settings (largest index) the FTS data was just under 32% of the total database (i.e. of your "total_complete.db" file). With "detail=none" that dropped to around 7.6%.

If you could run [sqlite3_analyzer] on one of the databases and post the number of pages in each database table we might be able to say more.

https://www.sqlite.org/download.html

Dan.