SQL Volltextsuche: Ausgabe nur wenn alles gefunden wurde

[Snooty]

Hio,

ich habe ein MySQL-Tabelle mit drei Spalten: name, plz, ort. Über ein Eingabefeld wird eine Volltextsuche durchgeführt. Da der Ort am wichtigsten ist, wird er bei der Ausgabe entsprechend höher gewertet. Das klappt soweit alles.

Wenn ich jetzt aber einen Namen und einen Ort eingebe, werden zwar Zeilen mit passendem Ort und Namen an erster Stelle ausgegeben, aber auch Zeilen, in denen nur Ort oder nur der Name vorkommen. Wie bekomme ich es hin, dass alle Eingaben vorkommen müssen?

Meine aktuelle Abfrage sieht so aus (Quelle):

"SELECT *,
  (
    (1.3 * (MATCH (ort) AGAINST ('$eingabe*' IN BOOLEAN MODE))) +
    (0.6 * (MATCH (name, plz)  AGAINST ('$eingabe*' IN BOOLEAN MODE)))
  ) AS relevance FROM Service
  WHERE
    (MATCH (name,ort,plz) AGAINST ('$eingabe*' IN BOOLEAN MODE))
  HAVING relevance > 0
  ORDER BY relevance";

Tabelle:
[table="width: 500, class: grid"]
[tr]
[td]id[/td]
[td]name[/td]
[td]plz[/td]
[td]ort[/td]
[/tr]
[tr]
[td]1[/td]
[td]mustermann[/td]
[td]12345[/td]
[td]münchen[/td]
[/tr]
[tr]
[td]2[/td]
[td]musterfrau[/td]
[td]67890[/td]
[td]köln[/td]
[/tr]
[tr]
[td]3[/td]
[td]hermann[/td]
[td]53472[/td]
[td]köln[/td]
[/tr]
[tr]
[td]4[/td]
[td]hermann[/td]
[td]23154[/td]
[td]berlin[/td]
[/tr]
[/table]

Suchbegriff: köln muster

Ausgabe:
[table="width: 500, class: grid"]
[tr]
[td]id[/td]
[td]name[/td]
[td]plz[/td]
[td]ort[/td]
[/tr]
[tr]
[td]2[/td]
[td]musterfrau[/td]
[td]67890[/td]
[td]köln[/td]
[/tr]
[tr]
[td]3[/td]
[td]hermann[/td]
[td]53472[/td]
[td]köln[/td]
[/tr]
[tr]
[td]1[/td]
[td]mustermann[/td]
[td]12345[/td]
[td]münchen[/td]
[/tr]
[/table]

Ich hätte aber gerne nur Folgendes als Ausgabe, da nur hier der komplette Suchbegriff köln muster existiert:
[table="width: 500, class: grid"]
[tr]
[td]id[/td]
[td]name[/td]
[td]plz[/td]
[td]ort[/td]
[/tr]
[tr]
[td]2[/td]
[td]musterfrau[/td]
[td]67890[/td]
[td]köln[/td]
[/tr]
[/table]

Ich hab schon versucht, jedem String des Suchbegriffs ein '+' voranzustellen und ein '*' anzuhängen, so dass aus der Eingabe köln muster letztendlich +köln* +muster* wird. Aber damit bekomme ich gar kein Ergebnis mehr; vermutlich weil die Begriffe ja nicht in jedem Feld existieren.

"SELECT *,
  (
    (1.3 * (MATCH(ort) AGAINST ('+köln* +muster*' IN BOOLEAN MODE))) + 
    (0.6 * (MATCH(name, plz) AGAINST ('+köln* +muster*' IN BOOLEAN MODE)))
  ) AS relevance FROM Service
  WHERE
    (MATCH(name,ort,plz) AGAINST ('+köln* +muster*' IN BOOLEAN MODE))
  HAVING relevance > 0
  ORDER BY relevance";

 

[benneq]

Nimm doch einfach mehrere MATCH AGAINST , jeweils mit AND verknüpft.

 

[Snooty]

Liefert mir auch gar kein Ergebnis.

