Index nachträglich erstellen und Index prüfen

[dieter99]

Hallo,
ich möchte auf eine Tabellenspalte, die bereits Datensätze enthält, nachträglich einen Index erstellen.
Lt. mysql-Handbuch muss ich folgenden Code anwenden:

CREATE INDEX indexName ON tabellenname (tabellenspalte); 

In mysqldumper sehe ich zwar, dass ein Index angelegt wurde. Doch ich sehe nicht, ob die betroffenen Datensätze tatsächlich indiziert wurden.
Wie kann ich eigentlich prüfen, ob bei einer SELECT-Anweisung der Index überhaupt greift?
Des weiteren weiss ich nicht wie ich vorgehen muss, wenn ich mehrere Spalten (tabellenübergreifend) indizieren will.
Beispiel:

tabelle1:
Spalte "firma_id"
Spalte "firma_name"
Spalte "firma_name_zusatz"

tabelle2:
Spalte "auftrag_id"
Spalte "datum"
Spalte "betreff"
Spalte "firma_id"

Ich möchte nun folgende Werte ausgeben lassen: betreff, firma_name, firma_name_zusatz

SELECT betreff, firma_name, firma_name_zusatz FROM tabelle2 LEFT JOINT tabelle1 ON tabelle2.firma_id= tabelle1.firma_id 

Welche Spalten sollte ich nun mit einem Index versehen?
Ist es nur die Spalte "firma_id" in der tabelle2?

[maxi89]

Mit

SHOW INDEX FROM `Tabelle`;

kannst du dir ausgeben lassen, auf welchen Tabellenfeldern ein Index liegt.
Wenn du wissen willst, wie groß die Indizes sind, kannst du mit

SHOW TABLE STATUS FROM `Datenbankname`;

die Datenmengen abfragen.

Grundsätzlich ist ein Index in folgenden Fällen sinnvoll:
Auf alle Tabellenfeldern, die regelmäßig im "WHERE" oder "JOIN" (nicht JOINT ) stehen. Also in deinem Falle nur tabelle1.firma_id.
Ein Index auf Feldern, die nicht zum Suchkriterium gehören, bremst eher, als dass er was beschleunigt
Als zweites sollte der Index generell nie größer werden als die eigentlichen Daten der Tabelle, deshalb auch die SQL-Anweisungen um die Indexgrößen zu ermitteln.
Wenn der Index nämlich größer ist, als die Nutzdatenmenge selbst, ist er eher kontraproduktiv

[dieter99]

Noch eine Verständnisfrage:

Nehmen wir an es existieren in einer Tabelle folgende Spalten:
Betreff
Nachricht
Unix-Zeitstempel
Status
Jahr

In der Spalte "Status" kommt nur der Wert "1" oder "0" vor.
In der Spalte "Jahr" gibt es nur die Werte "2008", "2009", "2010".
In den anderen Spalten (Betreff, Nachricht und Unix-Zeitstempel) sind die Werte immer anders.

Angeblich macht es nur Sinn diejenigen Tabellenspalten zu indizieren, die viele verschiedene Werte beinhalten.
In meinem Fall kommen nur die Spalten Betreff, Nachricht und Unix-Zeitstempel in Frage. Da in meiner sql-Anweisung in der WHERE-Klausel nur "Unix-Zeitstempel" vorkommt, indiziere ich nur die Spalte "Unix-Zeitstempel".

Die Abfrage heißt nun:
SELECT * FROM tabelleTest WHERE Status=1 AND Jahr=2008 AND Unix-Zeitstempel<453534534534.

Meine Frage lautet nun: kann mySQL die Abfrage schneller abarbeiten?

Auf "Unix-Zeitstempel" wurde zwar ein Index gesetzt, doch NICHT auf die Spalten "Status" und "Jahr". Um zu prüfen welche Datenbankeinträge den Status "1" und das Jahr "2008" beinhalten, muss mySQL trotz Index (auf Unix-Zeitstempel) ALLE Datensätze untersuchen. Liege ich richtig?

[Moritz]

Liege ich richtig?

Eine relativ genaue Antwort auf diese Frage liefert dir

EXPLAIN SELECT * FROM tabelleTest WHERE Status=1 AND Jahr=2008 AND Unix-Zeitstempel<453534534534

Du kannst bei MySQL übrigens auch Indizes über mehrere Spalten anlegen. AFAIK benutzt MySQL allerdings immer nur einen Index pro Abfrage - hier bin ich aber nicht sicher und lasse mich gerne korrigieren.

[dieter99]

Wenn ich das sql-Statement mit EXPLAIN ausführe, dann erhalte ich folgendes Ergebnis:

table: a (für tabelleTest)
type: ref (ich weiss nicht was das bedeutet)
possible_keys: meinIndex
key: meinIndex
key_len: 5 (Bedeutung?)

Possible Key heißt "Index vorhanden" und Key bedeutet, das der Index auch tatsächlich benutzt wird. Liege ich da richtig?

[Moritz]

Hallo!

Ja, diese Schlussfolgerung ist soweit korrekt. Details über die einzelnen Felder finden sich in der MySQL-Doku: http://dev.mysql.com/doc/refman/5.5/...in-output.html

Solltest Du dein Wissen zum Thema Performance-Optimierung für MySQL noch vertiefen wollen, kann ich dir folgendes Buch empfehlen: http://www.amazon.de/Performance-Opt...7036351&sr=8-1