Re: [sqlite] After deleting data from a FTS table and doing VACUUM, unwanted data remains

Tuesday, February 25, 2020, 3:00:09 PM, Luuk wrote: [tests snipped] > So, the index does not grow indefinitely > On 25-2-2020 14:00, Graham Holden wrote: >> It is an interesting problem. And the above is just guesswork... It would >> be good to verify experimentally that the index really does g

Re: [sqlite] After deleting data from a FTS table and doing VACUUM, unwanted data remains

script: #!/bin/bash if [ ! -f test.db ] ; then sqlite3 test.db "CREATE VIRTUAL TABLE tab USING fts5(x)"; fi sqlite3 test.db ".import wikipedia tab" a=$(sqlite3 test.db "SELECT count(*) FROM tab_data") echo "# records after import: $a" sqlite3 test.db "DELETE FROM tab" a=$(sqlite3 test.db "SELE

Re: [sqlite] After deleting data from a FTS table and doing VACUUM, unwanted data remains

This might be to do with how an FTS index works under the hood, involving various levels of "b-tree" that grow as entries are added, but aren't always shrunk when entries are deleted. There were a bunch of emails on the list around 4th to the 13th May 2014: sample below from Dan Kennedy (one of th

[sqlite] After deleting data from a FTS table and doing VACUUM, unwanted data remains

Dear sqlite users, I noticed that when you delete lines from a FTS virtual table, somehow there is some data remaining in the sqlite db, so that's it does not shrink much in size. $ sqlite3 test.sql "CREATE VIRTUAL TABLE tab USING fts5(x)" $ curl -s https://www.wikipedia.org | tr -cd '[:alnum:][