Volltextsuche mit SQLite: FTS5 und Fuzzy Search

In den bisherigen Teilen dieser Serie haben wir SQLite als eingebettete Datenbank für zum Beispiel .NET-Anwendungen kennengelernt: von der Performance über die Integration bis zur Verteilung. Doch all diese Artikel setzten eine Annahme voraus: Die Daten sind strukturiert. Ein Produkt hat einen Namen und einen Preis. Mit WHERE-Klauseln und Indizes funktioniert das gut. Doch die Realität ist messy: Benutzer tippen Suchbegriffe ein, machen Tippfehler und erwarten, dass die Anwendung ihre ungenaue Anfrage versteht. Genau hier betritt FTS5 die Bühne, die beeindruckender Volltext-Search-Engine von SQLite.

Was ist FTS5?

FTS steht für Full Text Search. FTS5 ist nicht einfach nur ein besserer Index für LIKE-Abfragen, sondern ein Indexierungsmodul mit eigener Speicherstruktur, Abfrageschnittstelle und Ranking-Engine. Während reguläre SQLite-Tabellen strukturierte Daten speichern, speichert FTS5 invertierte Indizes, eine Datenstruktur, die von Suchmaschinen seit Jahrzehnten verwendet wird. Das bedeutet konkret: Bei einer Suche nach „Laptop“ schlägt FTS5 den Begriff im Index nach, findet sofort alle Dokumente und gibt sie zurück, nicht durch Durchsuchen jeder Zeile wie LIKE. Das ist um ein Vielfaches schneller.

FTS5-Grundlagen: Virtuelle Tabellen und MATCH

Um FTS5 zu nutzen, erstellen wir eine virtuelle Tabelle:

CREATE VIRTUAL TABLE products_fts USING fts5(
  name,
  description,
  content = products,
  content_rowid = id
);

Das ist eine externe Content-Tabelle. Die Spalten verweisen auf Spalten in einer bestehenden products-Tabelle. FTS5 verwaltet nur den invertierten Index und referenziert Originalzeilen über content_rowid. Das spart Speicher und vereinfacht Updates durch Trigger. Mit dieser Tabelle führen wir FTS5-Abfragen aus:

SELECT p.id, p.name, p.price
FROM products p
JOIN products_fts fts ON p.id = fts.rowid
WHERE fts MATCH 'laptop AND gaming'
ORDER BY rank;

Die MATCH-Clause ist das Herzstück von FTS5. Sie erlaubt Boolean-Operatoren (AND, OR, NOT), Phrasen-Suche in Anführungszeichen und Wildcards mit Asterisken. Sehr wichtig: ORDER BY rank nutzt den BM25-Ranking-Algorithmus von FTS5. BM25 liefert bessere Suchergebnisse als einfache Wort-Frequency-Counts. Ein Produkt mit dem Suchbegriff in der Überschrift wird höher bewertet als eines mit dem Begriff nur in der Beschreibung. Seltene Begriffe werden stärker gewichtet als häufige.

Tokenizer und Internationalisierung

Tokenizer zerteilen Text in Wörter und normalisieren ihn. Beim Erstellen einer FTS5-Tabelle lässt sich der Tokenizer angeben. Der Standard unicode61 unterstützt Unicode bis Version 6.1 und behandelt Umlaute wie „Äpfel“ und „Überzeugung“ korrekt. Der Porter Stemmer reduziert Wörter auf ihre Stammform: „laufen“, „läuft“, „gelaufen“ werden alle zu „lauf“. Das funktioniert auch für Deutsch, wenn auch nicht perfekt. Für perfektes Stemming müsste man einen spezifischen Tokenizer schreiben, was aber für die meisten Fälle nicht nötig ist.

Fuzzy Search und Tippfehlertoleranz

Benutzer suchen nach „Laptopp“ (mit doppeltem P) und erwarten trotzdem „Laptop“ in den Ergebnissen. FTS5 allein bietet keine eingebaute Fuzzy Search, aber die spellfix1-Erweiterung implementiert Fehlertoleranz basierend auf Edit Distance. Alternativ kann man seit SQLite 3.34 Trigram-Tokenizer nutzen, 3-Zeichen-Sequenzen für effizientes Approximate Matching. Mit Trigrams findet eine Abfrage „lapt*“ auch „laptop“, selbst wenn ein Buchstabe fehlt. Das ist weniger flexibel als Edit Distance, aber deutlich schneller. Wichtig zu beachten: Das funktioniert alles sehr gut bei typischen Wörtern aus dem Wörterbuch. Bei Namen, Straßennamen, Ortschaften und dergleichen kann das Konzept dagegen schnell auf Grenzen stoßen.

Praktisches Beispiel: Produktsuche in .NET

