Error when creating an index in ApsaraDB RDS for MySQL: 'Specified key was too long; max key length is 767 bytes'-ApsaraDB RDS(RDS)-阿里云帮助中心

This error means your index exceeds the maximum length MySQL allows for that table's configuration. The fix depends on your MySQL version and the table's row format.

Error messages

When creating an index, you may see one of the following errors:

ERROR 1071: Specified key was too long; max key length is 767 bytes
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes

Why this happens

MySQL InnoDB limits index key length based on three factors:

MySQL version

Version	Default behavior	Max index length
MySQL 5.6	`innodb_large_prefix` is `OFF`	767 bytes
MySQL 5.7 (typically 5.7.7+)	`innodb_large_prefix` is `ON`	3,072 bytes
MySQL 8.0	Large prefix is built-in; `innodb_large_prefix` no longer exists	3,072 bytes

Row format

Row format	Large index key prefix support	Max index length
COMPACT	No	767 bytes
REDUNDANT	No	767 bytes
DYNAMIC	Yes	3,072 bytes
COMPRESSED	Yes	3,072 bytes

Character set: utf8mb4 uses 4 bytes per character. Multi-byte character sets require more storage space for indexes and can easily cause them to exceed the length limit.

Common triggers:

Indexing a VARCHAR(255) column with a multi-byte character set such as utf8mb4
Creating a compound index where the total column length exceeds the limit
Indexing a TEXT or BLOB column without specifying a prefix length
The table's row format is COMPACT or REDUNDANT

Diagnose your environment

Before making changes, run these queries to identify the exact cause:

-- Check the MySQL version
SELECT VERSION();

-- Check whether large prefix indexes are enabled (MySQL 5.6/5.7 only)
SHOW VARIABLES LIKE 'innodb_large_prefix';

-- Check the row format of the affected table
-- Replace 'your_table_name' with your actual table name
SHOW TABLE STATUS LIKE 'your_table_name';

Look at the Row_format field in the SHOW TABLE STATUS output. If it shows COMPACT or REDUNDANT, changing the row format to DYNAMIC resolves the error.

Fix for MySQL 8.0

MySQL 8.0 supports 3,072-byte indexes by default. If you still see the 767-byte error, the table is using the legacy COMPACT row format. Change it to DYNAMIC:

-- Replace 'your_table_name' with your actual table name
ALTER TABLE `your_table_name` ROW_FORMAT=DYNAMIC;

After the change completes, create the index again.

Important

ALTER TABLE ... ROW_FORMAT=DYNAMIC rebuilds the entire table. On large tables, this consumes significant time and I/O resources and may cause table locks. Run this during off-peak hours or in a maintenance window, and create a backup before executing.

Fix for MySQL 5.6 and 5.7

Both conditions must be met: innodb_large_prefix must be ON, and the table's row format must be DYNAMIC or COMPRESSED.

Step 1: Enable `innodb_large_prefix`

Log on to the ApsaraDB RDS console and go to the Parameter Settings page for the instance.
Find innodb_large_prefix, set its value to ON, and submit the change.

Step 2: Change the row format

For an existing table:

-- Replace 'your_table_name' with your actual table name
ALTER TABLE `your_table_name` ROW_FORMAT=DYNAMIC;

Important

For a new table, set the row format at creation time:

CREATE TABLE `your_new_table` (
  -- table schema definition
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

Alternative: use a prefix index

If changing the row format is not an option, index only a prefix of the column instead of the full value. This works well for TEXT and BLOB columns, or when you only need prefix-based lookups.

-- Index the first 100 characters of 'long_column'
CREATE INDEX idx_name ON your_table_name (long_column(100));

Choose a prefix length that covers the data you need to search while staying within the byte limit for your row format.