Abfrage über mehrere Felder optimieren

[nahpets]

Hallo,

habe vor Jahren mal 'ne Suchmaschine (für beliebige Texte) geschrieben, die war und ist sauschnell, auch wenn sie eine Volltextsuche enthält. Das Vorgehen ist ganz einfach:

Texte werden in Einzelwörter zerlegt. Diese Wortliste wird von Dubletten bereinigt, so dass es eine eindeutige Liste wird.

Es gibt (beispielhaft) insgesamt drei Tabellen:

1. Daten : DatenID : Integer; beliebige Spalten...
2. Wortliste : WortID : Integer; Wort : varChar
3. Referenzliste: DatenID : Integer; WortID : Integer

Vorgehen:
Beim Speichern eines Satzes in der Datentabelle werden alle Felder, die im Volltextindex benötigt werden, zu einem Text zusammengedaddelt.
Dieser Text wird in Einzelwörter zerlegt.
Jedes, noch nicht in der Wortliste enthaltene, Wort wird in die Wortliste eingefügt, die Wortliste ist also eine eindeutige Liste.
Es wird die Referenzliste gepflegt. Für jedes Wort des Datensatzes wird ein Eintrag DatenID <-> WortID in die Referenzliste eingefügt.

Bei der Suche muss man dann nur (per Index) alle Sätze suchen, die zum eingegebenen Wort passen.

Das geht (beispielhaft) per:

Code:

select a.* from Daten a, Referenzliste b, Wortliste c where a.DatenID = b.DatenID and b.WortID = c.WortID and c.Wort = :Eingabe

(Selbst ein Like auf die Wortliste sollte hier nicht zum Performanzkiller werden.)

Dafür braucht man auf der Datentabelle einen Index auf der DatenID, auf der Referenztabelle einen auf DatenID und einen auf WortID und für die Worttabelle einen auf Wort.

Wenn man nun die Wortliste grundsätzlich nur in Kleinschreibweise befüllt, benötigt man für den Suchbegriff nur einmal ein Lower und spart sich die ganze Schreibweisenveränderung in jedem SQL.

Für eine schnelle Suche muss man sich die Daten nunmal normalisieren und kommt mit einer Tabelle und einem SQL ala "Liebe Datenbank, guckt doch mal, ob Du nicht eventuellunterumständenvielleicht irgendwo was passendes finden könntest" nicht aus.

Dieses sehr ungenaue "Habenmöchten" dauert einfach zu lange, wenn man nicht vorher für eine passende Struktur gesorgt hat.

Das "Wortlisteerstellen" ist (mit Delphi) sehr einfach.
Alle benötigten Felder in eine Stringliste, Delimiter auf Blank und DelimitedText abrufen. Sort aufrufen, Dubletten raus und schon kann die Wortliste in der Datenbank gepflegt werden.

Hier im Beispiel sollte die Änderung des "Systems" in ein- bis zwei Tagen in ausgereifter Form möglich sein.

