MySQL InnoDB – Why Allow a 772 Byte Unique Constraint?

innodbMySQLunique-constraint

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

Best Answer

Revised math:

  • 8 bytes for BIGINT -- Do you really need more than 4 billion values? Consider INT UNSIGNED at only 4 bytes.
  • 2 byte length of VARCHAR
  • 764 bytes for 191 characters (potentially 4 bytes per utf8mb4 character)

2+764 < 767, so the VARCHAR column passes that test
8+2+764 < 3KB, so the entire index passes another test. (As would a pair of 191-char VARCHAR)

Edit

The reference manual has some complicated limits on indexes. Generally you will be safe with a column taking no more than 767 bytes, and the entire index taking no more than 3072 bytes. Keep in mind that one character in utf8mb4 counts (for these limits) as 4 bytes. I have a compilation of limits.

Related Question