ein oder zwei Tabellen für kurze und lange Texte

[Michael]

Hallo (wieder mal) Ich habe mich ja nun entscheiden die Sprachdateien, Variablen für meine Webseite in einer mysql Datenbank zu speichern. Auch hier hätte ich eine grundsätzliche Frage die mir Gedanken macht. Das meiste sind Wörter oder kleine Sätze, die man in den verschiedensten Sprachen abspeichern kann. So habe ich diese in einer Tabelle in eine VARCHAR Spalte abgelegt.

Doch ich stellte auch fest, dass es manchem längere Sprachteile (500 bis hin zu 1000 Zeichen) gibt. Ich habe zwar gelesen das man in einer VARCHAR weit über 244 Zeichen speichern kann, aber im Augenblick denke ich das es wohl besser wäre eine zweite Tabelle zu machen in der ein Text Feld für längere Texte vorhanden ist.

Ist das Grundsätzlich richtig, oder wäre eine Tabelle auch okey, in der es eine VARCHAR und eine TEXT Spalte gibt, in der man je nachdem die Inhalte hinterlegt?

Michael

[dedlfix]

Tach!

Ist das Grundsätzlich richtig, oder wäre eine Tabelle auch okey, in der es eine VARCHAR und eine TEXT Spalte gibt, in der man je nachdem die Inhalte hinterlegt?

Zwei Tabellen oder eine Tabelle mit zwei Spalten für die gleichen Werte ist beides unnötig umständlich. Nimm einfach nur eine Spalte mit TEXT.

dedlfix.

[Michael]

ist die Geschwindigkeit zu vernachlässigen?

bzw. was hat dann das Feld varchar und Text für einen Sinn?

[Rolf B]

Hallo Michael,

ist die Geschwindigkeit zu vernachlässigen?

Das weißt Du erst im laufenden Betrieb. Wie in meinem anderen Beitrag gesagt: Fang simpel an, kapsele den Textzugriff im Code und behalte Dir Optimierungen für den Fall vor, dass es zu langsam wird. Das kannst Du nur messen, messen, messen.

Mit microtime(true) oder hrtime(true) kannst Du messen, wie lange deine Textzugriffe brauchen. Bau das in dein Textzugriffsmodul ein (am Beginn und Ende die Zeit ermitteln, Differenz bilden, loggen, beobachten).

Wenn dein Code sauber gekapselt ist, ist eine Umstellung auf eine andere Repräsentierung der Texte ein Fingerschnips und schnell gemacht. Fang simpel an. Verliere Dich nicht zu Beginn in Mikrooptimierungen, deren Nutzen Du noch nicht kennen kannst. Du baust Software, keine Weltraumstation. Software zu ändern ist billig.

Rolf

[Rolf B]

Hallo dedlfix,

bist Du sicher? Im MYSQL 8 Handbuch steht

Each BLOB or TEXT value is represented internally by a separately allocated object. This is in contrast to all other data types, for which storage is allocated once per column when the table is opened.

MYSQLs Töchterlein, Data Mary, meint ähnliches:

TEXT and BLOB columns are stored with a pointer (4 or 8 bytes) + a 1-4 bytes length. The TEXT data is only stored once. This means that internally TEXT uses less memory for each open table but instead has the additional overhead that each TEXT object needs to be allocated and freed for each row access (with some caching in between).

Das könnte zu Performancestrafen führen, wenn man viele Textfragmente zusammensuchen muss. Aber man muss es messen.

VARCHAR hat den Nachteil, dass die Row-Länge laut MYSQL Handbuch auf 64K begrenzt ist, d.h. Textschlüssel, Text und ggf. 2-3 Verwaltungsinfos dürfen die 64K nicht überschreiten. TEXT löst das durch eine Speicherung außerhalb der Row. Wenn die Texte allesamt klein genug sind, sollte VARCHAR ausreichen. Der Umgang mit Rows variabler Länge sollte für MYSQL kein Problem sein.

