Volltextsuche

[mikluxo]

Hallo.
Ich habe folgendes Problem:

Ich möchte eine Volltextsuche in ein PHP-Script implementieren. Dafür habe ich die entsprechende Spalte "definition" als FULL TEXT definiert. Als Datentyp ist TEXT gesetzt. Ich möchte aber nicht, dass nach einzelnen Wörtern des Suchstrings gesucht wird, sondern es müssen in den Datensätzen alle Wörter des Suchstrings vorkommen. Gibt man also "Insel Pazifik" in das Suchfeld ein, müssen nur Datensätze ausgegeben werden, in denen diese beiden Begriffe zusammen vorkommen, z.B:

"Die Howlandinsel ist eine kleine Insel im Pazifik."
"Schiffsbrüchige wurden von einer einsamen Insel im Pazifik gerettet."
usw.
Die Reihenfolge ist dabei egal.

Dafür möchte ich den Operator "+" verwenden ('+Insel +Pazifik'), aber wie kann ich dieses Pluszeichen richtig an die Suchvariable übergeben? Ich habe es folgendermaßen versucht, aber das geht irgendwie nicht, denn es wird kein Datensatz gefunden:

$abfrage = "select * from tabelle
WHERE MATCH (definition) AGAINST ('implode(' +', explode(' ', $suche))' IN BOOLEAN MODE);

Die Suchvariable vom input-Feld ist am Anfang des Scripts bereits getrimt:
$suche = trim($_GET["suchbegriff"])

Ich dachte, vielleicht gilt dieses trim nicht mehr und habe in den Code ein weiters trim hinzugefügt, aber es geht trotzdem nicht:
MATCH (definition) AGAINST ('trim(implode(' +', explode(' ', $suche)))' IN BOOLEAN MODE);

Was mache ich falsch?

 

[akretschmer]

Was mache ich falsch?

Falsche DB? ;-)

Mal so als Test mit PostgreSQL:

test=# \d mikluxo_fts
Tabelle »public.mikluxo_fts«
Spalte |  Typ  | Attribute
--------+---------+-----------
id  | integer |
t  | text  |

test=# select * from mikluxo_fts;
id |  t 
----+----------------------------------------------------------------------
  1 | Die Howlandinsel ist eine kleine Insel im Pazifik.
  2 | Schiffsbrüchige wurden von einer einsamen Insel im Pazifik gerettet.
  3 | Heute ist endlich PostgreSQL 9.6 erschienen!
(3 Zeilen)

Also eine kleine Tabelle mit Deinen Texten, kein Index (bis jetzt)

test=*# explain select * from mikluxo_fts where to_tsvector('german',t) @@ to_tsquery('german', 'Insel & Pazifik & wurden');
  QUERY PLAN 
------------------------------------------------------------------------------------------------
Seq Scan on mikluxo_fts  (cost=10000000000.00..10000000001.79 rows=1 width=36)
  Filter: (to_tsvector('german'::regconfig, t) @@ '''insel'' & ''pazif'' & ''wurd'''::tsquery)
(2 Zeilen)

test=*# select * from mikluxo_fts where to_tsvector('german',t) @@ to_tsquery('german', 'Insel & Pazifik & wurden');
id |  t 
----+----------------------------------------------------------------------
  2 | Schiffsbrüchige wurden von einer einsamen Insel im Pazifik gerettet.
(1 Zeile)

test=*#

Die hohen Kosten kommen, weil set_enable_seqscan ausgeschaltet ist. Ich erzeuge mal einen Index:

test=*# create index idx_fts on mikluxo_fts using gin (to_tsvector('german', t)); 
CREATE INDEX

und wiederhole die Abfrage:

test=*# select * from mikluxo_fts where to_tsvector('german',t) @@ to_tsquery('german', 'Insel & Pazifik & wurden');
id |  t 
----+----------------------------------------------------------------------
  2 | Schiffsbrüchige wurden von einer einsamen Insel im Pazifik gerettet.
(1 Zeile)

test=*# explain select * from mikluxo_fts where to_tsvector('german',t) @@ to_tsquery('german', 'Insel & Pazifik & wurden');
  QUERY PLAN 