Wie sieht das in einer echten .NET-Anwendung aus? Angenommen, wir haben 500.000 Produkte und möchten eine performante, fehlertolerante Suche implementieren. Der Code ist straight forward und nutzt Microsoft.Data.Sqlite:

public async Task<List<ProductDto>>
    SearchAsync(string query)
{
  using var conn = new
    SqliteConnection(_connString);
  await conn.OpenAsync();
  var sql = @"
    SELECT p.id, p.name, p.price
    FROM products p
    JOIN products_fts fts
      ON p.id = fts.rowid
    WHERE fts MATCH @query
    ORDER BY fts.rank ASC
    LIMIT 50";
  using var cmd =
    new SqliteCommand(sql, conn);
  cmd.Parameters.AddWithValue(
    "@query", query);
  var results = new List<ProductDto>();
  using var reader =
    await cmd.ExecuteReaderAsync();
  while (await reader.ReadAsync())
  {
    results.Add(new ProductDto {
      Id = reader.GetInt32(0),
      Name = reader.GetString(1),
      Price = reader.GetDecimal(2)
    });
  }
  return results;
}

Zwei kritische Details: Erstens verwenden wir Parametrisierung mit @query statt String-Konkatenation, das schützt vor Injection und stellt sicher, dass der FTS5-Parser die Abfrage korrekt verarbeitet. Es lassen sich auch mehrere Bedingungen kombinieren: fts MATCH 'gaming AND (laptop OR notebook)' funktioniert perfekt. Zweitens limitieren wir auf 50 Ergebnisse, denn bei 500.000 Produkten gibt „laptop“ möglicherweise 20.000 Matches zurück. Diese über das Netzwerk zu schicken ist teuer. Mit LIMIT erhalten wir sofort die relevantesten 50, und wenn der Benutzer mehr Ergebnisse braucht, gibt es Paging-Mechanismen.

Kolumnen-Filter und Multi-Field-Suche

Häufig möchte man in spezifischen Feldern suchen, nicht in allen: „Suche nach ‚gaming‘ nur in der Beschreibung“. FTS5 erlaubt das über die column-Syntax: WHERE fts MATCH 'description:gaming AND category:Elektronik'. Das ist sehr mächtig. Auch Gewichte für verschiedene Spalten lassen sich vergeben – wenn der Suchbegriff in der Überschrift auftaucht, zählt er stärker als in der Beschreibung. Das geschieht über die bm25(column_weights)-Konfiguration bei der Tabellenerstellung und wird bei Abfragen automatisch angewendet.

Grenzen: Wann man zu Elasticsearch greifen sollte

Hier die ehrliche Antwort: Für die meisten .NET-Anwendungen reicht FTS5 aus. FTS5 glänzt bei kleinen bis mittleren Datenmengen (bis 500K Dokumente), Single-Server-Deployments, einfachen Relevanzanforderungen und keiner Notwendigkeit für separate Infrastruktur. Elasticsearch und OpenSearch bieten verteilte Indizierung über mehrere Server, komplexe Analyzers für verschiedene Sprachen, Faceted Search (Navigationshierarchien), Geo-Queries und Real-Time Analytics. Wir brauchen das, wenn wir mit Millionen von Dokumenten arbeiten, geografische Suche einsetzen oder mehrsprachige Inhalte indexieren müssen.

Praktischer Entscheidungsbaum: Produktkatalog unter 500K Einträge? Single Server? Keine Geo-Suche nötig? Keine Millionen-Dokumente? Nehmt FTS5. Falls ihr später migrieren müsst, schreibt einfach in beide Indizes und switcht um. Das ist nicht elegant, aber es funktioniert. Ein wichtiger Punkt: Bei Millionen von Dokumenten wird auch FTS5 langsamer, aber das ist nicht zwangsläufig ein Deal-Breaker, es hängt von der jeweiligen Latenzanforderung ab.

Updates und Maintenance

Ein häufiges Missverständnis: Beim Einfügen in products wird der FTS5-Index nicht automatisch aktualisiert. Wir müssen Trigger setzen. Das ist Boilerplate, aber notwendig. Viele ORM-Frameworks in .NET tun das nicht automatisch; man muss es selbst implementieren oder einen Custom-Interceptor schreiben.

Fazit

FTS5 ist massiv unterschätzt. Es bringt ohne zusätzliche Infrastruktur Search-Capabilities in .NET-Anwendungen. Für alles unter 500K Dokumenten auf einem Single Server ist es eine exzellente Wahl. Dabei gibt es Grenzen: Geo-Suche, Millionen-Dokumente-Scale, Distributed Search – hier braucht man spezialisierte Lösungen. Aber für die Mehrzahl der Business-Anwendungen ist FTS5 nicht nur ausreichend, sondern überlegen gegenüber klassischem „reguläres SQL + Elasticsearch-Cluster“.