Text durchsuchen

Kurz gesagt: Die Volltextsuche findet Wörter im Text über einen invertierten Index, der zu jedem Wort die Zeilen speichert, die es enthalten, statt mit LIKE alles zu lesen.

Problem

Das Suchfeld

Die Seite bekommt ein Suchfeld für die Bewertungen. Die erste Idee ist LIKE. Ein Kunde sucht „äpfel“:

SQLDatenbank: Shop

SELECT id, body FROM reviews WHERE body LIKE '%äpfel%';

SELECT id, body FROM reviews WHERE body LIKE '%äpfel%';

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Nichts, obwohl Bewertung 5 „Saftige, süße Äpfel.“ lautet. In SQLite macht LIKE zwischen großen und kleinen Buchstaben nur beim lateinischen Alphabet ohne Umlaute keinen Unterschied, und Ä und ä sind verschieden. Eine andere Suche:

SQLDatenbank: Shop

SELECT id, body FROM reviews WHERE body LIKE '%rot%';

SELECT id, body FROM reviews WHERE body LIKE '%rot%';

▶ AusführenAbfrage zurücksetzenNeue Datenbank

„rot“ findet auch „Vollkornbrot“, weil LIKE nach einem Stück des Textes sucht, nicht nach einem Wort. Und % am Anfang schließt einen Index aus: Bei einer Million Bewertungen liest die Datenbank für jede Suche eine Million Texte.

Genauer, für Neugierige

Dasselbe passiert mit Ö, Ü und im Griechischen oder Bulgarischen mit jedem großen Buchstaben: LIKE '%öl%' übersieht „Öl“. Der Volltextindex unten macht alle Buchstaben klein, auch diese, und streift außerdem die Akzente und Umlautpunkte ab.

Die Idee

Der invertierte Index

Die Volltextsuche macht das Gegenteil der Tabelle. Die Tabelle sagt „Bewertung 3 enthält die Wörter der, beste, kaffee, im, viertel“. Der invertierte Index sagt „das Wort kaffee steht in den Bewertungen 3, 6 und 13“. Ein Wort zu suchen ist ein Lesen im Index, kein Durchgehen der Texte.

Der Text wird zuerst in Wörter zerlegt (ein Tokenizer). unicode61 schneidet an Leerzeichen und Satzzeichen, macht alle Buchstaben klein, in jedem Alphabet, und entfernt die Akzente. SQLite hält den Index in einer virtuellen Tabelle mit fts4:

SQLDatenbank: Shop

CREATE VIRTUAL TABLE review_search USING fts4(body, tokenize=unicode61);
INSERT INTO review_search (docid, body) SELECT id, body FROM reviews;

SELECT docid, body FROM review_search WHERE review_search MATCH 'äpfel';

CREATE VIRTUAL TABLE review_search USING fts4(body, tokenize=unicode61); INSERT INTO review_search (docid, body) SELECT id, body FROM reviews; SELECT docid, body FROM review_search WHERE review_search MATCH 'äpfel';

▶ AusführenAbfrage zurücksetzenNeue Datenbank

docid ist die Nummer der Zeile im Index. Hier ist es die Nummer der Bewertung, damit er sich mit reviews verbinden lässt. MATCH ist die Suche im Index, nicht = oder LIKE. Und jetzt wird „Äpfel“ gefunden, das LIKE übersehen hat: „äpfel“, „Äpfel“ und „apfel“ sind im Index dasselbe Wort.

So sehen die Wörter im Index aus (fts4aux ist eine Tabelle, die die Wörter zeigt und in wie vielen Dokumenten jedes steht):

SQLDatenbank: Shop

CREATE VIRTUAL TABLE review_terms USING fts4aux(review_search);

SELECT term, documents
FROM review_terms
WHERE col = '*' AND term LIKE 'k%'
ORDER BY term;

CREATE VIRTUAL TABLE review_terms USING fts4aux(review_search); SELECT term, documents FROM review_terms WHERE col = '*' AND term LIKE 'k%' ORDER BY term;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Beachte kase: Das ä ist weg, „Käse“ ist im Index ohne Umlaut gespeichert.

Die Idee

Wortformen

Im Index sind „frisch“ und „Frische“ zwei verschiedene Wörter. Die einfachste Hilfe ist ein Präfix: frisch* bedeutet jedes Wort, das mit „frisch“ beginnt:

SQLDatenbank: Shop

SELECT docid, body FROM review_search WHERE review_search MATCH 'frisch';
SELECT docid, body FROM review_search WHERE review_search MATCH 'frisch*';

