MySQL Full-Text-Search: eigene Implementierung statt externer Suche

1. Was ein FULLTEXT-Index in MySQL leistet

Ein FULLTEXT-Index in MySQL zerlegt Textspalten in einzelne Wörter, entfernt Stoppwörter, und baut eine invertierte Indexstruktur auf, die Wörter auf die Zeilen abbildet, in denen sie vorkommen. Damit lassen sich Abfragen formulieren, die nach Relevanz sortiert Treffer liefern, statt nur nach exakter Übereinstimmung wie bei LIKE '%wort%' zu suchen. Der entscheidende Unterschied zu LIKE ist, dass ein FULLTEXT-Index tatsächlich einen Index nutzt und nicht bei jeder Abfrage die gesamte Tabelle scannen muss.

Ein FULLTEXT-Index wird mit FULLTEXT(spalte1, spalte2) beim CREATE TABLE oder nachträglich mit ALTER TABLE ... ADD FULLTEXT angelegt. Die Suche selbst erfolgt über die Funktion MATCH(spalten) AGAINST(suchbegriff), die einen Relevanz-Score zurückgibt und sowohl in der SELECT-Liste als auch in der WHERE-Klausel verwendet werden kann. Dieser Ansatz ist seit MySQL 5.6 auch für InnoDB verfügbar, nicht mehr nur für die ältere MyISAM-Engine.

Für viele Anwendungen mit überschaubarem Datenvolumen, etwa Produktkataloge, Blog-Archive oder interne Dokumentensuchen, deckt ein gut konfigurierter FULLTEXT-Index die Anforderungen vollständig ab, ohne dass ein separates Suchsystem betrieben, synchronisiert und überwacht werden muss. Die Entscheidung für oder gegen eine eigene MySQL Full-Text-Search-Implementierung sollte auf konkreten Anforderungen basieren, nicht auf der Annahme, dass Elasticsearch grundsätzlich überlegen ist.

2. Natural Language Mode: relevanzbasierte Suche

Der Natural Language Mode ist der Standardmodus von MATCH AGAINST und interpretiert den Suchbegriff als natürlichsprachlichen Text. MySQL zerlegt den Suchstring in Wörter, sucht nach Zeilen, die mindestens eines dieser Wörter enthalten, und berechnet für jede Zeile einen Relevanz-Score basierend auf der Häufigkeit der Suchwörter in der Zeile im Verhältnis zu ihrer Häufigkeit in der gesamten Tabelle. Wörter, die in fast jeder Zeile vorkommen, tragen weniger zum Score bei als seltene, spezifische Wörter.

Ein wichtiges Detail des Natural Language Mode: Wörter, die in mehr als fünfzig Prozent der Zeilen einer Tabelle vorkommen, werden bei der Relevanzberechnung als Stoppwort-ähnlich behandelt und tragen nicht zum Score bei, selbst wenn sie kein klassisches Stoppwort sind. Dieser Fünfzig-Prozent-Schwellenwert erklärt manche überraschende Ergebnisse bei kleinen Testtabellen, verschwindet aber in der Praxis bei realistischen Datenmengen mit ausreichender Wortvielfalt.

CREATE TABLE articles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  PRIMARY KEY (id),
  FULLTEXT KEY ft_title_body (title, body)
) ENGINE=InnoDB;

-- Natural language search, ranked by relevance
SELECT id, title,
       MATCH(title, body) AGAINST('mysql performance tuning') AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST('mysql performance tuning')
ORDER BY relevance DESC
LIMIT 10;

3. Boolean Mode: präzise Suchoperatoren

Der Boolean Mode, aktiviert mit IN BOOLEAN MODE, erlaubt explizite Operatoren für präzise Suchanfragen. Ein vorangestelltes Plus +wort erzwingt das Vorkommen des Wortes, ein Minus -wort schließt Zeilen mit diesem Wort aus, Anführungszeichen definieren eine exakte Phrase, und ein Sternchen am Wortende wort* aktiviert eine Präfixsuche. Diese Operatoren geben Anwendungen die Möglichkeit, komplexe Suchfilter wie "muss X und Y enthalten, aber nicht Z" ohne mehrere separate Abfragen abzubilden.

Ein wesentlicher Unterschied zum Natural Language Mode: Im Boolean Mode entfällt die Fünfzig-Prozent-Schwellenwert-Regel, und es wird keine automatische Relevanzsortierung erzwungen, auch wenn MATCH AGAINST weiterhin einen Score zurückgibt, der für eigene ORDER BY-Klauseln genutzt werden kann. Der Boolean Mode eignet sich besonders für Suchoberflächen mit erweiterten Filteroptionen, während der Natural Language Mode für einfache Freitextsuchfelder die bessere Standardwahl ist.

-- Must contain "mysql", must not contain "oracle", prefix match on "index*"
SELECT id, title FROM articles
WHERE MATCH(title, body)
  AGAINST('+mysql -oracle +index*' IN BOOLEAN MODE);

-- Exact phrase search
SELECT id, title FROM articles
WHERE MATCH(title, body)
  AGAINST('"composite index design"' IN BOOLEAN MODE);

