Understanding Index Prefix Length Limits in MySQL 8.0 and 5.7

1 Background

Colleagues reported an error when creating an index on a MySQL table; the simulated error is shown below:

CREATE INDEX t_reg_code_idx USING BTREE ON t(reg_code)
BLOB/TEXT column 'reg_code' used in key specification without a key length

The error indicates that the column reg_code is of type BLOB or TEXT, and a BTREE index requires a length definition for such columns.

What does it mean to have a length defined in the key specification?

2 Index Prefix Length Restrictions

MySQL 8.0

According to the official MySQL 8.0 manual, when creating an index on a BLOB or TEXT column, a prefix length must be specified. For InnoDB tables using REDUNDANT or COMPACT row format, the maximum prefix is 767 bytes; for DYNAMIC or COMPRESSED row format, the limit is 3072 bytes. For MyISAM tables, the limit is 1000 bytes.

MySQL 5.7

The MySQL 5.7 manual states a different limit: InnoDB tables allow up to 1000 bytes (actually should be 3072) if innodb_large_prefix is enabled for DYNAMIC or COMPRESSED formats; otherwise the limit is 767 bytes.

Thus MySQL 8.0 adjusts the prefix length limits compared with earlier versions, but the restriction still exists, unlike some other databases.

3 Verification

Experiments were performed to verify the limits in MySQL 8.0.

Creating an InnoDB table with COMPACT row format and a prefix length of 10000 results in an error indicating the maximum key length is 767 bytes.

create table test01 (
  id int(30) not null auto_increment,
  t_a text,
  primary key(id),
  index idx_t_a(t_a(10000))
) COLLATE='gbk_chinese_ci' ENGINE=InnoDB ROW_FORMAT=COMPACT;
SQL error [1071] [42000]: Specified key was too long; max key length is 767 bytes

Creating an InnoDB table with COMPRESSED row format and the same prefix length yields an error that the maximum key length is 3072 bytes.

create table test01 (
  id int(30) not null auto_increment,
  t_a text,
  primary key(id),
  index idx_t_a(t_a(10000))
) COLLATE='gbk_chinese_ci' ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
SQL error [1071] [42000]: Specified key was too long; max key length is 3072 bytes

4 Summary

The original requirement came from an ETL task that copied VARCHAR data from a source database into a TEXT column in the target, leading to the index error. Using TEXT for indexed columns is generally discouraged because of storage overhead and reduced selectivity.

The issue highlights the importance of proper database schema design rather than quick shortcuts.

References

[1] Column indexes: https://dev.mysql.com/doc/refman/8.0/en/column-indexes.html