MySQL Index wird nicht verwendet?

[mixmastertobsi]

Hallo,

habe eine Frage zum MySQL Index.

Ich habe eine Tabelle mit über 600.000 EInträge und habe auf die Spalte "value" ein Index angelegt.

Meine Abfrage sieht wie folgt aus.

SELECT * FROM auftrag_info as ai14 WHERE ai14.value=100096806

Nun wird aber leider der Index nicht verwendet. Warum nicht?

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	ai14	ALL	value				421286	Using where

[TS]

Hello,

ist der Index noch intakt? Hast Du mal ein Reindex vorgenommen? Welchen Tabellentyp hast Du benutzt?

Liebe Grüße
Tom S.

[mixmastertobsi]

Ich habe den Index auch bereits gelöscht und nochmals neu geschrieben. Tabelle ist eine INNODB und das Value-Feld ein varchar.

[TS]

Hello,

Ich habe den Index auch bereits gelöscht und nochmals neu geschrieben. Tabelle ist eine INNODB und das Value-Feld ein varchar.

SELECT * FROM auftrag_info as ai14 WHERE ai14.value=100096806

Müssen Strings bei MySQL nicht mehr quotiert werden?

Liebe Grüße
Tom S.

[dedlfix]

Tach!

SELECT * FROM auftrag_info as ai14 WHERE ai14.value=100096806

Müssen Strings bei MySQL nicht mehr quotiert werden?

Ja, außer du kannst es als Literal eines anderen Typs schreiben. MySQL wandelt sich das bei Bedarf.

dedlfix.

[Mitleser]

Müssen Strings bei MySQL nicht mehr quotiert werden? Ja, außer du kannst es als Literal eines anderen Typs schreiben. MySQL wandelt sich das bei Bedarf.

Evtl. ist Toms Nachfrage hier dennoch der Treffer. Ich hatte vor einiger Zeit tatsächlich einmal den Fall, dass bei der Wandlung der Index nicht griff.

[dedlfix]

Tach!

Müssen Strings bei MySQL nicht mehr quotiert werden? Ja, außer du kannst es als Literal eines anderen Typs schreiben. MySQL wandelt sich das bei Bedarf.

Evtl. ist Toms Nachfrage hier dennoch der Treffer. Ich hatte vor einiger Zeit tatsächlich einmal den Fall, dass bei der Wandlung der Index nicht griff.

War das ein einfacher impliziter Typecast oder hast du dafür einen Funktionsaufruf verwendet? Bei letzterem ist es verständlich, dass da kein Index genommen werden kann, weil das Ergebnis gegebenenfalls erst zur Laufzeit berechnet werden muss und nicht bereits beim Statement-Parsen umgewandelt werden kann.

dedlfix.

[Mitleser]

Evtl. ist Toms Nachfrage hier dennoch der Treffer. Ich hatte vor einiger Zeit tatsächlich einmal den Fall, dass bei der Wandlung der Index nicht griff. War das ein einfacher impliziter Typecast oder hast du dafür einen Funktionsaufruf verwendet? Bei letzterem ist es verständlich, dass da kein Index genommen werden kann, weil das Ergebnis gegebenenfalls erst zur Laufzeit berechnet werden muss und nicht bereits beim Statement-Parsen umgewandelt werden kann.

Schon klar. Es war tatsächlich ein einfacher, impliziter Typecast!

[dedlfix]

Tach!

Schon klar. Es war tatsächlich ein einfacher, impliziter Typecast!

Das hab ich grad mal versucht nachzuvollziehen, aber sowohl für String- als auch für numerische Werte konnte in beiden Richtungen der Index verwendet werden. Das ist es also anscheinend nicht.

dedlfix.

[Mitleser]

Schon klar. Es war tatsächlich ein einfacher, impliziter Typecast!

Das hab ich grad mal versucht nachzuvollziehen, aber sowohl für String- als auch für numerische Werte konnte in beiden Richtungen der Index verwendet werden. Das ist es also anscheinend nicht.

Das war kein generelles Problem, sondern trat damals erst mit einer neueren Mysql-Version auf, keine Ahnung mehr, welche das war. Womöglich ist das Problem mittlerweile auch wieder behoben.

[dedlfix]

Tach!

Nun wird aber leider der Index nicht verwendet. Warum nicht?

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	ai14	ALL	value				421286	Using where

Wie erklärt wird, sieht MySQL keinen nutzbaren Index. Manchmal hat der Optimizer eine andere Meinung von Nützlichkeit als der Verwender. Wenn du sicher bist, dass er wirklich vorhanden ist, kannst du nur mit einem entsprechenden index_hint-Keyword im Statement die Nutzung des Index vorschlagen oder auch erzwingen. Und messen, ob es eine Änderung gibt. Zudem kann es möglicherweise sein, dass du beim Testen mit wenigen Labordaten eine andere Situation hast, bei der die Indexverwendung keinen Vorteil bringt. Aber dann wäre er eigentlich zumindest bei den possible keys aufgelistet worden.

dedlfix.

[TS]

Hello,

Nun wird aber leider der Index nicht verwendet. Warum nicht?

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	ai14	ALL	value				421286	Using where

Ist es nicht so, dass innoDB die VarChars ohnehin nicht in Satzstruktur, sondern in einer separaten B+Tree-ähnlichen Struktur ablegt, weil die ja alle verschieden lang sein können und man nicht so viel Platz vergeuden will? Wenn sie denn dann aber ohnehin schon als B+Tree abgelegt werden, benötigt man keinen zusätzlichen Index mehr.

Darum fragte ich nach dem Tabellentyp.

Liebe Grüße
Tom S.

[dedlfix]

Tach!

Ist es nicht so, dass innoDB die VarChars ohnehin nicht in Satzstruktur, sondern in einer separaten B+Tree-ähnlichen Struktur ablegt, weil die ja alle verschieden lang sein können und man nicht so viel Platz vergeuden will? Wenn sie denn dann aber ohnehin schon als B+Tree abgelegt werden, benötigt man keinen zusätzlichen Index mehr.

Ich habe mit InnoDB getestet und mir wurden possible_keys angezeigt.

dedlfix.

[Rolf b]

Stimmt, InnoDB verlangt einen Primary Key und erzeugt daraus den Clustered Index. Wenn man ihm keinen PK gibt, denkt es sich selbst einen aus (Row-ID).

"Value" klingt nicht so, als wäre das eine Spalte mit eindeutigen Werten, der Index ist demzufolge vermutlich non-unique. Wenn dann noch der Typecast dazu kommt, könnte es sein, dass der Optimizer die virtuellen Schultern zuckt und lieber die Tabelle traversiert.

Rolf

[TS]

Hello,

beim VarChar kommt ja auch noch die Collation/Transcription(Upper/Lower) hinzu. Es ist also kein einfacher ordinaler Typ mehr. Wenn da also nur "Nummern" drinstehen sollen, bremst VarChar die Engine viel zu sehr aus.

Man könnte die Spalte nun noch als Binary kennzeichnen, damit zumindes die Transscription enfällt. Für eine vernünftige Aussage müssten wir aber vermutlich mehr über mixmastertobsis Datenmodell wissen.

Liebe Grüße
Tom S.

[TS]

Hello,

Ich habe eine Tabelle mit über 600.000 EInträge und habe auf die Spalte "value" ein Index angelegt.

Wie lange dauert denn der Zugriff auf einen speziellen Datensatz in etwa?

Liebe Grüße
Tom S.