Suchfunktion ohne MySQL Fulltext mit LIKE

Diese Seite verwendet Cookies. Durch die Nutzung unserer Seite erklären Sie sich damit einverstanden, dass wir Cookies setzen. Weitere Informationen

  • Suchfunktion ohne MySQL Fulltext mit LIKE

    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

    SQL-Abfrage

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


    - Bei: Hier mein Suchbegriff OR-Verknüpfung

    SQL-Abfrage

    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).

  • 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.

  • 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:

    dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
    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.

  • Hi,

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

    Quellcode

    1. 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


    Quellcode

    1. 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
    3. 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:

    Quellcode

    1. ALTER TABLE clique ADD FULLTEXT KEY name (name);
    2. ALTER TABLE clique ADD FULLTEXT KEY beschreibung (beschreibung);
    3. ALTER TABLE clique ADD FULLTEXT KEY thema (thema);
    4. ALTER TABLE clique ADD FULLTEXT KEY region (region);

  • Was hast du vor?

    Eine MySQL Abfrage ist doch so aufgebaut:

    Quellcode

    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

    Quellcode

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

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

    Also:

    Quellcode

    1. 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

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von Snowflake ()

  • Okay; Soweit, so gut:

    Quellcode

    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:

    Quellcode

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

    Quellcode

    1. $IDs = '2,3';



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

    Quellcode

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


    Ergebnis = 1; Sollte aber 2 sein.

    Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von Gnex ()

  • 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:

    Quellcode

    1. $sqlSearch = "SELECT searchData FROM gx_search WHERE searchID = ".$this->cn." AND userID = ".WCF::getUser()->userID." AND searchType = 'clique'";
    2. $search = WCF::getDB()->getFirstRow($sqlSearch);
    3. if (!isset($search['searchData'])) {
    4. require_once(WCF_DIR.'lib/system/exception/IllegalLinkException.class.php');
    5. throw new IllegalLinkException();
    6. }
    8. $IDs = implode(',', unserialize($search['searchData']));
    10. ### Page-Aufteilung Anfang
    11. $sqlCount = "SELECT COUNT(*) AS count FROM clique WHERE ID IN ('".$IDs."')";
    12. $rowCount = WCF::getDB()->getFirstRow($sqlCount);
    13. $this->items = $rowCount['count'];
    15. if (isset($_REQUEST['pageNo'])) {
    16. $this->pageNo = $_GET['pageNo'];
    17. } else {
    18. $this->pageNo = '1';
    19. }
    21. $this->itemsPerPage = THREAD_POSTS_PER_PAGE;
    23. $this->pages = intval(ceil($this->items / $this->itemsPerPage));
    25. if ($this->pageNo > $this->pages) $this->pageNo = $this->pages;
    26. if ($this->pageNo < 1) $this->pageNo = 1;
    28. $this->startIndex = ($this->pageNo - 1) * $this->itemsPerPage;
    29. $this->endIndex = $this->startIndex + $this->itemsPerPage;
    30. $this->startIndex++;
    31. if ($this->endIndex > $this->items) $this->endIndex = $this->items;
    32. WCF::getTPL()->assign('pageNo', $this->pageNo);
    33. WCF::getTPL()->assign('pages', $this->pages);
    34. ### Page-Aufteilung Ende
    36. $sql = "SELECT * FROM clique WHERE ID IN ('".$IDs."') ORDER BY time DESC";
    37. $result = WCF::getDB()->sendQuery($sql, $this->itemsPerPage, ($this->pageNo - 1) * $this->itemsPerPage);
    38. while ($row = WBBCore::getDB()->fetchArray($result)) {
    39. $cliqueUebersicht = new CliqueUebersicht($row['ID']);
    40. $cliqueUebersicht->handleData($row);
    41. $this->inhaltSearch[] = $cliqueUebersicht;
    42. }
    Alles anzeigen

  • Hallo,

    die Suche spuckt bei searchData folgendes aus:

    Quellcode

    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

    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:

    Quellcode

    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":

    Quellcode

    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?

  • Gnex schrieb:

    Quellcode

    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

    d0nut schrieb:

    Quellcode

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

    Gnex schrieb:

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

    Gnex schrieb:

    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:

    Quellcode

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

  • 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.

  • Gnex schrieb:

    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.