Why does InnoDB allow a 772 byte unique constraint?

Question

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

• ypercubeᵀᴹ: Where did that 8 come from?
• JMD Coalesce: The first column in the constraint is a BIGINT
• ypercubeᵀᴹ: I think the 767 applies only to a single varchar column. Not the full index length. It woul dbe good if you added the SHOW CREATE TABLE output. Index limits are different between myisam and innodb tables for example. And between mysql versions. Which version do you use?
• ypercubeᵀᴹ: Try having 2 varchar(191) columns in the same index ;)
• ypercubeᵀᴹ: Yes, the mysql docs are rather confusing at this point. (and with their rather loose use of index/key).

Answer 1

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.

• JMD Coalesce: Why should I change the column from BIGINT to INT UNSIGNED?
• Rick James: To save 4 bytes. Smaller --> more cacheable --> less I/O --> faster. When you have a billion users and a billion rows, speed becomes your biggest problem. It's much easier to get the datatype 'right' now.
• JMD Coalesce: But the BIGINT column is a foreign key. If I get billions of rows then being limited to 4 billion values will be a problem. Woudln't it?
• Rick James: True INT UNSIGNED is limited to 4B, but I argued that you are unlikely to exceed that. So change it in both tables. Anyway, long before you get to 4B, you will have to redesign your entire system to shard across multiple servers.
• JMD Coalesce: But you previously said ".. easier to get the datatype 'right' now" And if in that case I have to redesign my system anyway, way bother now?