Re: [sqlite] Does a search on an external content FTS table use the index of the underlying content?

[E. Timothy Uy]

Thanks Richard. In the case of using a index modifying tokenizer like
Porter, is there a way to know which of the MATCH terms it actually matched
on or is that lost information?

On Wed, Sep 19, 2012 at 3:44 AM, Richard Hipp  wrote:

> On Wed, Sep 19, 2012 at 2:42 AM, E. Timothy Uy  wrote:
>
> > Let's say I have a table Data_content(term TEXT UNIQUE) which has an
> > indexed column 'term'. I then create an FTS4 table using
> >
> > CREATE VIRTUAL TABLE Data USING fts4 (content="Data_content", term);
> >
> > If I later search using SELECT * FROM Data WHERE term IN (...), does it
> use
> > the indexes generated in Data_content, or do I have to generate another
> set
> > (or search Data_content directly).
> >
>
> FTS4 indices are only used with the MATCH operator.  And you cannot create
> an index on a virtual table.  So you'll either want to translate your IN
> into a MATCH or else search the Data_content table directly.
>
>
>
> >
> > Thanks.
> >
> > Respectfully,
> > Tim
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does a search on an external content FTS table use the index of the underlying content?

[Richard Hipp]

On Wed, Sep 19, 2012 at 2:42 AM, E. Timothy Uy  wrote:

> Let's say I have a table Data_content(term TEXT UNIQUE) which has an
> indexed column 'term'. I then create an FTS4 table using
>
> CREATE VIRTUAL TABLE Data USING fts4 (content="Data_content", term);
>
> If I later search using SELECT * FROM Data WHERE term IN (...), does it use
> the indexes generated in Data_content, or do I have to generate another set
> (or search Data_content directly).
>

FTS4 indices are only used with the MATCH operator.  And you cannot create
an index on a virtual table.  So you'll either want to translate your IN
into a MATCH or else search the Data_content table directly.



>
> Thanks.
>
> Respectfully,
> Tim
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Does a search on an external content FTS table use the index of the underlying content?

[E. Timothy Uy]

Let's say I have a table Data_content(term TEXT UNIQUE) which has an
indexed column 'term'. I then create an FTS4 table using

CREATE VIRTUAL TABLE Data USING fts4 (content="Data_content", term);

If I later search using SELECT * FROM Data WHERE term IN (...), does it use
the indexes generated in Data_content, or do I have to generate another set
(or search Data_content directly).

Thanks.

Respectfully,
Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users