I've defined a unique constraint for two columns: A bigint and a VARCHAR(256) COLLATE utf8mb4_unicode_ci
Then this error occured (which I'm already familiar with):
Specified key was too long; max key length is 767 bytes
I then tried to set the length of the VARCHAR column as high as possible without getting an error.
What suprised me was that MySQL allowed me to change the length to 191 but that would mean that the index is 8+191*4=772 bytes long but the error told that only 767 bytes are allowed.
This is the table:
CREATE TABLE `file_content`
(
`file_content_id` bigint(20) NOT NULL AUTO_INCREMENT,
`local_filename` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`directory_id` bigint(20) NOT NULL,
...
PRIMARY KEY (`file_content_id`),
UNIQUE KEY `fc_dir_name` (`directory_id`,`local_filename`),
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
I'm using MySQL 5.5.27
SHOW CREATE TABLE
output. Index limits are different between myisam and innodb tables for example. And between mysql versions. Which version do you use?varchar(191)
columns in the same index ;)