Help Center/ GaussDB(for MySQL)/ Troubleshooting/ SQL Issues/ Error Message "Too many keys specified" Displayed When a Secondary Index Is Created
Updated on 2023-10-19 GMT+08:00
View PDF

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

GaussDB(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.

Other restrictions on InnoDB tables:

  1. A table can contain a maximum of 1017 columns (including virtual generated columns).
  2. The index key prefix limit is 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format.
  3. A maximum of 16 columns is permitted for multicolumn indexes. Exceeding the limit returns an error.
Parent topic: SQL Issues