MySQL - MySQL: ORDER BY [Anzahl an Matches]

Beitrag von **danielsun174** » 31.08.2010, 09:11

Guten Tag zusammen.
Ich habe folgendes Problem:
Ich habe ein Array, welches die Wörter einer Suchanfrage auflistet, z.B.: [0] ->auto [1]->bmw [2]->felge

Nun habe ich in der Datenbank eine Spalte mit Titeln, z.B.: 'Neue Bmw Felge kaufen'; 'Felgen günstiger kaufen', 'Alles nur kaufen' usw.

Nun möchte ich zur Suchabfrage alle Titel aufzählen. Wichtig ist dabei jedoch das Order By, denn je nach dem, wie oft ein Worte des Suchabfragen-Arrays in dem Titel vorhanden ist, desto weiter oben sollte dieser Titel erscheinen.

z.B: Select * FROM article ORDER BY count(matches)

währe schön, wenn mir jemand weiterhelfen könnte.

mfg daniel

von **Anzeige von ABAKUS** »

Beitrag von **Rizzo** » 31.08.2010, 12:29

Moin (auch wenn es schon später ist),

Es gibt jetzt verschiedene Ansätze die man verfolgen könnte. Zunächst müsst ich aber wissen welche ART von Datenbank-Tabelle du da hast.

zB.: falls du eine MyISAM-Tabelle hast, die auf den Namen 'article' hört kann man das leicht mit einer Volltextindexsuche über die Bühne bringen. Vorraussetzung der Volltextindex auf die Spalte existiert

.

Wenn du aber z.B. eine InnoDB-Tabelle hast, muss man das etwas komplizierter angehen. Daraufhin die nächste Frage - stellst du deine DBVerbindung mit msql, mysqli, PDO oder etc. her?

Grüße Rizzo

Beitrag von **TonyBologni** » 31.08.2010, 13:51

Um überhaupt die entsprechenden Titel zu finden braucht man einen Fulltext-Index und eine "... where begriff like '%BMW%' .." Query. Alleine das riecht schon nach Performance-Problemen, je nach Größe der Tabelle und Anzahl solcher Queries / Zeiteinheit.

Um das dann nach Anzahl der Treffer zu sortieren, würde man unter anderen Umständen ein "group by begriff" machen, das geht aber nicht mit dem zutreffenden Muster der Like Query und imho gibt es in MySQL keine Möglichkeit, das mit SQL alleine zu machen.

Das würde gehen:
"select begriff, count(begriff) as cnt from tabelle where begriff = 'BMW' group by begriff, order by cnt desc"

Das geht nicht, da begriff der komplette Titel ist und nicht das Wort, nach dem Du suchst:
"select begriff, count(begriff) as cnt from tabelle where begriff like '%BMW%' group by begriff, order by cnt desc"

Demnach muss man hier entweder:
- einen richtige Volltextsuche wie z.B. Lucene verwenden
- oder die Suche ohne group by und so machen, dann in PHP über alle Ergebnisse loopen und zählen, was wie oft vorkommt

Merke auch gerade, Du willst wohl eingentlich was anderes!
Du willst nicht wissen, wie viele Titel "BMW" drin haben, sondern wie viele der Suchbegriffe pro Titel vorkommen!?

Dann kannst Du eine reine SQL Lösung komplett vergessen!
Auch hier würde wieder z.B. Lucene die richtige Sortierung liefern können oder wieder mit PHP über alle Treffer loopen und relativ aufwändig die gematchten Suchbegriffe zählen.

Die Query wird noch ein bisschen "schwergewichtiger":

"select * from tabelle where
begriff like '%bmw%' or begriff like '%felge%' or ...."

Genau für so was sind Datenbanken im allgemeinen nicht geeignet.

Gruß, Tony

Beitrag von **Synonym** » 31.08.2010, 14:06

Oder eben mit Match-Against, das liefert Dir dann die Ergebnisse schon mal vorsortiert. Ob das allerdings die gewünschte Sortierung ist musst Du entscheiden.
https://dev.mysql.com/doc/refman/5.1/de ... earch.html

Einen direkten Weg die Matches zu zählen kenne ich nicht, könnte aber über Prozeduren funktionieren.

Beitrag von **TonyBologni** » 31.08.2010, 15:14

Stimmt, "match against" gibt es ja auch noch, habe ich auch schon mal bei einem Kunden verwendet, allerdings die boolsche Variante. Damit kann man dann fast nachbauen, was z.B. Lucene kann.

Ich denke, Daniel will nur die "besten" Matches oben, die Anzahl der Treffer ist egal. Sollte also für Deinen Fall reichen, Daniel!

SELECT titel, MATCH(titel) AS score FROM articles WHERE MATCH(titel) AGAINST ('+bmw +auto +felge' IN BOOLEAN MODE) ORDER BY score DESC;

