Richtige Verwendung von Indizes

[Saftschubser]

Hallo zusammen,

ich benötige eine kurze Information, wie ich einen Index richtig anlege.

Inh meiner Datenbank x liegen in Tabelle y ca. 8 Mio. Datensätze bei etwa 10 Feldern.

Regelmäßig suche ich nach Datensätzen der Felder a,b,c,d

-> select feld, feld, feld where
a='xyz' and
b='xyz' and
c='xyz' and
d='xyz'

Ich habe daher über die Felder a,b,c,d einen gemeinsamen Index angelegt.

Nun suche ich in selbiger Tabelle auch mal nur im Feld d.

d ist aber schon im Index a,b,c,d indiziert.

Muss ich nun für den Fall der Suche über d einen neuen Index anlegen, der nur über d reicht oder ist MySQL so klug, den Index a,b,c,d zu verwenden?

Was wäre ratsam? Was ist performanter?

Danke fürs Lesen ;-)

 

[remote_mind]

Aus
http://dev.mysql.com/doc/refman/5.1/de/multiple-column-indexes.html

MySQL verwendet mehrspaltige Indizes derart, dass Abfragen schnell sind, wenn Sie einen bekannten Teil der ersten Spalte des Indexes in einer WHERE-Klausel angeben – und zwar auch dann, wenn Sie keine Werte für andere Spalten angeben.

In deinem Fall kann MySQL also den Index (a,b,c,d) auch dann verwenden, wenn du nur nach a suchst.

Du könntest also deinen Index umändern (d,a,b,c) oder (d,c,b,a) um deine Such nach d zu verbessern.

Mit EXPLAIN kannst Du dir von MySQL anzeigen lassen, ob der Index auch verwendet wird.

schöne Grüße,
Nils

 

[Saftschubser]

MySQL verwendet mehrspaltige Indizes derart, dass Abfragen schnell sind, wenn Sie einen bekannten Teil der ersten Spalte des Indexes in einer WHERE-Klausel angeben – und zwar auch dann, wenn Sie keine Werte für andere Spalten angeben.

Nur, damit ich das richtig verstehe:

als erste Spalte im Index verwende ich den Wert, der bei der Abfrage "bekannt" ist? Denn in meinem Falle sind ja a,b,c,d bekannt

Wonach würde ich denn hier die Priorität festlegen, welches Feld das erste nach dem Wort WHERE verwende? Ich handhabe das so, dass ich nach a suche, denn a stellt den kleinsten Wertebereich da.

Ist demnach die Reihenfolge im Index (also ob a,b,c,d oder b,a,c,d) von Bedeutung, wenn ja, dann würde das natürlich bedeuten, dass ich meine Indizes bisher scheinbar ohne Sinn und Verstand gestaltet habe.

Explain verwende ich immer, um zu prüfen, ob der Index auch verwendet wird.

Aber in Sachen Effizienz bin nich kein Fachmann, wenn ich aus 5 Mio Datensätze nach 4 Feldern suche und das Result 111 Datensätze habe dauert die Query immernoch 0,23 Sekunden, und ich meine mal gelesen zu haben, dass das erheblich schneller geht.

Viele Grüße
Boris

 

[remote_mind]

Meine Antwort bezog sich auf diese Frage:

Nun suche ich in selbiger Tabelle auch mal nur im Feld d.

Für Abfragen ala

'SELECT ... FROM ... WHERE d='foo';

gibt es zwei Möglichkeiten

a) einen Index über d anlegen.
b) deinen Index über a,b,c,d so umgestalten, dass er für obige (Suche nur nach d) auch genutzt werden kann.

D.h. will man einen zusammengesetzten Index auch für Abfragen verwenden, bei denen nur ein Feld in der WHERE-Clause auftaucht ist die Reihenfolge des Indexes relevant.