Suchfunktion ohne MySQL Fulltext mit LIKE

Professional

Saturday, March 14th 2009, 7:50pm

Hallo,

ich bin dabei eine Suchfunktion zu schreiben und habe gerade eine Denkblockade. Die Suchfunktion soll können:

- Explode der Wörter:

- Bei: "Hier mein Suchbegriff" AND-Verknüpfung

	Source code
1	SELECT * FROM datenbank WHERE suchfeld1 LIKE '%Hier%' AND suchfeld1 LIKE '%mein%' AND suchfeld1 LIKE '%Suchbegriff%'

- Bei: Hier mein Suchbegriff OR-Verknüpfung

	Source code
1	SELECT * FROM datenbank WHERE suchfeld1 LIKE '%Hier%' OR suchfeld1 LIKE '%mein%' OR suchfeld1 LIKE '%Suchbegriff%'

Das ganze soll dann auch noch für mehrere Suchfelder ("suchfeld2", ...) möglich sein.

Kann mir jemand auf die Sprünge helfen? MySQL-Fulltext bringt mir recht wenig, da es erst ab einer bestimmten Anzahl Einträge richtig funktioniert (hab ich bereits getestet).

Torben Brodt

Administrator

Posts: 8,454

2

Saturday, March 14th 2009, 10:24pm

Naja, die LIKE Suche ist de facto langsam. Das wird keiner leugnen. Wenn du mit vergleichsweise wenigen Daten arbeitest und auch nicht mit einem großen Wachstum rechnest (man kann ja immer noch umsteigen), dann mach eben die LIKE Suche.

Allerdings habe ich deine Frage überlesen? Was klappt denn nicht?
Gruß

Latest blogs: Neuer EX4 Server, Java EE Summit im Juni in München, Kostenlos zur Cebit + Windows 8 Entwickler Schulung

Gnex

Professional

Posts: 294

3

Sunday, March 15th 2009, 2:43pm

Hi,

sofern Du die MySQL-Fulltext-Suche meinst: Ich habe mal laut MySQL-Handbuch eine Testdatenbank erstellt und nur zwei Datensätzte in diese geschrieben; Resultat bei der Suche: 0 - Erst als 8 Datensätze in der Datenbank vorhanden waren, wurden zwei passende Ergebnisse ausgegeben.

Nachnach habe ich bei einem Test einfach 6 Leer-Datensätze dazugeschrieben und auch dort versucht, hierbei hat mir MySQL aber nur einen Eintrag ausgegeben, obwohl die beiden Nicht-Leereinträge das selbe Wort (einmal bei "name" und einmal bei "thema") in sich hatten. Die MySQL-Fulltext-Suche wurde über vier Spalten gezogen: name, beschreibung, thema, region - Das hat also nicht funktioniert und ich weiß leider nicht, wieso.

Torben Brodt

Administrator

Posts: 8,454

4

Sunday, March 15th 2009, 3:24pm

Na gut, wenn die Tabelle nur 6 Einträge enthalten wird brauchen wir nicht diskutieren.

Ansonsten musst du dich mal mit den Parametern beschäftigen. Per Default wird nur nach vollständigen Matches - nicht nach Teilworten gematcht.
Daher solltest du jeden Begriff mit einem * ergänzen und die BOOLEAN Suche verwenden.
Weil beim BOOLEAN Mode die Relevanz immer 1 oder 0 ist, könnte man außerdem noch die Relevanz des normalen Match hinzuziehen.
Wie sich das alles auf die Performance auswirkt habe ich selbst noch nicht beobachtet. Solltest du solche Experimente machen würde ich mich freuen, wenn du es hier zusammenfasst.

UPDATE Dazu interessant:

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Quoted

It should be noted in the documentation that IN BOOLEAN MODE will almost always return a relevance of 1.0. In order to get a relevance that is meaningful, you'll need to:
SELECT MATCH('Content') AGAINST ('keyword1 keyword2') as Relevance FROM table WHERE MATCH ('Content') AGAINST('+keyword1 +keyword2' IN BOOLEAN MODE) HAVING Relevance > 0.2 ORDER BY Relevance DESC

