Relevanzsortierung bei Fulltext-Select

[Codehunter]

Moin!

Ich habe vor, Suchergebnisse von Artikelnamen sortiert nach Relevanz auszugeben. Hat jetzt nichts mit Delphi zu tun, diesmal PHP. Aber das Kernproblem liegt bei der Datenbankabfrage. In einer Tabelle liegen Artikeltexte (Namen, Beschreibungen, Keywords etc.). Auf einer Ajax-Webseite gibt es ein Suchfeld, welches interaktiv während der Eingabe von Suchbegriffen eine Vorschlagsliste als Dropdownmenü anzeigt und laufend bei Keypresses aktualisiert. Die Ausgabe dieser Suchvorschläge möchte ich relevanzsortieren, sodass die passendsten Ergebnisse immer oben stehen. Dazu habe ich mir folgende Query ausgedacht:

Code:

SELECT name
FROM products_description
WHERE language_id=2
  AND MATCH(name) AGAINST ('optiset standard' IN NATURAL LANGUAGE MODE)
ORDER BY MATCH (name) AGAINST ('optiset standard' IN NATURAL LANGUAGE MODE) DESC

Dabei kommt folgendes Ergebnis heraus (auszugsweise):

Code:

Optiset E Standard
Optiset E Standard
Optiset E Standard
Optiset E Standard
Optiset E Standard
Optiset E Standard Gehäuse
Optiset E Standard Bodenwanne
Optiset E Standard Tastengitter Wahl Made in Germany
Optiset E Standard Tasten 8er Made in Germany
Optiset E Standard Plusminus Tasten Made in Germany
Optiset E Standard/Advance Tasten
Optiset E Standard/Advance Display Unterschale
Optiset E Standard/Advance Display Unterschale
Optiset E Standard/Advance Display Oberschale
Optiset E Standard/Advance Funktionstasten 8er-Block
Optiset E Standard/Advance Funktionstasten 4er-Block
Optiset E Standard/Advance/Memory Tasten Lautstärke (Plus und Minus)
Optiset E Standard/Advance Navigationstasten
Optiset E Standard/Advance Display Oberschale
Optiset E Standard/Advance Display Unterschale

Soweit sieht das Suchergebnis auch gut aus, eigentlich wie ich es brauche. Der Suchbegriff war hier "optiset standard", voll ausgeschrieben. Da die Suchvorschläge aber interaktiv sind, baut sich der Suchbegriff auch erst sukzessive während der User-Eingabe auf. Wenn man einen unvollständigen Suchbegriff (z.B. "optiset stand") in die selbe Query wirft, kommt folgendes bei raus:

Code:

Optiset E Standard
Optiset E Entry
Optiset E Advance plus
Optiset E Memory
Optiset E Memory
Optiset E Entry
Optiset E Basic
Optiset E Basic
Optiset E Basic
Optiset E Basic
Optiset E Standard
Optiset E Standard
Optiset E Standard
Optiset E Standard
Optiset E Advance
Optiset E Advance
Optiset E Advance plus
Optiset E Advance plus
Optiset E Advance Conference
Optiset E Advance Conference
Optiset E Memory
Optiset E Memory
Optiset E Memory
Optiset E Key Module
Optiset E Key Module
Optiset E Key Module

Wie man sieht, ist dann die Relevanzsortierung beim Teufel. Nur wie kommt das und vorallem, wie stellt man das ab?

[Ghostwalker]

Probiers doch mal so:

Code:

SELECT name
FROM products_description
WHERE language_id=2
  AND (MATCH(name) AGAINST ('optiset' IN NATURAL LANGUAGE MODE)) AND (MATCH(name) AGAINST ('standard' IN NATURAL MODE))

ORDER BY (MATCH (name) AGAINST ('optiset' IN NATURAL LANGUAGE MODE)) DESC ,(MATCH(name) AGAINST ('standard' IN NATURAL LANGUAGE MODE)) DESC;

Ungetestet und von einem SQL-Laien. Aber vielleicht eine Idee für einen Ansatz

[Codehunter]

Ne du, so einfach ist der Drops nicht gelutscht Genau genommen macht der "Natural Language Mode" schon genau das selbe: Slicen an den Leerzeichen, Bindestrichen etc. und dann AND-verknüpft matchen.

