MySQL utf-8 sehr langsam

[Peter]

Hallo,

ich habe in meiner DB von latin1 auf utf-8 umgestellt und jetzt sind die Abfragen um ca. das 10-fache langsamer.

Mit was hängt dies zusammen und wie kann ich es wieder auf die ursprüngliche Geschwindigkeit schaffen?

lg
Peter

[Vinzenz Mai]

Hallo Peter,

ich habe in meiner DB von latin1 auf utf-8 umgestellt und jetzt sind die Abfragen um ca. das 10-fache langsamer.

wie hast Du dies festgestellt?

Mit was hängt dies zusammen und wie kann ich es wieder auf die ursprüngliche Geschwindigkeit schaffen?

was sagt EXPLAIN zu Deinen Abfragen?

Freundliche Grüße

Vinzenz

[Peter]

Hallo Peter,

ich habe in meiner DB von latin1 auf utf-8 umgestellt und jetzt sind die Abfragen um ca. das 10-fache langsamer.

wie hast Du dies festgestellt?

die selects dauern eben jetzt um so viel länger

Mit was hängt dies zusammen und wie kann ich es wieder auf die ursprüngliche Geschwindigkeit schaffen?

was sagt EXPLAIN zu Deinen Abfragen?

hm, da kenn ich mich leider zu wenig aus - was soll Explain denn genau liefern?

lg
Peter

[Peter]

Hallo,

was sagt EXPLAIN zu Deinen Abfragen?

hm, da kenn ich mich leider zu wenig aus - was soll Explain denn genau liefern?

Ich hab jetzt ein wenig herumprobiert und gesehen, dass mir bei EXPLAIN keine einziger Index genommen wird und auch Force Index bringt nichts.

lg
Peter

[Vinzenz Mai]

Hallo Peter,

was sagt EXPLAIN zu Deinen Abfragen?

Ich hab jetzt ein wenig herumprobiert und gesehen, dass mir bei EXPLAIN keine einziger Index genommen wird und auch Force Index bringt nichts.

ich zitiere aus dem von mir verlinkten Handbuchabschnitt:

<zitat>
If you have a problem with indexes not being used when you believe that they should be, you should run ANALYZE TABLE to update table statistics such as cardinality of keys, that can affect the choices the optimizer makes. See Section 13.5.2.1, “ANALYZE TABLE Syntax”.
</zitat>

Hast Du diesen Abschnitt gelesen und den Ratschlag befolgt?

Freundliche Grüße

Vinzenz

[Peter]

Hallo Vinzenz,

ANALYZE TABLE

gibt mir zurück, dass die Tabelle up to date ist und alles ok ist.

lg
Peter

[David Aurelio]

Hallo Peter,

soweit ich weiß, müssen nach umstellen des Zeichensatzes einer Tabelle/Spalte die Indizes aktualisiert werden.
Das funktioniert mit REPAIR TABLE.

Interessant zum Thema Indizes ist auch das vierte Kapitel aus O'Reillys "High Performance MySQL".

schönen Gruß,
David

[Peter]

Hallo David,

soweit ich weiß, müssen nach umstellen des Zeichensatzes einer Tabelle/Spalte die Indizes aktualisiert werden.
Das funktioniert mit REPAIR TABLE.

Danke für die Info, aber dies habe ich natürlich auch gemacht und es hat nichts gebracht.

lg
Peter

[David Aurelio]

Hallo Peter,

ich war vorgestern noch auf einer Seite, wo genau erklärt war, welche Indizes man für welche Typen von Abfragen einrichten sollte. Ich werde mal schauen, ob ich das in meiner History wiederfinde.

schönen Gruß,
David

[David Aurelio]

Hallo Peter,

[...] in meiner History wiederfinde.

... und da habe ich es. Ist nicht so sensationell, wie ich es in Erinnerung hatte, enthält aber einige interessante Punkte und Hinweise, die Dir vielleicht helfen, die zu Deinen Abfragen passenden (mehrspaltigen!) Indizes anzulegen.

schönen Gruß,
David

[Vinzenz Mai]

Hallo Peter,

ich habe in meiner DB von latin1 auf utf-8 umgestellt und jetzt sind die Abfragen um ca. das 10-fache langsamer.

wie hast Du dies festgestellt?

die selects dauern eben jetzt um so viel länger

Gemessen oder gefühlt?

was sagt EXPLAIN zu Deinen Abfragen?

