Specified key was too long; max key length is 1000 byte

[hawkmaster]

Hallo zusammen,
Ich möchte gerne in einer MySQL Tabelle einen Unique Index über 3 Spalten setzen.
Dabei kommt aber diese Meldung

1071 Specified key was too long; max key length is 1000 byte

Die SPalten sind:
TypeID (Int)
OpenB (varchar 255) Kollation utf8_general_ci
OpenValue (varchar 100) Kollation utf8_general_ci

Ich habe etwas gelesen das es an der Kollation mit UTF8 liegen kann.
Ich kann aber auch nicht die varchar Länge kürzer machen.
Bedeutet dies, man kann dies also nicht machen mit 3 SPalten?

vielen Dank und viele Grüße
hawk

[dedlfix]

Hi!

1071 Specified key was too long; max key length is 1000 byte

Und zu der Meldung hast du über eine Suchmaschine nichts gefunden?

Die SPalten sind:
TypeID (Int)
OpenB (varchar 255) Kollation utf8_general_ci
OpenValue (varchar 100) Kollation utf8_general_ci

Int      = 4
255 * 3  = 765
100 * 3  = 300
zusammen = 1069

Ich habe etwas gelesen das es an der Kollation mit UTF8 liegen kann.

Ja, da werden für den Index immer 3 Byte pro Zeichen reserviert.

Ich kann aber auch nicht die varchar Länge kürzer machen.

Du könntest den Index kürzer machen. Bei einem CREATE INDEX kann nach dem Spaltennamen in Klammern eine Längenangabe folgen.

Bedeutet dies, man kann dies also nicht machen mit 3 SPalten?

Doch, nur eben nicht so lang.

Lo!

[hawkmaster]

Hallo dedlfix,
Danke dir für deine Erklärung.

Int      = 4
255 * 3  = 765
100 * 3  = 300
zusammen = 1069
Ja, da werden für den Index immer 3 Byte pro Zeichen reserviert.

OK, dann ist es klar.

Du könntest den Index kürzer machen. Bei einem CREATE INDEX kann nach dem Spaltennamen in Klammern eine Längenangabe folgen.

OK, ich habe nun die Varchar beide auf 100 gesetzt (das reicht)
dann klappt auch der Index.

  
ALTER TABLE `option` ADD UNIQUE (  
`TypeID` ,  
`Open` ,  
`OpenValue`  
);  

Aber angenommen ich brauche volle 255 für eine Spalte oder sogar für beide.
Dann könnte man den Prefix so kürzen?

ALTER TABLE option ADD UNIQUE (
TypeID ,
Open(100) ,
OpenValue(100)
);

Dann könnte man trotzdem einen String mit 255 Zeichen einfügen?

Würde dann der Index aber nur 100 Zeichen berücksichtigen?
Ich frage deshalb weil ich einen Insert IGNORE überlege der alle 3 Spalten berücksichtigt. Dann wäre ja wichtig das alle Zeichen überprüft werden.

vielen Dank und viele Grüße
hawk

[dedlfix]

Hi!