Notice that you are doing a regular relevance query to obtain relevance factors combined with a WHERE clause that uses BOOLEAN MODE. The BOOLEAN MODE gives you the subset that fulfills the requirements of the BOOLEAN search, the relevance query fulfills the relevance factor, and the HAVING clause (in this case) ensures that the document is relevant to the search (i.e. documents that score less than 0.2 are considered irrelevant). This also allows you to order by relevance.

This may or may not be a bug in the way that IN BOOLEAN MODE operates, although the comments I've read on the mailing list suggest that IN BOOLEAN MODE's relevance ranking is not very complicated, thus lending itself poorly for actually providing relevant documents. BTW - I didn't notice a performance loss for doing this, since it appears MySQL only performs the FULLTEXT search once, even though the two MATCH clauses are different. Use EXPLAIN to prove this.

Latest blogs: Neuer EX4 Server, Java EE Summit im Juni in München, Kostenlos zur Cebit + Windows 8 Entwickler Schulung

Gnex

Professional

Posts: 294

5

Sunday, March 15th 2009, 6:36pm

Hi,

ich bin schon dabei zu testen, jedoch erhalte ich einen Fehler:

PHP Quellcode

SELECT MATCH('".$this->suchbegriff."') AGAINST ('name beschreibung thema region') as Relevance FROM clique WHERE MATCH ('".$this->suchbegriff."') AGAINST('+name +beschreibung +thema +region' IN BOOLEAN MODE) HAVING Relevance > 0.2 ORDER BY Relevance DESC

Source code

1
2
3

Invalid SQL: SELECT MATCH('gnex') AGAINST ('name beschreibung thema region') as Relevance FROM clique WHERE MATCH ('gnex') AGAINST('+name +beschreibung +thema +region' IN BOOLEAN MODE) HAVING Relevance > 0.2 ORDER BY Relevance DESC

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''gnex') AGAINST ('name beschreibung thema region') as Relevance FROM clique WHER' at line 1

Fulltext so hinzugefügt:

Source code

ALTER TABLE clique ADD FULLTEXT KEY name (name);
ALTER TABLE clique ADD FULLTEXT KEY beschreibung (beschreibung);
ALTER TABLE clique ADD FULLTEXT KEY thema (thema);
ALTER TABLE clique ADD FULLTEXT KEY region (region);

Snowflake

Professional

Posts: 370

6

Monday, March 16th 2009, 1:09am

Was hast du vor?

Eine MySQL Abfrage ist doch so aufgebaut:

	SQL Code
1	SELECT tabellenspalten FROM tabelle WHERE x=y AND a=b ORDER BY XYZ DESC

Da macht für mich der Part hier deswegen keinen Sinn

	SQL Code
1	SELECT MATCH('".$this->suchbegriff."') AGAINST ('name beschreibung thema region') AS Relevance

[/syntag]
Weil dieses Feld gibt es in der Datenbank ja nicht.

Also:

SQL Code

SELECT tabellenspalte AS `Relevance` FROM clique WHERE MATCH ('".$suchbegriff."') AGAINST('+name +beschreibung +thema +region' IN BOOLEAN MODE) HAVING Relevance > 0.2 ORDER BY Relevance DESC

Und wenn du dir jetzt nochmal den richtigen Syntax von Match/Against anschaust, bekommst du das ganze bestimmt hin.
Bei MATCH soll ja nicht die Variable durchsucht werden, sondern die Tabellenspalten definert werden die durchsicht werden sollen against "gegen" der Suchvariable.

Dazu solltest du einen Fulltext hinzufügen indem die ganzen Felder dann aufgelistet sind die für die suche benötigt werden können

Wenn ich mich irre, bitte verbessert mich

This post has been edited 1 times, last edit by "Snowflake" (Mar 16th 2009, 1:16am)

Gnex

Professional

Posts: 294

7

Monday, March 16th 2009, 2:08pm

Hi,

was mich nur wunder ist, dass meine Abfrage dem Beispiel von Sun entspricht.

//Edit: Dein Code funktioniert auch nicht.

This post has been edited 1 times, last edit by "Gnex" (Mar 16th 2009, 2:18pm)

Snowflake

Professional

Posts: 370

8

Monday, March 16th 2009, 3:53pm

