MySQL ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

Question

I have a MySQL table and I have the need of increasing the capacity of two columns. One column resized fine with this command:

ALTER TABLE TABLE_NAME MODIFY COLUMN COLUMN_NAME varchar(7000);

The other column returned the error message:

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

Neither column is the Primary Key.

I am at a loss as to why one column would change in size without an issue while the other throws the error

Here is the table from the "DESCRIBE TABLE_NAME"; command:

+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
|              | int(11)       | NO   | PRI | NULL    | auto_increment |
|              | int(11)       | YES  | MUL | NULL    |                |
| changed col  | varchar(7000) | YES  |     | NULL    |                |
|              | varchar(1023) | YES  |     | NULL    |                |
|need to change| varchar(255)  | YES  |     | NULL    |                |
|              | varchar(128)  | YES  |     | NULL    |                |
|              | varchar(128)  | YES  |     | NULL    |                |
|              | varchar(64)   | YES  |     | NULL    |                |
+--------------+---------------+------+-----+---------+----------------+

Here is my configuration from the SHOW VARIABLES LIKE '%char%'; command:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

You have an index on the involved column???? and which version of mysql you are using ??? — scaisEdge
I do have an index on the column. Is there a way to expand the size and keep the index? — spiderwebdev
In mysql there is key length limit for index .. which version of mysql you are using ? — scaisEdge

Rick James Rick James · Accepted Answer · 2020-06-30T19:20:45

Switch from ENGINE=MyISAM to InnoDB -- for a lot of reasons. The relevant reason is that the limit is 3072, not 1000 in 5.7.

In general, it is not reasonable to index long string columns.

VARCHAR(7000) says truncate to 7000 characters. You may as well simply declare it TEXT. That has a limit of 64K bytes. It, also, cannot be indexed.

In some rare situations, you can use a "prefix" index: INDEX(foo(99))

For computing sizes "3 bytes per character" is used for utf8.

You may as well go all the way to utf8mb4. (up to 4 bytes/char).

More on limits: http://mysql.rjweb.org/doc.php/limits

MySQL ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

1 Answers