A full-text index in SQL

LIKE scans every row. A full-text index knows which rows hold the word.

CREATE VIRTUAL TABLE doc_search USING fts5(
    title,
    body,
    slug UNINDEXED,
    tokenize = 'porter unicode61'
);

INSERT INTO doc_search (title, body, slug)
VALUES ('Indexing text', 'Full-text search finds words, not substrings.',
        'indexing-text'),
       ('Query plans', 'Read the plan before optimizing the query.',
        'query-plans');

How it works

1. CREATE VIRTUAL TABLE ... USING fts5 lists the columns to index.
2. UNINDEXED keeps a column retrievable without adding it to the index.
3. Inserting into the virtual table is what builds the index.

Keywords and builtins used here

• CREATE
• INSERT
• INTO
• TABLE
• USING
• VALUES

The run, in numbers

Lines

12

Characters to type

319

Tokens

46

Three-star pace

95 tpm

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

Type this snippet

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

← Previous Next →