Man kann aber auch einfach beides ausprobieren. Angefangen mit einer TEXT Tabelle, kann man nach Erfassen der meisten Texte probieren, sie nach VARCHAR zu migrieren und die Performance vergleichen.

Auf jeden Fall ist es ineffizient, für jedes Textfragment eine eine SQL Query abzusetzen. Besser sammelt man erstmal alle Fragmentkeys in einem Array und generiert daraus eine einzige Abfrage:

SELECT key, text FROM texte WHERE language='$lang' AND key in ($keys)

$lang ist der Sprachschlüssel (den man vorher auf Gültigkeit validieren sollte, deswegen escape ich ihn nicht) und $keys ist die Liste aller benötigten Textfragmentschlüssel. Ich bin jetzt nicht sicher, wie lang ein Query String maximal sein darf, das mag auch installationsabhängig sein

Damit würde ich anfangen und an Optimierung noch nicht denken. Die einzige Investition in eine Optimierung, die ich an diesem Punkt machen würde, ist das Kennzeichnen der Schlüssel für lange Textstücke durch ein spezielles Präfix, z.B. "L_INTRO" statt nur "INTRO" für einen Einführungstext. Warum? Kommt gleich.

Was auch sinnvoll ist, ist das Verschlüsseln eines Themenbereichs in den Key, und zwar an den Anfang. Wenn der Key der Clusterindex ist, führt das dazu, dass gemeinsam verwendete Texte in der DB beieinander stehen, d.h. auf der gleichen Page im Tablespace liegen. Der Abruf benötigt dann weniger Zugriffe auf's Speichermedium. Auf einer schwach belasteten Seite ist das egal, da liegt nach kurzer Zeit eh alles im Cache, aber bei starker Last kann das Auswirkungen haben.

Erst wenn sich herausstellt, dass das zu langsam ist, kommt der zweite Schritt. Wie schon früher geschrieben: Der Texte-Zugriff sollte gekapselt sein, so dass eine andere Implementierung nicht die ganze Anwendung betrifft.

Ob eine Aufteilung auf 2 Tabellen alles schneller macht, muss man erstmal durchmessen. Über das L_ Präfix der langen Texte kann man sie jetzt in eine eigene Tabelle extrahieren, und man kann bei der Generierung von $keys gleich in $shortKeys und $longKeys aufteilen. Damit kann man die Abfrage in eine UNION aufspalten:

SELECT key, text 
   FROM short_text
   WHERE language='$lang' AND key in ($shortKeys)
UNION ALL
SELECT key, text 
   FROM long_text 
   WHERE language='$lang' AND key in ($longKeys)

UNION ALL deshalb, damit SQL nicht versucht, gleiche Zeilen zu finden und zu eliminieren. Das sollte überflüssig sein und kostet nur Zeit.

Alternativ sollte man prüfen, ob ein Caching der Texte, beispielsweise über memcached, nicht effizienter ist.

Rolf

[dedlfix]

Tach!

Das könnte zu Performancestrafen führen, wenn man viele Textfragmente zusammensuchen muss. Aber man muss es messen.

Naja, das sollte für Websiten mit handhabbarer Größe im nichtmessbaren Rahmen bleiben.

Auf jeden Fall ist es ineffizient, für jedes Textfragment eine eine SQL Query abzusetzen.

Das wird eher ein Problem sein, wenn man das so macht.

Besser sammelt man erstmal alle Fragmentkeys in einem Array und generiert daraus eine einzige Abfrage:

Ja, aber das macht die Verarbeitung im Script kompliziert. Man braucht dann zwei Durchgänge, einen der die Keys sammelt und einen, der sie dann ersetzt.

dedlfix.

[Rolf B]

Hallo dedlfix,

Ja, aber das macht die Verarbeitung im Script kompliziert. Man braucht dann zwei Durchgänge, einen der die Keys sammelt und einen, der sie dann ersetzt.

Wohl wahr, aber bevor man für jeden Key zur Datenbank schleicht… Wenn man einen Cache hat, mag es wieder anders aussehen.

Rolf