Error
Error Code:
3886
MySQL Error 3886: Index Key Length Exceeded
Description
This error occurs when attempting to modify a column in a MySQL table, or when an index is implicitly or explicitly rebuilt as part of that modification. The change would cause one of the table's indexes to exceed the maximum allowed key length for the storage engine, preventing the operation from completing.
Error Message
Could not change column '%s' of table '%s'. The resulting size of index '%s' would exceed the max key length of %d bytes.
Known Causes
3 known causesWidening an Indexed Column
Modifying a column that is part of an index to a larger data type (e.g., increasing VARCHAR length) can increase its size, pushing the total index key length beyond the maximum allowed.
Composite Index Length Exceeded
When altering a column that is part of a multi-column (composite) index, the combined length of all columns in that index may exceed the maximum key length after the change.
Storage Engine Key Limits
Different MySQL storage engines (e.g., InnoDB, MyISAM) have specific maximum key length limits. The attempted column change would cause an index to surpass the limit of the table's current storage engine.
Solutions
4 solutions available1. Reduce Column Length for Index easy
Shorten the data type or defined length of the column(s) involved in the index.
1
Identify the table and column(s) causing the error. The error message usually provides this information (e.g., 'column '%s' of table '%s'' and 'index '%s'').
2
Determine the current data type and length of the problematic column. If it's a string type (VARCHAR, TEXT), check its defined length.
SHOW CREATE TABLE your_table_name;3
Reduce the length of the column. For VARCHAR, this means choosing a smaller maximum length. For TEXT types, consider if a smaller variant (like MEDIUMTEXT or even VARCHAR) is appropriate.
ALTER TABLE your_table_name MODIFY COLUMN your_column_name VARCHAR(new_length);4
If the column is part of a multi-column index, you might need to reduce the length of multiple columns. Re-evaluate the index's purpose and the data it needs to store.
ALTER TABLE your_table_name MODIFY COLUMN column1 VARCHAR(new_length1), MODIFY COLUMN column2 VARCHAR(new_length2);5
Retry the operation that caused the error.
2. Remove Unnecessary Columns from Index medium
Recreate the index excluding columns that contribute significantly to the key length but are not essential for index functionality.
1
Identify the table, column, and index name from the error message.
2
Examine the existing index definition. You can typically find this using `SHOW CREATE TABLE` or by querying `INFORMATION_SCHEMA.STATISTICS`.
SHOW CREATE TABLE your_table_name;3
Determine if all columns in the index are truly necessary for its intended purpose (e.g., searching, ordering). Sometimes, a column added for convenience or as part of a broader index might be reducible or removable.
4
Drop the existing index.
DROP INDEX index_name ON your_table_name;5
Recreate the index with only the necessary columns, potentially adjusting column lengths if still needed.
CREATE INDEX index_name ON your_table_name (column1, column2, ...);6
Retry the operation that caused the error.
3. Use Column Prefixes for String Indexes easy
For large string columns, index only the prefix of the column to save space and avoid exceeding key length limits.
1
Identify the table, column, and index from the error message.
2
Determine if the problematic column is a large string type (VARCHAR, TEXT).
3
If the index is on a string column and the entire column length is contributing to the key length issue, consider indexing only a prefix of the string. The optimal prefix length depends on the data's cardinality and how it's used.
ALTER TABLE your_table_name DROP INDEX index_name; -- If index already exists
CREATE INDEX index_name ON your_table_name (your_column_name(prefix_length));4
The `prefix_length` is the number of characters to index. For example, `your_column_name(10)` would index the first 10 characters.
5
Choose a `prefix_length` that is sufficient for query performance but small enough to meet the index key length requirements. You might need to experiment or analyze your data.
6
Retry the operation that caused the error.
4. Increase Max Key Length (Advanced & Cautionary) advanced
Modify MySQL server configuration to allow for larger index keys, but this has performance implications.
1
Understand that this is a server-wide configuration change and can impact performance and memory usage. It should be a last resort after other options are exhausted.
2
Locate your MySQL configuration file (e.g., `my.cnf` or `my.ini`). The location varies by operating system and installation method.
3
Edit the configuration file and add or modify the `innodb_large_prefix` and `innodb_page_size` settings within the `[mysqld]` section. `innodb_large_prefix=1` enables support for longer keys. `innodb_page_size` needs to be at least 16K for `innodb_large_prefix` to be fully effective.
[mysqld]
innodb_large_prefix=1
innodb_page_size=163844
Restart the MySQL server for the changes to take effect.
sudo systemctl restart mysql # or equivalent command for your OS5
After restarting, you may need to alter the table to enable large prefixes for existing tables. This is done by setting `ROW_FORMAT=DYNAMIC` or `COMPRESSED`.
ALTER TABLE your_table_name ROW_FORMAT=DYNAMIC;6
Retry the operation that caused the error.