"SELECT *,
  (
    (1.3 * (MATCH (ort) AGAINST ('$eingabe*' IN BOOLEAN MODE))) +
    (0.6 * ((MATCH (plz)  AGAINST ('$eingabe*' IN BOOLEAN MODE) AND MATCH (name)  AGAINST ('$eingabe*' IN BOOLEAN MODE)))
  ) AS relevance FROM Service
  WHERE
    (MATCH (name) AGAINST ('$eingabe*' IN BOOLEAN MODE) AND MATCH (ort) AGAINST ('$eingabe*' IN BOOLEAN MODE) AND MATCH (plz) AGAINST ('$eingabe*' IN BOOLEAN MODE))
  HAVING relevance > 0
  ORDER BY relevance";

 

[benneq]

vermutlich weil dein Suchwort nicht im PLZ Feld steht.

da gibt's nun verschiedene Kombinationen. Meist benutzt man sowas (pseudocode):

WHERE
(feld1 CONTAINS suchwort1 OR feld2 CONTAINS suchwort1 OR feld3 CONTAINS suchwort1) AND
(feld1 CONTAINS suchwort2 OR feld2 CONTAINS suchwort2 OR feld3 CONTAINS suchwort2) AND
(feld1 CONTAINS suchwort3 OR feld2 CONTAINS suchwort3 OR feld3 CONTAINS suchwort3)

 

[Snooty]

Ja, an so etwas in der Art dachte ich auch - aber schön ist was anderes. Vor allem wenn mehr Felder durchsucht werden sollen und mehr Begriffe gesucht werden.

Aber ich wird das mal so testen.

---


Langsam komm ich aber evtl. mit dem +-Operator weiter. Ich weiß nur noch nicht genau, wie es korrekt ist. Das geht schon in die richtige Richtung:

SELECT *, ((1.3 * (MATCH (ort) AGAINST ('köln muster*' IN BOOLEAN MODE))) + (0.6 * (MATCH (name, plz) AGAINST ('köln muster*' IN BOOLEAN MODE)))) AS relevance FROM service WHERE (MATCH (name,ort,plz) AGAINST ('+köln* +muster*' IN BOOLEAN MODE)) HAVING relevance > 0 ORDER BY relevance

Aber das trifft nur, wenn die Suchbegriffe in allen Feldern vorkommen. Nicht, wenn köln muster in einem Feld steht (bspw. in ort) und in name nichts passendes.

Dooferweise suchen alle Beispiele nur in einem Feld, ich möchte aber in 3 suchen. Daher weiß ich nicht, ob das was ich vorhabe mit diesem Ansatz überhaupt möglich ist.

 

[benneq]

Du hast wohl das Ende von MySQL erreicht ;-)

