Fts5 indexes
An FTS5 index is a database structure that maps tokens produced from the indexed columns of an FTS5 virtual table to their occurrences in that table.
From the SQLite FTS5 documentation :
The FTS index is an ordered key-value store where the keys are document terms or term prefixes and the associated values are “doclists”. A doclist is a packed array of varints that encodes the position of each instance of the term within the FTS5 table. The position of a single term instance is defined as the combiation of:
- The rowid of the FTS5 table row it appears in
- The index of the column the term instance appears in (columns are numbered from left to right starting from zero), and
- The offset of the term within the column value (i.e. the number of tokens that appear within the column value before this one).
An fts5vocab table can give some insight about what’s going on:
“The fts5vocab table module allows users to extract information from an FTS5 full-text index directly.”
The fts5vocab table below was created with the type “instance”. It contains one row for each term instance that’s stored in the associated FTS index. The term column is the term from the FTS5 index, the doc is the rowid of the document that contains the term instance. The col is the name of the column that contains the instance. The offset is the index of the term instance within the column.
sqlite> CREATE VIRTUAL TABLE temp.token_test
...> USING fts5(title, content);
sqlite> INSERT INTO temp.token_test
...> VALUES ('Structure and Interpretation of Computer Programs', 'We are about to study the idea of a computational process. Computational processes are abstract beings that inhabit computers');
sqlite> INSERT INTO temp.token_test
...> VALUES ('Eloquent JavaScript', 'We think we are creating the system for our own purposes. We believe we are making it in our own image... But the computer is not really like us.');
sqlite> CREATE VIRTUAL TABLE temp.token_vocab
...> USING ftsvocab(token_test, instance);
Error near line 86: no such module: ftsvocab
sqlite> CREATE VIRTUAL TABLE temp.token_vocab
...> USING fts5vocab(token_test, instance);
sqlite> SELECT * FROM temp.token_vocab;
╭────────────────┬─────┬─────────┬────────╮
│ term │ doc │ col │ offset │
╞════════════════╪═════╪═════════╪════════╡
│ a │ 1 │ content │ 8 │
│ about │ 1 │ content │ 2 │
│ abstract │ 1 │ content │ 14 │
│ and │ 1 │ title │ 1 │
│ are │ 1 │ content │ 1 │
│ are │ 1 │ content │ 13 │
│ are │ 2 │ content │ 3 │
│ are │ 2 │ content │ 14 │
│ beings │ 1 │ content │ 15 │
│ believe │ 2 │ content │ 12 │
│ but │ 2 │ content │ 21 │
│ computational │ 1 │ content │ 9 │
│ computational │ 1 │ content │ 11 │
│ computer │ 1 │ title │ 4 │
│ computer │ 2 │ content │ 23 │
│ computers │ 1 │ content │ 18 │
│ creating │ 2 │ content │ 4 │
│ eloquent │ 2 │ title │ 0 │
│ for │ 2 │ content │ 7 │
│ idea │ 1 │ content │ 6 │
│ image │ 2 │ content │ 20 │
│ in │ 2 │ content │ 17 │
│ inhabit │ 1 │ content │ 17 │
│ interpretation │ 1 │ title │ 2 │
│ is │ 2 │ content │ 24 │
│ it │ 2 │ content │ 16 │
│ javascript │ 2 │ title │ 1 │
│ like │ 2 │ content │ 27 │
│ making │ 2 │ content │ 15 │
│ not │ 2 │ content │ 25 │
│ of │ 1 │ title │ 3 │
│ of │ 1 │ content │ 7 │
│ our │ 2 │ content │ 8 │
│ our │ 2 │ content │ 18 │
│ own │ 2 │ content │ 9 │
│ own │ 2 │ content │ 19 │
│ process │ 1 │ content │ 10 │
│ processes │ 1 │ content │ 12 │
│ programs │ 1 │ title │ 5 │
│ purposes │ 2 │ content │ 10 │
│ really │ 2 │ content │ 26 │
│ structure │ 1 │ title │ 0 │
│ study │ 1 │ content │ 4 │
│ system │ 2 │ content │ 6 │
│ that │ 1 │ content │ 16 │
│ the │ 1 │ content │ 5 │
│ the │ 2 │ content │ 5 │
│ the │ 2 │ content │ 22 │
│ think │ 2 │ content │ 1 │
│ to │ 1 │ content │ 3 │
│ us │ 2 │ content │ 28 │
│ we │ 1 │ content │ 0 │
│ we │ 2 │ content │ 0 │
│ we │ 2 │ content │ 2 │
│ we │ 2 │ content │ 11 │
│ we │ 2 │ content │ 13 │
╰────────────────┴─────┴─────────┴────────╯