SQLite Full-text Search Index

CREATE TABLE Diagnosis (
 diagnosisID INTEGER PRIMARY KEY NOT NULL, 
 code TEXT, 
 collect INTEGER NOT NULL, 
 description TEXT 
);

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

I currently have a diagnostic table. I want to use FTS to search for code and description fields. As far as I know, FTS table does not support index, I need to be able to find diagnosis by diagnosis ID very quickly. Do I have to create a second virtual table where all data is duplicated, just for full text search, or I missed A solution where I don’t need to copy all the diagnostic codes and descriptions?

CREATE TABLE Diagnosis (
 diagnosisID INTEGER PRIMARY KEY NOT NULL, 
 code TEXT, 
 collect INTEGER NOT NULL, 
 description TEXT 
);

The result shows that the FTS table has a hidden rowid field, which you can fill in when entering data: < p>

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

You can create an integer field in the standard table that references the FTS table by rowid, and move the column to be text searched into the FTS table.

All the information you need here