Für Profis? Match against mit mehreren values und Relevanzfilter

[TribunM]

Match against mit mehreren values und Relevanzfilter, geht das?

Hallo Datenbankspezis,

ich habe im Moment eine Abfrage die einen String mit einer Spalte vergleicht, und nur die Ergebnisse mit den beiden höchsten Relevanzwerten wiedergibt. Das klappt wunderbar. Etwas gekürzt sieht das so aus:

SELECT result.* FROM 
			(
			SELECT A.TITLE as title  
			MATCH(A.TITLE) AGAINST('$value1' IN BOOLEAN MODE) AS REL
			FROM Daten AS A) as result
			WHERE result.REL >=
				(
				(SELECT Max(rel_max.RELA) FROM 	
					(SELECT A.TITLE as title  
					MATCH(A.TITLE) AGAINST('$value1' IN BOOLEAN MODE) AS RELA
					FROM Daten AS A) as rel_max)-1)

Erste Frage wie, bzw. kann ich den zweiten Teil der SELECT Max Anweisung kürzen. Denn es ist ja praktisch der Pendant zu der oberen.

Der nächste Schritt ist eine erweiterung der Abfrage zu mehreren Values. in etwa so:

SELECT result.* FROM 
			(
			SELECT A.TITLE as title  
			MATCH(A.TITLE) AGAINST('$value1' IN BOOLEAN MODE) AS REL, MATCH(A.TITLE) AGAINST('$value2' IN BOOLEAN MODE) AS REL2, 
			FROM Daten AS A) as result
			WHERE result.REL >=
				(
				(SELECT Max(rel_max.RELA) FROM 	
					(SELECT A.TITLE as title  
				        MATCH(A.TITLE) AGAINST('$value1' IN BOOLEAN MODE) AS RELA
					FROM Daten AS A) as rel_max)-1) 
                         OR
	                 result.REL2 >=
				(
				(SELECT Max(rel_max2.RELA2) FROM 	
					(SELECT A.TITLE as title  
				        MATCH(A.TITLE) AGAINST('$value2' IN BOOLEAN MODE) AS RELA2
					FROM Daten AS A) as rel_max2)-1)

Na ja das haut hinten und vorne nicht hin. Später soll das ganze noch dynamisiert werden also value 1-n und Rel 1-n. Aber ich denke der Aufbau so ist falsch und in der vorhandenen Form unbrauchbar.

Vielleicht kann sich Jemand, der sich da besser auskennt, erbarmen und mir zeigen wie es richtig geht.

Danke schon einmal Tribbi

 

[Yaslaw]

die 2 mit der höchsten relevanz? sollte doch eigentlich etwa so gehen (ich nehme mal an, du arbeitest mit MySQL)

SELECT 
	A.TITLE AS title  
	MATCH(A.TITLE) AGAINST('$value1' IN BOOLEAN MODE) AS REL
FROM 
	Daten AS A
ORDER BY REL DESC
LIMIT 2

 

[TribunM]

So wieder auf den Beinen kann ich mein Problem weiter angehen.

Hallo yaslaw,

ja ich nutze mysql. Wenn ich das aber so machen würde, wie du vorschlägst, erhalte ich auch immer nur die ersten zwei Werte. Ich möchte aber wirklich alle Werte mit den zweihöchsten Relevanzen erhalten. Das geht ja nur indem ich den Maxwert ermittle und dann diesen sowie den ersten darunterliegenden (-1) ausgebe. Zumindest funktioniert es bisher so.

Was fehlt sind eben die weiteren Ebenen, die ich momentan mit OR verbinde. Das ist meiner Meinung nach aber keine gute Lösung und geht sicher besser.

Wir kann ich denn den Teil kürzen, evtl. mit einer Variable?

...
SELECT A.TITLE AS title MATCH(A.TITLE) AGAINST('$value1' IN BOOLEAN MODE) AS RELA FROM Daten AS A

Momentan stehe ich wirklich auf dem Schlauch. Deshalb wäre etwas Hilfe schon recht nett.

Danke

 

[Yaslaw]

Diese Lösung könnte gehen. Ist aber wahrscheinlich nicht ser Performant, weil du dein RELA zwei mal über die ganze Tabelle rechnen musst

SELECT 
	dat.TITLE AS title  
	MATCH(dat.TITLE) AGAINST('$value1' IN BOOLEAN MODE) AS REL
FROM
	Daten AS dat,
	(SELECT 
		MATCH(TITLE) AGAINST('$value1' IN BOOLEAN MODE) AS REL
	FROM 
		Daten
	ORDER BY REL DESC
	LIMIT 2) AS max2
