Sqlite using fts5 table to perform all queries instead of main table

Question

So let's say I have two tables: users and users_fts where users_fts being the search table. So users has two fields name and surname.

Normally I would create index for both of em'. But since I have users_fts should I need to create index for name and surname in users? And is there any caveat of using users_fts to perform all queries instead of using the main table users?

Answer 1

SQLite provides full text search, and I am assuming that is what you are using from your table name. I will show the sample code using FTS5 but you can adapt it backward if you need to. If you have a users table something like:

CREATE TABLE users(
    id INTEGER PRIMARY KEY, 
    name TEXT NOT NULL, 
    surname TEXT NOT NULL
);

then you made your full text search table using something like this:

CREATE VIRTUAL TABLE users_fts USING fts5(
    name, 
    surname, 
    content='user', 
    content_rowid='id'
);

At this point you we have to make sure that the records in the users table get indexed for the full text search and this can be done using triggers on the users table to do it automatically. The triggers would look like:

CREATE TRIGGER users_ai AFTER INSERT ON users
    BEGIN
        INSERT INTO users_fts (rowid, name, surname)
        VALUES (new.id, new.name, new.surname);
    END;

CREATE TRIGGER users_ad AFTER DELETE ON users
    BEGIN
        INSERT INTO users_fts (users_fts, rowid, name, surname)
        VALUES ('delete', old.id, old.name, old.surname);
    END;

CREATE TRIGGER users_au AFTER UPDATE ON users
    BEGIN
        INSERT INTO users_fts (users_fts, rowid, name, surname)
        VALUES ('delete', old.id, old.name, old.surname);
        INSERT INTO users_fts (rowid, name, surname)
        VALUES (new.id, new.name, new.surname);
    END;

With all of this in place, you can now use the users_fts table to perform full text searches.

So how do indexes on the users table affect the users_fts table? If you are only searching using the users_fts table, then indexes on the users table do not matter. I do not know how you plan on populating the users_fts table, but if you use triggers on the users table then the proposed indexes on the users table still do not matter. If you are manually keeping the users_fts table up to date, then the answer is that indexes on the users table might affect performance. Most people I know use the trigger approach, and that is what I do which let's you forget about manually maintaining the full text search and you get the added benefit that you can ignore indexes on the source table in regards to populating the full text search. Remember though this is the scenario where you are not querying the users table at all - if you have any queries against the users table, then you may need supporting indexes.

You also asked if there are any caveats to the approach of using the users_fts table for your queries - as long as you keep the users_fts table up to date, then there is no drawback to this approach. If you need full text search features and ranking, this is a very convenient approach baked into SQLite. It will require more storage space, but you can minimize that impact by using an external content table (I showed this when I created the users_fts table). You can read some details about it in section 4.4.2 in the FTS5 extension documentation at https://www.sqlite.org/fts5.html

This approach works well for full text search capabilities and as long as you maintain the indexes, it should work well and provide you with more capabilities for searching as well as ranking. In my experience, most full text searches are faster than what you could do using standard SQL functions and operators (such as LIKE, etc.) and are far more powerful.