[MySQL] Index wird nicht benutzt, wenn bestimmte Spalten im Ergebnis vorkommen

[CPoly]

Hallo,

Ich habe schon länger das Problem, dass eine meiner Abfragen etwa eine viertel Sekunde dauert (250ms). Bisher habe ich das einfach hingenommen, weil ich dachte das wäre normal, wenn man über 4 Tabellen joint. Aber heute wollte ich mich dann einfach mal dransetzen und das Problem beheben. Ich habe eine Tabelle mit mehreren zehntausend Einträgen, welche jeweils IDs enthält, um Daten aus drei anderen Tabellen(jeweils nur ein paar hundert Einträge) zu hohlen.
Um jetzt mal zum Punkt zu kommen. Ich habe die Joins entfernt und nur noch die Daten aus der großen Tabelle geholt. Ich war erstaunt, als die Abfrage immer noch 250ms gedauert hat. Nach ein bisschen try-and-error konnte ich das Problem eingrenzen, kann es mir aber nicht erklären. Wenn eine der Spalten im SELECT vorkommt, dauert es 250ms, wenn ich die Spalte rausnehme nur noch 30ms.
Hier nochmal veranschaulicht

SELECT a FROM tabelle WHERE c=4 ORDER BY d #250ms
SELECT b FROM tabelle WHERE c=4 ORDER BY d #30ms

Die Spalte(a) ist ein INT(10) UNSIGNED.
Wenn ich im ersten statement wahlweise das WHERE oder das ORDER BY entferne, bin ich wieder bei bei 30ms.
Auf a liegt kein Index
b hingegen ist Teil eines Index über mehrere Spalten(mit unter anderem c und d).

EXPLAIN SELECT sagt mir, dass es so langsam ist, weil kein INDEX benutzt wird. Aber wieso nutzt MySQL den INDEX nicht, wenn im SELECT irgendeine Spalte vorkommt, die nicht Teil des INDEX ist? Ein SELECT * dauer übrigens 670ms (Für die Ausführung! Der Netwerk-Transfer wurde abgezogen)

Entweder handelt es sich hierbei um einen Bug, oder ich bin auf etwas gestoßen, dass für mich einfach neu ist. Ich bin eben kein Datenbankspezialist.

Schonmal Danke im Vorraus.

 

[Stefan Reimers]

Hallo,

ich gehe deinen Erläuterungen nach davon aus, dass dein Index in der Reihenfolge b,c,d angelegt ist.
Erklärbar wäre das Verhalten beispielsweise damit, dass MySQL Composite Indizes (mehrspaltige) nur auswerten kann, solange die Spalten des Index der Reihe nach abgefragt werden. D.h. im obigen Fall nützt der Index bei der Abfrage auf c und d nichts, solange b nicht auch abgefragt wird. Um das gewünschte Verhalten auch in der ersten Abfrage zu erhalten, gibt es zwei Möglichkeiten. Zum einen - und das ist die Lehrbuchmethode - ändert man den Index in die Reihenfolge c,d,b. Es kann jedoch Gründe geben, die dagegen sprechen, z.B. Tuning für andere Anfragen auf dieselbe Tabelle, die genau die Indexreihenfolge benötigen. In dem Fall könnte man alternativ versuchen, MySQL zu "überreden", den Index zu verwenden, indem man b mit einer fadenscheinigen Bedingung mit ins WHERE nimmt, z.B.

SELECT a FROM tabelle WHERE c=4 and b>0 ORDER BY d
(wenn in b sowieso nur Zahlen größer 0 liegen)

Viele Grüße
Stefan
http://www.stefan-reimers.de

 

[gorefest]

Hi

wenn Du

SELECT a FROM tabelle WHERE c=4 ORDER BY d

ausführst, brauchst Du mindestens einen Index auf c, um über den Index einzuschränken. Da der Optimizer bei MySQL recht begriffsstutzig ist, kannst Du durch ein

SELECT a FROM tabelle USE INDEX (dein_index) WHERE c=4 ORDER BY d

