Performance Frage und Indices

[Norbert Hartl <ruediger_(at)_penguins.de>]

Hallo,

ich bin gerade etwas ratlos bezueglich meiner Datenbank.
Ich habe hier eine Tabelle, die aus 18 Spalten und einer
kompletten Laengen von 212 Bytes besteht.
Die durchschnittliche Anzahl an Datensaetzen ist eine
knappe Million. Ich versuche nun schon seit 2 Tagen
die ganze Sache zu optimieren.
Die Datenbank laeuft auf einem Dual-PII-233 und einem
RAID, das intern mit UW und extern mit Differential 
angebunden ist. Aus Erfahrung weiss ich, dass die I/O-
Performance des Systems sehr gut ist.
Hauptspeicher ist im Moment nur 128 MB, aber das reicht
eigentlich fuer das Noetige.
Und ich benutze im Moment MySQL 3.22.20a (noch dynamisch
gelinkt und mit egcs-1.0.3 kompiliert)

Es gibt hauptsaechlich 5 Spalten, die in der WHERE-Clause
auftauchen koennen. Diese sind kombinierbar, aber es 
werden 3 davon immer benutzt und eben maximial 5. Ab-
gefragt werden bei dem betreffenden SELECT 2 Spalten.
Angnommen diese Spalten heissen col1 bis col5, dann sind
die Kombinationen der WHERE-clause folgende:

Col1 bis col3 sind immer drin, also gibt es 3 Moeglichkeiten.

a.) col1,col2,col3,col4
b.) col1,col2,col3,col5
c.) col1,col2,col3,col4,col5

Als Datentyp tragen die Spalten:

col1 char(3)
col2 date
col3 tinyint
col4 char(3)
col5 decimal(10,2)

Ich habe nun alle Spalten auf NOT NULL gesetzt und folgende
Indices angelegt:

INDEX (col1,col2,col3,col4)
INDEX (col1,col2,col3,col5,col4)

Soweit ich die Dokumentation verstanden habe, wird fuer den
Fall b.) der zweite INDEX benutzt, so dass ich eigentlich
alle Faelle abgedeckt habe.

Was mich erstaunt ist, dass wenn nur ein einzelner Query
ablaeuft, es trotzdem bis zu 5 Minuten dauern kann bis
ein Ergebnis zurueckgeliefert wird. Und selbst wenn die
Rueckgabezeilen 250 nicht ueberschreiten.

Darueberhinaus habe ich per explain gesehen, dass oftmals
fuer den Fall c.) der erste und nicht der zweite INDEX
benutzt wird und das verstehe ich gar nicht.
Darueberhinaus habe ich jetzt den key_buffer auf 16M und
den sort_buffer auf 4M vergroessert, aber die Response-
zeiten sind trotzdem wie oben genannt.
Ausserdem habe ich die Indices per isamchk -r sortiert.
Temporaere Tabellen legt er auf der Platte auch nicht
an.

Folgende Fragen habe ich nun :)

1.) Wie gehe ich am besten ran, um herauszufinden, warum
er welchen Index benutzt. Im Moment ist es fuer mich nicht
ausmachbar, warum er meist den kleinen Index benutzt.

2.) Ich moechte die id als Primary Key von int auf medium-
int umstellen. Was passiert, wenn die ID 'ueberlaeuft'?
Soweit ich das sehe, hat MySQL keine Moegleichkeit untere
leere Bloecke wieder zu verwenden. Da ich jeden Tag ca.
200.000 Datensaetze loesche und einfuege, die nach einem
Datumskriterium geloescht werden, bekomme ich einen quasi
zusammenhaengen Block von ids, der nach oben wandert.

3.) Bringt es mir etwas, weitere Indices anzulegen oder
habe ich eventuell meine falsch angelegt? Mein Blick-
punkt besteht darauf, dass ich eben 200.000 Daten-
saetze pro Tag aendern muss und das mit jedem neuen
Index erlahmt.

4.) Hat jemand Erfahrung, was es wirklich bringt, mehr
Speicher zur Verfuegung zu stellen? Also irgendeine
Daumen- oder Erfahrungsregel was es bringen koennte,
512 MB reinzustecken und die key- und sortbuffer sehr
gross zu machen


Ich hoffe, dass das jetzt nicht unverschaemt viele Fragen
sind :)

Auf viele Antworten freut sich:

Norbert

---
Womit Computer am meisten rechnen ist, dass der Mensch denkt.