1

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

Improve this question
9
  • Where did that 8 come from?
    – ypercubeᵀᴹ
    Mar 14, 2015 at 15:15
  • The first column in the constraint is a BIGINT
    – JMD Coalesce
    Mar 14, 2015 at 15:16
  • 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ᵀᴹ
    Mar 14, 2015 at 15:16
  • Try having 2 varchar(191) columns in the same index ;)
    – ypercubeᵀᴹ
    Mar 14, 2015 at 15:34
  • 1
    Yes, the mysql docs are rather confusing at this point. (and with their rather loose use of index/key).
    – ypercubeᵀᴹ
    Mar 16, 2015 at 11:30

1 Answer 1

Reset to default
4

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.

Improve this answer
7
  • 1
    Why should I change the column from BIGINT to INT UNSIGNED?
    – JMD Coalesce
    Mar 14, 2015 at 16:00
  • 1
    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.
    – Rick James
    Mar 14, 2015 at 16:24
  • 1
    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?
    – JMD Coalesce
    Mar 14, 2015 at 16:45
  • 1
    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.
    – Rick James
    Mar 14, 2015 at 17:23
  • 1
    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?
    – JMD Coalesce
    Mar 14, 2015 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.