Normalisierung von Datenbanktabellen [5]

Kannst du den Codeabschnitt hier mal posten?

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();

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

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

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.

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.

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 = ?)))

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 = ?)))

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 = ?

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.

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.

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.

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 = ?

Art und Info in der Tabelle Zusatz.

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

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.

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.

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

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.

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

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?