You are here

MySQL: #1071 - Specified key was too long; max key length is 767 bytes

Submitted by Druss on Fri, 2015-04-03 01:33

Here I was simply creating a MySQL (5.5) table when suddenly up pops the following error:

#1071 - Specified key was too long; max key length is 767 bytes

After a little trial and error, I found that since one of my VARCHAR fields was being used for a UNIQUE index, MySQL was basically telling me that it was using too much space. When I reduced the length of this field from its initial 512 setting to 256 & then 255, it still complained. However, reducing it further to 128 fixed the issue!

While that's all well and good, the underlying problem is to do with the type of encoding used for the field. Even though I didn't need it, the field was being encoded with the multi-byte utf8mb4_unicode_ci as this was the database's default. Due to the fact that this encoding uses up more space (4 butes) for each character, this means that only strings of a certain length can be stored in InnoDB's default 767 byte length. This certain length happens to be 191.

Changing the field's encoding to utf8_unicode_ci (which uses 3 bytes for each character) allows this limit to be increased to a maximum of 255 (or 191 + 64). Changing the field's encoding to the good ol' latin_swedish_ci allows us to use the maximum InnoDB limit of 767.

To summarise, unless you actually need it, avoid utf8mb4 (particularly for indexes) as it can occupy a lot of space. If you're only working with English and if your field doesn't have to accommodate any crazy squiggly alphabet, simply use latin_swedish_ci if you're running out of space.

P.S. RTFM also helps: Quoting from the manual,

InnoDB has a maximum index length of 767 bytes, so for utf8 or utf8mb4 columns, you can index a maximum of 255 or 191 characters, respectively. If you currently have utf8 columns with indexes longer than 191 characters, you will need to index a smaller number of characters. In an InnoDB table, these column and index definitions are legal:

col1 VARCHAR(500) CHARACTER SET utf8, INDEX (col1(255))
To use utf8mb4 instead, the index must be smaller:

col1 VARCHAR(500) CHARACTER SET utf8mb4, INDEX (col1(191))