Varchar index length of innodb utf8 character set in mysql

Text and varchar character indexes are invalid when innodb in mysql is a uft8 character set. The index length of a single field is limited to 767 bytes due to the index length limit in the innodb utf8 character set.

The maximum index length is:

The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. (1024 bytes for non-64-bit builds before MySQL 5.0.17, and for all builds before 5.0.15 .)

In the utf8 character set, the [data part] of varchar (255) occupies 255*3 = 765 bytes, which is close to 767 bytes, 256*3 = 768 bytes, and has exceeded 767.

[BIGHD] (root @ localhost) [cm]> create table 'temp _ 2 '(
-> 'Id' BIGINT (20) unsigned not null AUTO_INCREMENT,
-> 'Pn 'varchar (128) default '',
-> 'First _ path' VARCHAR (256) DEFAULT '',
-> 'Dir' VARCHAR (255) DEFAULT '',
-> 'A' text,
-> Primary key ('id ')
->) ENGINE = InnoDB default charset = utf8
->
->;
Query OK, 0 ROWS affected (0.15 sec)
 
[BIGHD] (root @ localhost) [cm]>
[BIGHD] (root @ localhost) [cm]>
[BIGHD] (root @ localhost) [cm]> alter table temp_2 add key (first_path );
Query OK, 0 ROWS affected, 1 warning (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 1
 
[BIGHD] (root @ localhost) [cm]> SHOW warnings;
+ --------- + ------ + --------------------------------------------------------- +
| Level | Code | Message |
+ --------- + ------ + --------------------------------------------------------- +
| Warning | 1071 | Specified KEY was too long; max key length is 767 bytes |
+ --------- + ------ + --------------------------------------------------------- +
1 row in set (0.00 sec)
 
[BIGHD] (root @ localhost) [cm]> alter table temp_2 add key (dir );
Query OK, 0 ROWS affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
 
[BIGHD] (root @ localhost) [cm]> alter table temp_2 add key (a (767 ));
Query OK, 0 ROWS affected, 1 warning (0.32 sec)
Records: 0 Duplicates: 0 Warnings: 1
 
[BIGHD] (root @ localhost) [cm]> SHOW warnings;
+ --------- + ------ + --------------------------------------------------------- +
| Level | Code | Message |
+ --------- + ------ + --------------------------------------------------------- +
| Warning | 1071 | Specified KEY was too long; max key length is 767 bytes |
+ --------- + ------ + --------------------------------------------------------- +
1 row in set (0.00 sec)
 
[BIGHD] (root @ localhost) [cm]> show create table temp_2;
| Temp_2 | create table 'temp _ 2 '(
'Id' BIGINT (20) unsigned not null AUTO_INCREMENT,
'Pn 'varchar (128) default '',
'First _ path' VARCHAR (256) DEFAULT '',
'Dir' VARCHAR (255) default '',
'A' text,
Primary key ('id '),
KEY 'first _ path' (255 )),
KEY 'dir' ('dir '),
KEY 'A' (255 ))
) ENGINE = InnoDB default charset = utf8 |
Therefore, the (255) character in the key represents 255 characters (a UTF-8 character occupies 3 bytes ).

Let's take a look at the combined index:

[BIGHD] (root @ localhost) [cm]> alter table temp_2 add key (pn, first_path );
Query OK, 0 ROWS affected, 1 warning (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 1
 
[BIGHD] (root @ localhost) [cm]>
[BIGHD] (root @ localhost) [cm]> SHOW warnings;
+ --------- + ------ + --------------------------------------------------------- +
| Level | Code | Message |
+ --------- + ------ + --------------------------------------------------------- +
| Warning | 1071 | Specified KEY was too long; max key length is 767 bytes |
+ --------- + ------ + --------------------------------------------------------- +
1 row in set (0.00 sec)
 
[BIGHD] (root @ localhost) [cm]> show create table temp_2;
| Temp_2 | create table 'temp _ 2 '(
'Id' BIGINT (20) unsigned not null AUTO_INCREMENT,
'Pn 'varchar (128) default '',
'First _ path' VARCHAR (256) DEFAULT '',
'Dir' VARCHAR (255) default '',
'A' text,
Primary key ('id '),
KEY 'first _ path' (255 )),
KEY 'dir' ('dir '),
KEY 'A' (255 )),
KEY 'pn '('pn', 'First _ path' (255 ))
) ENGINE = InnoDB default charset = utf8 |

Even for federated indexes, the limit of 767 is also for fields, rather than the total length of the federated index.

Create table 'temp _ 2 '(
'Id' BIGINT (20) unsigned not null AUTO_INCREMENT,
'A' text,
'B' text,
'C' text,
'D text,
'E' text,
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8
 
 
255*3 = 765*4 = 3060 + 4*3 = 3072
[BIGHD] (root @ localhost) [cm]> alter table temp_2 add key (a (255), B (255), c (255), d (255 ), e (4 ));
Query OK, 0 ROWS affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
 
[BIGHD] (root @ localhost) [cm]>
[BIGHD] (root @ localhost) [cm]> alter table temp_2 add key (a (255), B (255), c (255), d (255 ), e (5 ));
ERROR 1071 (42000): Specified KEY was too long; max key length is 3072 bytes