InnoDB UTF8 Character Set varchar index length problem in MySQL

The limit for total index length is:

The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072. (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 portion] of varchar (255) occupies 255*3=765 bytes, the closest 767bytes, 256*3 = 768bytes, already over 767.

[BIGHD] (root@localhost) [cm]> CREATE TABLE ' temp_2 ' (
-> ' id ' BIGINT () 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 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 ' (' First_path ' (255)),
KEY ' dir ' (' dir '),
KEY ' A ' (' a ' (255))
) Engine=innodb DEFAULT Charset=utf8 |
So key inside a (255) represents 255 characters (one UTF8 character for 3 bytes).

Look at the Federated index again:

[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 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 ' (' First_path ' (255)),
KEY ' dir ' (' dir '),
KEY ' A ' (' a ' (255)),
KEY ' pn ' (' pn ', ' first_path ' (255))
) Engine=innodb DEFAULT Charset=utf8 |

Even for federated indexes, the 767 limit is also for fields, not the total length of the federated Index.

CREATE TABLE ' temp_2 ' (
' id ' BIGINT () 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]> A Lter 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