On Tue, 30 Apr 2013 23:56:35 -0700 (PDT)
Paul Vercellotti <pverce...@yahoo.com> wrote:
>
>
>
> Hi All,
>
> We've got some trouble with FTS4 queries taking too long in which
> we're looking for a subset of matching records in the FTS table, as
> narrowed by a non-FTS table.
>
> CREATE TABLE metadata( key AS INTEGER PRIMARY KEY, sectionID AS
> INTEGER ); CREATE INDEX sectionIdx on metadata(sectionID);
> CREATE VIRTUAL TABLE ftstable USING fts4( content );
>
> There is a pseudo foreign key relationship between the rowid's of
> ftstable and key in metadata. There's one entry in metadata for each
> entry in ftstable.
>
> Our query is of the general form:
>
> SELECT key FROM metadata WHERE sectionID = 12345 AND
> key IN ( SELECT rowid FROM ftstable WHERE content MATCH 'the' ),
> LIMIT 100;
>
> And it's quite slow (like a minute or two for a 1GB to 3GB database).
> Now you'll see the cause of the slowness - the subquery matching
> 'the' in the fts table returns millions of results in our case.
> Presumably that gets pumped into a temporary place, then joined with
> the first part of the query, which selects only those records which
> match the sectionID that we're interested in. In fact, of those,
> we're only interested in the first hundred.
>
> If I move "LIMIT 100" to the subquery, like this
>
> SELECT key FROM metadata WHERE sectionID = 12345 AND
> key IN ( SELECT rowid FROM ftstable WHERE content MATCH 'the' LIMIT
> 100);
>
> It's lightning fast again (6ms), but of course that doesn't work
> because we don't find the 100 records that match the sectionID in the
> outer query.
>
> Is there any good way to tell the MATCH query to only consider those
> records selected by the first part of the query, or any other way to
> speed this up? I feel like it's doing more work than is ideal to
> find the records that match the sectionID and contain the text.
Not really. All FTS has to work with is an index that maps from 'the'
to a sorted list of docids.
If you make it a join it might be a little better:
SELECT key FROM ftstable, metadata
WHERE sectionID=12345 AND
AND ftstable.docid=metadata.key
AND content MATCH 'the'
LIMIT 100;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
