MySQL Volltextsuche mit FULLTEXT Index

≈ 1 min
devops

Suche über LIKE '%suchbegriff%' ist langsam und unflexibel. Kein Index wird genutzt, keine Relevanz-Sortierung, kein Stemming. MySQL's eingebaute Volltextsuche ist für viele Anwendungsfälle eine bessere Wahl ohne externen Dienst.

Index anlegen

ALTER TABLE articles ADD FULLTEXT INDEX ft_content (title, body);
-- oder beim CREATE TABLE:
CREATE TABLE articles (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    title   VARCHAR(255) NOT NULL,
    body    TEXT NOT NULL,
    FULLTEXT INDEX ft_content (title, body)
);

Suche ausführen

-- Natural Language Mode (Standard)
SELECT id, title, MATCH(title, body) AGAINST ('php sicherheit' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST ('php sicherheit' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;

Der score gibt die Relevanz an — höherer Wert bedeutet bessere Übereinstimmung.

Boolean Mode

-- Muss "php" enthalten, "java" darf nicht enthalten sein, "sicherheit" ist optional
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST ('+php -java sicherheit' IN BOOLEAN MODE);
  • Operatoren:
  • +wort — muss enthalten sein
  • -wort — darf nicht enthalten sein
  • * — Prefix-Suche: php* findet php, phpunit, phpmyadmin
  • "phrase" — exakte Phrase

In PHP

function search(PDO $pdo, string $query, int $limit = 20): array
{
    // Sonderzeichen in Boolean Mode escapen
    $clean = preg_replace('/[+\-><\(\)~*\"@]+/', ' ', $query);
    $terms = implode(' ', array_map(
        fn($t) => '+' . $t . '*',
        array_filter(explode(' ', trim($clean)))
    ));

    if (empty($terms)) return [];

    $stmt = $pdo->prepare('
        SELECT id, title,
               MATCH(title, body) AGAINST (? IN BOOLEAN MODE) AS score
        FROM   articles
        WHERE  MATCH(title, body) AGAINST (? IN BOOLEAN MODE)
        ORDER  BY score DESC
        LIMIT  ?
    ');
    $stmt->execute([$terms, $terms, $limit]);
    return $stmt->fetchAll();
}

Einschränkungen

  • Wörter unter 3 Zeichen (konfigurierbar mit ft_min_word_len) werden ignoriert
  • Sehr häufige Wörter (in mehr als 50% der Zeilen) werden als Stopwords behandelt
  • Kein Fuzzy-Matching, kein Typo-Toleranz

Für diese Fälle oder für sehr große Datenmengen ist Elasticsearch oder Meilisearch besser geeignet. Für die meisten eigenen Projekte reicht MySQL FULLTEXT aber vollkommen aus.