The optimize() function was mainly written for privacy reasons (it drops all of the unused terms from the index). I'm a little concerned about promoting it as a general-purpose solution for size and performance, because for larger databases it can be a VERY expensive operation. I would rather find ways to make things work so that the problem optimize() solves isn't such a big problem. For instance, in this case I'd rather have a way to handle fts deletions that would not accumulate cruft in the index, in which case things would eventually reach a steady state. [As I said, I have such a solution, just haven't gotten it polished and checked in.]
If you're worried about things at the level of kilobytes, then you may be outside the core target for fts. optimize() should work reasonably well for that size of dataset. [You might now ask "What is the core target for fts?" It's ill-defined, but my job is Google's Gears and Chrome projects, which should provide some context.] -scott On Mon, Sep 15, 2008 at 2:44 PM, Holger Lembke <[EMAIL PROTECTED]> wrote: > Scott, > > Thanks for the "full table scan hints", I'll change my design. > >> You're seeing two effects. > > I set up a small test. Its a bunch of windows shell scripts (good old dos > stuff). > > http://www.lembke.eu/fts3.zip > > For security reasons the sqlite3.exe is missing. Run CREATE than FILL. > Repeat FILL. > > Database will grow from about 3.615 KB and continue growing. After 100 more > FILLs (40.000 delete+insert actions.) it reaches a size of 85.683 KB. > > With your "optimize" trick I start with 5.032 KB ("optimize" grows the > database from 3.615 KB to 5.032 KB) and end at 6.740 KB after the 100 more > FILLs and an optimize after each FILL. > > Thats very OK for me. :-) > > Thanks for the long answer, I think the WIKI page > http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex should contain the > "optimize" trick. > > -- > Holger Lembke > > _______________________________________________ > 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