-- Higher weight for "performance", lower for "tuning" via > and <
SELECT id, title FROM articles
WHERE MATCH(title, body)
  AGAINST('+mysql >performance <tuning' IN BOOLEAN MODE);

4. Stoppwörter und Mindestwortlänge

MySQL verwendet standardmäßig eine eingebaute Stoppwortliste mit häufigen Wörtern wie "the", "and" oder "of", die von der Indexierung ausgeschlossen werden, weil sie für die Suche keinen Unterscheidungswert bieten. Für deutschsprachige Inhalte ist diese Standardliste unzureichend, da sie primär englische Stoppwörter enthält. Über die Systemvariable innodb_ft_server_stopword_table lässt sich eine eigene Stoppwortliste als Tabelle hinterlegen, die deutsche Füllwörter wie "der", "die", "das" oder "und" enthält.

Ebenso wichtig ist die Mindestwortlänge, gesteuert über innodb_ft_min_token_size, standardmäßig drei Zeichen. Kurze, aber relevante Suchbegriffe wie Produktcodes oder Abkürzungen unter dieser Länge werden sonst gar nicht indexiert. Nach einer Änderung dieser Konfigurationswerte muss der betroffene FULLTEXT-Index mit ALTER TABLE ... ADD FULLTEXT oder OPTIMIZE TABLE neu aufgebaut werden, damit die neuen Einstellungen wirksam werden.

-- Custom stopword table for German content
CREATE TABLE de_stopwords (value VARCHAR(30)) ENGINE=InnoDB;
INSERT INTO de_stopwords (value) VALUES
  ('der'), ('die'), ('das'), ('und'), ('oder'), ('ist'), ('mit');

SET GLOBAL innodb_ft_server_stopword_table = 'shop/de_stopwords';
SET GLOBAL innodb_ft_min_token_size = 2;

-- Rebuild the fulltext index so new settings take effect
ALTER TABLE articles DROP INDEX ft_title_body;
ALTER TABLE articles ADD FULLTEXT KEY ft_title_body (title, body);

5. Relevanz-Scoring mit MATCH AGAINST verstehen

Der von MATCH AGAINST zurückgegebene Relevanz-Score basiert im Kern auf einer TF-IDF-ähnlichen Berechnung: Term Frequency, also wie oft ein Suchwort in der Zeile vorkommt, multipliziert mit Inverse Document Frequency, also wie selten das Wort in der Gesamttabelle ist. Zeilen mit vielen Vorkommen seltener Suchwörter erhalten höhere Scores als Zeilen mit wenigen Vorkommen häufiger Wörter. Dieser Score ist ein relativer Wert ohne feste Obergrenze und eignet sich primär zum Sortieren, nicht als absolute Qualitätsmetrik.

In der Praxis kombiniert man den reinen Relevanz-Score oft mit weiteren Signalen wie Aktualität oder Popularität, indem man den MATCH AGAINST-Wert als einen von mehreren Faktoren in einer gewichteten Sortierformel verwendet. Für einfache Anwendungsfälle reicht die direkte Sortierung nach Relevanz aus, für Produktsuchen mit Geschäftslogik lohnt sich häufig eine Kombination aus Textrelevanz und weiteren Rankingfaktoren wie Lagerbestand oder Verkaufszahlen.

-- Combine text relevance with recency and popularity into one ranking
SELECT id, title,
       MATCH(title, body) AGAINST('mysql performance') AS text_score,
       (MATCH(title, body) AGAINST('mysql performance') * 0.7)
         + (view_count / 1000 * 0.2)
         + (DATEDIFF(NOW(), created_at) < 30) * 0.1 AS final_score
FROM articles
WHERE MATCH(title, body) AGAINST('mysql performance')
ORDER BY final_score DESC
LIMIT 10;

6. Der ngram-Parser für CJK-Sprachen

Der Standard-FULLTEXT-Parser von MySQL basiert auf Wortgrenzen, die durch Leerzeichen und Satzzeichen definiert werden. Diese Annahme funktioniert für europäische Sprachen gut, versagt aber bei chinesischen, japanischen und koreanischen Texten, kurz CJK, weil diese Sprachen keine expliziten Wortgrenzen durch Leerzeichen verwenden. Für diesen Fall bietet MySQL den ngram-Parser, der Text in überlappende Zeichenfolgen fester Länge zerlegt, standardmäßig zwei Zeichen, statt sich auf Leerzeichen zu verlassen.

Der ngram-Parser wird beim Anlegen des Index explizit mit WITH PARSER ngram aktiviert und über die Systemvariable ngram_token_size konfiguriert. Eine ngram-Größe von zwei Zeichen ist ein guter Kompromiss für die meisten CJK-Texte, größere Werte erhöhen die Präzision der Suche, vergrößern aber den Index deutlich, weil mehr überlappende Zeichenfolgen gespeichert werden müssen. Für gemischtsprachige Inhalte mit sowohl europäischen als auch asiatischen Texten kann eine Kombination aus zwei separaten FULLTEXT-Indizes mit unterschiedlichen Parsern sinnvoll sein.