WHERE
	max2.REL = dat.rel

Hab keine Ahnung wie schnell es mit Variablen währe. Aber etwa so könnte es aussehen

SELECT
	A.title,
	A.rel
FROM
	(
		SELECT
			dat.title,
			@cnt := @cnt + IF(dat.rel = @rel, 0, 1) AS calc_cnt,
			@rel = dat.rel AS rel
		FROM
			(SELECT @rel := 0, @cnt := 0) AS vars,
			(SELECT 
				TITLE AS title  
				MATCH(TITLE) AGAINST('$value1' IN BOOLEAN MODE) AS REL
			FROM 
				Daten
			ORDER BY REL DESC) AS dat
	) AS A
WHERE A.calc_cnt <= 2

 

[TribunM]

Hi yaslaw,

es scheint so, als ob du Hier einer der wenigen wirklich fähigen SQL Programmierer bist.
Ja ich bin gerade dabei einen Mix zu erstellen. Die Idee mit den Variablen ist zum Kürzen auf jeden Fall nicht schlecht. Da spart man sich die zweite Max Abfrage, was Übersicht schafft. Der Performancegewinn hält sich aber in Grenzen. Bei sehr vielen gleichzeitigen Anfragen, wird der Unterschied eher auffallen.

Aber das Problem bei mehreren Suchstrings bleibt bestehen. Ich muss mal testen ob es schneller ist alles in eine Abfrage zu packen oder jeden Suchstring einzeln zu nehmen und die Ergebnisse dann in eine extra Tabelle zwischenzuspeichern.

Über weitere Anregungen freue ich mich

Tribbi

 

[Yaslaw]

Die Frage die du dir beantworten musst, ist die folgende.
Wie willst du die Resultate der mehreren Suchstrings handhaben?

Du kannst die MATCH-Resultate eines jedes Suchstrings zusammenzählen

MATCH(TITLE) AGAINST('$value1' IN BOOLEAN MODE) + MATCH(TITLE) AGAINST('$value2' IN BOOLEAN MODE) AS REL

Wenn du mit PHP arbeitest (und so sieht es aus mit $value), dann kannst du das Script elegant in PHP zusammensetzen

<?php 
//Testdaten
$value1 = 'abc';
$value3 = 'ghi';

//Values auswerten und in einem Array zusammenfassen
$values = array();
if($value1) $values[] = $value1;
if($value2) $values[] = $value2;
if($value3) $values[] = $value3;

//mit jedem Befehl eine MATCH-Statement erstellen
$matches = array_map(create_function('$value', 'return "MATCH(TITLE) AGAINST(\'{$value}\' IN BOOLEAN MODE)";'), $values);
//Alle MATCH-Statements mit einem + zu einem String zusammenfessen
$rel = implode(' + ', $matches);

//Das SQL erstellen
$sql = "
SELECT
    A.title,
    A.rel
FROM
    (
        SELECT
            dat.title,
            @cnt := @cnt + IF(dat.rel = @rel, 0, 1) AS calc_cnt,
            @rel = dat.rel AS rel
        FROM
            (SELECT @rel := 0, @cnt := 0) AS vars,
            (SELECT 
                TITLE AS title  
                {$rel} AS REL
            FROM 
                Daten
            ORDER BY REL DESC) AS dat
    ) AS A";

echo $sql;

?>

Ausgabe:

SELECT
    A.title,
    A.rel
FROM
    (
        SELECT
            dat.title,
            @cnt := @cnt + IF(dat.rel = @rel, 0, 1) AS calc_cnt,
            @rel = dat.rel AS rel
        FROM
            (SELECT @rel := 0, @cnt := 0) AS vars,
            (SELECT 
                TITLE AS title  
                MATCH(TITLE) AGAINST('abc' IN BOOLEAN MODE) + MATCH(TITLE) AGAINST('ghi' IN BOOLEAN MODE) AS REL
            FROM 
                Daten
            ORDER BY REL DESC) AS dat
    ) AS A

 

[TribunM]

Ja klar nutze ich PHP. Eine 100%ige Lösung habe ich immer noch nicht gefunden. Momentan siehts bei mir ähnlich zu deinem Vorschlag aus. Wenn man die Anzahl der Suchstrings begrenzt, ist es sogar recht performant .

On the fly ist das allgemein aber "noch" nicht zu gebrauchen. Vielleicht werde ich die Matchings per Cron oder SQL Evente vor der eigentlichen Abfrage durchlaufen lassen, zwischenspeichern dann nur noch ausgeben, was meinst du?

Denke das ist in jedem Fall schneller. Das Einzige ist nur, dass man immer einen Zeitverzug hinnehmen muss.