Bug #53475 | Not able to create index longer than 255 byte | ||
---|---|---|---|
Submitted: | 7 May 2010 6:45 | Modified: | 7 May 2010 10:35 |
Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.1.44 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[7 May 2010 6:45]
Peter Laursen
[7 May 2010 7:15]
Valeriy Kravchuk
Had you taken "3 bytes per character in utf8" into account? Our manual warns about this to some extent in several places, for example, in http://dev.mysql.com/doc/refman/5.1/en/create-index.html: "Prefix limits are measured in bytes, whereas the prefix length in CREATE INDEX statements is interpreted as number of characters for nonbinary data types (CHAR, VARCHAR, TEXT). Take this into account when specifying a prefix length for a column that uses a multi-byte character set."
[7 May 2010 7:31]
Peter Laursen
Actually I thought that index/prefix length was specified in bytes. I read that passage quoted by you but probably did not get the point. Maybe rearranging a little (putting your quote just after my quote) will clarify. DROP TABLE IF EXISTS `length`; CREATE TABLE `length` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `txt` VARCHAR(1001) DEFAULT NULL, PRIMARY KEY (`id`), KEY `NewIndex1` (`txt`(600)) ) ENGINE=INNODB DEFAULT CHARSET=utf8; SHOW WARNINGS; -- Warning 1071 Specified key was too long; max key length is 767 bytes Btw: what about 5.5 with 4-bit utf8 support (+utf16 +utf32): here the possible index length (with utf* and InnoDB) is then further reduced to less than 200 characters, I guess as the 5.5 documentation has the same passages?
[7 May 2010 7:58]
Valeriy Kravchuk
5.5 docs explains new character sets, see http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf32.html, for example. Do you have any specific suggestion on what should be added/clarified in the documentation?
[7 May 2010 10:35]
Peter Laursen
I am closing this