ERROR 1071 (42000)
I got an error when creating an index.
mysql> create unique index idx_name_1 on table_name (facebook_url) using btree;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
According to the official document MySQL :: MySQL 5.7 Reference Manual :: 14.23 InnoDB Limits. The limit is described as this:
By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 13.1.11, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum of 3 bytes for each character.
The prefix limit can be raised under the following conditions:
When the innodb_large_prefix configuration option is enabled, this length limit is raised to 3072 bytes, for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.
It appears that if we don't specify the index length for the string column explicitly, then the full length of this string column is applied, which could exceed the limit of 767 bytes.
Let's go back to our case.
mysql> desc table_name;
+--------------+------------------+------+-----+-------------------+...
| Field | Type | Null | Key | Default |...
+--------------+------------------+------+-----+-------------------+...
...
| facebook_url | varchar(512) | YES | | NULL |...
| twitter_url | varchar(512) | YES | | NULL |...
+--------------+------------------+------+-----+-------------------+...
9 rows in set (0.01 sec)
OK, the full length of the string column is 512 characters, it may take up to 512 * 3 = 1536 bytes in UTF8 tables or 512 * 4 = 2048 bytes in UTF8MB4 tables.
Now, let's do a simple math. For UTF8 tables, we should confine the number of characters to no more than the floor of 767 / 3 which is 255 on this column. For UTF8MB4 tables, the limit would be 767 / 4 = 191.
Since our tables are all based on UTF8MB4, we should explicitly specify the length as such:
mysql> create unique index idx_name_1 on table_name (facebook_url(191)) using btree;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
For composite indexes, you should limit the number of characters on every column.
mysql> create unique index idx_name_2 on table_name (facebook_url(191), twitter_url(191)) using btree;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
Last three things
- Only string type of columns can cause the error, numeric type of columns can't.
- The prefix limit also imposes on string type of primary keys.
- If the error is caused from the primary key, you should decrease the character size to 191.