----------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on mikluxo_fts  (cost=16.00..20.26 rows=1 width=36)
  Recheck Cond: (to_tsvector('german'::regconfig, t) @@ '''insel'' & ''pazif'' & ''wurd'''::tsquery)
  ->  Bitmap Index Scan on idx_fts  (cost=0.00..16.00 rows=1 width=0)
  Index Cond: (to_tsvector('german'::regconfig, t) @@ '''insel'' & ''pazif'' & ''wurd'''::tsquery)
(4 Zeilen)

Nun nutzt er auch den Index.

Noch ein paar Tests:

test=*# select * from mikluxo_fts where to_tsvector('german',t) @@ to_tsquery('german', 'erschienen');
id |  t 
----+----------------------------------------------
  3 | Heute ist endlich PostgreSQL 9.6 erschienen!
(1 Zeile)

test=*# select * from mikluxo_fts where to_tsvector('german',t) @@ to_tsquery('german', 'erschienen & Pazifik');
id | t
----+---
(0 Zeilen)

test=*#

Es kann so einfach sein, gell?

 

[mikluxo]

Hallo Akretschmer.
Vielen Dank für deine Mühe. Ich weiß deine Hilfsbereitschaft wirklich zu schätzen. Aber diese neu Datenbank ist mir völlig fremd. Ich werde über diese Syntax überhaupt nicht klar. Außerdem kann ich den Code nicht testen, da die Datenbank auf meinem Hosting-Server gar nicht installiert ist. Die Aufgabe sollte doch auch mit MySql gelöst werden. Vielleicht hat jemand eine Idee.

 

[akretschmer]

Ich werde über diese Syntax überhaupt nicht klar.

Das kann man lernen. Ist alles super dokumentiert. Aber wenn das Dein Hoster nicht anbietet, dann hast nur wenige Möglichkeiten:

• damit leben
• ihn treten, PostgreSQL bereit zu stellen
• ihn wechseln
• Dir ein Root-System zu suchen
• irgendwo in der Cloud Dir einen passenden Dienst zu suchen
• warten, bis MySQL das kann
• ...

 

[akretschmer]

Aber diese neu Datenbank ist mir völlig fremd. Ich werde über diese Syntax überhaupt nicht klar.

Nur um das mal kurz zu erklären:

der Index geht über diesen Ausdruck:

test=*# select to_tsvector('german', t) from mikluxo_fts;
  to_tsvector   
-----------------------------------------------------------------------
 'howlandinsel':2 'insel':6 'klein':5 'pazif':8
 'einsam':5 'gerettet':9 'insel':6 'pazif':8 'schiffsbruch':1 'wurd':2
 '9.6':5 'endlich':3 'erschi':6 'heut':1 'postgresql':4
(3 Zeilen)

Das sind also, um es mal so zu sagen, die Stammwörte aus dem Text gezogen. Das wird indiziert.

to_tsquery() liefert wiederum:

test=*# select to_tsquery('german', 'Insel & Pazifik');
to_tsquery
-------------------
'insel' & 'pazif'
(1 Zeile)

Das @@ ist eim Operator, der prüft, ob tsvector (das ist das obere, was den Text in Grundbestandteile zerlegt) mit dem, was tsquery liefert, übereinstimmt bzw. enthält. Das ist kein simpler Vergleich, daher benötigen wir hier einen recht speziellen Indextyp, GIN (Generalized Inverted Index), einer von 2 Indexen, den PG für Fulltextsuche bietet. Mehr dazu hier:

PostgreSQL: Documentation: 9.6: GIN and GiST Index Types


Ja, mag auf den ersten Blick alles recht komplex erscheinen. Ist es auch, was PG da bietet. Kann man aber lernen, und die Doku ist auch sehr gut. Und es gibt eine sehr hilfreiche Community, als deren Teil ich mich z.B. sehe. Auch wenn ich mein Geld mittlerweile zu 100% mit PostgreSQL-Support verdiene mache ich sowas noch nebenbei...

 

[mikluxo]

Vielen Dank, aber ich muss derzeit noch bei Mysql bleiben. Ich kann nicht das ganze PHP-Script ändern, nur weil ich mit einer Funktion nicht klarkomme. Ich bin mir sicher, dass meine Aufgaben ganz leicht mit Mysql bewältigt werden kann. Nur habe ich noch leider keine ausreichenden Kenntnisse.

 

[mikluxo]

Kennt denn keiner eine Lösung?