On 2 May 2014 10:22, Dan Kennedy <danielk1...@gmail.com> wrote: > > > A query like: > > SELECT level, count(*) AS ntree FROM yourftstablename_segdir; > > will tell you how many b-trees there currently are at each level. Which > might help you figure out what is going on and when you might expect > a merge to actually start removing data from the index. More on the > schema of the underlying data structure used by the FTS index here: > > http://www.sqlite.org/fts3.html#section_9 > > I ran my accelerated test for about 50 hours. At the end of the test I had a database with (393861 pages) 31.% of the space occupied by the index; Level | # of trees 0|3 1|6 2|5 3|6 4|4 5|5 6|2 7|1 8|1
If I then manually ran merge=100,2 until it stops having an effect (my app was using merge=300,8) I reduce the index size to (352121) 27.7% with; Level | # of trees 0|1 1|1 4|1 5|1 8|1 9|1 Running optimise shows that this could be reduced to (145200 pages) 11.4%. So I had about three times the optimal amount of index data. I can see that Dan's suggestion of preventing a merge spawning a Level-N+1 b-tree could prevent this growth. I was also wondering if a new merge function that allows the user to force merging between levels in the index might be worthwhile. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
