Was ist die beste Lösung für eine Keywort-Filterung per SQL ?

[Rollo62]

Hallo zusammen,

ich möchte gerne ein neues System aufbauen, was Tabellen nach Keyworten filtern kann.

Der Gedanke ist eine globale Keywort-Tabelle zu haben,

Code:

TabKeywort
Id        Keywort
1         Apfel
2         Birne
3         Banane
4         Grün
5         Gelb
...
998       Früchte
999       Obst

und eine Ziel-Tabelle, die ich eigentlich filtern möchte

Code:

TabZiel
Id        Descr      
101       Lorem1
102       Lorem2
103       Lorem3
104       Lorem4
...

dazu gibt es zu jedem Record eine Mapping-Tabelle, welche die entsprechenden Keyworte auflistet

Code:

TabMap
Id        IdZiel     IdKeywort
201       101        1      // Ziel 101 ==> Apfel, grün       
202       101        4
203       102        2      // Ziel 102 ==> Birne, gelb, Früchte, Obst
204       102        5
205       102        998
206       102        999
207       103        3      // Ziel 103 ==> Banane, gelb
208       103        5
...

Sowas in der Art, so das jeder Eintrag in TabZiel eine 1:n Liste von individuellen Keyworten bekommt.

Wie würde man das optimal anlegen, so dass es sich gut nach Keyworten filten lässt ?

Also es müsste so ungefähr gehen, aber meine Fragen sind:
Geht das besser/effektiver, um nach variablen Listen von Text-Keyworten zu filtern ?
Gibt es dafür Lösung, die genau auf so einen Fall ausgelegt ist, mit variablern Parametern ?
Wie bekomme ich in/vor dem SQL-SELECT die Keyworte am Besten als String-Parameter rein, und diese dann noch CaseInsenitiv oder LIKE gesucht ?

Code:

