[MySQL] Frage zu Tabellenstruktur
-
Hi!
In meiner Datenbank hab ich eine Tabelle, in der ich Textressourcen in 3 Sprachen speichere (für die GUI). Momentan sieht die Struktur folgendermaßen aus:
CREATE TABLE i18n ( IN_ID MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT, N_ID TINYINT(4) NOT NULL, //Fremdschlüssel zu einer anderen Tabelle, mit Namespace-Strings (z.B. MENU). string VARCHAR(150) NOT NULL, //Der universelle Bezeichner für die Ressource (z.b. DB_NO_CONNECTION) annot TEXT NOT NULL, //Eine Anmerkung um den Übersetzer auf Besonderheiten des zu übersetzenden Textes hinzuweisen. en TEXT NOT NULL, //Der englische Text und zugleich die Quelle für alle anderen Übersetzungen. de TEXT NOT NULL, //Deutscher Text ur TEXT NOT NULL, //Text in Urdu en_ts TIMESTAMP NOT NULL DEFAULT 0000-00-00 00:00:00, //Datum der Erstellung/letzen Änderung de_ts TIMESTAMP NOT NULL DEFAULT 0000-00-00 00:00:00, //-"- ur_ts TIMESTAMP NOT NULL DEFAULT 0000-00-00 00:00:00, //-"- PRIMARY KEY(IN_ID), );Es fällt bestimmt gleich auf, dass es nicht gerade schön normalisiert ist, aber für den ersten Entwurf war es zufriedenstellend, weil ich mit der Entwicklung meines Projektes etwas zügiger vorankommen wollte. Um alle Nachrichten in allen 3 Sprachen in einer Tabelle nebeneinander anzuzeigen verwende ich folgende Query:
// Relativ simpel... SELECT ns.namespace AS ns, IN_ID AS id, string, annot, en, de, ur, en_ts, de_ts, ur_ts FROM i18n, i18n_namespace ns WHERE ns.N_ID=i18n.N_IDWenn ich die Tabelle jetzt in eine höhere Normalform bringen würde, dann stellt sich mir die Frage wie die Query aussehen müsste um dasselbe wie oben zu erreichen. Die neue Tabellenstruktur:
CREATE TABLE i18n ( IN_ID MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT, lang ENUM('en','de','ur') NOT NULL DEFAULT 'en', N_ID TINYINT(4) NOT NULL, string VARCHAR(150) NOT NULL, annot TEXT NOT NULL, resource TEXT NOT NULL, res_ts TIMESTAMP NOT NULL DEFAULT 0000-00-00 00:00:00, PRIMARY KEY(IN_ID, lang), );Ich vereinige die Spalten en,de,ur zu resource und en_ts,de_ts,ur_ts zu res_ts; des weiteren erzeuge ich lang und verändere den Primärschlüssel, so dass er aus IN_ID und lang besteht. Das dient dazu um erkennen zu können welche Ressourcen zueinander gehören. Z.B.:
IN_ID lang resource ------------------------- 1 'en' English 1 'de' Englisch 1 'ur' k.a. 2 'en' German 2 'de' Deutsch 2 'ur' k.a.Selbstverständlich kann man die Tabelle noch weiter normalisieren, aber diese Aufgabe sei mal für später aufgehoben. Was bei dieser Struktur jetzt ein klarer Vorteil ist, ist, dass man sehr einfach weitere Sprachen hinzufügen kann (man braucht nur die ENUM-Liste erweitern). Allerdings bringt dieser Vorteil die Schwierigkeit mit sich, eine ausgeklügelte Query zu entwerfen, mit der ich alle Ressourcen selektieren kann, wobei jede Sprache eine eigene Spalte besitzt. Ein Beispiel:
IN_ID en de ur ------------------------------- 1 English Englisch k.a 2 German Deutsch k.aMir fallen diesbezüglich zwei Ideen ein um das zu bewerkstelligen:
1.) GROUP BY-Anweisung in Verbindung mit der GROUP_CONCAT Aggregatsfunktion:
SELECT IN_ID, GROUP_CONCAT(resource SEPARATOR '§$') AS res_list, GROUP_CONCAT(lang SEPARATOR ';') FROM i18n GROUP BY IN_IDUm die Wahrheit zu sagen: ich weiß nicht mit Sicherheit ob diese Query funktioniert. Als Resultat erwarte ich mir, dass alle Ressourcen in der Spalte resource zu einer Liste zusammengefügt werden, was dem Zwecke dienen soll, in PHP mit explode zu einem Array umgewandelt zu werden. Da ich aus dieser Liste nicht erfahren kann in welcher Sprache die Ressourcen sind, muss ich die Spalte lang ebenfalls gruppieren (wobei vorausgesetzt werden muss, dass sie immer mit der Reihenfolge der resource Liste übereinstimmt). Der Nachteil dieser Methode ist, dass die Gefahr besteht, dass der Separator selbst in den Daten vorkommen könnte. Das würde allerdings nur dann ein Problem darstellen, wenn man BLOBs als Ressourcen einfügen würde, weil es bei diesem Datentyp sehr viel wahrscheinlicher ist, dass der Separator darin vorkommt. Naja, vielleicht kommt's ja auch nur auf den Separator an, ich denke nicht, dass z.B. '<separator/>' in einem BLOB vorkommen würde.
2.) Multiple Self-Joins mit Sub-Selects:
SELECT en.IN_ID, en.resource AS en, de.resource AS de, ur.resource AS ur FROM (SELECT IN_ID, resource FROM i18n WHERE lang='en') as en, (SELECT IN_ID, resource FROM i18n WHERE lang='de') as de, (SELECT IN_ID, resource FROM i18n WHERE lang='ur') as ur WHERE en.IN_ID = de.IN_ID AND en.IN_ID = ur.IN_IDEignen tut sich diese Methode nicht wirklich, um die Ressourcen aller verfügbaren Sprachen in einer einzigen Query zu selektieren. Für jede weitere Sprache ein Sub-Select + ein Self-Join; wer weiß wie rechenintensiv das bei großen Tabellen sein muss. Außerdem ist ein WHERE-Join strikter als ein LEFT JOIN: es werden nur Datensätze selektiert die in allen Sprachen existieren. Ich hab probiert diese Sub-Selects in eine LEFT JOIN-Anweisung zu platzieren, aber es geht leider nicht. Entweder wegen MySQL 4.1x oder der SQL-Standard kennt sowas nicht (wahrscheinlich eher ersteres). Views könnten Abhilfe schaffen, aber ich hab ja kein MySQL 5.x, obwohl ich mit dem Gedanken spiele es zu installieren.
Mit großer Freude würde ich jetzt gerne Eure Kommentare und Ideen zu diesem Thema hören. SQL-Experten, meldet Euch!
Mfg,
AzizPS.: Mit Stored Procedures wäre bestimmt auch eine Lösung zu finden, und auch wenn ich mich damit noch nicht auseinandergesetzt habe (aufgrund mangelnden Supports seitens MySQL), wäre ich trotzdem daran interessiert wenn ihr solche Lösungen posten würdet..
-
Einschränkung: kenne MySql bestenfalls rudimentär.
Bist du sicher, dass Subqueries mit FROM funktionieren? Ist zwar laut Standard zulässig, ich kenne aber keine DBMS, die das unterstützt.
Versuch mal das hier:
SELECT DISTINCT i1.IN_ID, (SELECT i2.resource FROM i18n i2 WHERE lang='en' AND i2.in_id = i1.in_id ), (SELECT i2.resource FROM i18n i2 WHERE lang='de' AND i2.in_id = i1.in_id ), (SELECT i2.resource FROM i18n i2 WHERE lang='ur' AND i2.in_id = i1.in_id ) FROM i18n i1Wenn nicht für alle Sprachen ein Datensatz gefunden wurde, sind die betreffenden Spalten NULL.
-
Alternativ:
SELECT i1.IN_ID, (SELECT i2.resource FROM i18n i2 WHERE i2.lang='en' AND i2.in_id = i1.in_id ), (SELECT i2.resource FROM i18n i2 WHERE i2.lang='de' AND i2.in_id = i1.in_id ), (SELECT i2.resource FROM i18n i2 WHERE i2.lang='ur' AND i2.in_id = i1.in_id ) FROM i18n i1 GROUP BY i1.in_id
-
Mir ist übrigens auch nicht klar, warum du alle Sprachen brauchst. Lass doch den User eine Sprache wählen und filter dann auch nur nach dieser. Das erspart die Subqueries und GOUP BY bzw. DISTINCT.
-
Entschuldigt, dass ich euch nicht am Laufenden gehalten habe, denn es hat sich einiges getan in den letzten paar Tagen. Ich hab mein Problem auch den Usern im MySQL Newbie Forum geschildert, nachdem ich hier keine Antworten erhielt und mir dachte, dass die Leute dort drüben vielleicht mehr Ahnung von diesem Gebiet hätten. So überaus interessiert schienen die User dort an meinem Thread jedoch nicht gewesen zu sein; ungefähr 13 Reads zählte ich bei meinem Thread nach 2 Tagen.
Wie auch immer, ich habe folgende Dinge herausgefunden:
.) Ich hab mich geirrt was die Sache mit Sub-Queries in LEFT JOINs betrifft. Es ist doch möglich:
SELECT en.IN_ID, en.resource AS en, de.resource AS de, ur.resource AS ur FROM (SELECT IN_ID, resource FROM i18n WHERE lang='en') as en LEFT JOIN (SELECT IN_ID, resource FROM i18n WHERE lang='de') as de ON en.IN_ID=de.IN_ID LEFT JOIN (SELECT IN_ID, resource FROM i18n WHERE lang='ur') as ur ON en.IN_ID=ur.IN_IDMan könnte alternativ die WHERE-Bedingungen in den LEFT JOIN Sub-Queries in den ON-Teil verschieben,...
SELECT en.IN_ID, en.resource AS en, de.resource AS de, ur.resource AS ur FROM (SELECT IN_ID, resource FROM i18n WHERE lang='en') as en LEFT JOIN (SELECT IN_ID, resource, lang FROM i18n) as de ON en.IN_ID=de.IN_ID AND de.lang='de' LEFT JOIN (SELECT IN_ID, resource, lang FROM i18n) as ur ON en.IN_ID=ur.IN_ID AND ur.lang='ur'...was eigentlich unerheblich ist, weil beide Abfragen extrem schwach sind im Sinne von Schnelligkeit (und Lesbarkeit). Kann man also verwerfen, denn die nächste Abfrage ist viel besser.
.) Die Abfrage mit der GROUP BY Klausel kann folgendermaßen verbessert werden:
SELECT IN_ID , GROUP_CONCAT( CASE lang WHEN 'en' THEN resource END ) AS en , GROUP_CONCAT( CASE lang WHEN 'de' THEN resource END ) AS de , GROUP_CONCAT( CASE lang WHEN 'ur' THEN resource END ) AS ur FROM i18n GROUP BY IN_IDDiese Abfrage umgeht auf elegante Weise das Problem mit den Separatoren, und relativ schnell ist sie noch dazu. Es ist auch möglich statt GROUP_CONCAT die Funktion MAX zu verwenden. GROUP_CONCAT scheint aber einen Tick schneller zu sein, vielleicht weil bei MAX eine Sortierung nach dem "größten String" notwendig ist.
Nun zu deinem Beitrag Finten (vielen Dank, dass du dich gemeldet hast):
Finten schrieb:
Bist du sicher, dass Subqueries mit FROM funktionieren? Ist zwar laut Standard zulässig, ich kenne aber keine DBMS, die das unterstützt.
Ja, absolut! Anfangs dachte ich, dass es nur im FROM-Teil funktioniert, aber später fand ich heraus, dass es auch ohne Probleme in JOIN-Klauseln funktioniert. Ein tolles Feature kann ich nur sagen. Verwendest du zufälligerweise PostgreSQL? Man sagt diesem DBMS allerlei positiven Dinge nach, aber wenn es keine Sub-Queries in FROM/JOIN-Klauseln unterstützt, dann ist das ja ziemlich schwach!
Finten schrieb:
Versuch mal das hier:
SELECT DISTINCT i1.IN_ID, (SELECT i2.resource FROM i18n i2 WHERE lang='en' AND i2.in_id = i1.in_id ), (SELECT i2.resource FROM i18n i2 WHERE lang='de' AND i2.in_id = i1.in_id ), (SELECT i2.resource FROM i18n i2 WHERE lang='ur' AND i2.in_id = i1.in_id ) FROM i18n i1Wenn nicht für alle Sprachen ein Datensatz gefunden wurde, sind die betreffenden Spalten NULL.
Äußerst interessante Abfrage. Ich wusste garnicht, dass man Sub-Selects im SELECT-Teil selber verwenden kann. Und wieder was dazugelernt
Wenn man diese Abfrage mit meiner GROUP_CONCAT-Abfrage vergleicht, dann kann man eine gewisse Ähnlichkeit in der Logik festellen. Ich dachte mir die ganze Zeit: Warum kann ich bei einer GROUP BY-Abfrage im SELECT-Teil nicht sowas wie "GROUPSELECT resource WHERE lang='en'" schreiben? Dein Tip ist die Antwort auf diese Frage. Herzlichen Dank
Was ich außerdem originell an dieser Abfrage finde ist, dass MySQL den PRIMARY KEY-Index verwendet um die Datensätze zu finden und zu ordnen. Das ist bei meinen Abfragen nicht der Fall. Jedoch scheinen meine GROUP BY-Abfrage und deine Abfrage ungefähr gleich schnell zu sein (naja hab auch nur 712 Tupel im Moment). Werd ich bestimmt öfters auf Geschwindigkeit testen, wenn mal die Zahl der Datensätze in der Tabelle monströs steigt.Finten schrieb:
Mir ist übrigens auch nicht klar, warum du alle Sprachen brauchst. Lass doch den User eine Sprache wählen und filter dann auch nur nach dieser. Das erspart die Subqueries und GOUP BY bzw. DISTINCT.
Da hab ich mich wohl über den genauen Grund diesbezüglich etwas verschwiegen, und berechtigt ist deine Frage allemal. Der Grund warum ich so eine Abfrage suchte, ist, dass ich im Admin-Panel alle Ressourcen in allen Sprachen nebeneinander sehen möchte. Selbstverständlich wird das unübersichtlich wenn man mal mehr als 10 Sprachen hat, aber für diese Situation hätte ich eine Option eingebaut wo ich einstellen kann welche Sprachen ich sehen möchte. Ein weiterer und viel wichtigerer Grund ist, dass ich der Person, die die Ressourcen übersetzen soll, zwei Spalten anzeigen muss, wobei in der ersten Spalte Englisch (die Quellsprache) zu sehen ist und in der zweiten Spalte die Übersetzungssprache zu sehen ist (damit die Person sehen kann was er/sie bereits übersetzt hat, oder noch aktualisieren muss). Und wer weiß, vielleicht finde ich jemanden der das Englische in mehr als nur eine Sprache übersetzen kann. Diese Queries sind also nur für administrative Zwecke gedacht.
Für die Besucher meiner Webseite hol ich die Ressourcen immer nur in einer einzigen Sprache, wo die Abfrage mit einem "WHERE lang='xy'" sehr simpel ausschaut.
-
Hallo Aziz,
Ja, absolut! Anfangs dachte ich, dass es nur im FROM-Teil funktioniert, aber später fand ich heraus, dass es auch ohne Probleme in JOIN-Klauseln funktioniert. Ein tolles Feature kann ich nur sagen. Verwendest du zufälligerweise PostgreSQL? Man sagt diesem DBMS allerlei positiven Dinge nach, aber wenn es keine Sub-Queries in FROM/JOIN-Klauseln unterstützt, dann ist das ja ziemlich schwach!
Ich arbeite hauptsächlich mit MS-SQL-Server 2000. Dort sind eigentlich Überall (SELECT, WHERE, ORDER) Subqueries möglich, bis auf FROM. IMHO ist das auch nicht bei Oracle (Version
möglich.Finde ich interessant, dass MySql das kann, wenn man bedenkt, dass es in früheren Versionen gar keine Subqueries unterstützte. Sollte mir das vielleicht mal genauer ansehen, wobei ich diese Funktionalität eigentlich nicht mehr vermisst habe. Früher war ich auch enttäuscht, dass es nicht geht, aber es gibt eh fast immer verschiedene Möglichkeiten der Implemantation, so dass man das umgehen kann.
Hab' übrigens auch erst poe a poe bemerkt, wo man überall Subqueries verwenden kann. Zuerst hab ich bei WHERE, dann im SELECT und dann, als ich schon dachte, ein bestimmtes Problem wäre nicht zu lösen, in der ORDER-Clausel probiert, und... es hat funktioniert.
-
Hallo,
Vielleicht geht das in MySQL 4.1 ja auch nur weil es noch keine Views unterstützt (gibt's erst in v5.x). Wenn ich die Wahl hätte, dann würde ich wahrscheinlich Views statt Sub-Queries in FROM bzw. JOIN Klauseln verwenden.
Sub-Queries in ORBER BY-Klauseln? Hört sich ziemlich ungewöhnlich an
Ich hab mal ein paar EXPLAINs über deine Query laufen lassen, und festgestellt, dass es einen Unterschied zwischen der Verwendung von DISTINCT und GROUP BY gibt. Bei DISTINCT verwendet MySQL eine temporäre Tabelle, aber bei einem GROUP BY wird ausschließlich der PRIMARY KEY Index verwendet. Einer der wenigen Fälle wo ein GROUP BY wegoptimisiert werden kann.
Übrigens kann man deine Query etwas verkürzen, indem man ein paar Tabellen-Aliase weglässt (wird nur im WHERE-Teil der Sub-Queries benötigt):
SELECT IN_ID, (SELECT resource FROM i18n WHERE lang='en' AND in_id = i1.in_id ), (SELECT resource FROM i18n WHERE lang='de' AND in_id = i1.in_id ), (SELECT resource FROM i18n WHERE lang='ur' AND in_id = i1.in_id ) FROM i18n i1 GROUP BY IN_ID
-
Sub-Queries in ORBER BY-Klauseln? Hört sich ziemlich ungewöhnlich an
Jo, kann aber Sinn machen, wenn der Wert, nachdem sortiert werden soll, erst aus Spalten mehrer Tabellen berechnet werden muss.
Übrigens kann man deine Query etwas verkürzen, indem man ein paar Tabellen-Aliase weglässt (wird nur im WHERE-Teil der Sub-Queries benötigt):
Klar muss man die Alias nicht immer hinschreiben. Ich habs mir ab so angewöhnt; wenn man mit mehreren Verschachtelungsebenen arbeitet, weiß man so immer, worauf man sich bezieht. Ich zähl dann immer hoch, erste Ebene kriegt ne eins (oder nichts) drangehängt, ans Alias, die zweite eine zwei usw. .