EDIT: Und bitte auf die genaue Problemstellung achten. Bei ganzen Wörtern scheint MATCH AGAINST gut klar zu kommen, bei nicht fertig geschriebenen Wortstücken jedoch fällt es komplett aus der Rolle. Um genau zu sein liefert MATCH in dem Fall für alle Treffer exakt den selben Relevanzwert, wie folgende Query zeigt:

Code:

SELECT name,
       MATCH(name) AGAINST ('optiset stand' IN NATURAL LANGUAGE MODE)
FROM products_description
WHERE language_id=2
  AND MATCH(name) AGAINST ('optiset stand' IN NATURAL LANGUAGE MODE)
ORDER BY MATCH (name) AGAINST ('optiset stand' IN NATURAL LANGUAGE MODE) DESC

Code:

Optiset E Standard   1.4053090810775757
Optiset E Entry   1.4053090810775757
Optiset E Advance plus   1.4053090810775757
Optiset E Memory   1.4053090810775757
Optiset E Memory   1.4053090810775757
Optiset E Entry   1.4053090810775757
Optiset E Basic   1.4053090810775757
Optiset E Basic   1.4053090810775757
Optiset E Basic   1.4053090810775757
Optiset E Basic   1.4053090810775757
Optiset E Standard   1.4053090810775757
Optiset E Standard   1.4053090810775757
Optiset E Standard   1.4053090810775757
Optiset E Standard   1.4053090810775757
Optiset E Advance   1.4053090810775757
Optiset E Advance   1.4053090810775757
Optiset E Advance plus   1.4053090810775757
Optiset E Advance plus   1.4053090810775757
Optiset E Advance Conference   1.4053090810775757
Optiset E Advance Conference   1.4053090810775757
Optiset E Memory   1.4053090810775757
Optiset E Memory   1.4053090810775757
Optiset E Memory   1.4053090810775757
Optiset E Key Module   1.4053090810775757
Optiset E Key Module   1.4053090810775757

[nahpets]

Helfen die Dir weiter? Which SQL query is better, MATCH AGAINST or LIKE?

Bei unvollständiger Eingabe seitens des Nutzers eventuell sowas?

SQL-Code:

SELECT name
FROM products_description
WHERE language_id=2
  AND MATCH(name) AGAINST ('optiset stand*' IN NATURAL LANGUAGE MODE)
ORDER BY MATCH (name) AGAINST ('optiset stand*' IN NATURAL LANGUAGE MODE) DESC

[Codehunter]

Zitat von nahpets:

Bei unvollständiger Eingabe seitens des Nutzers eventuell sowas?

SQL-Code:

SELECT name
FROM products_description
WHERE language_id=2
  AND MATCH(name) AGAINST ('optiset stand*' IN NATURAL LANGUAGE MODE)
ORDER BY MATCH (name) AGAINST ('optiset stand*' IN NATURAL LANGUAGE MODE) DESC

Leider nein, das Ergebnis bleibt exakt identisch zum vorherigen:

Code:

Optiset E Standard   1.4053090810775757
Optiset E Entry   1.4053090810775757
Optiset E Advance plus   1.4053090810775757
Optiset E Memory   1.4053090810775757
Optiset E Memory   1.4053090810775757
Optiset E Entry   1.4053090810775757
Optiset E Basic   1.4053090810775757
Optiset E Basic   1.4053090810775757
Optiset E Basic   1.4053090810775757
Optiset E Basic   1.4053090810775757
Optiset E Standard   1.4053090810775757
Optiset E Standard   1.4053090810775757
Optiset E Standard   1.4053090810775757
Optiset E Standard   1.4053090810775757
Optiset E Advance   1.4053090810775757
Optiset E Advance   1.4053090810775757
Optiset E Advance plus   1.4053090810775757
Optiset E Advance plus   1.4053090810775757
Optiset E Advance Conference   1.4053090810775757
Optiset E Advance Conference   1.4053090810775757
Optiset E Memory   1.4053090810775757
Optiset E Memory   1.4053090810775757
Optiset E Memory   1.4053090810775757
Optiset E Key Module   1.4053090810775757
Optiset E Key Module   1.4053090810775757

EDIT: Du hast mich aber auf eine Idee gebracht:

Code:

