Hey,
Just saw your post. Really interesting stuff. Thanks a bunch!
- Jens
On Apr 16, 12:24 pm, Alexey Pechnikov <pechni...@mobigroup.ru> wrote:
> Hello!
>
> On Thursday 15 April 2010 11:21:17 Jens wrote:
>
> > I'd appreciate any feedback you might have one this. Also, does anyone
> > have experience with sqlite+ft3 and high-availability solutions? Has
> > anyone done any benchmarking of fts3?
>
> I did test FTS3 on about 400 millions of records and there is speed
> regressions
> for inserts and selects operations. The test database was about 60Gb. FTS3
> extension has a very nice index realization - more better than standart SQLite
> index on high-selective values.
>
> But you may remember about virtual table limitations. As example for joins
> use solution like to:
>
> CREATE TABLE address
> (
> id INTEGER PRIMARY KEY AUTOINCREMENT,
> timestamp INTEGER NOT NULL DEFAULT (strftime('%s',julianday('now'))),
> userid INTEGER NOT NULL DEFAULT (user())
> );
> CREATE VIRTUAL TABLE address_fts USING fts3(uid, datetime, fields, tracks,
> TOKENIZE icu ru_RU);
>
> CREATE VIEW view_address AS
> select orig.rowid as rowid, orig.*, content.c0uid as uid,
> content.c1datetime as datetime, content.c2fields as fields, content.c3tracks
> as tracks
> from address as orig, address_fts_content as content
> where content.rowid=orig.rowid;
>
> A joins with FTS3 tables is not effective and is needed to use the real
> %_content table.
>
> And you can use my patches for zlib-compression for FTS3. I'm planning to
> make the "fts3z" extension because I want to use as original FTS3
> as FTS3 with compression together.
>
> Sqlite is nice for all projects where are a lot of selects and a few
> insert/update operations per second. For typical web-project a selects
> operations
> are dominate.
>
> See some tests here:
> geomapx.blogspot.com/2010/04/sqlite-index-degradation-tests.html
> geomapx.blogspot.com/2010/01/sqlite-fts3.html
> geomapx.blogspot.com/2009/11/postgresql-81-vs-sqlite-3620-in-real.html
> geomapx.blogspot.com/2009/11/degradation-of-indexing-speed.html
> geomapx.blogspot.com/2009/09/sqlite-3617-mobigroup2.html
>
> P.S. And we wait for releasing of the WAL journal mode, of course :-)
>
> Best regards, Alexey Pechnikov.http://pechnikov.tel/
> _______________________________________________
> sqlite-users mailing list
> sqlite-us...@sqlite.orghttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> Subscription
> settings:http://groups.google.com/group/sqlite_users/subscribe?hl=en
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
