Zur Navigation

Normalisierung von Datenbanktabellen [5]

am Beispiel von Datenbanktabellen für ein Wörterbuch

41 Ranma (Gast)

Kannst du den Codeabschnitt hier mal posten?

Mein SQL-Skript:


DELIMITER $$

DROP PROCEDURE IF EXISTS p$$
CREATE PROCEDURE p()
BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE x,y VARCHAR(255);
  DECLARE j,k INTEGER;
  DECLARE cur CURSOR FOR SELECT wort_de FROM Woerterbuch.English;
  DECLARE sor CURSOR FOR SELECT wort_en FROM Woerterbuch.English;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;
  OPEN sor;
  OPEN cur;

  REPEAT
      FETCH cur INTO x;
      FETCH sor INTO y;
      SET j = (SELECT id FROM Woerterbuch.Worte WHERE Wort = x AND Sprache = 'de');
      SET k = (SELECT id FROM Woerterbuch.Worte WHERE Wort = y AND Sprache = 'en');
      INSERT IGNORE INTO Woerterbuch.Kombis (Kombi,Eine_id,Andere_id) VALUES ('trans',j,k);
  UNTIL done
  END REPEAT;

  CLOSE cur;
  CLOSE sor;
END$$

DELIMITER ;

CALL p();

Was nichts mit dem xampp-System zu tun hat, funktioniert währenddessen nach wie vor. Anders als übergroße XML-Dateien, die mir den gesamten Computer lahmlegen.


ALTER TABLE woerterbuch ADD UNIQUE KEY wort_index (wort, sprache);

Ich wußte garnicht, daß das geht. Sieht nach einer guten Lösung aus.

Die Ausgabe von top :

top - 20:49:15 up 54 min,  3 users,  load average: 0.13, 0.08, 0.06
Tasks: 151 total,   2 running, 149 sleeping,   0 stopped,   0 zombie
Cpu(s): 57.0%us,  4.5%sy,  0.0%ni, 38.2%id,  0.3%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   3355404k total,  1362764k used,  1992640k free,   163672k buffers
Swap:  2103292k total,        0k used,  2103292k free,   583060k cached

  PID USER      PR  NI  VIRT  RES  SHR S   %CPU %MEM    TIME+  COMMAND
 3310 mysql     20   0  178m  41m 5580 S    115  1.3  10:37.95 mysqld
 1254 root      20   0 69084  28m 5848 S      4  0.9   1:14.81 Xorg
 2649 chef      20   0  132m  28m  19m S      2  0.9   0:06.22 konsole
 2085 chef      20   0  152m  39m  26m S      1  1.2   0:27.18 kwin
 2863 root      20   0     0    0    0 S      0  0.0   0:00.73 kworker/1:0
 3581 chef      20   0  2512 1032  736 R      0  0.0   0:00.83 top
    1 root      20   0  5208 3312 1828 S      0  0.1   0:00.87 systemd
    2 root      20   0     0    0    0 S      0  0.0   0:00.00 kthreadd
    3 root      20   0     0    0    0 S      0  0.0   0:00.10 ksoftirqd/0
    6 root      RT   0     0    0    0 S      0  0.0   0:00.00 migration/0
    7 root      RT   0     0    0    0 S      0  0.0   0:00.01 watchdog/0
    8 root      RT   0     0    0    0 S      0  0.0   0:00.00 migration/1
   10 root      20   0     0    0    0 S      0  0.0   0:00.42 ksoftirqd/1
   12 root      RT   0     0    0    0 S      0  0.0   0:00.01 watchdog/1
   13 root       0 -20     0    0    0 S      0  0.0   0:00.00 cpuset
   14 root       0 -20     0    0    0 S      0  0.0   0:00.00 khelper
   15 root      20   0     0    0    0 S      0  0.0   0:00.00 kdevtmpfs
   16 root       0 -20     0    0    0 S      0  0.0   0:00.00 netns
   17 root      20   0     0    0    0 S      0  0.0   0:00.00 sync_supers
   18 root      20   0     0    0    0 S      0  0.0   0:00.00 bdi-default
   19 root       0 -20     0    0    0 S      0  0.0   0:00.00 kintegrityd
   20 root       0 -20     0    0    0 S      0  0.0   0:00.00 kblockd
   21 root       0 -20     0    0    0 S      0  0.0   0:00.00 md
   23 root      20   0     0    0    0 S      0  0.0   0:00.00 khungtaskd
   24 root      20   0     0    0    0 S      0  0.0   0:00.00 kswapd0
   25 root      25   5     0    0    0 S      0  0.0   0:00.00 ksmd
   26 root      39  19     0    0    0 S      0  0.0   0:00.36 khugepaged
   27 root      20   0     0    0    0 S      0  0.0   0:00.00 fsnotify_mark
   28 root       0 -20     0    0    0 S      0  0.0   0:00.00 crypto
   32 root       0 -20     0    0    0 S      0  0.0   0:00.00 kthrotld

