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

Reply via email to