ein oder zwei Tabellen für kurze und lange Texte

[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