Ich kann nicht behaupten, daß ich jetzt viel schlauer wäre als vorher. iotop ist nicht vorhanden. Ich denke, daß es am sinnvollsten ist, mit dem SQL-Skript zu einem Hoster umzuziehen. Dafür muß ich nur schauen wie ich die Sicherungsdatei hochgeladen bekomme, schließlich ist die inzwischen ungefähr 200 Megabyte groß.

Außerdem muß ich wahrscheinlich wegen der Sicherheit noch etwas unternehmen. Bei mir zuhause ist die Datenbank nicht abgesichert.

htmlentities() (oder besser htmlspecialchars()) würde ich immer erst bei der Ausgabe in HTML anwenden, weil dort alle Strings behandelt werden müssen, nicht nur die aus der Datenbank. Zum anderen kann es durchaus passieren, dass man Daten aus der Datenbank auch in Bereichen verwenden möchte, wo ein htmlentities() bzw. htmlspecialchars() wieder rückgängig gemacht werden müsste.

Kein htmlentities() bedeutet aber, sich jedem Angriff hilflos auszuliefern? Da braucht die Benutzereingabe, die entweder ein neuer Datensatz oder nur ein Suchbegriff sein kann, nur mit "> beginnen...
Oder war es "; ? Fast egal, jedenfalls muß es entschärft werden. Also kann ich nicht einfach so irgendetwas eintragen lassen. Dann muß auch noch, hätte ich fast vergessen, eine Behandlung mit mysql_real_escape_string() her, falls das überhaupt noch die aktuelle Fassung der Funktion ist. Ist htmlspecialchars() nicht nur eine veraltete Version von htmlentities() ?
Dann erinnere ich mich auch noch schwach an irgendetwas Sicherheitsrelevantes mit 1=1 in einer Query....


Eine JOIN Abfrage baut nicht auf Subqueries auf. Entscheidend sind die ON Statements, mittels derer zwei Tabellen über einen (Fremd-)Schlüssel verknüpft werden.

Also habe ich ON vergessen und die Query sollte so aussehen:


SELECT Sprache, Wort, Art, Info FROM Kombis INNER JOIN Zusatz ON ID = id WHERE id = ((SELECT anderes_Wort FROM Kombis WHERE Kombi = 'trans' AND ein_Wort = (SELECT id FROM Worte WHERE Wort = ? AND Sprache = ?)) OR (SELECT anderes_Wort FROM Kombis WHERE Kombi = 'trans' AND ein_Wort = (SELECT id FROM Worte WHERE Wort = ? AND Sprache = ?)))

Es sei denn, daß es id = ID statt ID = id heißen müßte. Die Tabelle Zusatz enthält nicht für jedes Wort einen Datensatz. Aber die Tabelle Kombis sollte immer mindestens ein Ergebnis liefern, weil die Suche nach einer Übersetzung der Hauptzweck der Query ist.
Ranma

14.12.2015 04:35

42 Jörg

Erstmal zur Ausgabe von top:

Tasks: 151 total,   2 running, 149 sleeping,   0 stopped,   0 zombie
Cpu(s): 57.0%us,  4.5%sy,  0.0%ni, 38.2%id,  0.3%wa,  0.0%hi,  0.0%si,  0.0%st

Der Rechner verfügt über 2 CPUs . id bedeutet idle, d.h. die beiden CPUs sind zu 38,2% nicht ausgelastet, bzw. zu 61,8% ausgelastet.

 PID USER      PR  NI  VIRT  RES  SHR S   %CPU %MEM    TIME+  COMMAND
3310 mysql     20   0  178m  41m 5580 S    115  1.3  10:37.95 mysqld

Der Prozess mysqld verbraucht 115% von 200% (für jede CPU werden 100% gezählt), also 57,5% aufs ganze bezogen. Es ist also v.a. der Datenbankserver, der die beiden CPU belastet, eine davon wohl schon zu 100%. Der Memory-Verbrauch ist zu vernachlässigen.

