On 05/02/2014 07:30 PM, Stadin, Benjamin wrote:
Is the delete marker also set on old keys on UPDATE? Or just DELETE -> INSERT?
Internally, FTS implements an UPDATE as a DELETE followed by an INSERT. Dan.
I ran into the ever-growing FTS index issue last year. I’m creating DB diffs which also contain some FTS3/4 tables. The tables get constantly updated for the checksum. The DBs were always vacuum’ed, but the growing FTS index caused the db to grow from 50MB to 150MB+ in my case (and I think the „non-grwon“ FTS tables including indices accounted for about 1/3 of the whole DB size according to the SQLite analyzer). On "server side“ (DBs are created on a server and then deployed to some smartphone apps) the issue was easily resolvable with the optimize command. On client side (smartphone) however each diff still causes the DB to grow - optimize() is there also not possible for the same reasons. Ben Am 02.05.14 11:22 schrieb "Dan Kennedy":So I'm thinking a solution might be: * Fix FTS so that it picks this case - when a merge includes so many delete markers that the output is small enough to be deemed a level-N b-tree, not a level-N+1 b-tree, and * Instead of using the default 16-way merges, the app could organize to periodically invoke the "merge=X,Y" command with a smaller Y value (say 2) to limit the maximum size of the index to Y times its optimal size (instead of 16 times). It is an interesting problem. And the above is just guesswork... It would be good to verify experimentally that the index really does grow indefinitely with this kind of input before trying to "fix" anything. Dan._______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
