0

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

  • Where did that 8 come from? – ypercubeᵀᴹ Mar 14 '15 at 15:15
  • The first column in the constraint is a BIGINT – JMD Coalesce Mar 14 '15 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 '15 at 15:16
  • Try having 2 varchar(191) columns in the same index ;) – ypercubeᵀᴹ Mar 14 '15 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 '15 at 11:30
3

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.

  • Why should I change the column from BIGINT to INT UNSIGNED? – JMD Coalesce Mar 14 '15 at 16:00
  • 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 '15 at 16:24
  • 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 '15 at 16:45
  • 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 '15 at 17:23
  • 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 '15 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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