Mein Code geht nicht, dass stimmt. Das sollte dir aber auch bewusst geworden sein wenn du mein Text gelesen hast, wodrin ich geschrieben hatte, dass du es sicherlich hinbekommst, wenn du dir jetzt nocheinmal den aufbau der SQL Abfrage mit Match und Against anschaust.

http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html

Schau dir die Beispiele mal an.

Gnex

Professional

Posts: 294

9

Monday, March 16th 2009, 4:05pm

Okay; Soweit, so gut:

	Source code
1	$sql = "SELECT * FROM clique WHERE MATCH (name,beschreibung,thema,region) AGAINST ('".$this->suchbegriff."' IN BOOLEAN MODE)";

Er gibt mir in diesem Fall einen Eintrag aus, er müsste aber zwei ausgeben, denn:

- Eintrag der Ausgegeben wird hat "name" = GneX und "thema" = "GneX"
- Eintrag der nicht Ausgegeben wird hat "thema" = "GneX"

Suchbegriff war "gnex". Somit fehlt ein Eintrag.

//Edit: Die Suche findet doch beide Einträge aber es wird nur einer ausgelesen:

	PHP Quellcode
1	$sql = "SELECT * FROM clique WHERE ID IN ('".$IDs."') ORDER BY time DESC";

	PHP Quellcode
1	$IDs = '2,3';

//Edit: Ich vermute den Fehler beim Zählen der Einträge, um eine Seitenaufteilung zu bekommen:

PHP Quellcode

1
2
3

$sqlCount = "SELECT COUNT(*) AS count FROM clique WHERE ID IN ('".$IDs."')";
  	$rowCount = WCF::getDB()->getFirstRow($sqlCount);
  	$this->items = $rowCount['count'];

Ergebnis = 1; Sollte aber 2 sein.

This post has been edited 2 times, last edit by "Gnex" (Mar 16th 2009, 4:22pm)

Snowflake

Professional

Posts: 370

10

Monday, March 16th 2009, 4:22pm

Wie viel einträge hast du denn in deiner Datenbank? Wie du vorher schon gesagt hattest, benötigt die MAtch/Against Suche schon ein paar mehr einträge in der Datenbank um richtige Suchergebnisse zu erziehlen.

	PHP Quellcode
1	$sql = "SELECT * FROM clique WHERE ID IN ('".$IDs."') ORDER BY time DESC";

Diese Abfrage funktoniert gar nicht. Da fehlt der entscheidene mysql_query()

Gnex

Professional

Posts: 294

11

Monday, March 16th 2009, 4:35pm

Hi,

momentan 2 Testeinträge - und es werden auch diese beiden Einträge richtig gefunden, nur nicht ausgegeben. Ich speichere das Suchergebnis in der DB, wie es auch das WCF/WBB macht.

Der Query ist natürlich da, ich hab ihn nur nicht mitgepostet, gesamter Auslese-Code:

PHP Quellcode

$sqlSearch = "SELECT searchData FROM gx_search WHERE searchID = ".$this->cn." AND userID = ".WCF::getUser()->userID." AND searchType = 'clique'";
		  $search = WCF::getDB()->getFirstRow($sqlSearch);
		  if (!isset($search['searchData'])) {
		  	require_once(WCF_DIR.'lib/system/exception/IllegalLinkException.class.php');
			  throw new IllegalLinkException();
	  	}
 
  	$IDs = implode(',', unserialize($search['searchData']));
 
  	### Page-Aufteilung Anfang
  	$sqlCount = "SELECT COUNT(*) AS count FROM clique WHERE ID IN ('".$IDs."')";
  	$rowCount = WCF::getDB()->getFirstRow($sqlCount);
  	$this->items = $rowCount['count'];
 
  	if (isset($_REQUEST['pageNo'])) {
    	$this->pageNo = $_GET['pageNo'];
  	} else {
    	$this->pageNo = '1';
  	}
 
  	$this->itemsPerPage = THREAD_POSTS_PER_PAGE;
 
		  $this->pages = intval(ceil($this->items / $this->itemsPerPage));
 
		  if ($this->pageNo > $this->pages) $this->pageNo = $this->pages;
		  if ($this->pageNo < 1) $this->pageNo = 1;
 
		  $this->startIndex = ($this->pageNo - 1) * $this->itemsPerPage;
		  $this->endIndex = $this->startIndex + $this->itemsPerPage;
		  $this->startIndex++;
		  if ($this->endIndex > $this->items) $this->endIndex = $this->items;
		  WCF::getTPL()->assign('pageNo', $this->pageNo);
		  WCF::getTPL()->assign('pages', $this->pages);
  	### Page-Aufteilung Ende
 
  	$sql = "SELECT * FROM clique WHERE ID IN ('".$IDs."') ORDER BY time DESC";
  	$result = WCF::getDB()->sendQuery($sql, $this->itemsPerPage, ($this->pageNo - 1) * $this->itemsPerPage);
  	while ($row = WBBCore::getDB()->fetchArray($result)) {
    	$cliqueUebersicht = new CliqueUebersicht($row['ID']);
    	$cliqueUebersicht->handleData($row);
    	$this->inhaltSearch[] = $cliqueUebersicht;
  	}