Wenn man nun noch (wie oben in 'nem anderen Beitrag beschrieben) die Kölner Phonetik für die Wortliste nutzt, dann kann man damit ähnlich unscharf Suchen, wie wir es von den Suchmaschinen gewohnt sind. Meier findet dann auch Maier und Mayer und Meyer und Hofmann findet auch Hoffmann..., Cafe und Café werden nicht mehr unterschieden...

Soll der Suchbegriff mehrere Wörter enthalten (können), dann passt man das SQL halt an:

Code:

select a.* from Daten a, Referenzliste b, Wortliste c where a.DatenID = b.DatenID and b.WortID = c.WortID and c.Wort in (:Wort1, Wort2, ..., WortN)

Auf Like kann man dann ganz verzichten und kann immer per Index suchen.

Wenn das dann (mit zusätzlichem Suchkomfort) nicht schnell wird, hat man ein vollkommen anderes Problem, das nur zufällig bei der Suche deutlich aufgefallen ist.

[jobo]

Eine schöne Sache, aber hier wäre ich vielleicht etwas vorsichtiger gegenüber dem TE:

Zitat von nahpets:

Hier im Beispiel sollte die Änderung des "Systems" in ein- bis zwei Tagen in ausgereifter Form möglich sein.

Neben der Implementierung, braucht es Tests und Migration (der Kundendaten). Passt eine Kölner Phonetik auch für französische Kundennamen, türkische oder polnische ..? Wenn nicht, was dann? Der anspruchsvolle Kunde wird dann vielleicht sauer, weil er sagt, er hat viel Geld ausgegeben für ein Verbesseung und nun funktioniert es nur für deutsche Datenbankeinträge.

[nahpets]

Also mit dieser "Methode" (sprich Kölner Phonetik) haben wir den kompletten Datenbestand der Telekom (also alle von denen gelieferten Adressen) gegen andere Systeme abgeglichen, um Dubletten zu bereinigen und Systeme, die über keine eindeutigen Zuordnungen, über wie auch immer geartete Schlüssel, verfügten, miteinander abzugleichen. Und die Resultate waren (sind) verblüffend gut. Neben dem Namensabgleich haben wir noch versucht, die Adressen gegen einen vollständigen Adressbestand der Bundesrepublik abzugleichen, so dass wir (im Idealfall) den Namensabgleich quasi "nur noch" auf Hausebene durchführen mussten. Wer sich aber die Qualität der Telefondaten (in Bezug auf Schreibweise, mehr oder weniger offensichtliche Fehler) anschaut, weiß, dass der Abgleich auf Hausebene eher unwahrscheinlich ist.

Gerade bei "ausländischen" Namen treten häufig unterschiedliche Schreibweisen auf, nehmen wir den Vornamen Juri und/oder Yuri, beides kann korrekt sein, in Bezug auf eine konkrete Person ist aber eine der beiden Schreibweisen falsch, wird so aber trotzdem gefunden.
Oder wie ist es bei meinem Vornamen: Stephan oder Stefan? Like hilft hier herzlich wenig.

Oder nehmen wir den aus dem Marokkanischen stammenden Nachnamen Bouauda, gesprochen Bo-u-da. Wer kann diesen Namen, beim erstmaligen Hören sofort richtig schreiben? Bei der Kölner Phonetik ist's egal, er wird gefunden.
Wie schreibt man den türkischen Namen Cigdem, gesprochen eher wie Schiedämm?
Oder wie schreibt man Koslow? Oder doch eher Kozlow? Oder war es Kozlov oder doch Koslov?
Alles ist richtig, aber wie ist die Schreibweise im konkreten Fall? Like mit '%KOSLOV%' hilft mir da herzlich wenig.
Auch hier wird die Kölner Phonetik eine gute Hilfestellung sein.
Aber die Fehlermöglichkeit des "Zuvielfindens" durch die Unschärfe, wird den Zusatzaufwand durch beim letztgenannten Namen ggfls. vierfachen Suchaufwand, sicherlich nicht überwiegen. Zumal ich bei unterschiedlichen Möglichkeiten der Namensschreibweise ja auch erstmal darauf kommen muss, dass der Name auch in anderer Form korrekt geschrieben sein könnte.

Natürlich ist mir klar, dass ich bei der Umsetzung einer derartigen Implementierung den Rückhalt beim Kunden haben muss. Den verschaffe ich mir zuerst und dann setze ich mich an die Umsetzung. Eine derartige Änderung würde ich nie ohne Einverständnis des Kunden vornehmen. Als "einfacher Programmierer" muss ich mir ggfls. auch bei meinem Team-, Projekt- oder Sonstwasleiter, Vorgesetzten, Chef... das Ok holen.
Aber bei der von mir skizzierten Lösung sind nur neue Tabellen erforderlich, der eigentliche Datenbestand muss nicht verändert werden, die technische Änderung ist (sollte) für den Anwender (und ggfls. die Anwendung) vollkommen transparent sein. Dies liegt allerdings auch an der Art der bisherigen Implementierung. Da mir diese nicht bekannt ist, kann ich hier auch keine sinnvollen und verlässlichen Abschätzungen vornehmen. Aber ausgehend davon, dass ich für die Entwicklung und die Datenbankänderungen einen "Freifahrschein" habe, also absolut selbständig entscheiden kann, sollte das in ein bis zwei Tagen umzusetzen sein. Der Datenbestand muss einmal komplett gelesen und gespeichert werden. Es ist also einmalig die zukünftig zu verwendende Speicherroutine für jeden Datensatz aufzurufen. Bei der angegebene Datenmenge sollte das (eigentlich) kein übermäßiges Laufzeitproblem werden.

Eine Implementierung der Kölner Phonetik für Delphi ist hier zu finden: (https://github.com/chaosben/theunkno...erPhonetik.pas)