-- ngram parser for CJK content: no whitespace word boundaries needed
SET GLOBAL ngram_token_size = 2;

CREATE TABLE articles_cjk (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  PRIMARY KEY (id),
  FULLTEXT KEY ft_body_ngram (body) WITH PARSER ngram
) ENGINE=InnoDB;

SELECT id, title FROM articles_cjk
WHERE MATCH(body) AGAINST('数据库性能' IN NATURAL LANGUAGE MODE);

7. FULLTEXT bei InnoDB versus MyISAM

Vor MySQL 5.6 war FULLTEXT-Suche ausschließlich bei der MyISAM-Engine verfügbar, was Entwickler häufig zwang, zwischen Transaktionssicherheit und Volltextsuche zu wählen. Seit InnoDB FULLTEXT unterstützt, entfällt dieser Kompromiss weitgehend, allerdings mit einigen betrieblichen Unterschieden: InnoDB pflegt eine Cache-Tabelle für kürzlich hinzugefügte Dokumente und schreibt Änderungen erst periodisch in den eigentlichen invertierten Index, was zu einer kurzen Verzögerung zwischen INSERT und Sichtbarkeit in der Suche führen kann.

Diese Verzögerung lässt sich über innodb_ft_cache_size und einen manuellen OPTIMIZE TABLE-Aufruf steuern, ist für die meisten Anwendungen aber unproblematisch, da sie im Bereich weniger Sekunden liegt. Für Anwendungen, bei denen neu eingefügte Inhalte sofort durchsuchbar sein müssen, etwa Live-Chat-Suchen, ist dieser Umstand relevant und sollte in der Systemarchitektur berücksichtigt werden.

8. Wann MySQL Full-Text-Search an Grenzen stößt

MySQL Full-Text-Search ist kein vollwertiger Ersatz für dedizierte Suchsysteme, wenn bestimmte Anforderungen ins Spiel kommen. Fuzzy-Matching für Tippfehler-Toleranz, echtes Facettieren über mehrere Dimensionen gleichzeitig, komplexe Synonym-Handhabung, oder eine Skalierung über mehrere Server hinweg mit horizontaler Sharding-Fähigkeit sind Bereiche, in denen spezialisierte Suchsysteme wie Elasticsearch oder OpenSearch deutlich überlegen sind. Auch bei sehr großen Textmengen im zweistelligen Gigabyte-Bereich pro Tabelle wird der Wartungsaufwand für FULLTEXT-Indizes spürbar.

Ein weiterer praktischer Grenzfall ist die fehlende native Unterstützung für gewichtete Mehrfeld-Suchen mit komplexen Boost-Faktoren, wie sie in E-Commerce-Suchen häufig gebraucht werden, etwa Produktname doppelt so wichtig wie Beschreibung, dreifach wichtig bei Übereinstimmung mit der Kategorie. Solche Anforderungen lassen sich mit MySQL nur über selbstgebaute Scoring-Formeln in der Anwendungsschicht nachbilden, was bei zunehmender Komplexität den Wechsel zu einem dedizierten Suchsystem nahelegt.

9. MySQL Full-Text-Search versus Elasticsearch

Die folgende Übersicht stellt beide Ansätze anhand praxisrelevanter Kriterien gegenüber.

Für kleine bis mittlere Anwendungen mit überschaubarem Suchbedarf und ohne Anforderungen an Fuzzy-Matching oder Facettierung ist MySQL Full-Text-Search oft die pragmatischere und wartungsärmere Lösung. Sobald jedoch Anforderungen wie Tippfehler-Toleranz, komplexe Facetten oder Skalierung über einen einzelnen Datenbankserver hinaus entstehen, überwiegen die Vorteile eines dedizierten Suchsystems die zusätzliche Betriebskomplexität.

10. Zusammenfassung

MySQL Full-Text-Search bietet mit FULLTEXT-Indizes eine vollwertige, in die Datenbank integrierte Volltextsuche, die für viele Anwendungen ohne zusätzliche Infrastruktur ausreicht. Natural Language Mode liefert relevanzbasierte Freitextsuche, Boolean Mode ermöglicht präzise Operatoren für erweiterte Filterfunktionen. Stoppwörter und Mindestwortlänge müssen für nicht-englische Inhalte angepasst werden, der ngram-Parser löst das Problem fehlender Wortgrenzen in CJK-Sprachen.

Die Grenzen von MySQL Full-Text-Search liegen bei Fuzzy-Matching, komplexer Facettierung und horizontaler Skalierung über einen einzelnen Server hinaus. Wer diese Anforderungen nicht hat, spart sich mit einer eigenen FULLTEXT-Implementierung erhebliche Betriebskomplexität gegenüber einem separaten Suchsystem wie Elasticsearch. Die Entscheidung sollte immer anhand konkreter Anforderungen getroffen werden, nicht anhand der Annahme, dass externe Suchsysteme grundsätzlich überlegen sind.

11. FAQ: MySQL Full-Text-Search