SELECT name,
       MATCH(name) AGAINST ('+optiset +stand*' IN BOOLEAN MODE)
FROM products_description
WHERE language_id=2
  AND MATCH(name) AGAINST ('+optiset +stand*' IN BOOLEAN MODE)
ORDER BY MATCH (name) AGAINST ('+optiset +stand*' IN BOOLEAN MODE) DESC

ergibt:

Code:

Optiset E Standard   4.073927879333496
Optiset E Standard   4.073927879333496
Optiset E Standard   4.073927879333496
Optiset E Standard   4.073927879333496
Optiset E Standard   4.073927879333496
Optiset E Standard Gehäuse   4.073927879333496
Optiset E Standard Bodenwanne   4.073927879333496
Optiset E Standard Tastengitter Wahl Made in Germany   4.073927879333496
Optiset E Standard Tasten 8er Made in Germany   4.073927879333496
Optiset E Standard Plusminus Tasten Made in Germany   4.073927879333496
Optiset E Standard/Advance Tasten   4.073927879333496
Optiset E Standard/Advance Display Unterschale   4.073927879333496
Optiset E Standard/Advance Display Unterschale   4.073927879333496
Optiset E Standard/Advance Display Oberschale   4.073927879333496
Optiset E Standard/Advance Funktionstasten 8er-Block   4.073927879333496
Optiset E Standard/Advance Funktionstasten 4er-Block   4.073927879333496
Optiset E Standard/Advance/Memory Tasten Lautstärke (Plus und Minus)   4.073927879333496
Optiset E Standard/Advance Navigationstasten   4.073927879333496
Optiset E Standard/Advance Display Oberschale   4.073927879333496
Optiset E Standard/Advance Display Unterschale   4.073927879333496
Optiset E Standard/Advance Satz Tasten und Lichtleiter   4.073927879333496
Optiset E Standard   4.073927879333496

Sieht schon mal viel besser aus! Warum jetzt aber der Relevanzwert trotzdem bei allen identisch ist, obwohl das ORDER BY ganz offensichtlich anderes vermuten lässt, das erschließt sich mir jetzt auch nicht.

BTW: Wie hast du hier eigentlich das SQL-Syntax-Highlight gemacht? Das findet sich in meiner Editor-Toolbar gar nicht...

[nahpets]

code="sql" in eckigen Klammern.

[Codehunter]

Zitat von nahpets:

code="sql" in eckigen Klammern.

Ah, ein Osterei! Das trifft sich ja gut, ist bald wieder Ostern Spaß beiseite, ist das irgendwo dokumentiert, welche Highlighter ansprechbar sind?

[nahpets]

Ostern ist hier genauer definiert: BBCode

Allerdings steht dort nicht genauer erläutert, was man bei Code noch so alles hinter das = schreiben kann. #Code

[jobo]

Ich find das ja etwas drollig. Hab mich noch nie mit diesen SQL Funktionen rumgeschlagen. Es erklärt im Grunde aber die besch.. Ausgabe in gefühlt jedem 1. Webshop mit sortierung nach "Relevanz"

Der erste wichtige Schritt ist naheliegend und bereits geschehen:
Bei unerklärlichen Sortierungen in der Ausgabe, das Sortierfeld mit ausgeben. Verblüffendes Ergebnis, naja nicht wirklich.

So wie es aussieht, kommt man recht bald zu der Erkenntnis, das Relevanz offenbar recht unterschiedlich bewertet wird.
Also Sortierkriterium selbst festlegen. Ich nehm bei sowas gern Levenshtein oder so, keine Ahnung ob es hier hilft, aber wäre mein Ansatz.

Im Übrigen wäre meine Frage auch bei dem Punkt "vollständige Eingabe" mal bitte zu erläutern, was das denn bitte sein soll?
Wann weiß ein Entwickler oder ein Stück Code, wann mein Suchbegriff vollständig ist?

[p80286]

Da ich diese Funktion noch nie genutzt habe mal spekuliert
"Optiset" ist klar, muß man nicht diskutieren.
dann kommt in der DB ein "E" nach dem aber nicht gefragt wird, und schon passt die ganze Chose nicht mehr, der Rest ist nur noch geraten?

Interessant wären die nicht selektierten Daten.
Gibt es darunter "Optiset"?

Gruß
K-H