On Fri, Jul 18, 2008 at 12:54 AM, Jiri Hajek <[EMAIL PROTECTED]> wrote:
> from what I have read about FTS3, it stores the original data as well as the
> index needed for fast full-text access. Therefore, in several posts here it
> was recommended to use two tables joined one-to-one in case it's needed to
> store both text data and some other, possibly numeric data. Let's use an
> example:
>
> Table1:
> ID Int
> Value1 Int
> Value2 Int
>
> Table2 (FTS3):
> Text1
> Text2
>
> However, according to my tests, the join needed in case I want to get all
> data in tables Table1 and Table2 takes some time, it looks like it's almost
> twice as slow as having all the data in one table only.
It's hard to say - you might want to describe your tables and the join
by simply including the SQL itself, rather than a loose description of
it. It may be that there's a simple change which will clear things
up.
> My question to SQLite experts is whether there is any solution to this
> choice between speed and DB size. Preferrably I'd need to specify that just
> some fields of an ordinary table participate in FTS3 index, but that
> obviously isn't possible, at least not yet. Are there any plans for this?
No plans at this time. The goal of the current functionality
breakdown was to keep SQLite core from having to understand too much
about FTS, and FTS from having to understand too much SQL. For
instance, one way you could accomplish this would be to have the FTS
create statement be able to handle something like this:
CREATE VIRTUAL TABLE t USING fts3(
ID integer primary key autoincrement,
Value1 INTEGER,
Value2 INTEGER,
Text1 TEXT,
Text2 TEXT
);
As you start making it more and more powerful, FTS has to handle
things like "Text1 TEXT UNIQUE" or "Text1 TEXT CHECK(Text1 IN ('a',
'b', 'c'))", and make the appropriate decisions based on that, and in
the limit FTS becomes a little SQL engine of its own. I've thought a
little about how you could simply reflect things into the backing
tables, but it seems likely that there will be some subtle issues to
think through. Even then, you would need some way to have the
equivalent of a non-FTS index on such a table, I have no idea how to
model that.
-scott
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