iotop ist nicht vorhanden

Lässt sich aber nachinstallieren. Dann weisst du nämlich auch, ob die Festplattenzugriffe stark erhöht sind.

Die Frage zu htmlentities() und htmlspecialchars() beantworte ich in einem eigenen Thread:

https://joergs-forum.de/htmlentities-und-htmlspecialchars-t-4706-1

Dann muß auch noch, hätte ich fast vergessen, eine Behandlung mit mysql_real_escape_string() her, falls das überhaupt noch die aktuelle Fassung der Funktion ist.

Falls du mysqli verwendest:

http://php.net/manual/de/mysqli.real-escape-string.php

... oder du verwendest PDO

Die Fragen zu den Queries beantworte ich in einem späteren Post

14.12.2015 20:05 | geändert: 14.12.2015 20:40

43 Jörg


SELECT Sprache, Wort, Art, Info FROM Kombis INNER JOIN Zusatz ON ID = id WHERE id = ((SELECT anderes_Wort FROM Kombis WHERE Kombi = 'trans' AND ein_Wort = (SELECT id FROM Worte WHERE Wort = ? AND Sprache = ?)) OR (SELECT anderes_Wort FROM Kombis WHERE Kombi = 'trans' AND ein_Wort = (SELECT id FROM Worte WHERE Wort = ? AND Sprache = ?)))

Die Subqueries müssen wie gesagt, raus. Ich habe das Gefühl, du möchtest mit diesen einen JOIN nachbauen, aber so funktioniert das nicht. Ich vermute mal, du möchtest so was in der Richtung:

SELECT W2.Sprache, W2.Wort, W2.Art, W2.Info
FROM Worte AS W1
INNER JOIN Kombis AS K
ON W1.id = K.ein_wort
INNER JOIN Worte AS W2
ON K.anderes_wort = W2.id
WHERE K.Kombi = 'trans'
AND W1.Wort = ? AND W1.Sprache = ?
AND W2.Sprache = ?

W1 als Alias für die Wortetabelle bezieht sich auf das zu übersetzende Wort, und W2 auf das übersetzte Wort

Die Query mit dem REPEAT scheint mir doch sehr von hinten durchs Auge und es ist von daher wohl kein Wunder, dass ein INSERT geschlagene sechs Sekunden benötigt. Du hattest weiter oben auch was von Schleife geschrieben, aber solche Konstruktionen sind denke ich eher für andere Anwendungsfälle gedacht. Damit befasse ich mich aber ein anderes mal...

14.12.2015 22:58 | geändert: 14.12.2015 22:59

44 Ranma (Gast)

Ach je, ich habe auch kein mysqli_real_escape_string() verwendet, weil ich den Daten in den Dateien vertraut habe, nachdem ich sie mir im Editor angesehen hatte. Aber noch ist phpMyAdmin nicht abgeschmiert, also sollte das so funktionieren können. Nur mit fremden Daten muß ich vorsichtiger sein.


Die Query mit dem REPEAT scheint mir doch sehr von hinten durchs Auge und es ist von daher wohl kein Wunder, dass ein INSERT geschlagene sechs Sekunden benötigt. Du hattest weiter oben auch was von Schleife geschrieben, aber solche Konstruktionen sind denke ich eher für andere Anwendungsfälle gedacht.

REPEAT ... UNTIL ist eine Schleife. Mit einem Cursor läuft sie vom ersten bis zum letzten Datensatz durch eine Tabelle durch. Von der damit erzielten Funktionalität her also so etwas wie eine FOREACH-Schleife in PHP. Es ist die einzige Möglichkeit, die ich gefunden habe, mit der sich für jeden Datensatz einer Datenbanktabelle die gleichen Operationen durchführen lassen.

