Detailed Interpretations for the Use of innodb_large_prefix Parameters
Last Updated：2021-03-10
Background
On the MySQL, InnoDB storage engine's table is subject to a series of limits, including the field index length limit (a common limit), which has something to do with the parameter "innodb_large_prefix".
Failure Phenomenon
After executing the following two SQL actions for table creation, the system returns the error information, resulting in a failure to create the table:
- Error information: ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
- Error information: ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
Analysis of cause
The aforesaid error reports result from the InnoDB table's index length limit, and the parameter "innodb_large_prefix" is introduced after MySQL5.6 to resolve this problem. This parameter is used to control the length of single-column index (longer than 767 bytes or not), and there are two values "ON" and "OFF", of which:
- ON: on the premise that Innodb table's row format is "Dynamic" or "Compressed", single-column index length's upper limit is extended to 3072 bytes.
- OFF: Innodb table's single-column index length is up to 767 bytes. In case of exceeding the index length limit, the creation of a primary key index fails, and the secondary index is truncated to a prefix index.
Solution
- Reduce the field index length using a prefix sindex.
- Set MySQL's global parameter innodb_large_prefix=ON, and extend the upper limit of InnoDB table's index length to 3072 bytes.
Case review
Test environment
MySQL Core Version：5.7
ROW_FORMAT = Dynamic | Compressed
innodb_page_size = 16K
innodb_file_format = Barracuda
Test process
| Statements for creating table: | innodb_large_prefix=OFF | innodb_large_prefix=ON |
|---|---|---|
| CREATE TABLE tb_01( id int(11) NOT NULL AUTO_INCREMENT, column1 varchar(256) NOT NULL DEFAULT '', PRIMARY KEY (id,column1) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; |
Fail to create the table Error: Specified key was too long; max key length is 767 bytes |
Create the table successfully |
| CREATE TABLE tb_02( id int(11) NOT NULL AUTO_INCREMENT, column1 varchar(256) NOT NULL DEFAULT '', PRIMARY KEY (id,column1) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; |
Create the table successfully index field is truncated to prefix index: KEY idx01 (column1(255)) |
Create the table successfully |
| CREATE TABLE tb_03( id int(11) NOT NULL AUTO_INCREMENT, column1 varchar(256) NOT NULL DEFAULT '', column2 varchar(256) NOT NULL DEFAULT '', column3 varchar(256) NOT NULL DEFAULT '', column4 varchar(256) NOT NULL DEFAULT '', column5 varchar(256) NOT NULL DEFAULT '', PRIMARY KEY (id,column1,column2,column3,column4,column5) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; |
Fail to create the table Error: Specified key was too long; max key length is 767 bytes |
Fail to create the table Error: Specified key was too long; max key length is 767 bytes |
| CREATE TABLE tb_04( id int(11) NOT NULL AUTO_INCREMENT, column1 varchar(256) NOT NULL DEFAULT '', column2 varchar(256) NOT NULL DEFAULT '', column3 varchar(256) NOT NULL DEFAULT '', column4 varchar(256) NOT NULL DEFAULT '', column5 varchar(256) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY idx01(column1,column2,column3,column4,column5) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; |
Fail to create the table Error: Specified key was too long; max key length is 767 bytes |
Fail to create the table Error: Specified key was too long; max key length is 767 bytes |
| CREATE TABLE tb_05( id int(11) NOT NULL AUTO_INCREMENT, column1 varchar(256) NOT NULL DEFAULT '', column2 varchar(256) NOT NULL DEFAULT '', column3 varchar(256) NOT NULL DEFAULT '', column4 varchar(256) NOT NULL DEFAULT '', PRIMARY KEY (id,column1,column2,column3,column4) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; |
Fail to create the table Error: Specified key was too long; max key length is 767 bytes |
Fail to create the table Error: Specified key was too long; max key length is 767 bytes |
| CREATE TABLE tb_06( id int(11) NOT NULL AUTO_INCREMENT, column1 varchar(256) NOT NULL DEFAULT '', column2 varchar(256) NOT NULL DEFAULT '', column3 varchar(256) NOT NULL DEFAULT '', column4 varchar(256) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY idx01(column1,column2,column3,column4) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; |
Create the table successfully index field is truncated to prefix index: KEY idx01(,column1(255),column2(255),column3(255),column4(255)) |
Create the table successfully |
| CREATE TABLE tb_07( id int(11) NOT NULL AUTO_INCREMENT, column1 varchar(255) NOT NULL DEFAULT '', column2 varchar(255) NOT NULL DEFAULT '', column3 varchar(255) NOT NULL DEFAULT '', column4 varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (id,column1,column2,column3,column4) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; |
Create the table successfully | Create the table successfully |
| CREATE TABLE tb_08( id int(11) NOT NULL AUTO_INCREMENT, column1 varchar(255) NOT NULL DEFAULT '', column2 varchar(255) NOT NULL DEFAULT '', column3 varchar(255) NOT NULL DEFAULT '', column4 varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (id), KEY idx01(id,column1,column2,column3,column4) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; |
Create the table successfully | Create the table successfully |
Note
In the field varchar(N), N denotes the character length rather than the number of bytes, and you need to calculate the number of bytes using character set. For example, one character in "utf8" character set occupies 3 bytes. If the single-column index limit is 767, the maximum length supported is N=255.
Conclusions & suggestions
Conclusions
- "innodb" table's single-column index length is up to 767 bytes (it may be extended to 3072 bytes if "innodb_large_prefix" is enabled). If the second index exceeds the length, it is truncated. If the primary key index exceeds the length, it is not truncated, but there is a direct error report, resulting in a failure to create the table.
- In "innodb" table's compound index, each field occupies up to 767 bytes (the length may be extended to 3072 bytes after "innodb_large_prefix" is enabled), and the compound index's total length should not exceed 3,072 bytes.
- Truncation to the primary key index is not allowed, but the second index may be truncated.
- Set parameter "innodb_large_prefix=ON", which can extend the single-column index length to 3072 bytes at most, but the upper limit of the compound index's total length keeps unchanged, and it is still 3072 bytes.
Suggestions
- During database migration and database version upgrading, you must align the source database's "innodb_large_prefix" parameter value to the target database. Violation of this rule might result in failure to create the table
- During the design of database table structure, you are advised to use the prefix index (whenever possible) if you create an index on a column likely containing a very long character string.