Aber angenommen ich brauche volle 255 für eine Spalte oder sogar für beide.
Dann könnte man den Prefix so kürzen?
ALTER TABLE option ADD UNIQUE ( [...]

Prefix? Meinst du Index?
Den Index so zu kürzen ginge, aber dann leidet auch dein Unique darunter, denn das wird ja nun nur noch auf den String-Anfang kontrolliert.

Dann könnte man trotzdem einen String mit 255 Zeichen einfügen?
Würde dann der Index aber nur 100 Zeichen berücksichtigen?

Ja und ja. Du kannst solche Anworten auch durch Ausprobieren finden. Dabei erfährst du auch gleich noch wie die Reaktion des DBMS darauf aussieht (inklusive Fehlermeldungstext).

Ich frage deshalb weil ich einen Insert IGNORE überlege der alle 3 Spalten berücksichtigt. Dann wäre ja wichtig das alle Zeichen überprüft werden.

Ja. Das geht dann mit dem Index nicht.

Lo!

[Tom]

Hello,

Ich möchte gerne in einer MySQL Tabelle einen Unique Index über 3 Spalten setzen.
Dabei kommt aber diese Meldung

1071 Specified key was too long; max key length is 1000 byte

Dann benötigst Du wieder den guten alten "Matchcode"

Dafür hat man von den betroffenen Spalten immer nur die ersten paar (relevanten) Zeichen in den Index einbezogen. Das kann MySQL auch.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[ChrisB]

Hi,

Ich möchte gerne in einer MySQL Tabelle einen Unique Index über 3 Spalten setzen.
Dabei kommt aber diese Meldung

1071 Specified key was too long; max key length is 1000 byte

Dann benötigst Du wieder den guten alten "Matchcode"

Dafür hat man von den betroffenen Spalten immer nur die ersten paar (relevanten) Zeichen in den Index einbezogen. Das kann MySQL auch.

Die Frage ist nur, wie hilfreich das ist, wenn es um einen UNIQUE-Index geht.

Wenn du von “The quick brown fox jumps over the lazy dog” nur die ersten zehn Zeichen berücksichtigst, ist das für einen zum Suchen gedachten Index sicher ausreichend.

Davon, den Eintrag “The quick brown fox jumps over the lazy dog” ein zweites mal einzutragen, hält es mich aber nicht ab - und das ist aber das, was man mit einem UNIQUE-Index wohl erreichen möchte.

Deshalb würde ich in einem solchen Fall, wo man die UNIQUE-Eigenschaft über längere Texte braucht, vorschlagen, den MD5-Wert dieses langen Textes in einer zusätzlichen Spalte abzulegen - und *darauf* den UNIQUE-Index zu setzen. Die sehr geringe Kollisionsgefahr sollte m.E. ausreichen, um das zweimalige Eintragen eines Textes zu verhindern - ohne sich schädlich auszuwirken, in dem zwei Texte für gleich gehalten werden, die es nicht sind. (Wenn man diesbezüglich Bedenken hat, kann man auch noch einen „besseren“ Hash-Algorithmus als MD5 wählen.)

Dieser Mechanismus ist natürlich wirkungslos, wenn „irgendwer“ Einträge per SQL-Statement machen darf - und den Hashwert beim Eintragen einfach nicht oder irgendwas dafür angibt. Aber innerhalb einer geschlossenen Applikation sollte der Ansatz m.E. durchaus brauchbar sein.

Und wenn man ganz auf Nummer sicher gehen will - dann kann man in dem Fall, wo der Hashwert eine Verletzung des Unique Index ergibt, ja auch noch explizit prüfen, ob die Texte wirklich 1:1 mit einem vorhandenen Eintrag identisch sind - wenn das nicht der Fall ist, dann muss man den Eintrag halt noch mal machen, und dabei irgendetwas anderes statt des MD5-Hashes eintragen (der ja sonst keinerlei Bedeutung hat). Das wäre zumindest immer noch performanter, als diese Prüfung bei jedem neuen Eintrag auf Verdacht vorzunehmen.

MfG ChrisB

[dedlfix]

Hi!

Deshalb würde ich in einem solchen Fall, wo man die UNIQUE-Eigenschaft über längere Texte braucht, vorschlagen, den MD5-Wert dieses langen Textes in einer zusätzlichen Spalte abzulegen - und *darauf* den UNIQUE-Index zu setzen. Die sehr geringe Kollisionsgefahr sollte m.E. ausreichen, um das zweimalige Eintragen eines Textes zu verhindern - ohne sich schädlich auszuwirken, in dem zwei Texte für gleich gehalten werden, die es nicht sind. (Wenn man diesbezüglich Bedenken hat, kann man auch noch einen „besseren“ Hash-Algorithmus als MD5 wählen.)

Das Eintragen kann man mit einem Trigger automatisieren. Wenn man wirklich Kollisionsbedenken hat, kann man auch das Ergebnis eines zweites Checksum-Verfahrens dazunehmen, das sollte dann noch schwieriger zu kollisionieren sein.

Lo!