Die andere Query will nicht unbedingt einen JOIN nachbauen, sondern die auf drei normalisierte Datenbanktabellen verteilten Informationen abrufen und wieder zusammenführen, damit sie zusammen ausgegeben werden können. Dabei enthält Worte die id-Nummern der Wörter und die Wörter dazu, deshalb muß immer auf diese Tabelle zugegriffen werden. Zusatzangaben zu den Wörtern, wie Aussprache, grammatische Anmerkungen oder den Hinweis auf einen Fachausdruck eines bestimmten Gebietes, stehen in der Tabelle Zusatz. Das kann von keinem bis mehrere Einträge pro Wort (aber in der Tabelle nur durch die id-Nummer identifiziert) gehen. In der Tabelle Kombis befinden sich dann noch Kombinationen aus den id-Nummern von Wörtern, in dieser Abfrage zum Zweck der Übersetzung. Dabei kann der eingegebene Suchbegriff sowohl dazu führen, daß die id in der linken Spalte vorgegeben und Kombinationen mit id-Nummern aus der rechten Spalte dazu gesucht werden, als auch dazu, daß die id in der rechten Spalte vorgegeben und Kombinationen mit id-Nummern aus der linken Spalte dazu gesucht werden. Die gefundenen id-Nummern wiederum taugen noch nicht für die Ausgabe, weil man als Benutzer nicht irgendeine Nummer sucht, sondern ein Wort oder mehrere, das als Übersetzung taugt. Deswegen geht es in der Query dann wieder zurück in die Tabelle mit den Wörtern und deren id-Nummern.

Vielleicht nicht so leicht vorstellbar, aber das ist das Resultat der Normalisierung (oder wird es sein, sobald ich damit fertig bin). Darum nochmal aus der Sicht des Benutzers:

Es gibt ein Formular, in das ein Suchterm eingegeben werden kann. Schickt man damit einen Begriff an das Skript, dann erwartet man als Ergebnis passende Wörter aus anderen Sprachen und dazu, sofern vorhanden, ein paar zusätzliche Informationen. Solche zusätzlichen Informationen wie ob es sich bei einem englischem Wort um ein nur britisches oder ein nur amerikanisches Wort handelt oder ob das Wort im Zusammenhang mit Computern Verwendung findet. Auf letzteres sollte man achten, wenn man ein Computerprogramm lokalisieren möchte.

Um zu dem Ergebnis zu kommen, muß zunächst nach dem eingegebenen Suchbegriff (in Kombination mit der eingestellten Sprache) gesucht werden und zwar ist der entweder in der Datenbanktabelle Worte vorhanden oder nirgendwo in meiner Datenbank vorhanden. Das ist das erste Resultat der Normalisierung. Aber diese erste Tabelle liefert nur eine Nummer.

Dann wird in der zweiten Tabelle nach Kombinationen mit dieser Nummer gesucht, wobei es egal ist, in welcher Spalte der Tabelle Kombis diese Nummer steht, sonst würde nur ein Teil der gesuchten Datensätze gefunden. Die beiden Spalten in dieser Tabelle erfüllen die gleiche Funktion und müssen darum beide auf die gleiche Weise abgesucht werden und Kombinationen (mit den Nummern in der jeweils anderen Spalte) liefern. Das ist ein weiteres Resultat der Normalisierung. Die Query sollte hier nun keinen, einen oder mehrere Nummern als Zwischenergebnis haben.

Mit dem Zwischenergebnis muß die Query noch die Tabelle Zusatz und nochmal die Tabelle Worte durchsuchen. Dadurch gibt es statt Nummern Zeichenketten als Ergebnis, nämlich Wörter aus anderen Sprachen und eventuell dazu vorhandene zusätzliche Informationen. Diese Zeichenketten kommen alle in eine HTML-Tabelle, die das Resultat der Suche darstellt.
Ranma

15.12.2015 02:24

45 Jörg

Ach je, ich habe auch kein mysqli_real_escape_string() verwendet, weil ich den Daten in den Dateien vertraut habe, nachdem ich sie mir im Editor angesehen hatte.

Es sind in erster Linie ' (Hochkommata), die eine Query zum Straucheln bringen können, wie z.B. in einem String "don't"

Die andere Query will nicht unbedingt einen JOIN nachbauen, sondern die auf drei normalisierte Datenbanktabellen verteilten Informationen abrufen und wieder zusammenführen, damit sie zusammen ausgegeben werden können.

Genau dafür sind JOINs gedacht, und genau dies tut auch mein Beispiel-Code in Beitrag 43. In den meisten Fällen sind JOINs von der Performance her um einiges schneller als Subqueries. Zudem finde ich den JOIN-Code lesbarer (was aber auch daran liegen mag, dass ich ihn auf mehrere Zeilen verteilt habe)

Um zu dem Ergebnis zu kommen, muß zunächst nach dem eingegebenen Suchbegriff (in Kombination mit der eingestellten Sprache) gesucht werden und zwar ist der entweder in der Datenbanktabelle Worte vorhanden oder nirgendwo in meiner Datenbank vorhanden. Das ist das erste Resultat der Normalisierung. Aber diese erste Tabelle liefert nur eine Nummer.

