Error when creating an index in ApsaraDB RDS for MySQL: 'Specified key was too long; max key length is 767 bytes' - ApsaraDB RDS

Problem description

The following error messages appear when I create an index for a table on the RDS instance:

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

Scenarios

This error commonly occurs in the following scenarios:

Create an index on a VARCHAR(255) field that uses a multi-byte character set, such as utf8mb4.
Create a compound index where the total length of all columns exceeds the limit.
Create a TEXT or BLOB field and directly indexing it without specifying a prefix length.
The table's row format is COMPACT or REDUNDANT.

Causes

The maximum length of a MySQL index is limited by several factors, including the following:

Version limitations
- For RDS instances running MySQL 5.6, the innodb_large_prefix parameter is set to OFF by default, and the maximum index length is 767 bytes.
- For RDS instances running MySQL 5.7, the innodb_large_prefix parameter is set to ON by default (typically in version 5.7.7 and later), which supports large indexes of up to 3072 bytes.
- For RDS instances running MySQL 8.0, the large index feature is built-in. The innodb_large_prefix parameter is no longer available, and indexes up to 3072 bytes are supported by default.
Row format (ROW_FORMAT)
- COMPACT and REDUNDANT: The maximum index length is 767 bytes.
- DYNAMIC and COMPRESSED: The maximum index length is 3072 bytes. These formats are better suited for long text and multi-byte character sets.
Character set
- Multi-byte character sets, such as utf8mb4 which uses 4 bytes per character, require more storage space for indexes and can easily cause them to exceed the length limit.

Solution

To resolve this issue, first diagnose your environment to find the cause. Then, select a solution based on the diagnosis and your MySQL version.

Step 1: Diagnose the environment

Before you make any changes, connect to your database and run the following SQL queries to gather key information.

-- Replace 'your_table_name' with your table name.

-- 1. Check the MySQL version. The version determines the default parameter behavior.
SELECT VERSION();

-- 2. Check the key InnoDB parameters that affect index length.
-- innodb_large_prefix: Checks if large prefix indexes are enabled.
SHOW VARIABLES LIKE 'innodb_large_prefix';

-- 3. Check the row format (ROW_FORMAT) of the target table.
-- Pay attention to the 'Row_format' field in the query result.
SHOW TABLE STATUS LIKE 'your_table_name';

Step 2: Apply a fix based on the diagnosis

Based on the diagnosis from the previous step and the MySQL version of your instance, choose the appropriate solution.

For RDS instances running MySQL 8.0

By default, MySQL 8.0 supports large indexes of up to 3072 bytes. If you still encounter a 767-byte limit error, it is usually because the table's ROW_FORMAT is set to the legacy COMPACT format.

Change the table row format to DYNAMIC. This allows the table to support large indexes without affecting existing features.
```
-- Replace 'your_table_name' with your table name.
ALTER TABLE `your_table_name` ROW_FORMAT=DYNAMIC;
```
After the change is successful, you can create the index again.
Important
ALTER TABLE ... ROW_FORMAT=DYNAMIC; is a resource-intensive operation that rebuilds all data in a table. For large tables, this operation consumes significant time and I/O resources and may cause table locks. Perform this operation during off-peak business hours or in a maintenance window, and create a backup before you execute the statement.

For MySQL 5.6/5.7

For MySQL 5.6 and 5.7, you must enable the innodb_large_prefix parameter and set the row format of the table to DYNAMIC or COMPRESSED.

Enable the innodb_large_prefix parameter
- Log on to the ApsaraDB RDS console and navigate to the Parameter Settings page for the target instance.
- Find the innodb_large_prefix parameter, change its value to ON, and submit the change.
Apply the DYNAMIC row format
- For existing tables:
```
-- Replace 'your_table_name' with your table name.
ALTER TABLE `your_table_name` ROW_FORMAT=DYNAMIC;
```
  After the change is successful, you can create the index again.
  Important
  ALTER TABLE ... ROW_FORMAT=DYNAMIC; is a resource-intensive operation that rebuilds all data in the table. For large tables, this operation consumes significant time and I/O resources and may cause table locks. Execute this operation during off-peak business hours or in a maintenance window, and create a backup before you execute the statement.
- For new tables:
```
CREATE TABLE `your_new_table` ( 
 -- ... table schema definition ...
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
```

(Optional) Step 3: Use a prefix index

If you do not want to modify the table schema, or to index only part of the content of oversized fields such as TEXT or BLOB, you can use prefix indexes.

-- Example: Create an index for the first 100 characters of the 'long_column' field.
CREATE INDEX idx_name ON your_table_name (long_column(100));