SELECT docid, body FROM review_search WHERE review_search MATCH 'frisch'; SELECT docid, body FROM review_search WHERE review_search MATCH 'frisch*';

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Ein Präfix ist grob: frisch* würde auch „frischgebacken“ finden. Echte Suchmaschinen haben für jede Sprache Analysatoren, die Wörter auf den Stamm zurückführen (Stemming): „Tomaten“ wird zu „Tomate“. SQLite hat so etwas nur für Englisch (tokenize=porter), Elasticsearch und OpenSearch haben es für viele Sprachen. Im Deutschen kommen die zusammengesetzten Wörter dazu: „Vollkornbrot“ ist ein Wort, und weder brot noch brot* findet es. Gute deutsche Analysatoren zerlegen es in „voll“, „korn“ und „brot“, und das kann kein Präfix.

Probier es aus

Tomaten

Finde im Index alle Bewertungen über Tomaten, in welcher Form das Wort auch steht (die Spalten docid und body).

SQLDatenbank: Shop

SELECT docid, body FROM review_search LIMIT 3;

SELECT docid, body FROM review_search LIMIT 3;

Prüfen▶ Ausführen

Die Idee

Mehr als ein Wort

Zwei Wörter hintereinander bedeuten AND: Die Bewertung muss beide haben. OR bedeutet mindestens eins davon:

SQLDatenbank: Shop

-- Beide Wörter (AND ist implizit)
SELECT docid, body FROM review_search WHERE review_search MATCH 'kaffee lieferung';
-- Das eine oder das andere
SELECT docid, body FROM review_search WHERE review_search MATCH 'äpfel OR tomaten';

-- Beide Wörter (AND ist implizit) SELECT docid, body FROM review_search WHERE review_search MATCH 'kaffee lieferung'; -- Das eine oder das andere SELECT docid, body FROM review_search WHERE review_search MATCH 'äpfel OR tomaten';

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Eine Wortgruppe in Anführungszeichen sucht die Wörter direkt nebeneinander und in dieser Reihenfolge. NEAR/3 sucht die zwei Wörter höchstens 3 Wörter auseinander:

SQLDatenbank: Shop

-- Genau diese Wortgruppe, die Wörter direkt nebeneinander
SELECT docid, body FROM review_search WHERE review_search MATCH '"wie neu"';
-- Die zwei Wörter höchstens 3 Wörter auseinander
SELECT docid, body FROM review_search WHERE review_search MATCH 'pfanne NEAR/3 gut';

-- Genau diese Wortgruppe, die Wörter direkt nebeneinander SELECT docid, body FROM review_search WHERE review_search MATCH '"wie neu"'; -- Die zwei Wörter höchstens 3 Wörter auseinander SELECT docid, body FROM review_search WHERE review_search MATCH 'pfanne NEAR/3 gut';

▶ AusführenAbfrage zurücksetzenNeue Datenbank

NOT entfernt die Dokumente mit einem bestimmten Wort: kaffee NOT abgelaufen.

Genauer, für Neugierige

FTS4 hat zwei Abfragesyntaxen. In der Standardsyntax schreibt man „ohne das Wort“ mit einem Minus (kaffee -abgelaufen), in der erweiterten mit NOT. sql.js, das SQLite im Browser ausführt, nutzt die erweiterte, und dort ist -abgelaufen einfach das Wort „abgelaufen“. Die erweiterte erlaubt auch Klammern: (äpfel OR tomaten) NOT zerrissen. In Elasticsearch ist dasselbe eine bool-Abfrage mit must, should und must_not.

Probier es aus

Kaffee ohne Beschwerden

Finde die Bewertungen über Kaffee, die nicht „abgelaufen“ erwähnen (die Spalten docid und body).

SQLDatenbank: Shop

SELECT docid, body FROM review_search WHERE review_search MATCH 'kaffee';

SELECT docid, body FROM review_search WHERE review_search MATCH 'kaffee';

Prüfen▶ Ausführen

Die Idee

Ausschnitte und Reihenfolge

Eine Suchmaschine zeigt einen Ausschnitt mit markiertem Wort. snippet macht das von selbst:

SQLDatenbank: Shop

SELECT docid, snippet(review_search, '[', ']', '…') AS found
FROM review_search
WHERE review_search MATCH 'vollkornbrot';

SELECT docid, snippet(review_search, '[', ']', '…') AS found FROM review_search WHERE review_search MATCH 'vollkornbrot';

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Die ersten Treffer sollten die relevantesten sein. Echte Suchmaschinen ordnen sie nach BM25: Je seltener das Wort in allen Dokumenten und je öfter es in diesem steht, desto höher, und ein kurzer Text mit dem Wort schlägt einen langen. Die Tabelle fts4aux aus der Karte zum Index liefert dafür die Zahlen: in wie vielen Dokumenten jedes Wort steht. FTS4 ordnet nicht von selbst (FTS5 und Elasticsearch haben BM25 eingebaut), deshalb kommt die Reihenfolge hier aus ORDER BY.