//Hier könnte man auch was Verschachteltes davorbauen, statt die Integer Id's von aussen reinzugeben,
//direkt mit String-Keys reinzukommen, und diese in aus der TabKeywork zu IdKeywort zu mappen
SELECT z.* 
FROM TabZiel z, TabMap m, TabKeywort k
WHERE ( z.Id        = m.IdZiel  )
AND   (( m.IdKeywort = :PInt1   )
  OR   ( m.IdKeywort = :PInt2   )
  OR   ( m.IdKeywort = :PInt3   )
  ...  //<== Hier ist es unschön, weil die Parameter-Liste jeweils unterschiedlich lang ist
ORDER BY z.Id
;

Meine Überlegungen zu den drei Tabellen sind:

• Die TabZiel Tabelle kann mal sehr groß werden (weiss noch nicht genau), weil Texte/Links/Sonstwas da drin sein können,
  deshalb sollte das zwischengeschaltete Mapping dafür optimal sein (mit Integer Id's).
  
• Die TabMap Tabelle wird dann entsprechend noch größer (was die RecCount betrifft),
  deshalb auch hier reines Integer-Mapping um Platz zu sparen.
  
• Die TabKeywort Tabelle wird auch anwachsen mit der Zeit, aber überschaubar bleiben,
  deshalb können mögliche Keyworte schnell und leicht gefunden werden.

Oder mache ich mir da zuviel Gedanken, und man könnte statt Integer-ID's auch GUUID's, Hashes oder sonstwas Modernes verwenden ?
Weil die Datenbanken mittlerweile vielleicht so perfekt sind, dass die das selbst optimieren können
(es geht mit dabei um DB's von Sqlite, zu MySql, Postgres bis MsSql, das liegt noch nicht fest, deshalb Standard-SQL ).

Macht man sowas heutzutage überhaupt besser mit Mongo (aber ich würde schon gerne bei SQL bleiben) ?

[jfheins]

Ich würde die empfehlen, dafür ein spezialisiertes Tool zu verwenden.

Ich habe mal eine Anwendung geschrieben, wo dann bei jeder Suche die komplette DB gezogen und durchsucht wird. Je mehr Zeug in der SB gespeichert wird, desto langsamer die Suche. Aber es war damals einfach zu programmieren und ist offenbar bis heute ausreichend.

Heutzutage würde ich das anderes angehen: Erfinde nicht das Rad neu, sondern nutze bspw. elasticsearch oder Azure Search (falls du deine Anwendung bereits in oder mit Azure betreibst).

Da wirfst du einfach deine Dokumente mit ID rein und kannst später nach Keywords suchen. Man könnte sagen, der harte Teil ist fertig, man braucht nur noch das interface

[jobo]

Wenn Du Dir ein eigenes DM baust, hast Du natürlich alle Möglichkeiten, es für Deine Bedürfnisse exakt abzustimmen. Und das kann man ausführlich machen und viel Zeit investieren.

Es gibt viele fertige Lösungen dafür, wie schon vorgeschlagen. Ich schlage vor, schau Dir mal die Volltextsuche von Postgres an. Sie bietet sehr viele Möglichkeiten, gute Performance und ist nur eines der vielen guten Features dieser DB. Du sparst Dir ein separates Produkt für Deine Anwendung.

Wenn Du Datenbank unabhängig sein willst und diese "Spezialaufgabe" einem "Spezialtool" anvertrauen willst, dann wäre sowas wie das vorgeschlagene Elastic wahrscheinlich die bessere Wahl.

[Rollo62]

Ja, Elasticsearch und ähnliches habe ich in Erwägung gezogen.
Das Problem ist aber das es möglichst auch auf kleinen, virtuellen Servern laufen soll.

Mit der Integer Filterung sollte doch eigentlich jede DB klarkommen,
Ich hoffe auch effizient genug.
Das ist der Grundgedanke, damit der Filter auf der DB läuft,
und eben nicht Alles runtergeladen werden muss.

StoredProcedures wären auch eine Option, z.B.

Spricht denn generell etwas gegen eine eigene, kleine Filtersuche, wie unten ?
Selbst wenn TabZiel im GB Bereich liegt sollte das flott rüberkommen,
Ich habe ähnliche Abfragen in gleicher Größe seit Jahren laufen, ohne Probleme.

In erster Linie wollte ich checken wie man SQL und Parameter dafür optimieren könnte.

[Papaschlumpf73]

Wenn die Anzahl von idKeyword variable ist, kannst du auch eine SP mit einem TABLE-VALUE-Parameter verwenden. Hier kann dann eine variable Anzahl von idKeywords übergeben werden. In der WHERE-Klausel der SP könnte dann stehen

...WHERE (m.IdKeywort IN (SELECT idkey FROM @DeinTableValueParameter))

Und wenn du die Key-Value-Tabelle auch auf dem SQL-Server hast, könnte die SP statt der IDs auch die "echten" Keywords annehmen und die IDs für die WHERE-Klausel aus der Key-Value-Tabelle ermitteln.

Letztendlich wird der optimale Einsatz der Indizes über die Performance entscheiden.

[Delphi.Narium]

Vor Jahren hab' ich mir mal 'ne Suchmaschine für alle meine Quelltexte geschrieben. Die Dateien liegen halt auf diversen Festplatten rum.

Dazu gibt es nun eine Tabelle Quelltexte: Sie enthält eine eindeutige ID je Quelltext und den vollständigen Pfad zu dem Quelltext.

Dann gibt es eine Tabelle Wörter: Sie enthält alle gefundenen Wörter und eine eindeutige ID für das Wort. (Jedes Wort kommt in dieser Tabelle natürlich nur einmal vor.)

Dann gibt es noch eine Tabelle WortQuelltext: Sie stellt die Verbindung zwischen Quelltexte und Wörter her: Spalten sind WortID und QuelltextID.

Die Tabelle Wörter wird banal und einfach aufgebaut: Alles, was kein Buchstabe ist, wird durch einen Zeilenumbruch ersetzt. Das Ergebnis wird eindeutig sortiert. Groß-/Kleinschreibung wird dabei ignoriert. Jede Zeile enthält nun ein Wort.

Die Wörter werden (soweit nicht schon vorhanden) in die Tabelle Wörter eingefügt. Die Tabelle WortQuelltext wird mit den IDs der Wörter und der ID des Quelltextes befüllt.

Per SQL geht die Abfrage in etwa so:

SQL-Code:

select Wörter.Wort, Quelletxte.* from Quelletxte, WortQuelltext, Wörter
where Quelltexte.id = WortQuelltext.QuelltextID
and WortQuelltext.WortID = Wörter.id
and Wörter.Wort in ('eins',zwei'drei','ganz','viele',Wörter']

Index auf jede ID und auf die Wörter.

Ist verblüffend schnell und war in 'nem Tag implementiert, kann man analog natürlich auch auf Blob-Spalten, ... anwenden.

Prinzipiell entspricht die Struktur Deinem Vorschlag aus dem Eingangspost.

Soll das Ganze dann noch etwas fehlertoleranter werden, kann man neben den Wörtern auch noch diverse Spalten für SoundEx, phonetische Suchen (wie z. B. Kölner Phonetik), ... in der Tabelle Wörter anlegen. Da kommt es halt dann ein bisserl auf die konkreten Anforderungen an.

Achso: Da das so gut geklappt hat, gibt es das inzwischen auch für alle HTML-Seiten (wie z. B. ct-Archive von CDs), diverse Tutorials von anno schlagmichtot, Rezepte, ... (egal ob die zu durchsuchenden Texte nun irgendwo auf Datenträgern rumschwirren oder in Datenbanken gesammelt sind). Das Prinzip ist immer das Gleiche und das Schöne daran ist: Die Datenbank ist völlig egal.

[jobo]

Zitat von Rollo62:

..
Das Problem ist aber das es möglichst auch auf kleinen, virtuellen Servern laufen soll.

Mit der Integer Filterung sollte doch eigentlich jede DB klarkommen,
Ich hoffe auch effizient genug.
..
StoredProcedures wären auch eine Option, z.B.

Spricht denn generell etwas gegen eine eigene, kleine Filtersuche, wie unten ?
Selbst wenn TabZiel im GB Bereich liegt sollte das flott rüberkommen,
..
In erster Linie wollte ich checken wie man SQL und Parameter dafür optimieren könnte.

Klein? RAM, Platte, CPU? Eine Postgres Installation hat ca. 90MB glaub ich, weit weg von GB Datenfiles, trotzdem natürlich viel mehr als SQLite. Postgres läuft problemlos auf einem Raspberry. Es gibt fertige Dockerimages dafür (und andere DB natürlich). Kommt vom Nutzeffekt (Plattenverbrauch) stark auf die Konstellation an. Eine Virtualisierung über ein VM Image in VBOX oder VMware benötigt selbst mit Linux ohne X Oberfläche >1-2Gb plus Nutzdaten. Ein winziges Dockerimage (nur DB) ist natürlich toll, wenn es denn zum Host passt. Unter Windows ist es sehr wahrscheinlich nicht besser. Eine SQLite Lib ist natürlich winzig, aber kein Performance Brüller.

Das Prinzip nach vorbestimmten ID zu suchen ist eben das simple Prinzip solch einer Volltextsuche, da sehe ich kein Problem.

StoredProc werden häufig missverstanden als pauschale Beschleunigung, die alles schneller machen. Das ist nicht richtig, SP sind schnell, weil sie per Definition auf Server-lokale Daten zugreifen. Lässt man funktional identischen Code auf einem Client laufen, muss er viele Daten herunterladen. Das ist das, was Du vermeiden willst. Sehr häufig sind normale SQL Statements genauso schnell, so lange sie auch nur ein auf dem Server ausgewertetes (kleines) Ergebnis runterladen. SP bieten allerdings meist viel mehr Möglichkeiten, als reines SQL.

SQL Optimierung ist m.E. sehr Hersteller spezifisch. Zumindest wenn man ordentlich normalisiert hat und keine logischen Fehler im Modell stecken. Performance Optimierung bedeutet u.U. dann ab da auch, eine ordentliche Normalisierung gezielt zurückzunehmen. Der Preis für mehr Geschwindigkeit ist häufig redundante Datenhaltung. Die muss halt trotzdem wasserdicht sein, damit keine Artefakte entstehen. Parameternutzung empfiehlt sich immer, ein SQL, das im Client gemäß Suchanfrage dynamisch Kriterien eingepflanzt bekommt, muss auch dynamisch mit Parametern versorgt werden. Wenn Du Dir dagegen ein Fulltext Search Beispiel mit ts_vektor in PG anschaust, stellst Du fest, hier kann bereits ein Parameter mehrere Suchbegriffe enthalten.

[Rollo62]

Dankesehr für die guten Vorschläge, das zeigt mir das ich nicht ganz falsch liege.

Zitat von Papaschlumpf73:

...WHERE (m.IdKeywort IN (SELECT idkey FROM @DeinTableValueParameter))

Sehr interessant, mit variablen TableParametern habe ich noch nichts gemacht, das schaue ich mir mal an.

Zitat von Papaschlumpf73:

Und wenn du die Key-Value-Tabelle auch auf dem SQL-Server hast, könnte die SP statt der IDs auch die "echten" Keywords annehmen und die IDs für die WHERE-Klausel aus der Key-Value-Tabelle ermitteln.

Du meinst eine temporäre Tabelle für jede Suche zu Erzeugen, an sowas hatte ich auch schon gedacht, aber
ich hätte Bedenken dass dies doch eher weniger effizient ist als die paar Parameter direkt zu übergeben.

Zitat von Delphi.Narium:

Dann gibt es eine Tabelle Wörter: Sie enthält alle gefundenen Wörter und eine eindeutige ID für das Wort. (Jedes Wort kommt in dieser Tabelle natürlich nur einmal vor.)

Dann könnte das eine Art "Wörterbuch über Alles" werden, wächst das nicht extrem an ?
Auf der anderen Seite hatte ich schonmal vor Jahren mit solchen Text-Wörderbücher gearbeitet, das war durchaus überschaubar von der Größe.
Man benutzt eben doch nicht unendlich viele Kombinationen.
Wie groß wird Dein Wörterbuch denn ?

Zitat von Delphi.Narium:

Per SQL geht die Abfrage in etwa so:

SQL-Code:

select Wörter.Wort, Quelletxte.* from Quelletxte, WortQuelltext, Wörter
where Quelltexte.id = WortQuelltext.QuelltextID
and WortQuelltext.WortID = Wörter.id
and Wörter.Wort in ('eins',zwei'drei','ganz','viele',Wörter']

Index auf jede ID und auf die Wörter.

Ja richtig, das "in" ist auch eine sehr gute Alternative für variable Eingaben.

Zitat von Delphi.Narium:

Ist verblüffend schnell und war in 'nem Tag implementiert, kann man analog natürlich auch auf Blob-Spalten, ... anwenden.

...

Achso: Da das so gut geklappt hat, gibt es das inzwischen auch für alle HTML-Seiten (wie z. B. ct-Archive von CDs), diverse Tutorials von anno schlagmichtot, Rezepte,

Dankesehr, das bestätigt ja meine Einschätzung.
Es muss nicht immer ElasticSearch sein, mit dem ganzen Overhead.

Zitat von jobo:

Klein? RAM, Platte, CPU? Eine Postgres Installation hat ca. 90MB glaub ich, weit weg von GB Datenfiles, trotzdem natürlich viel mehr als SQLite. Postgres läuft problemlos auf einem Raspberry.

Ich würde das eher über Nutzen und Zugriffe definieren.
Also vielleicht 5 Nutzer, mal Zugriffe 10x pro Woche, also keine große Sache.

Klein bedeutet für mich auch lauffähig auf einem kleinen virtuellen PHP Internet-Host mit REST-Anbindung,
damit es von überall her erreichbar ist.

[generic]

Manche Datenbanken haben spaltenbasierte Indizes, welche sich für sowas sehr gut eignen.
Eine De-Normalisierung und einer Volltextsuche kann ggf. auch etwas sein.

[Delphi.Narium]

Zitat von Rollo62:

Zitat von Delphi.Narium:

Dann gibt es eine Tabelle Wörter: Sie enthält alle gefundenen Wörter und eine eindeutige ID für das Wort. (Jedes Wort kommt in dieser Tabelle natürlich nur einmal vor.)

Dann könnte das eine Art "Wörterbuch über Alles" werden, wächst das nicht extrem an ?
Auf der anderen Seite hatte ich schonmal vor Jahren mit solchen Text-Wörderbücher gearbeitet, das war durchaus überschaubar von der Größe.
Man benutzt eben doch nicht unendlich viele Kombinationen.
Wie groß wird Dein Wörterbuch denn ?

Bedenke bitte: Die Tabelle Wörter enthält jedes Wort genau einmal, egal wie oft es vorkommt.

Der deutsche Wortschatz beträgt ca. 75.000 Wörter, nimmt man alle Fremdwörter ... dazu, kommt man auf sowas von 300.000 bis 500.000 Wörtern.
Sollte in den von Dir zu "verarbeitenden" Daten das alles vorkommen, so kannst Du damit halt die Größe der Tabelle Wörter "dimensionieren".

Der aktive Wortschatz (also die Wörter, die wir im täglichen Bedarf nutzen) liegt bei ca. 12.000 bis 16.000 Wörtern, der passive Wortschatz (also die Wörter, die wir verstehen) liegt mit ca. 50.000 deutlich darüber. (Der Umfang des deutschen Wortschatzes.)

Mengengerüst einer Datenbank mit Texten ala ct:

SQL-Code:

select count(*) from FILES; -- Dateien = 269314

select count(*) from FILES_WORDLIST; -- 1:n-Beziehung Files zu Wordlist: 14090788 -> ca. 52 unterschiedliche Wörter pro Datei

select count(*) from WORDLIST; -- Wörter: 79313

Bei meinen gesammelten Onlinebüchern, Tutorials, ... (englisch, deutsch bunt gemischt) sieht es so aus:

SQL-Code:

select count(*) from files; -- Dateien = 497130

select count(*) from FILES_WORDLIST; -- 1:n-Beziehung Files zu Wordlist: 48458032 -> ca. 97 unterschiedliche Wörter pro Datei

select count(*) from WORDLIST; -- Wörter: 848999

Und nun noch meine Quelltextverwaltung (da sind neben den Sourcen auch die Readme, Dokumentationen, ... drinne):

SQL-Code:

select count(*) from files; -- Dateien = 748459

select count(*) from FILES_WORDLIST; -- 1:n-Beziehung Files zu Wordlist: 31176940 -> ca. 42 unterschiedliche Wörter pro Datei

select count(*) from WORDLIST; -- Wörter: 1732472

Beim Aufbau bzw. der Befüllung der Tabelle Wörter solltest oder könntest Du ggfls. etwas "sparen", indem Du dafür sorgst, dass "Dauerwörter" (sowas wie der die das ... ein eine einer ..., oder, auf, und, davon ... - also alles das, was für eine sinnvolle Suche irrelevant ist, erst garnicht in den Index aufnimmst. Das Zeugs kostet nur Platz und Laufzeit.
Was Du aus der Indexaufnahme ausklammerst ist natürlich von der Sprache und ggfls. der Fachlichkeit abhängig. Das könnte man ggfls. auch konfigurierbar machen oder als Grundregel z. B.: Wörter mit weniger als 4 Buchstaben werden grundsätzlich ignoriert, ...
Hier kann man nicht pauschal sagen: Mach das so und so, es kommt immer auf den zu "verindexenden" Inhalt an.

Handelt es sich z. B. um ausschließlich deutschsprachige Texte, so kannst Du hergehen und alle Umlaute durch den entsprechenden Vokal ersetzen. Dann wird alles "entmehrzahlt", sprich: Wortendungen wie en, er, es, ens werden radikal abgeschnitten. (Grob: ist der letzte, der vorletzte oder der drittletzte Buchstabe ein e, dann ab dort abschneiden.)

Warum? Ganz einfach: Wer nach Flughäfen sucht, findet dann auch den Flughafen. Wenn im Text der Genitiv genutzt wird, wird auch "des Flughafens" gefunden.

Baum, Bäume, des Baumes, ...
Haus, Häuser, des Hauses, ...
Höhle, Höhlen, ...
Kartoffelsalat, Kartoffelsalate, des Kartoffelsalates, den Kartoffelsalaten, ...

Weiterer Vorteil: Suchen mit Like werden dadurch ggfls. entbehrlich, was auf das Suchtempo sicherlich nicht unbedingt negative Auswirkungen hat

Wie gesagt: Es kommt auf die Fachlichkeit an, für die die Suche implementiert werden soll.