Dann wird in der zweiten Tabelle nach Kombinationen mit dieser Nummer gesucht, wobei es egal ist, in welcher Spalte der Tabelle Kombis diese Nummer steht, sonst würde nur ein Teil der gesuchten Datensätze gefunden. Die beiden Spalten in dieser Tabelle erfüllen die gleiche Funktion und müssen darum beide auf die gleiche Weise abgesucht werden und Kombinationen (mit den Nummern in der jeweils anderen Spalte) liefern. Das ist ein weiteres Resultat der Normalisierung. Die Query sollte hier nun keinen, einen oder mehrere Nummern als Zwischenergebnis haben.

Mit dem Zwischenergebnis muß die Query noch die Tabelle Zusatz und nochmal die Tabelle Worte durchsuchen. Dadurch gibt es statt Nummern Zeichenketten als Ergebnis, nämlich Wörter aus anderen Sprachen und eventuell dazu vorhandene zusätzliche Informationen.

Ja, und genau so ein Ergebnis soll auch meine JOIN-Query in Beitrag 43 liefern - ich habe sie praktisch aus deiner Subqueries-Query "übersetzt". Die Tabelle Zusatz habe ich erstmal weglassen - es ging hier erst mal um das Grund-Prinzip

Ungefähr folgendermaßen ließe sich die Query um einen JOIN auf Zusatz erweitern:

SELECT W2.Sprache, W2.Wort, W2.Art, W2.Info,
Z.zusatz_1, Z.zusatz_2
FROM Worte AS W1
INNER JOIN Kombis AS K
ON W1.id = K.ein_wort
INNER JOIN Worte AS W2
ON K.anderes_wort = W2.id
LEFT JOIN Zusatz AS Z
ON Z.id = W2.id
WHERE K.Kombi = 'trans'
AND W1.Wort = ? AND W1.Sprache = ?
AND W2.Sprache = ?

Die Tabelle Zusatz wird mit einem LEFT JOIN statt INNER JOIN angebunden, wenn auch Wörter zurückgegeben werden sollen, für die es keine Zusatzdaten gibt. Wenn ich dich richtig verstanden habe, entsprechen sich die IDs von Worte und Zusatz, daher die Verknüpfung Z.id = W2.id. Da ich die Spaltennamen nicht kenne, habe ich sie beispielshalber zusatz_1 und zusatz_2 genannt. Kann sein, dass du auch noch an anderen Stellen Anpassungen vornehmen musst - es geht hier erstmal um den Ansatz.

Wichtig für die Performance ist, dass auf alle Fremdschlüssel, also K.ein_wort und K.anderes_wort ein Index gesetzt wird

15.12.2015 22:08 | geändert: 15.12.2015 22:17

46 Ranma (Gast)

Ich glaube, daß du deine mehrzeilige Query deswegen leichter findest, weil du erst eine Tabelle weniger abgefragt hast, während ich mich gewundert habe, wo die wohl steckt. Außerdem hast du einen Teil umbenannt. Dabei habe ich schon alle Spaltennamen zum Zweck der Veröffentlichung umbenannt und wenn ich es nicht so gemacht hätte, dann wären sie durch meinen vorherigen Beitrag schon veröffentlicht. Also wundere ich mich, was die Umbenennungen sollen?

Übrigens finde ich eine Query mit JOIN immer schwer lesbar, aber meine enthielt schon einen JOIN. Subqueries funktionieren meistens nicht. Nur hätte ich es leichter gefunden, falls das richtig gewesen wäre.

Also die Namen von Tabellen und Spalten, die in meinem Versuch einer Query alle enthalten waren:

Art und Info in der Tabelle Zusatz. Der Index ist eine Spalte namens ID. Ich befürchtete Konflikte, falls die gleichen Spalten in unterschiedlichen Tabellen die gleichen Namen hätten. Dann müßte in einer Query immer x AS u stehen. Die Tabelle Zusatz ist eigentlich dein Beispiel aus Beitrag #4, nur ohne nochmalige eigene id-Nummer für die einzelnen Datensätze. Die habe ich überall da weggelassen, wo mir keine sinnvolle Query einfällt, die eine solche id-Nummer abfragen würde.

Wort, Sprache und id in der Tabelle Worte, dabei ist id der Index.

Kombi, ein_Wort und anderes_Wort in der Tabelle Kombis. In der Tabelle gibt es keinen Index, weil sowieso nur Fremdschlüssel miteinander kombiniert werden. Also das Ergebnis einer Abfrage ist sowieso immer eine Zahl, die in der Tabelle Worte und manchmal auch in der Tabelle Zusatz als Index dient.

