Welchen Index verwenden

[Kukulkan]

Hallo,

ich habe eine einfache Frage, deren Antwort aber wohl doch nicht so einfach ist. Hier das bestehende Tabellenschema:

Nutzdatenzeugs
ZuordnungsID
Datum

Jetzt gibt es auf diese Tabelle viele Abfragen im Stil von
SELECT * FROM Tabelle WHERE ZuordnungsID = 5 AND Datum > '2011-01-02 22:34:56'

In ZuordnungsID gibt es nur wenig Variation (zB 30 verschiedene Nummern). Es gibt aber viele 100'000 Einträge in der Tabelle.

Wie sollte jetzt der Index am besten angelegt werden? Lege ich besser zwei Indexe auf die beiden Spalten an oder besser einen kombinierten Index auf die beiden? Spielt die Reihenfolge beim Anlegen eigentlich eine Rolle?

ALTER TABLE `Tabelle` ADD INDEX `idx_DateID`(`Datum`, `ZuordnungsID`);
oder
ALTER TABLE `Tabelle` ADD INDEX `idx_DateID`(`ZuordnungsID`, `Datum`);

Klappt das auch, wenn ich (wie im Beispiel-Select oben) nicht ein festes Datum suche sondern zB alle, die neuer als ein bestimmtes Datum sind?

Ist da ein Unterschied? Hängt das auch an der angegebenen Reihenfolge im SELECT?

Ein kleiner DESC Test in MySQL brachte, dass bei zwei separaten Indexen wohl nur einer verwendet wird (beide waren möglich, aber nur der Datumsindex mit 4 Byte wurde verwendet). Wenn ich einen kombinierten Index verwende, scheint dieser auch direkt verwendet zu werden (8 Byte). Ich trau der Sache aber nicht...

Kann mir jemand auf die Sprünge helfen?

Kukulkan

[thomas_w]

Zitat von Kukulkan

Jetzt gibt es auf diese Tabelle viele Abfragen im Stil von
SELECT * FROM Tabelle WHERE ZuordnungsID = 5 AND Datum > '2011-01-02 22:34:56'

Spielt die Reihenfolge beim Anlegen eigentlich eine Rolle?

a) ALTER TABLE `Tabelle` ADD INDEX `idx_DateID`(`Datum`, `ZuordnungsID`);
oder
b) ALTER TABLE `Tabelle` ADD INDEX `idx_DateID`(`ZuordnungsID`, `Datum`);

Die Reihenfolge der Spalten spielt eine wichtige Rolle. Wenn die Eindeutigkeit (Kardinalität oder Selektivität) der Werte bei der Spalte ZuordnungsID gering ist ( ca. 30 verschiedene Werte wie Du sagst), dann ist der Index A) der bessere für diese Abfrage, aber auch der Index B) kann sinnvoll sein.

Ob und wie MySQL einen Index verwendet, läßt sich mit EXPLAIN ermitteln.

Beispiel EXPLAIN..

mysql> EXPLAIN SELECT * FROM kunde;
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
|  1 | SIMPLE      | kunde | system | NULL          | NULL | NULL    | NULL | 0    | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
1 row in set (0.19 sec)

mysql>

Bitte sauber formatieren, sonst kann man ihn nicht richtig lesen.

Grüße
Thomas

[Kukulkan]

Hallo,

Danke für die Antwort. Das mit dem EXPLAIN hab ich schon gemacht (hab versehentlich DESC geschrieben). Es scheint so, als würde der Index verwendet.

Wie ist das eigentlich, wenn der SELECT so lautet und ZuordnungsID ein VARCHAR ist?

SELECT * FROM Tabelle WHERE Datum > '2011-01-02 22:34:56' AND ZuordnungsID LIKE 'DE%'

Wird der mit

ALTER TABLE `Tabelle` ADD INDEX `idx_DateID`(`Datum`, `ZuordnungsID`);

angelegte Index auch für die ZuordnungsID verwendet? Ich hab da zweifel, weil ja bereits das erste Kriterium (Datum) nicht ein fester Vergleich ist sondern ein > (grösser als).

Sollte der Index dann besser andersrum angelegt werden (zuerst ZuordnungsID)? Leider trifft das aber nur auf einen kleinen Bruchteil aller mit DE beginnenden Datensätze zu (Datum ist definitiv wichtiger).

Kukulkan

[thomas_w]

Zitat von Kukulkan

Wie ist das eigentlich, wenn der SELECT so lautet und ZuordnungsID ein VARCHAR ist?

SELECT * FROM Tabelle WHERE Datum > '2011-01-02 22:34:56' AND ZuordnungsID LIKE 'DE%'

Wird der mit

ALTER TABLE `Tabelle` ADD INDEX `idx_DateID`(`Datum`, `ZuordnungsID`);

angelegte Index auch für die ZuordnungsID verwendet? Ich hab da zweifel, weil ja bereits das erste Kriterium (Datum) nicht ein fester Vergleich ist sondern ein > (grösser als).

Sollte der Index dann besser andersrum angelegt werden (zuerst ZuordnungsID)? Leider trifft das aber nur auf einen kleinen Bruchteil aller mit DE beginnenden Datensätze zu (Datum ist definitiv wichtiger).

Kukulkan

Am einfachsten ist es, wenn Du beide Indices (mit verschiedenen Namen) anlegst und den EXPLAIN mit den genannten Varianten ausführt. Dann wird klar, ob und wie wlecher Index verwendet wird. Mach einfach eine kleine Testserie.

Bei

SELECT * FROM Tabelle WHERE Datum > '2011-01-02 22:34:56' AND ZuordnungsID LIKE 'DE%'

In diesem Fall kann auch für ZuordnungsID (VARCHAR) ein Index verwendet werden. Allerdings sind INT besser für Indices geeignet als VARCHAR, da VARCHAR zumeist mehr Platz benötigen.

Grüße
Thomas

[Kukulkan]

Ok, vielen Dank. Das hat mir gut weiter geholfen.

Grüße,

Kukulkan