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