On Wed, Sep 17, 2008 at 12:15 AM, Holger Lembke <[EMAIL PROTECTED]> wrote: >>> So it reads: grows from 3 MB to 85 MB. >> That IS an important distinction! > > So what to do? > > a.) let it grow. > b.) do optimize() (but how often and how to detect when it is needed?) > c.) create a new database by reindexing my data. (same problems as b.) > d.) ignore the problem until complains come in and get nasty > > I tend to d.)
I would argue against optimize() on a "when it is needed" basis. The problem is detecting when it is needed, and figuring out when it is possible. Meanwhile, you've effectively bought into ignoring the problem anyhow. Instead, if you think optimize() adds value, you might try just doing it periodically. Like once a day, or once a week, or every 5000 items, or something like that. I would base it on how many items have run off the end of the treadmill. For instance, you might optimize() after you've deleted as many items as the table holds, on average. Or 2x or 4x as many items. For fts3, reindexing (basically rename, recreate, copy, drop the renamed table) is pretty expensive compared to optimize(). It has to re-tokenize all of the content. I know of one customer that has separate tables for current data and older data (which isn't changing as much), and every week or month or somesuch they finalize it by renaming the table to a new location and running optimize() on it. Hmm, and as a last item, maybe try to develop a test representative of a long period of time. If your dataset tends to have terms with a regular distribution (a few terms represent most hits), then over time the growth curve might moderate. The worst-case time to measure is when the old positive hits (from INSERT) _and_ the negative hits (from DELETE) are both in the index. Might be worth reading the couple pages of comments at the top of fts3.c just for a sense of how this plays out over time. -scott _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