Das ist halt ein RDBMS und keine Datenbank, die auf Textsuchen optimiert ist. Wenn einige Stunden/Tage Zeit mitbringt und es dir den Aufwand wert ist, könntest du dir ElasticSearch ( http://en.wikipedia.org/wiki/Elasticsearch ) ansehen. Das basiert auf Lucene ( http://de.wikipedia.org/wiki/Apache_Lucene ) und ist somit prädestiniert für Suchen aller Art und insbesondere Volltextsuche.
Lucene ginge natürlich auch, aber die Konfiguration und die Querys sind dann 1000 mal komplizierter.

 

[Snooty]

Würde mich doch stark wundern, wenn das mit MySQL nicht mehr möglich wäre

 

[Janush]

Ich kenn mich jetzt zwar ned aus mit my SQL, aber rein vom logischen her sollte deine Abfrage doch "name AND ort OR plz" sein? Kann ja nix bei rauskommen wenn du bei einer Suche mit +Muster, +Koeln auch PLZ mit einbeziehst.

Kann aber auch sein das ich das total falsch verstanden habe

 

[Snooty]

Ich habs auch ohne PLZ versucht, das ändert aber nichts.

Es sollen ja auch nur die Zeilen ausgegeben werden, wo alle Suchbegriffe existieren - egal in welchem Feld. Wenn also das Gesuchte nicht in plz steht aber in name oder ort, soll die Zeile ausgegeben werden.

Bislang werden die relevantesten Zeilen zwar richtig nach ort sortiert ganz oben ausgegeben, darunter aber auch Zeilen, wo nicht alle Suchbegriffe vorkommen.

 

[Janush]

Auf welche deiner Versionen referenzierst du?

Funktioniert es denn auch nicht wenn du den Part mit "((1.3 * (MATCH (ort) AGAINST ('köln muster*' IN BOOLEAN MODE))) + (0.6 * (MATCH (name, plz) AGAINST ('köln muster*' IN BOOLEAN MODE)))) AS relevance FROM service" weglaesst? Sorry wenn ich mich hier n bisschen anstelle, aber ich habe nichts wo ich das testen und ein bisschen dran rumexperimentieren koennte.

Wenn ich das mit deiner Quelle vergleiche, kann der Fehler ja eigentlich nur im "Ranking" liegen. Vielleicht mal den Part mit "'+apple +(>turnover <strudel)'" versuchen.

 

[Snooty]

Das Ranking sorgt ja nur für die Reihenfolge.

Ich finde halt kein Beispiel, wo jemand über mehrere Felder sucht und alle Treffer vorkommen müssen.

 

[mambokurt]

Hio,

ich habe ein MySQL-Tabelle mit drei Spalten: name, plz, ort. Über ein Eingabefeld wird eine Volltextsuche durchgeführt. Da der Ort am wichtigsten ist, wird er bei der Ausgabe entsprechend höher gewertet. Das klappt soweit alles.

Wenn ich jetzt aber einen Namen und einen Ort eingebe, werden zwar Zeilen mit passendem Ort und Namen an erster Stelle ausgegeben, aber auch Zeilen, in denen nur Ort oder nur der Name vorkommen. Wie bekomme ich es hin, dass alle Eingaben vorkommen müssen?

Ich denke es ist _ein_ Eingabefeld, wie kannst du da Name _und_ Ort eingeben? Nimmst du da noch die Eingabe auseinander?

Davon mal ab: SELECT * FROM table WHERE name like ? OR ort LIKE ? OR plz LIKE ?, das holst du dir einfach ab und sortierst es dann in deinem Programm nach Gutdünken. Nichts gegen Match und Konsorten, aber die Zehntelsekunde die du damit einsparst musst du, wie du gerade selber merkst, oft mit Stunden Entwicklung erkaufen. Das kanns IMHO nicht sein, jedenfalls nicht wenn du nicht für Fratzenbuch arbeitest und da > 100 Requests/Minute mit abarbeiten musst. Ich persönlich mach da lieber Quick&Dirty was im Programm und spare mir den Aufwand im SQL, das arme Schwein was später deinen Code mal warten muss und von einem anderen DBMS kommt, wird es dir zusätzlich noch danken....

EDIT:
Bevor es Kommentare regnet: was ich damit sagen will: Performance sollte optimiert werden wenn es an der Performance hakt. Es macht für den Endnutzer NULL Unterschied ob er auf ein Suchergebnis eine oder zwei Sekunden wartet, für dich macht es aber schon einen Unterschied beim Programmieren. Es kommt natürlich darauf an ob du die Suche für eine Blätterfunktion verwendest oder da alles stur raushaust oder ob da vielleicht 100 Suchen in der Minute aufschlagen, das geht aus deinem Post leider nicht hervor.

 

[Snooty]

Ich denke es ist _ein_ Eingabefeld, wie kannst du da Name _und_ Ort eingeben? Nimmst du da noch die Eingabe auseinander?

Nö.

Google bietet dir doch in Maps auch nur ein Feld, wo du Straße XY 12345 München suchst - oder wenn du lustig bist sogar 12345 Straße XY München.

Das ist einfach komfortabler für den Nutzer.

 

[benneq]

Pseudocode:

inputArray[] = input.split(" ");

query = "SELECT ... WHERE";

for(elem : inputArray) {
  query += "(MATCH(name,ort,plz) AGAINST ('"+elem+"*' IN BOOLEAN MODE)) AND";
}

Das sollte die einzige wirkliche Möglichkeit sein, um dein Vorhaben so umzusetzen.


Alternative: Denormalisierung!
Erweitere deine Tabelle um eine Spalte, die einfach nur name + ort + plz enthält (mit Leerzeichen getrennt).
Danach kannst du mit deinem ursprünglichen Query auf dieser Spalte suchen.

 

[Snooty]

Jetzt hab ich's geschafft. So muss es aussehen:

SELECT *,
  MATCH(ort) AGAINST ('+köln* +muster*' IN BOOLEAN MODE) * 10 as rel1, 
  MATCH(name) AGAINST ('+köln* +muster*' IN BOOLEAN MODE) * 3 as rel2, 
  MATCH(plz) AGAINST ('+köln* +muster*' IN BOOLEAN MODE) as rel3
FROM service
WHERE 
  MATCH (ort, name, plz) AGAINST ('+köln* +muster*' IN BOOLEAN MODE)
ORDER BY (rel1)+(rel2)+(rel3) DESC

edit:

So ganz passt's doch nicht Wenn bspw. der Name aus mehreren Worten besteht und auch mehrere Worte davon eingegeben werden + zusätzlich ein Ort, dann wird gar nichts mehr gefunden.