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)