I'm implementing a simple full text search in a sqlite database, using an
additional table for the indexed words, associated record ids, and word
offsets. A typical query will have multiple keywords, plus some additional
fields specified.
CREATE TABLE words (word TEXT, recordid INTEGER, word_offset INTEGER);
CREATE TABLE labels (label TEXT, recordid INTEGER);
I'm thinking of writing my query like this:
SELECT recordid FROM words WHERE word = "whitebeard"
INTERSECT
(SELECT recordid FROM words WHERE word = "wizard"
INTERSECT
(SELECT recordid FROM labels WHERE label = "well-hidden"))
My question is, is this an efficient way to execute such a query? Are each
of the individual selects performed independently, or do they filter based
on the intersect? I will be able to determine which keywords are likely to
return more results, and therefore can order my selects. Right now I'm
operating under the assumption that they filter based on the intersect,
querying from left to right.
Thanks,
Ken
