SQLite User Forum

understanding FTS5 with content=tbl option
Login

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.

  1. Is the rowid in SELECT rowid, a, b FROM v_tbl('word') guaranteed to be the same as tbl.id?
  2. If I wanted to return columns tbl.c or tbl.d for a full text search of word against fulltext, 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

  1. Is there a performance hit with the content option?
  2. I cannot understand the usecase for a contentless table. What are the advantages of content='' over content='<table>'? How would I do an fts search if there is no content?