Keeping a search index in sync in SQL

The index should not be a second copy of the truth, so point it at the table and keep it fed.

CREATE VIRTUAL TABLE posts_fts USING fts5(
    title,
    body,
    content = 'posts',
    content_rowid = 'id'
);

CREATE TRIGGER posts_fts_insert
AFTER INSERT ON posts
BEGIN
    INSERT INTO posts_fts (rowid, title, body)
    VALUES (NEW.id, NEW.title, NEW.body);
END;

CREATE TRIGGER posts_fts_delete
AFTER DELETE ON posts
BEGIN
    INSERT INTO posts_fts (posts_fts, rowid, title, body)
    VALUES ('delete', OLD.id, OLD.title, OLD.body);
END;

How it works

1. content= makes the table the content source; the index stores only terms.
2. Three triggers keep it current: insert, delete, and update as delete plus insert.
3. A delete is spelled as an insert of the special 'delete' command row.

Keywords and builtins used here

• AFTER
• BEGIN
• CREATE
• DELETE
• END
• INSERT
• INTO
• NEW
• OLD
• ON
• TABLE
• TRIGGER
• USING
• VALUES

The run, in numbers

Lines

20

Characters to type

413

Tokens

94

Three-star pace

95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 59 seconds.

Type this snippet

Step 4 of 4 in Full-text search, step 12 of 14 in JSON & search.

← Previous Next →