wobei dein_index über c,d gehen muss

Das sollte es ein wenig beschleunigen. Wichtig : Gegenläufige indizes (c ASC, d DESC) funktionieren in MySQL nicht!

Grüße
gore

 

[CPoly]

Vielen Dank für eure beiden Antworten.
Leider konnte ich immer noch nicht die gewünschte Performance erreichen. Ich habe zwar inzwischen verstanden, dass MySQL die Indizes nur unter bestimmten Umständen nutzen kann (ich wusste nicht, dass die Reihenfolge eine Rolle spielt) aber in meinem Fall kann ich diese Umstände nicht herbeiführen, weil ich habe einen Index über fünf Spalten, von denen kommen manche im WHERE vor, andere im GROUP BY und wieder anderen im ORDER BY. Ob die Spalten vorkommen hängt von Nutzereingaben ab, aber es können auch alle fünf auf einmal vorkommen.
Ich hatte sowieso geplant irgendwann auf besagter Tabelle ein paar Schema-Änderungen zu machen und ein Teil der Spalten in eine zweite Tabelle auszulagern. Dann muss ich auf der Tabelle nur noch nach zwei Spalten gruppieren (anstelle von vier) und habe anstelle eines WHERE über fünf Spalten nur noch zwei Spalten plus eine IN() Bedingung.

Trotz allem habe ich noch nicht verstanden, wieso die Spalten im Ergebnis eine Rolle spielen. Ich dachte der Index wird verwendet, um bestimmte Reihen schneller zu finden, aber es spielt doch dann keine Rollte, welche Spalten der Reihe ich gerne im Ergebnis möchte.

Mein Beispiel aus dem Anfangspost nochmal verdeutlicht
Index über (a,b,c,d,e)
Und f kann eine beliebige andere Spalte sein
Eine USE INDEX im zweiten Fall scheint MySQL nicht zu beeindrucken.

#schnell:
SELECT a,b,c,d,e FROM tabelle WHERE a=1 AND b=2 AND c=3 GROUP BY a,b,c,d

#langsam:
SELECT a,b,c,d,e,f FROM tabelle WHERE a=1 AND b=2 AND c=3 GROUP BY a,b,c,d

Das möchte ich noch gerne verstehen.

Danke schonmal!

 

[gorefest]

#langsam:
SELECT a,b,c,d,e,f FROM tabelle WHERE a=1 AND b=2 AND c=3 GROUP BY a,b,c,d

ist syntaktisch auch vollkommen daneben. alles,was nicht im GROUP BY steht, muss einen Aggregationsoperator bekommen (MIN,MAX,AVG). Das MySQL sowas zulässt ist mir eh schleierhaft, aber gesehen habe ich das auch schon mal.

Wenn das Resultat Deiner WHERE Clause nicht allzu riesig ist versuchs mal mit

#langsam:
SELECT a,b,c,d
-> avg,min,max,whatever(e)
-> avg,min,max,whatever(f)
FROM (
 SELECT idColumm
    FROM tabelle 
   WHERE a=1 
    AND b=2 
    AND c=3 
) X
WHERE idColumn = X.idColumn
GROUP BY a,b,c,d

alternativ solltes du mal beim ersten statement einen aggregationsoperator verwenden.

grüße
gore

 

[CPoly]

Danke für dein Antwort. Ich werde mich morgen ausgiebig dem Problem witmen.

ist syntaktisch auch vollkommen daneben. alles,was nicht im GROUP BY steht, muss einen Aggregationsoperator bekommen (MIN,MAX,AVG). Das MySQL sowas zulässt ist mir eh schleierhaft, aber gesehen habe ich das auch schon mal.

In manchen fällen will ich aber weder MIN noch MAX, sondern einfach >irgendeinen< der Werte, weil es mir egal ist (es handelt sich dabei um das Vorschaubild eines Fotoalbums). Und ich meine mich zu erinnern, dass gestern das Entfernen von MAX() auch die Performance verbessert hat. Das kann aber damit zusammenhängen, dass für MAX() der besagte index, wegen besagtem Problem, nicht verwerdet wurde.

 

