A detailed introduction to the index length issue of MySQL indexes

The length of the index created in each single table of MySQL is limited, and there are different restrictions on tables under different storage engines.

In the MyISAM table, when creating a combined index, the length of the created index cannot exceed 1000. Note that the calculation of the length of the index here is based on the length set by the table field. For example:

create table test(id int,name1 varchar(300),name2 varchar(300),name3 varchar(500))charset=latin1 engine=myisam;
create index test_name on test(name1,name2,name3);

At this time, an error message is reported:

Specified key was too long;max key length is 1000 bytes.

Modify the table structure:

alter table test convert to charset utf8;
　　create index test_name3 on test(name3).

At this time warning:

Specified key was too long;max key length is 1000 bytes.

But the index is created successfully. Looking at the table structure, you can see that the created index is a prefix index:

‘key test_name3(name3(333))’

The conclusion is: for the myisam table, if you create a combined index, the sum of the lengths of the created indexes cannot exceed 1000 bytes, otherwise an error will be reported , the creation failed; for myisam's single-column index, the maximum length cannot exceed 1000, otherwise an alarm will occur, but the creation is successful, and the final creation is a prefix index (the first 333 bytes are taken).

In the Innodb table, create a composite index:

create table test1(id int,name1 varchar(300),name2 varchar(300),name3 varchar(500))charset=latin1 engine=innodb;
　　create index test1_name on test(name1,name2,name3);

At this time, is given

warning：Specified key was too long;max key length is 767 bytes.

Modify the table structure:

alter table test1 convert to charset utf8;
　　create index test1_name3 on test(name3).

At this time,

warning：Specified key was too long;max key length is 767 bytes.

is given. The conclusion is: for creating a combined index in innodb, if the length of each column does not exceed 767, the total length of all columns will no longer be calculated. If there is more than 767, an alarm will be given. The index will be created successfully in the end, but for more than 767 The column of bytes takes the prefix index; for the single-column index of InnoDB, if it exceeds 767, a warning is given. In the end, the index is created successfully, and the prefix index is taken (the first 255 bytes are taken).

The above is the detailed introduction to the index length issue of MySQL index. For more related content, please pay attention to the PHP Chinese website (www.php .cn)!