Tabelle-Sortierung dauert sehr lang

[cortex]

darüber hinaus habe ich das gefühl, dass die tabelle nicht optimal "designed" ist:

Zitat von daudi

t_verkaeufer mediumint(11) unsigned NOT NULL default '0'

der wertebereich von MEDIUMINT ist max. 0 bis 16777215 (vorzeichenlos).

Zitat von daudi

t_beschreibung text collate latin1_german1_ci NOT NULL

brauchst du für die beschreibung tatsächlich 65.535 zeichen ? bei der erstellung der tabelle würde ich ausserdem die max. länge angeben.

Zitat von daudi

t_preis double(10,2) default '0.00'

DOUBLE (fliesskommazahl mit E +/- 308 ) ist für preisangaben ungeeignet; besser (und platzsparender) ist DECIMAL

Zitat von daudi

t_katalog smallint(11) unsigned NOT NULL default '0'

wertebereich von SMALLINT liegt vorzeichenlos zwischen 0 und 65535.

nun, das sind vielleicht keine grossen fehler; sie deuten allerdings auf ein gewisses missverständnis der verwendeten technik hin. kenntnisse über den einsatz des richtigen datentyps gehören zu den basics eines "anständigen" db-designs...

cx

[cortex]

Zitat von daudi

Ja, ich brauche alle Einträge auf einmal auf der main-seite.

vielleicht ist dein server / hosting-paket schlichtweg überfordert. wenn du so grosse datenmengen speichern und in einem rutsch verarbeiten musst... upgrade ?

cx

[daudi]

Zitat von cortex

darüber hinaus habe ich das gefühl, dass die tabelle nicht optimal "designed" ist:

der wertebereich von MEDIUMINT ist max. 0 bis 16777215 (vorzeichenlos).

Das stimmt. Habe auf smallint umgestellt.

brauchst du für die beschreibung tatsächlich 65.535 zeichen ? bei der erstellung der tabelle würde ich ausserdem die max. länge angeben.

Nein. Aber ich glaube, es wird trotzdem kein platz reserviert, wie z.b bei INT

DOUBLE (fliesskommazahl mit E +/- 308 ) ist für preisangaben ungeeignet; besser (und platzsparender) ist DECIMAL

Das ändere ich auch.

wertebereich von SMALLINT liegt vorzeichenlos zwischen 0 und 65535.

Hier ist richtig. Es gibts kataloge mit id 3000. Also tinyint passt nicht.

nun, das sind vielleicht keine grossen fehler; sie deuten allerdings auf ein gewisses missverständnis der verwendeten technik hin. kenntnisse über den einsatz des richtigen datentyps gehören zu den basics eines "anständigen" db-designs...

Danke für hinweise. Es löst mein Problem nicht, aber die Tabelestruktur ist jetzt ordentlicher.

[daudi]

Zitat von cortex

vielleicht ist dein server / hosting-paket schlichtweg überfordert. wenn du so grosse datenmengen speichern und in einem rutsch verarbeiten musst... upgrade ?

cx

Nein, ich habe ein Dedizierter Server mit 4 GB RAM und Dual-Core

[cortex]

also checken wir nochmal die problematischen punkte:

- ressourcen prüfen (hardware / hosting)
- datentypen korrigieren
- SELECT * meiden
- indizies definieren

hoppla: die indizies. du hast zwar welche definiert aber - bitte korrigiert mich, wenn ich daneben liege - dass ein index vorliegt, heisst nicht, dass er auch wirksam ist / von MYSQL verwendet wird.

bsp.: tabelle mit 2 spalten a und b. index auf a.

wenn ich SELECT a, b ausführe, brauche ich auch einen index, der die spalten a und b umfasst (?) stichwort mehrspaltiger index.

ausserdem - aus dem handbuch:

MySQL verwendet Indizes für die folgenden Operationen:

- Zum schnellen Auffinden von Datensätzen, die der WHERE-Klausel entsprechen.

- Zum Ignorieren bestimmter Datensätze. Wenn mehrere Indizes vorhanden sind, verwendet MySQL normalerweise denjenigen, der die kleinste Anzahl von Datensätzen findet.

- Zum Abrufen von Datensätzen aus anderen Tabellen bei der Durchführung von Joins.

- Zum Auffinden des MIN()- oder MAX()-Werts für eine bestimmte indizierte Spalte key_col.

- Zum Sortieren oder Gruppieren einer Tabelle, sofern das Sortieren bzw. Gruppieren auf der Basis des linken Präfixes eines verwendbaren Schlüssels erfolgt (z. B. ORDER BY key_part1, key_part2). Wenn auf alle Schlüsselteile DESC folgt, wird der Schlüssel in umgekehrter Reihenfolge gelesen.

da du lediglich einen schlichtes SELECT aufrufst... ich tippe (immer noch) auf eine unglückliche kombination aus SELECT * und ggfs. unwirksamen indizies.

cx

[Crisps]

wenn ich SELECT a, b ausführe, brauche ich auch einen index, der die spalten a und b umfasst (?) stichwort mehrspaltiger index.

Einen Index braucht man bei:

ORDER BY id DESC/ASC

WHERE id = 123

JOIN ... ON a.id = b.id

