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-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users