SQLite Volltextsuche mit FTS5: MATCH & BM25

LIKE skaliert nicht

Wer in SQLite schon mal nach Text gesucht hat, hat vermutlich zu LIKE '%word%' gegriffen. Bei kleinen Tabellen geht das gut, bei großen bricht es zusammen. Es gibt keinen Index, der hier helfen könnte – SQLite muss jede Zeile durchgehen, kleinschreiben und auf den Teilstring prüfen. Wortgrenzen, Ranking, Mehrwortabfragen und Präfixsuche darfst du dann auch noch selbst bauen.

Genau dafür gibt es die SQLite Volltextsuche mit FTS5. Es handelt sich um einen virtuellen Tabellentyp, der einen invertierten Index über deine Textspalten pflegt, eine kleine Abfragesprache versteht und die Treffer per BM25 sortiert. FTS5 ist standardmäßig in SQLite enthalten – du musst keine Extension installieren.

FTS5 Virtual Table erstellen

Eine FTS5-Tabelle legst du mit CREATE VIRTUAL TABLE ... USING fts5(...) an und gibst dabei die Textspalten an, die indiziert werden sollen:

CREATE VIRTUAL TABLE posts USING fts5(title, body);

INSERT INTO posts (title, body) VALUES
  ('SQLite Volltextsuche', 'FTS5 ist schnell und integriert.'),
  ('Indizes in SQLite', 'B-Bäume treiben die meisten Suchvorgänge in SQLite an.'),
  ('Warum SQLite verwenden', 'Es läuft im Prozess und speichert Daten in einer Datei.');

SELECT title FROM posts WHERE posts MATCH 'sqlite';

Drei Dinge sind hier wichtig. Die Spalten haben keine Typen – FTS5 behandelt alles als Text. Der MATCH-Operator bezieht sich auf den Tabellennamen (posts MATCH ...), nicht auf eine einzelne Spalte. Und die Suche ist case-insensitive und tokenisiert, das heißt 'sqlite' findet SQLite in jeder beliebigen Zeile.

Die Abfragesprache des MATCH-Operators

MATCH versteht weit mehr als nur ein einzelnes Wort. Der Suchstring folgt einer eigenen, kleinen Grammatik:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Volltextsuche', 'FTS5 unterstützt Phrasen- und Präfixabfragen.'),
  ('Indizes', 'Ein B-Baum-Index beschleunigt Gleichheitssuchen.'),
  ('Trigger', 'Verwende Trigger, um FTS mit der Quelltabelle synchron zu halten.');

SELECT title FROM posts WHERE posts MATCH 'fts5 AND prefix';
SELECT title FROM posts WHERE posts MATCH '"keep fts"';
SELECT title FROM posts WHERE posts MATCH 'trig*';
SELECT title FROM posts WHERE posts MATCH 'index NOT trigger';

Was die einzelnen Suchanfragen machen:

• 'fts5 AND prefix' — beide Tokens müssen vorkommen (in beliebiger Reihenfolge, an beliebiger Stelle der Zeile).
• '"keep fts"' — exakte Phrase in genau dieser Reihenfolge.
• 'trig*' — Präfixsuche, trifft auf trigger, triggers, trigonometry...
• 'index NOT trigger' — enthält index, aber nicht trigger.

Du kannst die Suche auch auf eine bestimmte Spalte einschränken, etwa mit column:term, also zum Beispiel 'title:sqlite'. Die komplette Grammatik unterstützt zusätzlich Klammern zur Gruppierung und OR für Alternativen — also genau das, was man von einer Suchmaschine gewohnt ist.

Ranking mit BM25

Standardmäßig hängt FTS5 jeder Zeile eine versteckte Spalte rank an. Darin steckt der BM25-Relevanzwert — je kleiner die Zahl, desto besser der Treffer. Sortiere danach, um die relevantesten Ergebnisse zuerst zu bekommen:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite-Tutorial', 'Lerne SQLite von Grund auf.'),
  ('SQLite vs. Postgres', 'Vergleich von SQLite mit Postgres für kleine Anwendungen.'),
  ('SQLite SQLite SQLite', 'Ein Beitrag, der SQLite mehrfach erwähnt.');

SELECT title, rank
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY rank;

Wenn du einzelne Spalten stärker gewichten willst als andere, übergibst du bm25() einfach passende Gewichte – eines pro Spalte, in der Reihenfolge der Deklaration:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite-Indizes', 'Eine kurze Notiz über B-Bäume.'),
  ('Postgres-Notizen', 'Enthält einen Absatz über sqlite zum Vergleich.');

