CODEWITHSUNDEEP
mysql
spraff
spraff

Reputation: 33395

SQL key length error makes no sense

The following looks perfectly reasonable to me:

CREATE TABLE `mydb`.`Temp` (
 `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 `x` VARCHAR ( 300 ) NOT NULL ,
 `id_foo` INT NULL DEFAULT NULL,
 FOREIGN KEY ( `id_foo`) REFERENCES `Foo` (`id`) ON DELETE CASCADE ,
 INDEX (`id_foo`),
 INDEX (`x`),
 UNIQUE (`id_foo`, `x`)
) ENGINE = INNODB;

With MySQL this gives an error

#1071 - Specified key was too long; max key length is 767 bytes

This seems wrong because the whole row is 309 bytes: less than 767, not even half. What's going on?

Upvotes: 2

Views: 639

Answers (4)

Korhan Ozturk
Korhan Ozturk

Reputation: 11308

According to the MYSQL Documantation : http://dev.mysql.com/doc/refman/5.0/en/create-index.html

MySQL has different limits on the amount of space you can use to define indexes on column(s)

  • for MyISAM it's 1,000 bytes;
  • for InnoDB it's 767 .

Moreover, the data type of those columns matters - for VARCHAR, it's 3x

So, an index on a VARCHAR(300) just like in your table will take 900 of those bytes which is greater than 767 bytes, max key length.

EDIT: Apparently this is not a bug of MySQL, but the UTF8 in MySQL that supports up to 3 bytes. Also, with introducing 4-byte utf8 character set (WL#1213) maximum possible key length changed from 255 to 191 characters (191 * 4 + 2 = 766 where 2 bytes hold for the length). All -utf, -utf8mb4, -utf16, -utf32 are affected from this change beginning with the MySQL version 5.5 or higher.

Upvotes: 6

Ike Walker
Ike Walker

Reputation: 65537

What character set are you using?

Based on that error, it appears you are using a multi-byte character set, probably utf8, which reserves 3 bytes per character. So a varchar(300) in utf8 results in a 900 byte key length, which exceeds the innodb limit of 767.

In order to create your table with those indexes, you either need to a different character set, or shorten the length of your column. If you just had the index on x I would recommend simply indexing the first 255 characters of that column, but given your unique index that include x that solution is not viable, since it would reject values as duplicates if they match on the first 255 characters, even if they differ in the last 45 characters.

Here are a couple of example that will work:

-- shorten x to 255 characters
CREATE TABLE `mydb`.`Temp` (
 `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 `x` VARCHAR ( 255 ) NOT NULL ,
 `id_foo` INT NULL DEFAULT NULL,
 FOREIGN KEY ( `id_foo`) REFERENCES `Foo` (`id`) ON DELETE CASCADE ,
 INDEX (`id_foo`),
 INDEX (`x`),
 UNIQUE (`id_foo`, `x`)
) ENGINE = INNODB;

-- use single-byte character set
CREATE TABLE `mydb`.`Temp` (
 `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 `x` VARCHAR ( 300 ) NOT NULL ,
 `id_foo` INT NULL DEFAULT NULL,
 FOREIGN KEY ( `id_foo`) REFERENCES `Foo` (`id`) ON DELETE CASCADE ,
 INDEX (`id_foo`),
 INDEX (`x`),
 UNIQUE (`id_foo`, `x`)
) ENGINE = INNODB DEFAULT CHARSET LATIN1;

Upvotes: 0

RumpRanger
RumpRanger

Reputation: 654

Try determining how long that index needs to be in order to remain effective:

SELECT COUNT(DISTINCT(`x`)) as n_unique,
COUNT(DISTINCT(LEFT(`x`,200))) as n_100,
COUNT(DISTINCT(LEFT(`x`,150))) as n_150,
COUNT(DISTINCT(LEFT(`x`,100))) as n_100,
COUNT(DISTINCT(LEFT(`x`,50))) as n_50,
COUNT(DISTINCT(LEFT(`x`,25))) as n_25,
COUNT(DISTINCT(LEFT(`x`,10))) as n_10
FROM Temp;

Dividing each n_ result by the n_unique will give you the percent coverage. Once you have that you can likely get decent coverage with a smaller number of characters. 

ALTER TABLE Temp ADD index x_improved(20)

Where 20 is really the n_ count of distinct variables given above.

Upvotes: 2

DerShodan
DerShodan

Reputation: 463

It's your INDEX (x) that's the problem. It works if the VARCHAR is shorter.

Upvotes: 0

Related Questions