Gewichtete Suche mit MySQL

In diesem Beitrag zeige ich euch wie man eine gewichtete Suche mit MySQL implementiert. Die Qualität einer Software wird immer häufiger dadurch bewertet, wie gut man an die gewünschten Daten kommt. Im Web gilt nach wie vor die Regel, dass man mit so wenig Klicks wie möglich das Ergebnis finden muss. Eine Suche muss das gesuchte Objekt auf der ersten Seite ausgeben.

Gewichtete Suche mit MySQL

Die ersten 3 Suchergebnisse auf Google bekommen über 80% der Klicks. Findet man die gewünschte Information dort nicht hat man schlechte Keywords benutzt oder sucht sehr spezielle Details. Ob das Suchergebnis gut ist oder nicht, die Top 3 Einträge bekommen trotzdem die häufigsten Klicks ab. Google hat jedenfalls alles richtig gemacht, die relevantesten Infos stehen immer oben. Ausnahmen gibt es nur bei schlechten Keywords.

MySQL

Was hat das nun mit MySQL und einer gewichteten Suche zu tun? Ein Großteil von Software im Web basiert auf MySQL als Datenbanksystem. Es gibt kaum eine Webseite ohne interne Suche. In einem Onlineshop möchte ich das Produkt entweder über 3 Klicks durch die Kategorien finden oder bei einem sehr großen Sortiment sofort über die Suche. Ein No-Go ist, wenn in einem Supermarkt bei der Suche nach „Milch“ das Produkt „Kinder Milchschnitte“ auf Platz eins steht. Mehr kann man bei der Suche gar nicht falsch machen. Die Suche in dem Shop wäre damit schon unbrauchbar.

Ungewichtete Suche

Der oben beschrieben Fall passiert, wenn man beispielsweise folgendes an die Datenbank schickt:

SELECT * FROM produkte WHERE name like "%suchbegriff%"

Das Ergebnis enthält alle Namen die den Suchbegriff beinhalten. Die Reihenfolge wird durch eine andere Spalte außer name definiert, meist der normale Primärschlüssel der Tabelle. Das Suchergebnis wird im Fall von Produktbezeichnungen katastrophal schlecht aussehen und ist als  Suche der Produktdatenbank völlig ungeeignet.

Gewichtete Suche

MySQL bietet standardmäßig eine Gewichtung an, die leider viel zu selten benutzt wird. Dabei wäre das recht einfach umzusetzen. Man benötigt dafür mehrere Spalten in denen man das gesuchte Wort suchen kann. Beispiele im Onlineshop sind: Produktbezeichnung, Kurzbeschreibung, Langbeschreibung, Kategorie, Subkategorie usw. Suchen wir nun beispielsweise nach „Milch“, dann sind jene Ergebnisse weiter oben, bei denen das Wort in mehreren Spalten vorkommt. Gewichtet man zum Beispiel die Kategorien höher, dann wird man sehr gute Ergebnisse erhalten, sofern es eine Kategorie oder Subkategorie mit dem Namen „Milch“ gibt. Im Code sieht so eine Abfrage wie folgt aus:

SELECT name, short_description, long_description, category, subcategory, (
0.5 * (MATCH (name) AGAINST ('+Milch' IN BOOLEAN MODE))
+
3 * (MATCH (category) AGAINST ('+Milch' IN BOOLEAN MODE))
+
5 * (MATCH (subcategory) AGAINST ('+Milch'IN BOOLEAN MODE))
) AS relevance
FROM product
HAVING relevance > 2.5
ORDER BY relevance DESC
LIMIT 100

Bei diesem SELECT Statement wird eine temporäre Spalte relevance erzeugt mit der das Ergebnis absteigend sortiert ist. relevance ist dabei eine Dezimalzahl, die sich aus den MATCH AGAINST Klauseln ergibt. Je nach Gewichtung und vorkommen des Suchbegriffs in der jeweiligen Spalte ist der relevance Wert höher oder niedriger. Eine MATCH AGAINST Zeile sieht im Detail wie folgt aus:

0.5 * (MATCH (name) AGAINST ('+Milch' IN BOOLEAN MODE))

0.5 ist die Gewichtung. Diese muss man selbst festlegen und ist abhängig davon wie wichtig diese Spalte im Suchergebnis sein darf. Danach folgt mit MATCH die Spalte der Tabelle und mit AGAINST das gesuchte Wort. IN BOOLEAN MODE bedeutet, dass wir mit + und – vor dem Suchbegriff festlegen können ob das Wort gefunden werden muss. Beispielweise könnten wir mit einem zusätzlichen „-Schokolade“ Ergebnisse mit „Milch-Schokolade“ entfernen.

Mit der HAVING Zeile kann man Zeilen entfernen, die unter einer gewissen Schwelle der relevance liegen. Ist Optional und wegen der LIMIT 100 Zeile (das Ergebnis enthält maximal die 100 besten Ergebnisse) nicht notwendig.

Full Text Index

Die einzige Bedingung für die gewichtete Suche einer Spalte der Tabelle ist ein gesetzter FULLTEXT Index. Diesen kann man beispielsweise mit PhpMyAdmin auf der Tabellen Struktur Seite setzen. Das könnte dann so aussehen:

Fazit

Mit einer gewichteten Suche verbessert man das Suchergebnis auf drastische Weise. Die Voraussetzung dafür sind mehrere Spalten die alle einen Vollindex haben.Für Professionelle Webseiten und Onlineshops ist das ein MUSS.

Wie könnte man die Suche weiter verbessern? Welche spezialisierten Suchabfragen nutzt ihr?