Übung

Suchen mit den Produkten

1. Der Index hält nur den Text. Der Name des Produkts und die Note stehen in den Tabellen.

Finde die Bewertungen über Kaffee mit dem Namen des Produkts und der Note (die Spalten name, rating und body), von der höchsten Note abwärts.

SQLDatenbank: Shop

SELECT docid FROM review_search WHERE review_search MATCH 'kaffee';

SELECT docid FROM review_search WHERE review_search MATCH 'kaffee';

Prüfen▶ Ausführen

2. Der Index ist eine eigene Tabelle und weiß nicht von selbst, wann sich reviews ändert. Ein Trigger aktualisiert ihn bei jeder neuen Bewertung:

SQLDatenbank: Shop

CREATE TRIGGER review_added AFTER INSERT ON reviews
BEGIN
  INSERT INTO review_search (docid, body) VALUES (new.id, new.body);
END;

INSERT INTO reviews (product_id, customer_id, rating, body, created_at)
VALUES (17, 4, 5, 'Der Kaffee kam frisch geröstet an.', '2026-10-25');

SELECT docid, body FROM review_search WHERE review_search MATCH 'kaffee';

CREATE TRIGGER review_added AFTER INSERT ON reviews BEGIN INSERT INTO review_search (docid, body) VALUES (new.id, new.body); END; INSERT INTO reviews (product_id, customer_id, rating, body, created_at) VALUES (17, 4, 5, 'Der Kaffee kam frisch geröstet an.', '2026-10-25'); SELECT docid, body FROM review_search WHERE review_search MATCH 'kaffee';

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Schreibe einen Trigger review_removed, der die Bewertung aus review_search entfernt, wenn sie aus reviews gelöscht wird.

SQLDatenbank: Shop

SELECT COUNT(*) FROM review_search;

SELECT COUNT(*) FROM review_search;

Prüfen▶ Ausführen

Die Idee

Wann eine eigene Suchmaschine

Für die Suche in ein paar tausend oder Millionen Texten reicht der Volltextindex der Datenbank: FTS in SQLite, tsvector und ein GIN-Index in PostgreSQL, FULLTEXT in MySQL. Eine eigene Suchmaschine (Elasticsearch, OpenSearch, Meilisearch, Typesense) lohnt sich, wenn die Suche die Hauptaufgabe der Seite ist: Analysatoren für viele Sprachen, Korrektur von Tippfehlern („Kafee“ → „Kaffee“), Filter nach Kategorie mit den Anzahlen daneben, Vorschläge beim Tippen und viele Maschinen. Dann werden die Daten dorthin kopiert, meist aus einem Ereignisstrom, und die Wahrheit bleibt in der Datenbank.

Genauer, für Neugierige

In PostgreSQL ist dieselbe Suche WHERE to_tsvector('german', body) @@ to_tsquery('german', 'kaffee') mit dem Index CREATE INDEX ON reviews USING gin (to_tsvector('german', body)). In Elasticsearch bekommt das Feld "analyzer": "german", und die Suche ist {"query": {"match": {"body": "kaffee"}}}.

Test

Teste dich

1. Warum ist LIKE '%kaffee%' bei einer Million Bewertungen langsam?

Das % am Anfang schließt einen Index aus, und die Datenbank liest jeden Text von vorn bis hintenWeil LIKE ein alter Befehl istWeil Kaffee beliebt ist

2. Im Volltextindex findet MATCH 'frisch' nicht „Frische Milch wie vom Bauernhof“. Warum?

„Frische“ ist im Index ein anderes Wort als „frisch“Das große FDie Bewertung ist schlecht

3. Was macht ein invertierter Index?

Er sortiert die Bewertungen nach der NoteEr speichert die Texte rückwärtsEr hält zu jedem Wort fest, in welchen Dokumenten (und wo darin) es vorkommt

4. Die Bewertungen liegen in reviews und die Suche in review_search. Was passiert, wenn jemand eine Bewertung löscht und es keinen Trigger gibt?

Nichts, der Index merkt es von selbstDie Suche findet die gelöschte Bewertung weiterhinDie Tabelle reviews kommt zurück

Prüfen

Lektion von vorn beginnen

Verwandte Lektionen

• Baut auf: INSERT: Zeilen einfügen · DELETE: Zeilen löschen · Trigger · Indizes
• Geht weiter in: Suchen nach Bedeutung · Wie du eine Datenbank wählst
• Aufgaben zum Thema: Arten von Datenbanken