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.
Thanks!
-P
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