Ein paar Modifikationen werde ich noch vornehmen müssen, um die Resultate der Query einzuschränken, zum Beispiel auf nur eine bestimmte Zielsprache oder auf Wörter, die nur im Zusammenhang mit Computern (der Hinweis findet sich gegebenenfalls in der Tabelle Zusatz) verwendet werden.

Deine Query benennt Zusatz um in Z, Kombis in K, Worte sowohl in W1 als auch in W2. Spalten daraus kommen in der Query schon vor, bevor die Alias-Namen vergeben werden, das fand ich erst etwas verwirrend. Aber der SELECT-Teil wird ja zuletzt abgearbeitet. Darum finde ich nur noch andere Teile verwirrend, vor allem die Umbenennung von Zusatz und Kombis. Wozu ist das gut?

Die Tabelle Worte braucht vielleicht einen zweiten Namen, um beide Spalten durchsuchen zu können, es kommt mir trotzdem nicht so vor als würden nun sowohl die Kombinationen von anderes_Wort mit ein_Wort als auch die Kombinationen von ein_Wort mit anderes_Wort gesucht. Der gesuchte Begriff oder genauer dessen id-Nummer kann entweder in ein_Wort oder in anderes_Wort stehen. Beide Fälle müssen gefunden werden und in beiden Fällen gibt es Kombinationen mit der jeweils anderen Spalte. Die Nummern der jeweils anderen Spalte sind das Zwischenergebnis. Keines davon wird doppelt sein, deswegen müssen beide Richtungen durchsucht werden. Andernfalls könnte die Hälfte der Ergebnisse fehlen.

Ich habe den Eindruck, daß deine Query das nicht leistet. Meine würde das, falls sie syntaktisch richtig und ausführbar sein sollte. Vermutlich würde sie sehr lange dafür brauchen.

Vielleicht war mein Versuch doch etwas wirr, weil deine Verbesserung W2.Art und W2.Info konstruiert. Trotz Umbenennung würden die beiden ins Leere greifen. Die Tabelle Worte enthält weder Art noch Info, sondern nur Sprache und Wort. Deshalb das JOIN in meinem Versuch der Query. Das scheint halbwegs richtig gewesen zu sein, denn auch in deiner Fassung gibt es nur ein SELECT nach dem alle abgefragten Spaltennamen folgen. W2.Art müßte also Z.Art heißen und W2.Info müßte Z.Info heißen und wären Aliase von Z.zusatz_1 und Z.zusatz_2 . Möglicherweise hast du die Beschränkung auf eine Zielsprache schon eingebaut. Das hatte ich noch nicht. Nur falls du das ergänzen wolltest, dann verstehe ich einigermaßen die drei Fragezeichen im WHERE-Teil. Andernfalls nicht.

Die Mißverständnisse berücksichtigend, da wo ich sie vermute, komme ich so langsam mit deiner Query klar. Trotzdem finde ich sie nicht so leicht zu verstehen, vor allem wegen der Umbenennungen und den zwei Namen für die Tabelle Worte, obwohl mir nur eine Richtung durchsucht zu werden scheint.
Ranma

16.12.2015 03:02

47 Jörg

Die Aliase verwende ich aus mehreren Gründen:

1. es empfiehlt sich bei allen Spalten die jeweilige Tabelle (durch einen Punkt getrennt) voranzustellen, um Namenskollisionen zu vermeiden. Damit man nicht überall den Tabellennamen ausschreiben muss, kann man Aliase aus ein oder zwei Buchstaben verwenden.

2. wenn man eine Tabelle mehr als einmal einbindet, wie im Fall der Tabelle Worte, kommt man um Aliase nicht herum, um diese unterscheiden zu können.

3. wenn ich Aliase verwende, dann durchgehend - das erhöht die Lesbarkeit und man sieht so auf einen Blick, zu welcher Tabelle welche Spalte gehört.

Art und Info in der Tabelle Zusatz.

OK, dann müsste man das SELECT-Statement anpassen:

SELECT W2.Sprache, W2.Wort, Z.Art, Z.Info

Vielleicht wäre ein Alias Z2 auch noch besser, dann erkennt man gleich die Zugehörigkeit zu W2

Spalten daraus kommen in der Query schon vor, bevor die Alias-Namen vergeben werden, das fand ich erst etwas verwirrend. Aber der SELECT-Teil wird ja zuletzt abgearbeitet.

