MySQL Bugs: #103146: compound indexes size greater than 767 bytes allowed for redundant innodb tables

Bug #103146	compound indexes size greater than 767 bytes allowed for redundant innodb tables
Submitted:	30 Mar 2021 5:56	Modified:	30 Mar 2021 6:51
Reporter:	Chelluru Vidyadhar	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7.33	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
As per the documentation, one of the InnoDB limitation of 767 bytes index length applicable for InnoDB tables that use the REDUNDANT or COMPACT row format.

"The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character."

https://dev.mysql.com/doc/refman/5.7/en/innodb-limits.html

It looks like this limit is only applicable for individual indexes and we can create compound indexes of length greater than 767 bytes. However, in case - any of the column in the compound index has length greater than 767 bytes then we receive error. 

How to repeat:
Below are the examples:

drop database if exists test;
create database test;
use test;

CREATE TABLE `test123` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `b` varchar(255) DEFAULT NULL,
  `c` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into test123 (b,c) select repeat('b',255), repeat('c',255);

create index idx123 on test123(b); ## <- This will fail as expected because the index length will be 4 (utf8mb4) * 255 (column length) bytes

CREATE TABLE `test456` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `b` varchar(255) DEFAULT NULL,
  `c` varchar(255) DEFAULT NULL,
  `d` varchar(255) DEFAULT NULL,
  `e` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into test123 (b,c,d,e) select repeat('b',255), repeat('c',255), repeat('d',255), repeat('e',255);

create index idx123 on test456(b,c,d,e); ## <- This will succeed even the total compound index length is greater than 767 bytes and all individual columns length inside index less than 767 bytes.

CREATE TABLE `test789` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `b` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `c` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into test123 (b,c) select repeat('b',255), repeat('c',255);

create index idx123 on test789(b,c); ## <- This command will fail because one of the column (b) has total length greater than 767 bytes.

Suggested fix:
Consider to update the documentation to provide more detailed explanation. For example,

"the limitation applicable for single column indexes and multi column indexes involving any column with length greater than 767 bytes"

There are some typo in provided examples. Below are the updated examples.

====

set global innodb_default_row_format='redundant';

drop database if exists test;
create database test;
use test;

CREATE TABLE `test123` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `b` varchar(255) DEFAULT NULL,
  `c` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into test123 (b,c) select repeat('b',255), repeat('c',255);

create index idx123 on test123(b); ## <- This will fail as expected because the index length will be 4 (utf8mb4) * 255 (column length) bytes

CREATE TABLE `test456` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `b` varchar(255) DEFAULT NULL,
  `c` varchar(255) DEFAULT NULL,
  `d` varchar(255) DEFAULT NULL,
  `e` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into test456 (b,c,d,e) select repeat('b',255), repeat('c',255), repeat('d',255), repeat('e',255);

create index idx123 on test456(b,c,d,e); ## <- This will succeed even the total compound index length is greater than 767 bytes and all indvidual columns length inside index less than 767 bytes.

CREATE TABLE `test789` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `b` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `c` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

insert into test789 (b,c) select repeat('b',255), repeat('c',255);

create index idx123 on test789(b,c); ## <- This command will fail because one of the column (b) has total length greater than 767 bytes.
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

====

Hello Chelluru,

Thank you for the report and feedback.

regards,
Umesh