#1071 - Specified key was too long; max key length is 767 bytes

Question

When I executed the following command:

ALTER TABLE `mytable` ADD UNIQUE (
`column1` ,
`column2`
);

I got this error message:

#1071 - Specified key was too long; max key length is 767 bytes

Information about column1 and column2:

column1 varchar(20) utf8_general_ci
column2  varchar(500) utf8_general_ci

I think varchar(20) only requires 21 bytes while varchar(500) only requires 501 bytes. So the total bytes are 522, less than 767. So why did I get the error message?

#1071 - Specified key was too long; max key length is 767 bytes

Answer 1

What character encoding are you using? Some character sets (like UTF-16, et cetera) use more than one byte per character.

Answer 2

767 bytes is the stated prefix limititation for InnoDB tables - its 1,000 bytes long for MyISAM tables.

According to the response to this issue, you can get the key to apply by specifying a subset of the column rather than the entire amount. IE:

ALTER TABLE `mytable` ADD UNIQUE ( column1(15), column2(200) );

Tweak as you need to get the key to apply, but I wonder if it would be worth it to review your data model regarding this entity to see if there's improvements that would allow you to implement the intended business rules without hitting the MySQL limitation.