Ja genau.

Die Tabelle Worte braucht vielleicht einen zweiten Namen, um beide Spalten durchsuchen zu können, es kommt mir trotzdem nicht so vor als würden nun sowohl die Kombinationen von anderes_Wort mit ein_Wort als auch die Kombinationen von ein_Wort mit anderes_Wort gesucht.

Dann sind die Daten m.E. noch nicht optimal strukturiert. In ein_Wort sollten die Indexe aller zu übersetzenden Wörter stehen, in anderes_Wort die Indexe der jeweiligen Übersetzungen. Dies ist denke ich auch deswegen notwendig, weil die Relation einer Übersetzung oft nicht bidirektionaler Natur ist. Man könnte natürlich auch die Query so erweitern, dass sie beide Richtungen abgrast, aber das würde ich aus Performance-Gründen schon nicht tun.

Möglicherweise hast du die Beschränkung auf eine Zielsprache schon eingebaut.

Ja. Wenn man die letzte Bedingung weglässt, werden die Übersetzungen in allen Sprachen gesucht.

16.12.2015 19:56

48 Ranma (Gast)

Dann sind die Daten m.E. noch nicht optimal strukturiert. In ein_Wort sollten die Indexe aller zu übersetzenden Wörter stehen, in anderes_Wort die Indexe der jeweiligen Übersetzungen. Dies ist denke ich auch deswegen notwendig, weil die Relation einer Übersetzung oft nicht bidirektionaler Natur ist. Man könnte natürlich auch die Query so erweitern, dass sie beide Richtungen abgrast, aber das würde ich aus Performance-Gründen schon nicht tun.

Gerade weil die Relation einer Übersetzung nicht bidirektionaler Natur ist, muß in beide Richtungen abgegrast werden! Das bedeutet nämlich das ein_Wort auf mehrere Datensätze von anderes_Wort abgebildet sein kann oder umgekehrt mehrere Datensätze von ein_Wort auf nur einem Datensatz von anderes_Wort. Außerdem, ob links oder rechts soll in einer normalisierten Tabelle keinen Unterschied machen? In den Tabellen, aus denen die Tabelle Worte aufgebaut wird, steht meistens Deutsch links und die fremdsprachliche Übersetzung dazu rechts. Eine Möglichkeit, um das beizubehalten, wäre gewesen für jede Sprachpaarung eine Tabelle beizubehalten und dann entweder eine Vielzahl an völlig leer bleibenden Tabellen herumzuschleppen oder zur Laufzeit ständig neue Tabellen anlegen zu lassen. Über die Methode habe ich gelesen, daß sie immer ein Fehler in der Projektplanung ist. Wenn ein Skript im produktiven Einsatz ist, dann sollte es keine der höheren Rechte wie ALTER, CREATE oder DROP bekommen. Die Performance dürfte durch mehr Tabellen auch nicht erhöht werden.

Eine andere Methode wäre vielleicht je nach der Quelle der Daten einen Unterschied zu machen. Dann wäre schon die Verarbeitung der Liste des Monash Mulitilingual Japanese Dictionary Project sehr schwierig. Wären die Sprachen in getrennten LIsten untergebracht gewesen, dann hätte ich meine Tabelle dafür auf Deutsch <--> Japanisch beschränkt. Aber es sind andere dabei und es hat wahrscheinlich nichtmal eine Zeile Code extra ausgemacht, die miteinzubauen, also gab es keinen Grund, darauf zu verzichten. Das wäre nicht in zwei Spalten unterzubringen gewesen, also müßten es wieder mehrere Tabellen sein. Oder eine Reihenfolge, was jeweils links oder jeweils rechts zu stehen hätte. Das macht dann sowohl mein umräumendes SQL-Skript komplizierter, als auch den vorgesehenen Crawler als auch Eingaben von Benutzern. Selbst falls ich das hinbekäme, dann bräuchte ich, wegen der fehlenden Eineindeutigkeit, doch zwei Tabellen dafür und ein Teil davon wäre nur ein Spiegelbild der anderen. Wenn nicht sogar komplett.

