Index für Tabellen setzen

[TriphunEM]

Hallo, wozu Indizes da sind und was sie bewirken,m weiß ich alles!

Jetzt hab ich aber dennoch eine Frage zu der Erstellung von Indizes mit mehreren Feldnamen! Wann machen die sind!

Wenn ich zum Beispiel eine Abfrage mit

WHERE userid=1 und password='bla'

muss ich da in der Tabelle einen Index haben, der die beiden Felder "userid" und "password" hat, oder reicht jeweils ein Index für "userid" und einen für "password"?

Angenommen ich will auch noch schauen ob der User aktiv ist

WHERE userid=1 und active=1

muss ich dann zusätzlich zu dem Index mit der "userid" und dem "password", nochmal ein index mit "userid" und "active" setzen?

Seh ich das so richtig?

Danke

[wahsaga]

Für Indices gilt (wie für so vieles im Leben) - so viele wie nötig, so wenige wie möglich.

Einfach auf alles einen Index zu setzen, führt zu massiv erhöhtem Speicherbedarf und kostet bei Daten verändernden Operationen Performance.

Wenn ich zum Beispiel eine Abfrage mit

WHERE userid=1 und password='bla'

muss ich da in der Tabelle einen Index haben, der die beiden Felder "userid" und "password" hat, oder reicht jeweils ein Index für "userid" und einen für "password"?

Wieso du User-ID und Passwort abgleichst, erschliesst sich mir nicht so ganz.
Username und Passwort sind doch normalerweise das, was man bei einem Login abprüft.
Und auf dem Usernamen wirst du in so einem Fall sowieso schon einen Unique Index haben, denn doppelte Vergabe von Usernamen willst du ja von vornherein ausschliessen.
Auf das Passwort auch noch einen Index zu setzen, dürfte aber im Normalfall hier kaum sinnvoll sein.

Angenommen ich will auch noch schauen ob der User aktiv ist

WHERE userid=1 und active=1

muss ich dann zusätzlich zu dem Index mit der "userid" und dem "password", nochmal ein index mit "userid" und "active" setzen?

Nein, das wäre ein Fall von zu viel hält nicht besser, sondern macht nur unnötig langsam.

Die User-ID ist das Kriterium, mit dem du auf die Datensätze zugreifst - also sollte darauf ein Index liegen.
Ob sich noch weitere Spalten zum indizieren anbieten kommt darauf an, wie oft diese für eine Suche oder Sortierung herangezogen werden.


EXPLAIN vor eine Abfrage gesetzt erklärt dir, welche Indices MySQL für die Abfrage nutzt, und lässt auch erkennen, welche ggf. noch fehlen.
Also mach das erst mal für deine konkreten Queries.

[TriphunEM]

naja, das hier oben ist nur ein beispiel. wende ich so in der praxis nicht an.

mich interessiert eher genau, wann man mehrere tabellenfelder in einer index packt? wann das sind macht? und ob es nötig ist, wenn eh ein feld schon ein index hat!

[AmicaNoctis]

Hallo,

Mehrfeldschlüssel machen eigentlich nur in zwei Situationen Sinn.

Ein Unique Index über mehrere Spalten stellt die Integrität sicher. Wenn es in einer Tabelle einen Fremdschlüssel auf die Gruppe (group_fkey) und einen Namen gibt, stellt

UNIQUE INDEX (name, group_fkey)

sicher, dass innerhalb derselben Gruppe alle Namen eindeutig sind.

Wenn du eine Tabelle fast immer mit zwei Spalten filterst, kann ein Mehrfeldschlüssel sinnvoll sein, der genau diese beiden Spalten enthält. Wenn z. B. in einer Tabelle PLZ und Ortsname gespeichert sind und du fast immer beides gegeben hast, lohnt sich ein

INDEX (zipcode, name(3))

Gruß,

Amica

[TriphunEM]

ok danke!

[forestsoft_de]

Hallo

vieleicht noch als Ergänzung:

Mit der Query: Explain SELECT... kann man sich anzeigen lassen wie die Datenbank die gewünschten Informationen raussucht.

EXPLAIN SELECT * FROM user WHERE user='root' AND host='localhost';
+----+-------------+-------+-------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------------+------+-------+
| 1 | SIMPLE | user | const | PRIMARY | PRIMARY | 228 | const,const | 1 | |
+----+-------------+-------+-------+---------------+---------+---------+-------------+------+-------+
1 row in set (0.00 sec)

Die Abfrage in der Tabelle mysql.user durchsucht genau einen Datensatz (Spalte rows) weil per Definition ein Index auf host und user gesetzt ist.

Wenn man nun nur nach dem Benutzernamen sucht ist rows = Anzahl der Datensätze = Anzahl aller User

EXPLAIN SELECT * FROM user WHERE user='root';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 53 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Mit Hilfe der Explainanweisung kann man also schauen ob man auf die richtigen Spalten einen Index gesetzt hat. Je kleiner der Wert unter rows, je grösser die Geschwindigkeit.

Gruss
Sebastian