Wofür brauche ich Full-Text Search in InnoDB bei MySQL

Was ist Full-Text Search (FTS)

FTS ist eine Suche in einer Menge von Dokumenten der gesammte Inhalt eben dieser durchsucht wird. Im Gegensatz dazu gibt es auch Suchen bei denen nur Teile der Dokumente wie zum Beispiel eine Zusammenfassung o.ä. durchsucht werden.
Im Zusammenhang mit Datenbanken bezieht sich FTS auf die Unterstützung einer Volltextsuche entweder durch die Datenbank selber (FTS-Index) oder durch auf der Datenbank aufsetzenden anderen Softwarelösungen.

FTS bei MySQL

Eine Volltextsuche, implementiert mittels Index, gibt es bei MySQL schon länger, aber bisher nur für Tabellen im Format MyISAM. Seit der Version MySQL 5.6.4 gibt es einen solchen Fulltext-Index auch für InnoDB-Tabellen. InnoDB-Tabellen haben gegenüber MyISAM einige Vorteile wie zum Beispiel referenzielle Integrität über foreign-keys.

Wofür braucht man FTS

Angenommen man hat eine Benuzterdatenbank user mit den Spalten username, firstname, lastname.
Nun möchte man eine Suche anbieten bei der man einen Such-String angibt der sowohl mit den Benutzernamen aus der Spalte username, als auch mit dem Vor- und Nachname aus den Spalten firstname sowie lastname verglichen wird.

Zunächst kann man die Abfrage so formulieren:

SELECT * FROM user WHERE username LIKE '% :search %' OR firstname LIKE '% :search %' OR lastname LIKE '% :search %';

(:search wird mittels php/pdo prepared statements mit dem Such-String ersetzt)

Diese Abfrage funktioniert zwar, es muss aber, da der Wildcard-Operator % auch am Anfang des Ausdrucks steht, von MySQL jede Zeile ausgewertet werden. Das ist auch der Fall wenn man einen Index über diese Spalten angelegt hat. Dieser kann nicht verwendet werden wenn der Wildcard-Operator % am Anfang steht.
Falls man also eine große Anzahl an Benutzern hat, muss bei jeder Suche mit dieser Anfrage alle Zeilen gelesen und verglichen werden, und das für alle 3 hier verwendeten Spalten.

Nun kommt der Fulltext-Index, den es seit MySQL 5.6.4 auch für InnoDB gibt, ins Spiel:
Diesen legt man wie folgt an:

ALTER TABLE user ADD FULLTEXT(username, firstname, lastname);

Die obige Abfrage formuliert man jetzt so:

SELECT * FROM user WHERE MATCH (username,firstname,lastname) AGAINST(:search IN BOOLEAN MODE)';

Diese Anfrage benutzt nun den erstellten Fulltext-Index und muss so nicht mehr alle Zeilen auswerten sondern nur die Zeilen zurückgeben welche vom Index für den übergebenen Such-String als passend ausgewählt wurden. Noch schneller geht es wenn man nur Spalten zurückgeben möchte welche vom Index bereits abgedeckt werden. Dann müssen garkeine Zeilen mehr so aus der Datenbank gelesen werden:

SELECT username FROM user WHERE MATCH (username,firstname,lastname) AGAINST(:search IN BOOLEAN MODE)';

(:term wird jeweils mit

':search' => "+$search*",

zugewiesen)

Dieser Beitrag wurde am von in Tutorials, Vorstellung veröffentlicht. Schlagworte: , , , , , .

Über Kai

Ich habe Informatik im Masterstudiengang an der Universität Stuttgart studiert. Im Studium habe ich, wie mein Kollege und Kommilitone, die Vertiefungslinien "Theoretische Informatik und Wissenschaftliches Rechnen" und "Visualisierung und Interaktive Systeme" belegt. Auch in meiner Freizeit beschäftige ich mich oft mit Softwareentwicklung, im Speziellen der Softwarearchitektur, sowie mit der, teils ehrenamtlichen, Betreuung und Weiterentwicklung von Webplattformen. Darüberhinaus gehe ich dem Leistungssport Schwimmen nach, um für einen gesunden Ausgleich mit Sport zu sorgen. Mehr über mich unter kmindi.de.