SELECT title, bm25(posts, 10.0, 1.0) AS score
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY score;

Der erste Beitrag gewinnt, weil sqlite im title auftaucht (10-fach gewichtet) und nicht nur im body (1-fach gewichtet). Welche Gewichte sinnvoll sind, hängt davon ab, wie deine App tatsächlich ranken soll.

Den Index synchron halten

Die einfachste FTS5-Tabelle speichert eine eigene Kopie des Textes. Für reine Log-Daten, in die du nur einfügst, reicht das völlig aus. In den meisten Apps gibt es aber bereits eine richtige Tabelle, und FTS soll dieser folgen. Das saubere Muster dafür ist eine External-Content-FTS5-Tabelle in Kombination mit drei Triggern.

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT,
    body TEXT
);

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

CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

INSERT INTO articles (title, body) VALUES
  ('SQLite-Trigger', 'Wie man AFTER INSERT-Trigger verwendet.'),
  ('FTS5-Muster', 'Externer Inhalt hält die Daten an einem Ort.');

SELECT a.id, a.title
FROM articles a
JOIN articles_fts f ON f.rowid = a.id
WHERE articles_fts MATCH 'triggers';

content='articles' weist FTS5 an, den Text nicht selbst zu speichern – stattdessen wird er bei Bedarf aus der Tabelle articles geholt. Die Trigger spiegeln dann jede Schreiboperation in den FTS-Index. Damit ist articles die Single Source of Truth, und articles_fts daneben nur noch die Suchstruktur. Das ist das klassische Muster für External Content FTS5.

Die etwas seltsam wirkende Zeile INSERT INTO articles_fts(articles_fts, ...) VALUES ('delete', ...) ist übrigens die Befehlssyntax von FTS5, mit der man dem Index mitteilt, dass eine Zeile entfernt werden soll.

Snippets und Hervorhebung von Treffern

In Suchergebnissen möchte man meist eine Vorschau anzeigen, in der die Suchbegriffe hervorgehoben sind. Genau dafür stellt FTS5 zwei Funktionen bereit:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Erste Schritte', 'SQLite ist eine kleine, schnelle, eingebettete Datenbank-Engine, die Daten in einer einzelnen Datei auf der Festplatte speichert.');

SELECT
    highlight(posts, 0, '<b>', '</b>') AS title,
    snippet(posts, 1, '<mark>', '</mark>', '...', 10) AS preview
FROM posts
WHERE posts MATCH 'sqlite database';

• highlight(table, column_index, open, close) liefert den vollständigen Spaltentext zurück, wobei die Treffer-Tokens umschlossen werden.
• snippet(table, column_index, open, close, ellipsis, token_count) liefert einen kurzen Textausschnitt rund um den Treffer.

Spaltenindizes sind nullbasiert und folgen der Reihenfolge der Deklaration. Damit hast du die Bausteine für das klassische "Treffer in Gelb"-Verhalten, das jede Such-UI früher oder später braucht.

Stolperfallen, die du kennen solltest

Ein paar Dinge, über die viele anfangs stolpern:

• MATCH funktioniert nur auf FTS-Tabellen. Auf eine normale Spalte kannst du MATCH nicht anwenden. Wenn du eine bestehende Tabelle durchsuchbar machen willst, ist der oben gezeigte Ansatz mit external content fts5 der richtige Weg.
• Vergiss nicht, nach rank zu sortieren. Ohne ORDER BY rank gibt FTS5 die Zeilen in Speicherreihenfolge zurück — und die hat mit Relevanz nichts zu tun.
• Der Tokenizer macht den Unterschied. Der Standard-Tokenizer (unicode61) trennt an Unicode-Wortgrenzen und schreibt alles klein. Für Stemming (also damit run auch running findet) nimmst du den Porter-Tokenizer: USING fts5(body, tokenize='porter').
• FTS5 verzeiht keine Tippfehler. Es macht Präfixsuche, keine Fuzzy-Suche. Wenn du "Meintest du …?" brauchst, ist das eine Schicht oberhalb von FTS5.
• Contentless-Tabellen (content='') sind kleiner, aber verlustbehaftet. Du kannst darin suchen, aber den Originaltext nicht zurückbekommen — nur die rowid. Praktisch, wenn der eigentliche Text woanders liegt.

Als Nächstes: Window Functions

Damit ist das Thema Volltextsuche abgedeckt. Auf der nächsten Seite geht es um eine ganz andere Art fortgeschrittener Abfragen — Window Functions. Damit berechnest du laufende Summen, Rankings und gruppenweise Analysen, ohne dass deine Zeilen zu Aggregaten zusammenfallen.