[gorefest]

Hi,

ein GROUP BY erzeugt ein Subset über alle nicht-gruppierten Werte.

z.B.

A       B
1       X       
1       Y
1       Z
2       X
2       Y
3       Z

würde wärend des GROUP BY folgende Subsets bilden

1       -> {X,Y,Z}
2       -> {X,Y}
3       -> {Z}

auf diese Subset wird dann ein Aggregationsoperator angewendet, z.b. MAX

1       Z
2       Y
3       Z

ich will damit sagen, daß ein weglassen des Aggregationsoperator ein syntaktischer Fehler ist und afaik ausschließlich von MySQL unterstützt wird (Oracle beispielsweise haut einem das direkt um die Ohren).
Wenn das MAX die Abfrage verlangsamt hat, dann wurde per se schon ein GROUP BY ohne Index gemacht.

Kannst Du mal das EXPLAIN dafür posten?

Mal ne blöde Frage,

bei Deinem zweiten Statement handelt es sich nicht rein zufällig um eine BLOB Spalte, oder?

Grüße
gore

 

[CPoly]

Danke für den Hinweis mit dem group by. Bisher habe ich auch (fast) immer eine Aggregationsfunktion benutzt, aber während der Fehlersuche hatte ich es testweise entfernt.

Es handelt sich bei keiner der Spalten um BLOB-Werte, sondern es sind fast alles INT in verschiedenen größen und date/dateime Spalten.

Ich konnte das Problem inzwischen so gut wie lösen. Mit deinem Vorschlag, ein Join über ein Subselect zu machen, konnte ich das Query beschleunigen, weil ich im Subselect gezielt den Index auslösen konnte. Das fertige Query, so wie ich es ganz ursprünglich benutzt habe, mit den joins über 3 weitere Tabellen, rennt jetzt zwischen 20 und 30ms.

ABER: Ich habe ja erwähnt, dass im where nicht immer alle Spalten auch benutzt werden sollen, abhängig von Nutzereingaben. Dein Vorschlag bei U-INT-Werten einfach "WHERE spalte>0" zu nehmen, um den Index auszulösen, klingt logisch, aber funktioniert nicht ausnahmslos.
Hier mal Beispiele mit dem entsprechenden explain:

#Alle Kriterien haben einen Wert: 10ms
WHERE a=1 AND b=2 AND c=32 AND d=5 AND e='2010-01-16'
#"id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra"
#"1";"SIMPLE";"tabelle";"ref";"METAINFO";"METAINFO";"16";"const,const,const,const,const";"1";""


#c und d sollen ignoriert werden: 10ms
WHERE a=1 AND b=2 AND c>0 AND d>0 AND e='2010-01-16'
#"id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra"
#"1";"SIMPLE";"tabelle";"range";"METAINFO";"METAINFO";"9";NULL;"251";"Using where"


#b soll ignoriert werden: 200ms
WHERE a=1 AND b>0 AND c=32 AND d=5 AND e='2010-01-16'
#"id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra"
#"1";"SIMPLE";"tabelle";"ref";"METAINFO";"METAINFO";"1";"const";"18805";"Using where"

Die entsprechenden Spalten einfach wegzulassen ist genauso langsam.

a: TINYINT
b,c,d: INT
e: DATE

Danke für deine Hilfe!

 

[CPoly]

Kann mir vielleicht noch jemand weiter helfen? Kam seit meinem letzten Post nicht wirklich weiter.
Nach dem ich heute an anderer Stelle ein Query und damit den Seitenaufbau von 750ms auf 50ms drücken könnte, will ich hier natürlich auch Erfolg haben

 

[gorefest]

Aus dem Stehgreif würde ich mal vermuten, dass für c) kein passender Index existiert.

Wie viele Rückgabewerte erwartest Du?

Nachtrag:

es werden immer a und e berücksichtigt beim Einschränken. Versuch mal Deinen Index über a und c zu legen und evtl mit USE INDEX bei der Benutzung nachzuhelfen.