Error Message "Too many keys specified" Displayed When a Secondary Index Is Created
Scenario
A secondary index failed to be created, and the error message "Too many keys specified; max 64 keys allowed" was displayed.
Fault Analysis
RDS for MySQL limits the maximum number of secondary indexes in each InnoDB table to 64. If the number of secondary indexes exceeds 64, the error message "Too many keys specified; max 64 keys allowed" will be displayed. For details, see InnoDB Limits.
Solution
Do not create too many indexes for a single table.
Note
Other restrictions on InnoDB tables include the following:
- A table can contain a maximum of 1,017 columns (including virtual generated columns).
- The index key prefix limit is 3,072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format.
- A maximum of 16 columns is permitted for multicolumn indexes. Exceeding the limit returns an error.
Parent topic: SQL Issues
- Scenario
- Fault Analysis
- Solution