Wenn ich aber weder Einschränkungen dabei machen will, welche Übersetzungen aufgenommen werden, noch welche Abfragen ein Benutzer machen darf, dann ist es am sinnvollsten, wenn ein Teil der möglichen Suchbegriffe die rechte Spalte und ein anderer Teil der Suchbegriffe die linke Spalte von Worte durchsuchen muß. Während einer Suche ist es trotzdem immer nur eine Richtung, aber ich kann nicht vorhersehen, welche Richtung das sein wird. Vielleicht liegt hier das eigentliche Problem. Nur weil ich nicht vorhersehen kann, welche der beiden Richtungen in einer konkreten Abfrage benötigt wird, deshalb müssen beide Richtungen in der Query durchsucht werden.

Durch spätere Einträge kann es dann allerdings doch noch dazu kommen, daß auch in einer Abfrage beide Richtungen Resultate erbringen, einfach weil die Reihenfolge des Eintragens in einer normalisierten Tabelle keinen Unterschied machen soll. Steht zumindest auf der Wikibook-Seite zur Normalisierung.

Die meisten Wörterbücher, die man online findet, müssen immer zwischen Deutsch-->Fremd und Fremd-->Deutsch umgeschaltet werden. Aber bei LEO ist das nicht erforderlich und trotzdem ist es mindestens genauso schnell und dazu noch muß dessen Datenbank gewaltig sein. So leicht sind Unterschiede in der Performance also nicht zu erklären.

Um die andere Suchrichtung in die Query mitaufzunehmen, brauche ich nochmal zwei mittels INNER JOIN verknüpfte Aliase Worte AS W3 und Worte AS W4, mit denen der Teil von W1 und W2 wiederholt wird?

Ja. Wenn man die letzte Bedingung weglässt, werden die Übersetzungen in allen Sprachen gesucht.

Kann man als Wert für die letzte Bedingung * schreiben? Das würde eine Verzweigung ersparen, wenn ich eine Option für Übersetzungen in alle Sprachen hinzufüge. Die wäre nämlich zum lokalisieren von Netzseiten hilfreich. Aber * paßt in keinen Variablentyp, wäre '*' stattdessen korrekt?
Ranma

17.12.2015 05:32

49 Jörg

Die meisten Wörterbücher, die man online findet, müssen immer zwischen Deutsch-->Fremd und Fremd-->Deutsch umgeschaltet werden. Aber bei LEO ist das nicht erforderlich und trotzdem ist es mindestens genauso schnell und dazu noch muß dessen Datenbank gewaltig sein. So leicht sind Unterschiede in der Performance also nicht zu erklären.

Dazu muss man ja auch nur das WHERE-Statement erweitern - zusätzliche JOINs sind dazu gar nicht notwendig:

WHERE K.Kombi = 'trans'
AND W1.Wort = ? AND 
(W1.Sprache = ? AND W2.Sprache = ?) OR 
(W1.Sprache = ? AND W2.Sprache = ?)

Für die ? könnten dann z.B. 'Blabla', 'de', 'en', 'en', 'de' definiert werden

Jeder zusätzliche JOIN würde die Abfrage um ein Vielfaches verlangsamen.

Wenn du der Meinung bist, dass in jedem Fall einer Übersetzung A -> B eine Übersetzung B -> A entspricht, dann kannst du diese entsprechend zweifach eintragen: einmal ein_Wort = A und anderes_Wort = B und einmal umgekehrt. Mehrere Tabellen `Kombis` benötigst du dafür nicht, das sollte alles in eine Tabelle.

Kann man als Wert für die letzte Bedingung * schreiben? Das würde eine Verzweigung ersparen, wenn ich eine Option für Übersetzungen in alle Sprachen hinzufüge. Die wäre nämlich zum lokalisieren von Netzseiten hilfreich. Aber * paßt in keinen Variablentyp, wäre '*' stattdessen korrekt?

Wildcards gibt es nach einem = nicht, nur beim Gebrauch von LIKE - aber das ist performancetechnisch auch wieder nicht gut.

Du kannst die Query ja in PHP zusammenbauen: wenn nur in eine Sprache übersetzt werden soll, hängst du die letzte Bedingung noch dran, andernfalls nicht.

17.12.2015 22:31

50 Ranma (Gast)

Ich versuche gerade mein SQL-Skript, das die Datenbanktabellen umsortiert, mit Adminer als Alternative zu phpMyAdmin laufen zu lassen. Dafür habe ich natürlich noch ein LIMIT eingefügt, damit die Inserts pro Minute gezählt werden können. Aber es hört und hört nicht auf. Läuft jetzt schon sechzig Prozent länger als phpMyAdmin, das inzwischen fertig wäre. Dabei soll Adminer im Durchschnitt 28 % schneller sein als phpMyAdmin.
Ranma

19.12.2015 03:20