Specified key was too long; max key length is 767 bytes

Question

I understand the InnoDB index max length is 767 bytes.

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(254) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  .....
  `token` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `rank` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_token_index` (`token`),
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I want to create a index on my email.

alter table agent add UNIQUE index idx_on_email (email);

But got the error message:

Specified key was too long; max key length is 767 bytes.

But the length of token column only 128 bytes, email is 254 bytes, not above 767 bytes. Hope anyone can help me! Thanks in advance!

Possible duplicate of [#1071 - Specified key was too long; max key length is 767 bytes](http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes) — Sadikhasan, Jan 11 '16 at 08:15
254 is _characters_. If you are using `CHARACTER SET utf8mb4`, that needs to be multiplied by 4 to get _bytes_. — Rick James, Jan 11 '16 at 18:11

score 12 · Accepted Answer · edited Nov 17 '17 at 16:34

12

varchar(254) when you use utf8mb4, means 254 character and each character has 4 bytes, the email field requires at least 1016 bytes (254 * 4). you may look at this article: http://wildlyinaccurate.com/mysql-specified-key-was-too-long-max-key-length-is-767-bytes/ so you can make your email column: varchar(100)

edited Nov 17 '17 at 16:34

julen

4,959
1
23
31

answered Jan 11 '16 at 09:07

Gouda Elalfy

6,888
1
26
38

6

191 is the limit for `utf8mb4`, which is presumably what he is using. – Rick James Jan 11 '16 at 18:10
1

Note since MySQL 5.7.7 there's a [`innodb_large_prefix` option](https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix) (defaults to true) which allows keys up to 3072 bytes (which are 768 characters for `utf8mb4`). In other words, newer MySQL versions don't exhibit this limitation. – julen Nov 17 '17 at 16:36

score 0 · Answer 2 · answered Nov 19 '21 at 23:30

An alternate option would be to reassess the nature and the constraints of the data stored in that table, and how they relate to other data in JOINs, then justify, or not, that a charset and collation of utf8mb4 is needed.

Example: if the data stored and/or compared to other will never have special characters longer then 2 bytes, you may just replace charset and collation with utf8 and utf8_general_ci respectively (or alternate). You may go even shorter for ascii ones.

This assessment / justifying job is a good practice anyway, and may bring accrued performance for free.

Specified key was too long; max key length is 767 bytes

2 Answers2