hm, da kenn ich mich leider zu wenig aus - was soll Explain denn genau liefern?

Ich habe Dir den Link auf das Handbuchkapitel mit EXPLAIN gegeben, damit Du Dich mit Deinen Abfragen beschäftigen kannst, damit Du Dir den Ausführungsplan anschauen kannst, damit Du ermitteln kannst, wo Du optimieren kannst. Ich kenne weder Deine Tabellen noch deren Inhalte und auch nicht Deine Abfragen. Ohne diese Kenntnisse kann man Dir nur ganz allgemeine Ratschläge geben.

Deine Aufgabe ist es, mit EXPLAIN Deine Abfragen zu überprüfen. Fange mit der Abfrage an, deren Ausführungszeit Deiner Ansicht nach am dramatischsen angestiegen ist.

Freundliche Grüße

Vinzenz

[Peter]

Hallo Vinzenz,

die selects dauern eben jetzt um so viel länger

Gemessen oder gefühlt?

Natürlich gemessen.
Vorher durchschnittlich 2 Sekunden und jetzt durchschn. 40 Sekunden.

Das ist meine Tabelle:
CREATE TABLE tbl\_search (
  pk\_id int(10) NOT NULL,
  ti varchar(600) character set utf8 collate utf8_bin NOT NULL,
  be text character set utf8 collate utf8_bin,
  pd date default NULL,
  fk\_id tinyint(3) default NULL,
  tx longtext,
  pid varchar(20) default NULL,
  qid int(10) default NULL,
  dd date NOT NULL default '0000-00-00',
  indate date NOT NULL default '0000-00-00',
  cc varchar(4500) default NULL,
  nu varchar(4500) default NULL,
  rid tinyint(3) unsigned NOT NULL default '0',
  tsframe tinyint(3) unsigned default '0',
  qAID int(10) unsigned default NULL,
  PRIMARY KEY  (pk\_id),
  KEY Index\_2 (ti(333)),
  KEY Index\_4 (fk\_id),
  KEY Index\_6 (rid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 105472 kB';

Meine Abfrage geht hauptsächlich auf ti, be, tx und rid.
Jetzt kann ich aber nicht mal mehr einen Index auf be und tx setzen.

Die Tabelle hat 60.000 Datensätze und die Abfragen sind sehr komplex die darauf gemacht werden.

lg
Peter

[Vinzenz Mai]

Hallo Peter,

die selects dauern eben jetzt um so viel länger
Natürlich gemessen.
Vorher durchschnittlich 2 Sekunden und jetzt durchschn. 40 Sekunden.

es könnte schlimmer sein, wenn kein Index genutzt werden kann :-)

Das ist meine Tabelle:
CREATE TABLE tbl\_search (
  ti varchar(600) character set utf8 collate utf8_bin NOT NULL,
  be text character set utf8 collate utf8_bin,

[...]

tx longtext,

[...]

PRIMARY KEY  (pk\_id),
  KEY Index\_2 (ti(333)),
  KEY Index\_4 (fk\_id),
  KEY Index\_6 (rid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 105472 kB';

Meine Abfrage geht hauptsächlich auf ti, be, tx und rid.
Jetzt kann ich aber nicht mal mehr einen Index auf be und tx setzen.

wie soll ich diesen Satz verstehen? Welche Fehlermeldung gibt MySQL zurück?
Wie hast Du die Daten in den betreffenden Spalten behandelt?

Freundliche Grüße

Vinzenz

[Peter]

Hallo Vinzenz,

Jetzt kann ich aber nicht mal mehr einen Index auf be und tx setzen.

wie soll ich diesen Satz verstehen? Welche Fehlermeldung gibt MySQL zurück?

Ich kann keinen Index darauf legen, weil die 2 Spalten Text-Spalten sind und man da nur 333-Chars nehmen darf und eine Volltextsuche kann ich bei den komplexen Abfragen leider nicht verwenden, da ansonsten die Abfragen zu ungenau würden - ist leider so!

Wie hast Du die Daten in den betreffenden Spalten behandelt?

Diese Frage verstehe ich leider nicht.
Kannst du sie bitte anders vormulieren?

lg
Peter

[Vinzenz Mai]

Hallo Peter,

Jetzt kann ich aber nicht mal mehr einen Index auf be und tx setzen.

wie soll ich diesen Satz verstehen? Welche Fehlermeldung gibt MySQL zurück?

Ich kann keinen Index darauf legen, weil die 2 Spalten Text-Spalten sind und man da nur 333-Chars nehmen darf

wieso nur 333 Zeichen? Du hast 1000 Bytes, enthalten Deine Inhalte wirklich mal nur 3-Byte-Zeichen? Außerdem sollte dies reichen. Bei Vergleichen mit %suchbegriff ... hilft Dir sowieso kein Index, außer einem Volltextindex.

und eine Volltextsuche kann ich bei den komplexen Abfragen leider nicht verwenden, da ansonsten die Abfragen zu ungenau würden - ist leider so!

Ohne Kenntnis von Beispieldaten und Deiner Abfragen kann man Dir nicht weiterhelfen.

Wie hast Du die Daten in den betreffenden Spalten behandelt?
Diese Frage verstehe ich leider nicht.
Kannst du sie bitte anders vormulieren?

Wenn Deine Tabelle vorher nach ISO-xxxx-y kodierte Daten enthalten hat, nun nach UTF-8 kodierte Daten enthalten soll, dann mußt Du die Daten entsprechend konvertieren. Bloß weil Du ein neues Etikett auf eine Spalte klebst, ändert sich doch nicht deren Inhalt. Daher frage ich nach Deinem Vorgehen, daher frage ich wie Du die Inhalte behandelt hast.

Freundliche Grüße

Vinzenz

[dedlfix]

echo $begrüßung;

Ich kann keinen Index darauf legen, weil die 2 Spalten Text-Spalten sind und man da nur 333-Chars nehmen darf

wieso nur 333 Zeichen? Du hast 1000 Bytes, enthalten Deine Inhalte wirklich mal nur 3-Byte-Zeichen? Außerdem sollte dies reichen. Bei Vergleichen mit %suchbegriff ... hilft Dir sowieso kein Index, außer einem Volltextindex.

MySQL reserviert bei UTF-8 3 Bytes pro Zeichen für jeden Index-Eintrag. Anscheinend kommt es damit schneller zurecht als mit einer variablen Zeichen-Länge.

Bis zu 500 Zeichen bekommt man, wenn man für diese Spalte UCS-2 verwendet. Das hat den Nachteil, dass nun generell 2 Byte für jedes Zeichen für dieses Feld verwendet werden. Die Frage ist, ob der zur Verfügung stehende Platz (Festplatte, Quota beim Provider) dann immer noch reicht.

Ansonsten kann man den Index auch auf x Zeichen beschränken.

Wie hast Du die Daten in den betreffenden Spalten behandelt?
Diese Frage verstehe ich leider nicht.
Kannst du sie bitte anders vormulieren?

Wenn Deine Tabelle vorher nach ISO-xxxx-y kodierte Daten enthalten hat, nun nach UTF-8 kodierte Daten enthalten soll, dann mußt Du die Daten entsprechend konvertieren. Bloß weil Du ein neues Etikett auf eine Spalte klebst, ändert sich doch nicht deren Inhalt.

Beim Etikett-Kleben konvertiert MySQL den Inhalt gleich mit. Voraussetzung, dass dabei alles klappt, ist, dass das alte Etikett zum bisherigen Inhalt passte.

ALTER TABLE foo CHANGE bar bar VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_general_ci

Das reicht, um den Inhalt von was auch immer nach utf8 zu konvertieren.

echo "$verabschiedung $name";

[Peter]

Hallo,

Wenn Deine Tabelle vorher nach ISO-xxxx-y kodierte Daten enthalten hat, nun nach UTF-8 kodierte Daten enthalten soll, dann mußt Du die Daten entsprechend konvertieren. Bloß weil Du ein neues Etikett auf eine Spalte klebst, ändert sich doch nicht deren Inhalt.

Beim Etikett-Kleben konvertiert MySQL den Inhalt gleich mit. Voraussetzung, dass dabei alles klappt, ist, dass das alte Etikett zum bisherigen Inhalt passte.

ALTER TABLE foo CHANGE bar bar VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_general_ci

Das reicht, um den Inhalt von was auch immer nach utf8 zu konvertieren.

Genau vorher waren Sie Iso und jetzt sind noch andere Formate dazugekommen und ich hab sie dann auf UTF-8 geändert.

Dann müsste ja eigentlich alles wieder gleichschnell sein, oder woran kann es noch liegen?

lg
Peter