Geek Answers Handbook

SQLite Full-Text Search Indexes

I currently have a diagnosis table. I want code and description fields to be searchable with FTS. As I understand it, FTS tables do not support indexes, and I really need to quickly find the Diagnostic Diagnosis ID. Should I create a second virtual table with all the duplicate data for full-text search, or am I missing a solution in which I do not need to duplicate all my code codes and descriptions?

CREATE TABLE Diagnosis (  
    diagnosisID     INTEGER PRIMARY KEY NOT NULL,  
    code            TEXT,  
    collect         INTEGER NOT NULL,  
    description     TEXT  
);
+5
source share
1 answer

It turns out that the FTS table has a hidden field rowidthat you can fill in when entering data:

sqlite> create virtual table test1 using fts3;
sqlite> insert into test1 values ("This is a document!");
sqlite> insert into test1(docid,content) values (5,"this is another document");
sqlite> select rowid,* from test1;
1|This is a document!
5|this is another document

, FTS rowid, , , FTS.

:)

+5

More articles: