MySql: Wie stark beeinträgtigen Text-Felder die Datenbank-Performance?

Hallo zusammen,

in letzter Zeit ist der Web-Zugriff auf bestimmte Inhalte unserer
Website teilweise extrem langsam. Die Ursachenforschung gestaltet sich
schwierig, da wir mit vielen anderen auf demselben Webserver sind, und
der Seitenaufbau manchmal 30Sekunden, manchmal aber auch nur 1 Sekunde
braucht.

Ich habe den Verdacht, dass die Struktur unserer Datenbank die Ursache
sein könnte.

Kurz zur Applikation:
Ca. 1.000 User (4.000 PIs) täglich suchen auf unserer Website nach
Ansprechpartnern in ihrem PLZ-Gebiet - entweder über direkte Eingabe
der PLZ, oder die User hangeln sich in ihrem PLZ-Gebiet nach unten
(wählen also z.B. PLZ 2 > 25 > 251 > 2514 > 25148). Die Userzahl wird
sich in den kommenden beiden Jahren (hoffentlich) deutlich erhöhen -
möglicherweise um den Faktor 100.

Wie gesagt, die Zugriffe in die PLZ-Gebiete sind teilweise sehr langsam
geworden: Manchmal braucht der Seitenaufbau unsägliche 30 Sekunden,
manchmal aber auch nur 1 Sekunde.

Können denn die Textfelder die Ursache sein? Die betreffende
DB-Tabelle enthält 65.000 Datensätze und besteht aus 34 Feldern, von
denen 12 praktisch immer gefüllt sind. Die anderen Felder füllen sich
erst noch und stehen bis dahin auf NULL (ist das sinnvoll so?). 2 der
34 Felder sind vom Typ Text, sie sollen die Korrespondenz mit den
65.000 Ansprechpartnern aufnehmen. Ca. 200 Datensätze enthalten in
diesen Textfeldern bereits Inhalte (jeweils ca. 2.000 Zeichen). Mit der
Zeit werden sich die Inhalte der beiden Textfelder, die die
Korrespondenz dokumentieren sollen, aber deutlich erhöhen: Ich rechne
damit, dass binnen 2 Jahren die Textfelder *jedes* Datensatzes im
Schnitt mit ca. 5.000 Zeichen gefüllt sein werden.

Wichtig zu wissen ist sicher noch folgendes: Wenn der User PLZ-Gebiete
abruft, werden die Textfelder (in der Select-Anweisung) *nicht*
abgefragt. Die Abfragen lauten so (oder ähnlich): "SELECT COUNT(Pid)
AS anzahl FROM adressen WHERE Status NOT LIKE 'gesperrt' AND
Land=3D'$land' AND Plz LIKE '$plz%' AND LetzteAenderungDurchKunde IS
NULL".

Nun (endlich ;-)) meine Frage: Woran kann's liegen? Wie stark
beeiträchtigen die Textfelder die Performance der Datenbank?

Wenn davon auszugehen ist, dass die Textfelder wirklich das Problem
sind bzw. das diese bei stärkerer Nutzung zum Problem werden, würde
ich die Tabelle natürlich sofort aufteilen. Andererseits macht das
aber die Programmierung komplizierter - ich würde also darauf
verzichten, wenn es nicht wirklich was bringt ...

Wer weiß Rat?

Danke, Annika

Re: MySql: Wie stark beeinträgtigenText-Felder die Datenbank-Performance?

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Re: =?iso-8859-1?q?MySql:_Wie_stark_beeinträgtigen_Text-Felder_die_Datenbank-Performance=3F?

"Annika Weber" wrote:
>
> in letzter Zeit ist der Web-Zugriff auf bestimmte Inhalte unserer
> Website teilweise extrem langsam. Die Ursachenforschung gestaltet sich
> schwierig, da wir mit vielen anderen auf demselben Webserver sind, und
> der Seitenaufbau manchmal 30Sekunden, manchmal aber auch nur 1 Sekunde
> braucht.

LOL.

Wenn das ein Geschäft werden soll, dann *willst* du das nicht auf einem
Shared Host betreiben. Ein eigener Rootserver ist definitiv notwendig.
Wenn die ca. EUR 50,- im Monat nicht reinkommen, wird das sowieso nie
etwas werden...

> Ich habe den Verdacht, dass die Struktur unserer Datenbank die Ursache
> sein könnte.

[viel Prosa]

65.000 Datensätze? Das ist sehr wenig. Gibts denn Indizes? Lern EXPLAIN
kennen. Im Zweifelsfall poste Tabellenstruktur (SHOW CREATE TABLE) und
das problematische (langsame) SQL-Statement hier. Und natürlich auch
die Ausgabe von EXPLAIN.

> Die Abfragen lauten so (oder ähnlich): "SELECT COUNT(Pid)
> AS anzahl FROM adressen WHERE Status NOT LIKE 'gesperrt' AND
> Land='$land' AND Plz LIKE '$plz%' AND LetzteAenderungDurchKunde IS
> NULL".

Mit vernünftigen Indizes, sagen wir mal einem Index auf (Land, Plz)
sollte das halbwegs schnell sein. Allerdings deutet "Status NOT LIKE
'gesperrt'" auf einen Designfehler hin. Status sollte ein ENUM sein.
Und der Vergleich ein !=

> Nun (endlich ;-)) meine Frage: Woran kann's liegen?

Vermutlich: fehlende Grundkenntnisse in Sachen Datenbanken.

> Wie stark
> beeiträchtigen die Textfelder die Performance der Datenbank?

Kaum. Wenn sie nicht abgefragt werden, fast gar nicht.


XL

Re: =?iso-8859-1?q?MySql:_Wie_stark_beeinträgtigen_Text-Felder_die_Datenbank-Performance=3F?

Andreas und Axel, schon mal danke für Eure Antworten - wenngleich ich
mit "42. Wie immer." nichts anfangen kann.

Axel: Du hast recht, auf einem Shared Host macht das natürlich keinen
Sinn. Wird sich auch demnächst ändern. Dennoch: Wenn in der
DB-Struktur was nicht o.k. ist, will ich das ausmerzen.

> Vermutlich: fehlende Grundkenntnisse in Sachen Datenbanken.

Tja, wie recht du hast. Derweil: Als Selbständige mit geringem Budget
bleibt mir nichts anderes übrig, als mich nur soweit in die Technik
einzuarbeiten wie dies unumgänglich ist. Hat nichts mit mangeldem
Interesse o.ä. zu tun sondern mit mangelnder Zeit und Möglichkeiten,
dies abzugeben.

Zur DB-Struktur: Es sind Indizes gesetzt (s. Create Table).

Der vermeintliche Übeltäter soll lt. Hoster sein:
"SELECT COUNT(Pid) AS anzahl FROM b2b_stammdaten WHERE Status NOT LIKE
'gesperrt' AND Land=3D'D' AND Plz LIKE 'plz%' AND
LetzteAenderungDurchKunde IS NULL"

Ergebnis von "EXPLAIN SELECT COUNT(Pid) AS anzahl FROM b2b_stammdaten
WHERE Status NOT LIKE 'gesperrt' AND Land=3D'D' AND Plz LIKE '6%' AND
LetzteAenderungDurchKunde IS NULL":

table type possible_keys key key_len ref rows Extra
b2b_stammdaten range Land,Plz,LetzteAenderungDurchKunde Plz 5 NULL 4134
Using where


"EXPLAIN b2b_stammdaten" gibt folgendes aus:

Field Type Null Key Default Extra
Pid mediumint(6) unsigne... PRI NULL auto_increment
Pw varchar(10)
Status enum('gesperrt','nic... MUL nicht gesperrt
StatusGrund enum('Aufnahme ist z... YES NULL
Genre3 varchar(40) YES NULL
Anrede enum('Frau','Herr') YES NULL
Titel varchar(30) YES NULL
Vorname varchar(20) YES NULL
Name varchar(30) YES NULL
Firmenname varchar(100)
Zusatz varchar(100) YES NULL
Strasse varchar(50)
Land enum('A','CH','D') MUL D
Plz varchar(5) MUL
Ort varchar(40)
Telefon varchar(20)
Telefax varchar(20) YES NULL
Mobil varchar(20) YES NULL
Email varchar(80) YES NULL
Website varchar(60) YES NULL
Kontoinhaber varchar(50) YES NULL
Kontonummer varchar(20) YES
Bankleitzahl varchar(20) YES
Bank varchar(50) YES
LetzteAenderungDurch... date YES MUL NULL
LetztesLoginDurchKun... datetime YES NULL
Historie text
Kundenbetreuer varchar(10)
LetzterKontaktDatum datetime YES NULL
LetzterKontaktBearbe... varchar(10) YES NULL
WvDatum date YES NULL
WvZeit time YES NULL
WvZweck enum('s. letzte WV',... YES NULL
WvBearbeiter varchar(10) YES NULL


Das Ergebnis von "SHOW CREATE TABLE":

CREATE TABLE `b2b_stammdaten` (
`Pid` mediumint(6) unsigned zerofill NOT NULL auto_increment,
`Pw` varchar(10) NOT NULL default '',
`Status` enum('gesperrt','nicht gesperrt') NOT NULL default 'nicht
gesperrt',
`StatusGrund` enum('Aufnahme ist zu prüfen','Aufnahmekriterien nicht
erfüllt','Dieser DS wurde durch einen anderen ersetzt','Der Kunde
wollte aus dem Verzeichnis entfernt werden','Der Kunde wollte aus dem
kompletten Datenbestand gelöscht werden') default NULL,
`Genre3` varchar(40) default NULL,
`Anrede` enum('Frau','Herr') default NULL,
`Titel` varchar(30) default NULL,
`Vorname` varchar(20) default NULL,
`Name` varchar(30) default NULL,
`Firmenname` varchar(100) NOT NULL default '',
`Zusatz` varchar(100) default NULL,
`Strasse` varchar(50) NOT NULL default '',
`Land` enum('A','CH','D') NOT NULL default 'D',
`Plz` varchar(5) NOT NULL default '',
`Ort` varchar(40) NOT NULL default '',
`Telefon` varchar(20) NOT NULL default '',
`Telefax` varchar(20) default NULL,
`Mobil` varchar(20) default NULL,
`Email` varchar(80) default NULL,
`Website` varchar(60) default NULL,
`Kontoinhaber` varchar(50) default NULL,
`Kontonummer` varchar(20) default '',
`Bankleitzahl` varchar(20) default '',
`Bank` varchar(50) default '',
`LetzteAenderungDurchKunde` date default NULL,
`LetztesLoginDurchKunde` datetime default NULL,
`Historie` text NOT NULL,
`Kundenbetreuer` varchar(10) NOT NULL default '',
`LetzterKontaktDatum` datetime default NULL,
`LetzterKontaktBearbeiter` varchar(10) default NULL,
`WvDatum` date default NULL,
`WvZeit` time default NULL,
`WvZweck` enum('s. letzte WV','Angebot unterbreiten','Angebot per
autom. E-Mail nachfassen','Angebot per Telefon nachfassen','Aufnahme
prüfen','Kundenanfrage erfassen','Support per E-Mail','Support per
Telefon','Keine Wiedervorlage') default NULL,
`WvBearbeiter` varchar(10) default NULL,
PRIMARY KEY (`Pid`),
KEY `PLZ_SUCHE` (`Status`,`Land`,`Plz`,`LetzteAenderungDurchKunde`),
KEY `Status` (`Status`),
KEY `Land` (`Land`),
KEY `Plz` (`Plz`),
KEY `LetzteAenderungDurchKunde` (`LetzteAenderungDurchKunde`)
) TYPE=3DMyISAM


Nochmals vielen Dank, Annika

Re: =?iso-8859-1?q?MySql:_Wie_stark_beeinträgtigen_Text-Felder_die_Datenbank-Performance=3F?

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Re: =?iso-8859-1?q?MySql:_Wie_stark_beeinträgtigen_Text-Felder_die_Datenbank-Performance=3F?

Hallo,

auch Andreas zweite Antwort bringt mich leider der Lösung nicht
näher. Axel, kannst du mit meinem Posting von 14:08 das Problem enger
eingrenzen?

Danke, Annika

Re: =?iso-8859-1?q?MySql:_Wie_stark_beeinträgtigen_Text-Felder_die_Datenbank-Performance=3F?

Andreas Kretschmer schrieb:
> begin Annika Weber schrieb:
>
>
>>Ergebnis von "EXPLAIN SELECT COUNT(Pid) AS anzahl FROM b2b_stammdaten
>>WHERE Status NOT LIKE 'gesperrt' AND Land='D' AND Plz LIKE '6%' AND
>>LetzteAenderungDurchKunde IS NULL":
>>
>>table type possible_keys key key_len ref rows Extra
>>b2b_stammdaten range Land,Plz,LetzteAenderungDurchKunde Plz 5 NULL 4134
>>Using where
>
>
>
> Fragen an die MySQL-Leute hier: (ich nutze es ja nicht)
>
> Gibt EXPLAIN unter MySQL wirklich nur sooo wenig aus? Ich vermisse hier
> echt einen Ausführungsplan und eine Kostenaufstellung, Zeiten, ...

Das hatten wir schon oft hier, auch mit dir in der Diskussion.

@Annika Weber: Wenn du mit dem mysql-client arbeitest, dann ersetze
bitte das ";" durch ein "\G". Dadurch wird lesbar formatiert.

>
> Will mir obiges sagen, es gibt 3 Indexe (Land, Plz,
> LetzteAenderungDurchKunde), von denen nur PLZ genutzt wird? Kann MySQL
> für eine Suche nur einen von mehreren Indexen nutzen?

Ja, es wird nur einer genutzt. Wobei da irgendeine Änderung geplant ist,
oder gar schon umgesetzt.

Grüße

Dirk

Re: =?iso-8859-1?q?MySql:_Wie_stark_beeinträgtigen_Text-Felder_die_Datenbank-Performance=3F?

Annika Weber schrieb:
> Andreas und Axel, schon mal danke für Eure Antworten - wenngleich ich
> mit "42. Wie immer." nichts anfangen kann.
>
> Axel: Du hast recht, auf einem Shared Host macht das natürlich keinen
> Sinn. Wird sich auch demnächst ändern. Dennoch: Wenn in der
> DB-Struktur was nicht o.k. ist, will ich das ausmerzen.
>
>
>>Vermutlich: fehlende Grundkenntnisse in Sachen Datenbanken.
>
>
> Tja, wie recht du hast. Derweil: Als Selbständige mit geringem Budget
> bleibt mir nichts anderes übrig, als mich nur soweit in die Technik
> einzuarbeiten wie dies unumgänglich ist. Hat nichts mit mangeldem
> Interesse o.ä. zu tun sondern mit mangelnder Zeit und Möglichkeiten,
> dies abzugeben.

Ein Tipp: Aus Designsicht wäre es besser, wenn du weniger Enums
verwendest. Stattdessen besser eine zweite Tabelle, in der dein Status
als Text mit einem Primarykey steht und dann dann in der Haupttabelle
nur einen Fremdschlüssel. Der Vorteil ist, dass du einen weiteren Status
durch ein Insert erzeugen kannst, in dem anderen Fall müsstest du ein
Alter TABLE machen, das kann evtl. sehr lange dauern.

Ausserdem würde ich dir empfehlen zumindestens ein ER-Diagramm zu
erstellen und dieses dann von einem versierten Kenner der
Datenbankmaterie checken zu lassen. Das kostet vielleicht Geld, aber das
ist garantiert gut angelegt, weil die ganze Applikation genau auf dieses
ER-Diagramm aufbaut. Wenn du später einen Designfehler feststellst, kann
das evtl. sehr teuer werden.

Grüße

Dirk

Re: =?iso-8859-1?q?Re:_=?iso-8859-1=3Fq=3FMySql:=5FWie=5Fstark=5Fbeeinträgtigen=5FText-Felder=5

"Annika Weber" wrote:
>
>> Vermutlich: fehlende Grundkenntnisse in Sachen Datenbanken.
>
> Tja, wie recht du hast. Derweil: Als Selbständige mit geringem Budget
> bleibt mir nichts anderes übrig, als mich nur soweit in die Technik
> einzuarbeiten wie dies unumgänglich ist. Hat nichts mit mangeldem
> Interesse o.ä. zu tun sondern mit mangelnder Zeit und Möglichkeiten,
> dies abzugeben.

Das hört man oft. Zu oft. Viele Startups sterben auch daran, weil
schnell hingepfriemelte Lösungen nicht skalieren. Insofern hast du
Glück, daß dir das jetzt schon passiert.

Mein Rat: such dir professionelle Hilfe. Hier zu fragen ist ein Anfang,
ich schätze einige der Mitleser hier machen entweder selber Consulting
oder können jemanden empfehlen. Nicht zuletzt hat auch MySQL AB (die
Firma hinter MySQL) Consulting-Angebote. OK, derzeit wahrscheinlich
außerhalb deines Preislimits.

> Zur DB-Struktur: Es sind Indizes gesetzt (s. Create Table).

> CREATE TABLE `b2b_stammdaten` (

> `Status` enum('gesperrt','nicht gesperrt') NOT NULL default 'nicht gesperrt',
> `StatusGrund` enum('Aufnahme ist zu prüfen','Aufnahmekriterien nicht
> erfüllt','Dieser DS wurde durch einen anderen ersetzt','Der Kunde
> wollte aus dem Verzeichnis entfernt werden','Der Kunde wollte aus dem
> kompletten Datenbestand gelöscht werden') default NULL,

Oops. Hier würde ich dir in deinem eigenen Interesse empfehlen,
die ENUM-Werte auf jeweils ein Wort zu verkürzen.
Und aus
Status ENUM('gesperrt','nicht gesperrt') würde ich
Gesperrt ENUM('ja','nein') oder gar
Gesperrt BOOL machen.

> `Anrede` enum('Frau','Herr') default NULL,

Das könnte sich irgendwann als Sackgasse erweisen.

> `Land` enum('A','CH','D') NOT NULL default 'D',

Dito.

> `WvZweck` enum('s. letzte WV','Angebot unterbreiten','Angebot per
> autom. E-Mail nachfassen','Angebot per Telefon nachfassen','Aufnahme
> prüfen','Kundenanfrage erfassen','Support per E-Mail','Support per
> Telefon','Keine Wiedervorlage') default NULL,

Zu ENUMs habe ich ja schon was gesagt.

> KEY `PLZ_SUCHE` (`Status`,`Land`,`Plz`,`LetzteAenderungDurchKunde`),
> KEY `Status` (`Status`),
> KEY `Land` (`Land`),
> KEY `Plz` (`Plz`),
> KEY `LetzteAenderungDurchKunde` (`LetzteAenderungDurchKunde`)

Indizes sind immer ein schweres Thema. Vorweg: `Status` und `Land` zu
indizieren bringt praktisch nichts, da diese Spalten nur 2 bzw. 3
verschiedene Werte annehmen können. Da man i.d.R. nicht ohne Land nach
PLZ suchen wird, erschließt sich mir der Sinn des Index auf Plz auch
nicht so recht. Ich würde sogar vorschlagen, Land und PLZ in der
Datenbank zusammen in eine Spalte zu schreiben a'la "D-60000".

Aber offensichtlich kennst du die Daumenregel: wenn ein SELECT ...
WHERE ... zu langsam ist, mach einen Index über die Spalten in der
WHERE-Klausel. Gut. Aber wenn du von einer Spalte nur einen Präfix-
Match machst (wie in Plz LIKE '$foo%') - nützen dir weitere Spalten
im Index gar nichts.

> Ergebnis von "EXPLAIN SELECT COUNT(Pid) AS anzahl FROM b2b_stammdaten
> WHERE Status NOT LIKE 'gesperrt' AND Land='D' AND Plz LIKE '6%' AND
> LetzteAenderungDurchKunde IS NULL":

[unlesbar]

Wiederhole das nochmal mit \G statt ; am Zeilenende. Versuche das
auch mal mit ... WHERE Status='nicht gesperrt' ...

Dann ändere deinen Index `PLZ_SUCHE` mal wie folgt:

Index (Land, Status, LetzteAenderungDurchKunde, Plz)

und mach die EXPLAINs nochmal. Und immer schön versuchen, die
Ergebnisse zu verstehen.


HTH, XL

Re: =?iso-8859-1?q?Re:_=?iso-8859-1=3Fq=3FMySql:=5FWie=5Fstark=5Fbeeinträgtigen=5FText-Felder=5

"Annika Weber" wrote:
> Hallo,
>
> auch Andreas zweite Antwort bringt mich leider der Lösung nicht
> näher. Axel, kannst du mit meinem Posting von 14:08 das Problem enger
> eingrenzen?

Sachte. Usenet ist kein Echtzeitmedium.


XL

Re: Wie stark beeinträgtigen Text-Felder die Datenbank-Performance?

Axel Schwenke schrieb:

> Dann ändere deinen Index `PLZ_SUCHE` mal wie folgt:
>
> Index (Land, Status, LetzteAenderungDurchKunde, Plz)
>
> und mach die EXPLAINs nochmal. Und immer schön versuchen, die
> Ergebnisse zu verstehen.

1.Ergänzend würde ich noch vorschlagen:
Pid in den Index mit aufzunehmen
Index (Land, Status, LetzteAenderungDurchKunde, Plz, Pid)
Dann kann^Wsollte ein "Using Index" dabei raus springen.
Muss man aber ausprobieren ob das nach LIKE auch noch funktioniert.
Außerdem sollte dann die index-Datei noch in den Speicher() passen!

2.Mysql wird sich eventuell weigern diesen Index zu benutzen, weil Plz
IMHO die selektivste Spalte ist, und für die anderen zu wenig Werte
existieren. Du kannst versuchen MySQL mit FORCE INDEX zu seinem Glück
zwingen.
Mach mal ein "ANALYZE TABLE adressen;" damit die Kardinalitäten neu
ausgerechnet werden. Und schick dann mal die Ausgabe von "SHOW INDEX
FROM adressen;" das gibt dann einen Anhaltspunkt, warum der Opimzier
nicht den von Dir gewünschten Index auswählt!
Den Index von Plz ganz runter zu nehmen ist eventuell kontraproduktiv,
da es ja noch andere Statements geben kann.



Ach ja:
Status enum('gesperrt','nic... MUL nicht gesperrt

Status ist ein reserviertes Wort!!!!!11elfeins
(http://dev.mysql.com/doc/refman/4.0/de/reserved-words.html) Auch wenn
mit deiner Anwendung trotzdem alles funktioniert. Kann Software von
Drittherstellern eventuell ganz schön Probleme mit solchen Namen machen!
Zum Beispiel benennt der DB-Designer diese Spalte einfach ungefragt um!

Nenne die Spalte lieber mal "Zustand" oÄ.

P.S.
Betreff repariert!

Re: Wie stark beeinträgtigen Text-Felder die Datenbank-Performance?

Harald Stowasser wrote:
> Axel Schwenke schrieb:
>
>> Dann ändere deinen Index `PLZ_SUCHE` mal wie folgt:
>>
>> Index (Land, Status, LetzteAenderungDurchKunde, Plz)
>>
>> und mach die EXPLAINs nochmal. Und immer schön versuchen, die
>> Ergebnisse zu verstehen.
>
> 1.Ergänzend würde ich noch vorschlagen:
> Pid in den Index mit aufzunehmen
> Index (Land, Status, LetzteAenderungDurchKunde, Plz, Pid)
> Dann kann^Wsollte ein "Using Index" dabei raus springen.

Das kann man IMNSHO auch lassen. Da Pid UNIQUE ist (ist ja der PK) ist
COUNT(Pid) identisch zu COUNT(*) - d.h. MySQL braucht die Spalte gar
nicht, es reicht die Kardinalität des Resultsets. Ich bin ziemlich
sicher, daß der spätestens der 5.0 Optmizer das erkennt.

> Muss man aber ausprobieren ob das nach LIKE auch noch funktioniert.

IMHO ist das LIKE *das* eigentliche Problem. LIKE
kann keinen Index verwenden. Da der PLZ_SUCHE-Index aber gerade mit
der `Status` Spalte anfängt - ist der ganze tolle Index nutzlos.

> 2.Mysql wird sich eventuell weigern diesen Index zu benutzen, weil Plz
> IMHO die selektivste Spalte ist, und für die anderen zu wenig Werte
> existieren.

Die Selektivität eines mehrteiligen Index ist besser als die der ersten
Spalte. I.d.R. wird die Selektivität mit jeder weiteren Spalte besser.
Der Index über (Land, Status, LetzteAenderungDurchKunde, Plz) *ist*
der selektivste welche. Insbesondere ist er selektiver als ein Index
nur über `Plz`.


XL

Re: Wie stark beeinträgtigen Text-Felder die Datenbank-Performance?

Axel Schwenke schrieb:

> Harald Stowasser wrote:

>>1.Ergänzend würde ich noch vorschlagen:
>>Pid in den Index mit aufzunehmen
>>Index (Land, Status, LetzteAenderungDurchKunde, Plz, Pid)
>>Dann kann^Wsollte ein "Using Index" dabei raus springen.
>
> Das kann man IMNSHO auch lassen. Da Pid UNIQUE ist (ist ja der PK) ist
> COUNT(Pid) identisch zu COUNT(*) - d.h. MySQL braucht die Spalte gar
> nicht, es reicht die Kardinalität des Resultsets. Ich bin ziemlich
> sicher, daß der spätestens der 5.0 Optmizer das erkennt.

Also einige meiner Mysql rattern da los. Wenn 5.0 das schon erkennt um
so besser. Der Tipp mit COUNT(*) ist allerdings goldrichtig! Was man so
alles übersehen kann ;-)

> IMHO ist das LIKE *das* eigentliche Problem. LIKE
> kann keinen Index verwenden. Da der PLZ_SUCHE-Index aber gerade mit
> der `Status` Spalte anfängt - ist der ganze tolle Index nutzlos.

Ja.

> Die Selektivität eines mehrteiligen Index ist besser als die der ersten
> Spalte. I.d.R. wird die Selektivität mit jeder weiteren Spalte besser.
> Der Index über (Land, Status, LetzteAenderungDurchKunde, Plz) *ist*
> der selektivste welche. Insbesondere ist er selektiver als ein Index
> nur über `Plz`.

Nicht unbedingt. Wenn für Land, Status, LetzteAenderungDurchKunde
jeweils nur 1 Wert steht, und/oder die Kardinalitäts-Schätzwerte gleich
sind. Dann sieht der Optimizer doch das der Plz-Index gleich Selektiv
ist. Und nimmt dann den, wo Plz weiter vorne ist.
Darum wollte ich doch "SHOW INDEX FROM adressen;" sehen.

Re: Wie stark beeinträgtigen Text-Felder die Datenbank-Performance?

Harald und Axel,

vielen Dank erst mal an dieser Stelle! Ich habe den gröbsten Fehler
(das LIKE) nun bereinigt, und die ganze Sache scheint auch schon
deutlich besser zu flutschen. Scheint deshalb, weil eine definitive
Aussage dazu derzeit kaum möglich ist, da die Domain ja noch auf einem
Shared Host liegt. Auch das wird sich ja zum Glück demnächst ändern.

Auf die anderen Änderungen muss ich momentan wohl oder übel
verzichten, damit das Projekt vorankommt. Aber aufgeschoben ist nicht
aufgehoben - und spätestens bei den nächsten Performance-Problemen
weiß ich, wo ich ran muss ...

Also, nochmals vielen Dank Euch beiden!!

Schöne Grüße, Annika

Re: Wie stark beeinträgtigen Text-Felder die Datenbank-Performance?

Axel Schwenke wrote:
> Harald Stowasser wrote:

>> Ergänzend würde ich noch vorschlagen:
>> Pid in den Index mit aufzunehmen
>> Index (Land, Status, LetzteAenderungDurchKunde, Plz, Pid)
>> Dann kann^Wsollte ein "Using Index" dabei raus springen.
>
> Das kann man IMNSHO auch lassen. Da Pid UNIQUE ist (ist ja der PK) ist
> COUNT(Pid) identisch zu COUNT(*) - d.h. MySQL braucht die Spalte gar
> nicht, es reicht die Kardinalität des Resultsets. Ich bin ziemlich
> sicher, daß der spätestens der 5.0 Optmizer das erkennt.

Nachtrag:

Ich habe das gerade mal überprüft mit MySQL 5.0.12 (war grade da).
MySQL macht diese Optimierung nicht! MaW:

SELECT COUNT(*)
FROM
WHERE

ist schnell - es macht einen reinen Index-Lookup, hingegen ist

SELECT COUNT()
FROM
WHERE

langsam. Es liest tatsächlich alle Treffer aus dem Datenfile.


Guckst du:

show create table t1\G;
*************************** 1. row ***************************
CREATE TABLE `t1` (
`c1` int(11) NOT NULL,
`c2` int(11) default NULL,
PRIMARY KEY (`c1`),
KEY `c2` (`c2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 2 |
| 6 | 3 |
| 7 | 3 |
+----+------+

explain select count(c1) from t1 where c2=2\G
*************************** 1. row ***************************
select_type: SIMPLE
possible_keys: c2
key: c2
key_len: 5
ref: const
rows: 2
Extra: Using where

explain select count(*) from t1 where c2=2\G
*************************** 1. row ***************************
select_type: SIMPLE
possible_keys: c2
key: c2
key_len: 5
ref: const
rows: 2
Extra: Using where; Using index
^^^^^^^^^^^

XL

Re: =?iso-8859-1?q?Re:_Wie_stark_beeinträgtigen_Text-Felder_die_Datenbank-Performance=3F?

"Annika Weber" wrote:

Da bist du ja wieder. Erst konnte es dir nicht schnell genug gehen
und dann kam 2 Tage nichts mehr von dir.

> vielen Dank erst mal an dieser Stelle! Ich habe den gröbsten Fehler
> (das LIKE) nun bereinigt, und die ganze Sache scheint auch schon
> deutlich besser zu flutschen.

Das ist schön zu hören. Verrätst du uns nun auch, um welche Website
es sich handelt?

> Auf die anderen Änderungen muss ich momentan wohl oder übel
> verzichten, damit das Projekt vorankommt.

Du solltest auf jeden Fall noch das COUNT(Pid) durch COUNT(*) ersetzen.


XL

Re: =?iso-8859-1?q?Re:_Wie_stark_beeinträgtigen_Text-Felder_die_Datenbank-Performance=3F?

Hallo Axel,

> Da bist du ja wieder.
Ja, sorry - ich war zwischenzeitlich anderweitig unterwegs.

Danke für den neuen Tipp, SELECT COUNT() durch SELECT
COUNT(*) zu ersetzen!

Allerdings: Bei mir liefert Explain *in beiden Fällen* nur "Using
where" - obwohl ja der Index gesetzt ist.

Ich bin nun nicht sicher, ob ich die von dir vorgeschlagene Änderung
wirklich durchführen soll. Meinst du, dieser Schuss könnte auch nach
hinten losgehen?

Danke nochmals, Annika

Re: =?iso-8859-1?q?Re:_=?iso-8859-1=3Fq=3FRe:=5FWie=5Fstark=5Fbeeinträgtigen=5FText-Felder=5Fdi

"Annika Weber" wrote:
>
> Danke für den neuen Tipp, SELECT COUNT() durch SELECT
> COUNT(*) zu ersetzen!

Genauer gesagt: die betreffende Spalte muß das NOT NULL Attribut haben,
damit es funktioniert. Ist beim PK aber auch gegeben.

> Allerdings: Bei mir liefert Explain *in beiden Fällen* nur "Using
> where" - obwohl ja der Index gesetzt ist.

Ich habe dir die entsprechenden Hinweise für einen besseren Index
zusammen mit der Bitte um ein neuerliches EXPLAIN schon gegeben.
Wenn du Hilfe willst, solltest du mitarbeiten.

> Ich bin nun nicht sicher, ob ich die von dir vorgeschlagene Änderung
> wirklich durchführen soll. Meinst du, dieser Schuss könnte auch nach
> hinten losgehen?

Ohne passenden Index wird sich nichts ändern. Mit passendem Index
sollte die Performance nochmal deutlich besser werden.


XL