Gruß, Tony

Beitrag von **Rizzo** » 31.08.2010, 17:16

So, nehmen wir an die Datenbank Tabelle unterstützt die Volltext-Suche UND die Spalte 'article' ist mit FULLTEXT in der DB indiziert. Dann ist die Variante vom TonyBologni die einfachste^^.

Man sollte bei "match-against" 2 Sachen beachten...
1. es gibt eine Minimallänge bei Wörtern (default ist glaube 4) - wenn man die unterschreitet wird das Wort nicht in die Suche mit eingebunden - somit verhindert man die Suche von "den, sie, es"- aber auch "bmw"

mit " SHOW VARIABLES LIKE 'ft_min_word_len' " kann man sehen was eingestellt ist als Defaultwert.

2. wenn es mehr als 50% Ergebnisse gibt wird die Anfrage abgebrochen und man bekommt einen leeren String zurück.

--------------------
Noch was zu der Abfrage, benutze am besten noch das "HAVING" vor dem ORDER BY...

zB.
SELECT *, MATCH(title) AGAINST('+auto +felge' IN BOOLEAN MODE) AS mtch FROM tabelle HAVING mtch > 0 ORDER BY mtch LIMIT 100

...somit werden die Ergebnisse ausgelassen wo er keins der Suchwörter gefunden hat.

Beitrag von **TonyBologni** » 31.08.2010, 18:13

zu 2.)
"Benutzer, die die 50-Prozent-Grenze umgehen wollen, können den booleschen Suchmodus verwenden. Siehe auch Abschnitt 12.7.1, „Boolesche Volltextsuche“." -> https://dev.mysql.com/doc/refman/5.1/de ... olean.html

U. A. deshalb hatte ich in meinem Beispiel die boolesche Variante verwendet.

zu 1.)
Ja, 4 ist default. Und man muss ggf. nach der Umstellung die Fulltext Indizes neu erstellen.

Having by ist natürlich eine gute Idee!

Gruß, Tony

Beitrag von **nerd** » 31.08.2010, 23:15

Wollte auch die Match-against function vorschlagen.

eine andere loesung ware, die texte manuell in die einzelnen woerter zu zerlegen, davon den wortstamm bilden und wortstamm, anzahl vorkommen im text und die artikel_id in einer tabelle zu speichern.

beim suchen zerlegst du die suchabfrage wieder in die einzelnen woerter, bildest den wortstamm und durchsuchst damit die datenbank und kannst dann deine eigene sortierfunktion anwenden (z.b. artikel mit den meisten treffern zuerst; artikel mit suchbegriff im titel zuerst usw. usf.)

ich hatte das mal implementiert; allerdings is es dann jedesmal sehr langsam wenn du einen neuen artikel speicherst ind manuell indizierst. die suche dagegen ist extrem schnell.

Beitrag von **TonyBologni** » 01.09.2010, 09:41

nerd hat geschrieben:Wollte auch die Match-against function vorschlagen.

eine andere loesung ware, die texte manuell in die einzelnen woerter zu zerlegen, davon den wortstamm bilden und wortstamm, anzahl vorkommen im text und die artikel_id in einer tabelle zu speichern.

beim suchen zerlegst du die suchabfrage wieder in die einzelnen woerter, bildest den wortstamm und durchsuchst damit die datenbank und kannst dann deine eigene sortierfunktion anwenden (z.b. artikel mit den meisten treffern zuerst; artikel mit suchbegriff im titel zuerst usw. usf.)

ich hatte das mal implementiert; allerdings is es dann jedesmal sehr langsam wenn du einen neuen artikel speicherst ind manuell indizierst. die suche dagegen ist extrem schnell.

Der Kollege Daniel wird ohnmächtig, wenn er das liest.

Ohne zu viel Aufwand zu treiben und ohne vermutlich vorprogrammierte Performance-Probleme würde ich das mit Lucene machen. Ist zwar in der PHP Version auch nicht ganz so doll, aber wenn man weiß, wie man es machen muss, die beste/einfachste Lösung. Hit ranking kann Lucene jedenfalls und das ist ja das, was er braucht.

-Tony

Beitrag von **nerd** » 01.09.2010, 11:37

Pha; ihr kauft wahrscheinlich auch das Brot im supermarkt statt es mit viel liebe selbst zu backen

Beitrag von **TonyBologni** » 01.09.2010, 11:50

nerd hat geschrieben:Pha; ihr kauft wahrscheinlich auch das Brot im supermarkt statt es mit viel liebe selbst zu backen

Ja, das stimmt.

DB und Volltextsuche gehören einfach nicht zusammen, da sollte man "Spezialisten" ran lassen.

-Tony