Torben Brodt

Administrator

Posts: 8,454

12

Monday, March 16th 2009, 6:48pm

Fasse dein Problem bitte mal grob in wenige Queries mit für uns nachvollziehbare Werten zusammen.
Deinen Code können wir nicht nachvollziehen, weil die ganzen Variablen unbekannt sind.

Außerdem macht der MySQL Volltextindex bei nur zwei Einträgen keinen Sinn. Das steht auch in der Doku.

Latest blogs: Neuer EX4 Server, Java EE Summit im Juni in München, Kostenlos zur Cebit + Windows 8 Entwickler Schulung

Gnex

Professional

Posts: 294

13

Monday, March 16th 2009, 8:30pm

Hallo,

die Suche spuckt bei searchData folgendes aus:

	Source code
1	a:2:{i:0;s:1:"2";i:1;s:1:"3";}

Das heißt, es wurden beide Einträge (ID 2 und ID 3) gefunden. Nun habe ich diesen Eintrag aus der DB mit

	PHP Quellcode
1	$IDs = implode(',', unserialize($search['searchData']));

aufgelöst. Nun sieht die Ausgabe davon so aus: "2,3".

Nun sollte die Abfrage einfach nur noch schauen, ob die ID der Clique hier dabei ist, also mit auslesen:

	SQL Code
1	SELECT * FROM clique WHERE ID IN ('".$IDs."') ORDER BY time DESC"

Jedoch gibt er mir nicht beide Cliquen (2 und 3) sondern nur 2 aus. Meine Vermutung war nun, dass das "Counten":

	SQL Code
1	SELECT COUNT(*) AS count FROM clique WHERE ID IN ('".$IDs."')

nur einen Eintrag findet und meine Vermutung lag richtig. Ich habe mir den Wert ausgegen lassen und siehe da, er gibt 1 aus - also einen DB-Eintrag, es sollten aber zwei sein.

Dort liegt also der Fehler, nur wie bekomme ich diesen weg?

Torben Brodt

Administrator

Posts: 8,454

14

Tuesday, March 17th 2009, 9:20am

Also liegt der Fehler nicht beim Matchen
Um die Werte des IN gehören keine Anführungszeichen.

Vergleich:

SQL Code

1
2

SELECT COUNT(*) AS count FROM clique WHERE ID IN (1,2)
SELECT COUNT(*) AS count FROM clique WHERE ID IN ('1,2')

Latest blogs: Neuer EX4 Server, Java EE Summit im Juni in München, Kostenlos zur Cebit + Windows 8 Entwickler Schulung

Gnex

Professional

Posts: 294

15

Tuesday, March 17th 2009, 3:52pm

Hi,

danke für den Tipp. Hab ich geändert, leider immer noch der selbe Fehler.

Torben Brodt

Administrator

Posts: 8,454

16

Wednesday, March 18th 2009, 6:20pm

und das selbe query im phpmyadmin?

	SQL Code
1	SELECT COUNT(*) AS count FROM clique WHERE ID IN (1,2)

vielleicht existieren die einträge nicht

Latest blogs: Neuer EX4 Server, Java EE Summit im Juni in München, Kostenlos zur Cebit + Windows 8 Entwickler Schulung

Gnex

Professional

Posts: 294

17

Wednesday, March 18th 2009, 8:21pm

Hallo,

in phpMyAdmin zählt er zwei Einträge, also richtig.

