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:
None 
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
Description:
http://dev.mysql.com/doc/refman/5.1/en/create-index.html
.. says:

Indexes can be created that use only the leading part of column values, 
using col_name(length) syntax to specify an index prefix length:
...
For example, a prefix can be up to 1000 bytes long for MyISAM tables, and 767 bytes for InnoDB tables

.. but 

How to repeat:
SELECT version(); -- 5.1.44-community

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`(250))
) ENGINE=INNODB DEFAULT CHARSET=utf8;

ALTER TABLE `blah`.`length` DROP KEY `NewIndex1`, ADD INDEX `NewIndex1` (`txt`(600));

SHOW CREATE TABLE `length`;

/* returns 

CREATE TABLE `length` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `txt` varchar(1001) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `NewIndex1` (`txt`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8

(note index length of 255)
*/

Suggested fix:
If this is 'not a bug' then docs are very unclear!
[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