Description:
The index length limit for InnoDB is 767 byte, for MyISAM it is 1024 bytes.
In a utf8/multibyte environment that comes down to 256/341 characters for an index, which is very small.
I am currently with the United Nations FAO in Rome, Italy. This organisation has 5 official languages, including arabic and chinese - UTF8 is an absolute requirement.
How to repeat:
root@localhost [cds_onefish]> show create table search_hits\G
*************************** 1. row ***************************
Table: search_hits
Create Table: CREATE TABLE `search_hits` (
`hits` int(6) NOT NULL,
`ref_date` datetime NOT NULL,
`search_utf8` varchar(512) character set utf8 collate utf8_bin NOT NULL,
PRIMARY KEY (`ref_date`,`search_utf8`(200))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Table definition originally on Oracle, converted using the Migration Toolkit. PRIMARY KEY definiton had to be changed manually from PRIMARY KEY (`ref_date`, `search_utf8`) to the shown definition for the migration to work. There is a lot of more tables with similar problems (a few thousand tables in 322 schema definitions).
Suggested fix:
For this to be useful, the index length limits should be able to match the VARCHAR lengths provided even if the VARCHARs are UTF8 all the way.
I know that this is not efficient, but that is not the issue of the migration project, it is an issue for the followup performance tuning project.
Description: The index length limit for InnoDB is 767 byte, for MyISAM it is 1024 bytes. In a utf8/multibyte environment that comes down to 256/341 characters for an index, which is very small. I am currently with the United Nations FAO in Rome, Italy. This organisation has 5 official languages, including arabic and chinese - UTF8 is an absolute requirement. How to repeat: root@localhost [cds_onefish]> show create table search_hits\G *************************** 1. row *************************** Table: search_hits Create Table: CREATE TABLE `search_hits` ( `hits` int(6) NOT NULL, `ref_date` datetime NOT NULL, `search_utf8` varchar(512) character set utf8 collate utf8_bin NOT NULL, PRIMARY KEY (`ref_date`,`search_utf8`(200)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) Table definition originally on Oracle, converted using the Migration Toolkit. PRIMARY KEY definiton had to be changed manually from PRIMARY KEY (`ref_date`, `search_utf8`) to the shown definition for the migration to work. There is a lot of more tables with similar problems (a few thousand tables in 322 schema definitions). Suggested fix: For this to be useful, the index length limits should be able to match the VARCHAR lengths provided even if the VARCHARs are UTF8 all the way. I know that this is not efficient, but that is not the issue of the migration project, it is an issue for the followup performance tuning project.