Was im SELECT-Bereich geschieht (Mit Ausnahme von MIN() und MAX()), ist in Bezug auf vorhandene Indexe unerheblich. Übrigens Danke für den Auszug aus dem Manual - dass ein Index auch bei MIN/MAX greift, wusste ich noch nicht. In MySQL können Funktionen nämlich nicht auf Indexe zurückgreifen, aber offensichtlich gilt das nicht für MIN/MAX.

Der von dir angesprochene mehrspaltige Index, kommt bei MySQL zum Einsatz, wenn z.B. eine solche Abfrage ausgeführt wird:

SELECT id FROM tb WHERE a = 1 AND b = 2;

Da MySQL nur einen Index pro Query (bzw. pro Query/Tabelle) verwenden kann, sollte man für diese Query einen mehrspaltigen (Composite oder Covered - wie man ihn auch immer nennen will) Index anlegen. Ein Index nur auf "a" würde auch nur einen Index auf dieser Spalte verwenden. Bei der Suche von "2" in "b" würde MySQL wieder alle Reihen von "b" durchsuchen - Deshalb der mehrspaltige Index auf a und b für solche Queries.

Aber um zum eigentlichen Thema zurückzukommen: Ich glaube bei der Abfrage von > 1.000.000 Datensätzen auf einmal, muss man wohl auf jeden Fall mit Performanceeinbußen rechnen. Indexe helfen in diesem Fall nichts, da ja auch schon der PRIMARY KEY in der ORDER BY verwendet wird.

[daudi]

Zitat von Crisps

Der von dir angesprochene mehrspaltige Index, kommt bei MySQL zum Einsatz, wenn z.B. eine solche Abfrage ausgeführt wird:

SELECT id FROM tb WHERE a = 1 AND b = 2;

Da MySQL nur einen Index pro Query (bzw. pro Query/Tabelle) verwenden kann, sollte man für diese Query einen mehrspaltigen (Composite oder Covered - wie man ihn auch immer nennen will) Index anlegen. Ein Index nur auf "a" würde auch nur einen Index auf dieser Spalte verwenden. Bei der Suche von "2" in "b" würde MySQL wieder alle Reihen von "b" durchsuchen - Deshalb der mehrspaltige Index auf a und b für solche Queries.

Ich habe das nicht gewusst. Aber gleich bei mir entsteht die folgende Frage.
Z.b. ich habe spalten: a,b,c,d,e. Die spalte a und c sind enzeln indiziert. In abfragen können verschiedene kombinationen dieser spalten vorkommen, z.B. a=1 and b=3 and c=4 oder a=3 and e=3. Wie in diesem Fall, wenn ich weiss nicht, welche spalten zusammenkommen, diesen mehrspaltige Index zu erstellen. Oder reicht es, mindestens ein Index pro Abfrage?

[daudi]

Zitat von cortex

da du lediglich einen schlichtes SELECT aufrufst... ich tippe (immer noch) auf eine unglückliche kombination aus SELECT *

Ja, stimmt. Ich habe im SELECT * um die Spaltennamen ersetzt und jetzt läuft es schneller.

Vielen Dank

[Crisps]

a,b,c,d,e

Die spalte a und c sind enzeln indiziert.

In abfragen können verschiedene kombinationen dieser spalten vorkommen, z.B. a=1 and b=3 and c=4 oder a=3 and e=3.

Der Index auf a würde dann nur bei diesen Abfragen genutzt:

SELECT irgendwass FROM die_tabelle WHERE a=1;

SELECT irgendwass FROM die_tabelle WHERE a=1 and b=3;

SELECT irgendwass FROM die_tabelle WHERE a=1 and b=3 and c=4;

Zwar würde bei den letzen zwei Abfragen der Index auf "a" genutzt um Einträge mit "1" auf "a" zu finden, aber bei "b=3" und/oder "c=4" würde der Optimizer wieder ohne Index auf den Spalten b und/oder c suchen. Für die zweite Query würde sich deshalb ein Composite Index auf a und b anbieten:

ALTER TABLE die_tabelle ADD INDEX ix_ab (a, b);

Wie sollte dann ein mehrspaltiger Index für die dritte Query aussehen? Genau - so:

ALTER TABLE die_tabelle ADD INDEX ix_abc (a, b, c);

Aber halt, heißt das jetzt, dass man drei verschiedene Indexe für eben diese drei Abfrage braucht? Nein, zum Glück nicht! Denn der MySQL-Optimizer liest solche mehrspaltigen Indexe von Links nach Rechts. Also wird der letztere, der dreispaltige, Index auch bei allen drei Abfragen verwendet.

oder a=3 and e=3

Hier hast du dann die Qual der Wahl. Entweder reicht dir hier der dreispaltige Index aus meinem Beispiel (a=3 wirkt ja auch hier, auch wenn e ignoriert wird), oder du erstellst einen zweiten mehrspaltigen Index:

ALTER TABLE die_tabelle ADD INDEX ix_ae (a, e);

Es kommt halt auch auf die Verschiedenheit der Daten an.

Ich hoffe, meine Erklärung macht Sinn - Frag einfach noch einmal nach wenn ich etwas zu kompliziert erklärt habe. :)

[cortex]

das kapitel wie mySQL indizes benutzt im handbuch:

http://dev.mysql.com/doc/refman/5.1/...l-indexes.html

cx