MySQL Error #1071 - Specified key was too long; max key length is 767 bytes
767 bytes in MySQL version 5.6 (and prior versions), is the stated prefix limitation for InnoDB tables. It's 1,000 bytes long for MyISAM tables. This limit has been increased to 3072 bytes In MySQL version 5.7 (and upwards).
You also have to be aware that if you set an index on a big char or varchar field which is utf8mb4 encoded, you have to divide the max index prefix length of 767 bytes (or 3072 bytes) by 4 resulting in 191. This is because the maximum length of a utf8mb4 character is four bytes. For a utf8 character it would be three bytes resulting in max index prefix length of 255 (or minus null-terminator, 254 characters).
One option you have is to just place lower limit on your VARCHAR fields.
Another option (according to the response to this issue) is to get the subset of the column rather than the entire amount, i.e.:
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 possible, which would allow you to implement the intended business rules without hitting the MySQL limitation.
If anyone is having issues with INNODB / Utf-8 trying to put an UNIQUE index on a VARCHAR(256) field, switch it to VARCHAR(255). It seems 255 is the limitation.
When you hit the limit. Set the following.
- INNODB
utf8VARCHAR(255) - INNODB
utf8mb4VARCHAR(191)
MySQL assumes worst case for the number of bytes per character in the string. For the MySQL 'utf8' encoding, that's 3 bytes per character since that encoding doesn't allow characters beyond U+FFFF. For the MySQL 'utf8mb4' encoding, it's 4 bytes per character, since that's what MySQL calls actual UTF-8.
So assuming you're using 'utf8', your first column will take 60 bytes of the index, and your second another 1500.