Analysis of the Principle of MySQL Index Length Limit

This article mainly introduces the analysis of the principle of MySQL index length limit. The article introduces it in great detail through sample code, which has certain reference learning value for everyone's study or work. Friends in need can refer to it.

index

TextField does not support indexing.

MySQL has a limit on the length of index fields

The length of each index column of the InnoDB engine is limited to 767 bytes, and the total length of all index columns cannot be greater than 3072 bytes.

The length of each index column of the MyISAM engine is limited to 1000 bytes, and the total length of all index columns cannot be greater than 1000 bytes.

The maximum length of varchar refers to the character length. If the database character set is utf-8, one character occupies 3 bytes. Therefore, under the UTF-8 character set, the length of a single-column index created by the InnoDB engine cannot exceed 255 characters.

Different MySQL versions result in different index length limits

In MySQL version 5.5, innodb_large_prefix was introduced to disable large prefix indexes for compatibility with earlier versions of InnoDB that do not support large index key prefixes.

Enabling innodb_large_prefix can limit the length of a single index to 3072 bytes (but the total length limit of a combined index is still 3072 bytes). When disabled, the length limit of a single index is 767 bytes.

In MySQL 5.5 and MySQL 5.6, innodb_large_prefix is ​​disabled by default, and enabled by default in MySQL 5.7 and above

In MySQL 8.0, innodb_large_prefix has been removed

This is why I can create an index with a length of 1024 characters (3072 bytes in the utf8 character set) on my machine (MySQL 8.0), but not on the server (MySQL 5.5)

Script to test index length limit:

use test;
drop table if exists test_index_len;
create table 
test_index_len(long_char varchar(1025) primary key) ENGINE=InnoDB charset=utf8;

use test;
drop table if exists test_index_len;
create table 
test_index_len(
  long_char varchar(24),
  origin_str varchar(1000),
  key test_index(long_char, origin_str)) ENGINE=InnoDB charset=utf8;

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.