Torben Brodt

Administrator

Posts: 8,454

18

Wednesday, March 18th 2009, 8:58pm

Quoted from "Gnex"

	SQL Code
1	SELECT COUNT(*) AS count FROM clique WHERE ID IN ('".$IDs."')

[..]Ich habe mir den Wert ausgegen lassen und siehe da, er gibt 1 aus

Quoted from "d0nut"

	SQL Code
1	SELECT COUNT(*) AS count FROM clique WHERE ID IN (1,2)

Quoted from "Gnex"

Hab ich geändert, leider immer noch der selbe Fehler.

Quoted from "Gnex"

in phpMyAdmin zählt er zwei Einträge, also richtig.

Willst du damit sagen, dass exakt die gleiche Anfrage. (Hast dir den Query ja ausgeben lassen) im phpmyadmin andere Ergebnisse bringt als im PHP Code?
Sorry, aber da musst du was falsch gemacht haben.

Minimalbeispiel:

PHP Quellcode

class FooPage extends AbstractPage() {
  function show() {
  $rowCount = WCF::getDB()->getFirstRow("SELECT COUNT(*) AS count FROM clique WHERE ID IN (1,2)");
  echo $rowCount['count'];
  }
}

Latest blogs: Neuer EX4 Server, Java EE Summit im Juni in München, Kostenlos zur Cebit + Windows 8 Entwickler Schulung

Gnex

Professional

Posts: 294

19

Saturday, March 21st 2009, 11:13am

Hallo,

nun funktioniert alles. Lag an einem kleinen Fehler: Bei der Abfrage "SELECT * FROM clique WHERE ID IN (".$IDs.") ORDER BY time DESC" standen zwei ' in der Klammer.

Vielen Dank für eure Hilfe.

Nebenbei habe ich auch noch ein wenig getestet: Eine "normale" Suche mit LIKE ist im gegensatz zu Fulltext ca. 1,145 ms langsamer. Dafür verursacht die Fulltext-Suche ein ganz bisschen mehr Auslastung (ca. 0,000345%).

Daten gemessen unter einem Debian Lenny System.

Torben Brodt

Administrator

Posts: 8,454

20

Saturday, March 21st 2009, 1:43pm

Quoted from "Gnex"

Nebenbei habe ich auch noch ein wenig getestet: Eine "normale" Suche mit LIKE ist im gegensatz zu Fulltext ca. 1,145 ms langsamer. Dafür verursacht die Fulltext-Suche ein ganz bisschen mehr Auslastung (ca. 0,000345%).

Deinen Benchmark in allen Ehren, aber je nachdem obs ein Plugin für große Foren wird oder nur für deine eigene Anwendung ist, solltest du viele, viele Demodaten einfügen. Und weil die Volltextsuche Stoppwörter ignoriert, müssen diese Demodaten sogar ziemlich variabel/realistisch sein. Dann sollte die Volltext-Suche die LIKE Suche um Längen schlagen.

Bei deiner LIKE Suche werden die gesamten angefragten Daten durchsucht. Je nachdem wie flexibel (Char/Varchar, ...) und groß (ints/blob) die Zeilen der Tabelle sind dauert das mitunter ziemlich lange.
Schlüssel, wie sie bei der Volltextsuche verwendet werden hingegen sind optimiert (alphabetisch z.B.). Sie liegen in einer seperaten Datei, verweisen dann auf das Speicherabbild der Daten und machen nur einen Bruchteil der Dateigröße aus.
Außerdem werden sie bis zu einer bestimmten Größe (key_buffer_size) von MySQL im Arbeitsspeicher gecached (was länger hält als der Query-Daten-Cache, der spätestens bei der nächsten Tabellenänderung gelöscht wird).

Wenn du erstmal nur Bahnhof verstehst - kein Problem

Vielleicht hilft es irgendwann mal.

Jedenfalls solltest du auf uns hören und bei großen Daten von der LIKE Suche absehen.
Wenn du kein großes Datenaufkommen erwartest nimm LIKE - wenn es so böse wäre, dann hätten die Entwickler es nicht programmiert.

Latest blogs: Neuer EX4 Server, Java EE Summit im Juni in München, Kostenlos zur Cebit + Windows 8 Entwickler Schulung

Coder Forum