understanding FTS5 with content=tbl option
(1.1) By punkish on 2022-10-19 18:07:20 edited from 1.0 [source]
clarifying doubts re FTS5
I only very recently (yesterday) became aware of the "content='<table>' content_rowid='<row name>'" and the UNINDEXED
options of the FTS5 virtual table (shame on me). There is much I have to understand about these options, so in that quest, I have the following questions. Given this schema
CREATE TABLE tbl (id INTEGER PRIMARY, a, b, c, d, description, fulltext);
CREATE VIRTUAL TABLE v_tbl USING fts5(
a UNINDEXED,
b UNINDEXED,
description,
fulltext,
content='tbl',
content_rowid='id'
);
1. In the query SELECT rowid, a, b FROM v_tbl WHERE v_tble MATCH 'word')
, which column, description
or fulltext
, is being searched for word
?
2. How would I restrict the full text search to a specific column, either description
or fulltext
?
Note: The above two can be accomplished via column filters.
- Is the
rowid
inSELECT rowid, a, b FROM v_tbl('word')
guaranteed to be the same astbl.id
? - If I wanted to return columns
tbl.c
ortbl.d
for a full text search ofword
againstfulltext
, I am assuming I would have to join the two tables like so
SELECT
v_tbl.a, v_tbl.b,
tbl.id, tbl.c, tbl.d
FROM
v_tbl JOIN tbl ON v_tbl.rowid = tbl.id
WHERE
v_tbl MATCH 'word'
Would I be better off adding c
and d
also to v_tbl
with the UNINDEXED
option just as I have done with a
and b
?
Finally
- Is there a performance hit with the
content
option? - I cannot understand the usecase for a
contentless
table. What are the advantages ofcontent=''
overcontent='<table>'
? How would I do an fts search if there is no content?