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

Question

I get following error when I Import the Sqlbackup i have:

SQL-Befehl:

CREATE TABLE jos_hikashop_zone_link (
    zone_parent_namekey VARCHAR(255) NOT NULL,
    zone_child_namekey VARCHAR(255) NOT NULL,
    PRIMARY KEY (zone_parent_namekey, zone_child_namekey)
)
ENGINE = MYISAM
AVG_ROW_LENGTH = 49
CHARACTER SET utf8
COLLATE utf8_general_ci
MySQL meldet: Dokumentation

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

I have tried to "set GLOBAL storage_engine='InnoDb'" before I Import the file.

I can`t get a new SQLImportfile.

I am using XAMPP on a local machine.

You specified a composite key of 510 characters, but each UTF-8 character could be as large as 4 bytes, hence you are requesting a key over 2K in size, which MySQL doesn't like. Shrink the size of your composite key in order to proceed. — Tim Biegeleisen, Oct 18 '16 at 13:42

score 6 · Accepted Answer · answered Oct 18 '16 at 13:58

Because your primary key is a multi-column primary key on both UTF8 VARCHAR(255) columns, the index size is the size of both columns, added together.

Indexes on UTF8 columns automatically allocate all possible space, where each character could take up to 3 bytes. Therefore, your index size is 255 characters * 3 bytes * 2 columns = 1530 bytes.

MyISAM has a limit of 1000 bytes for indexes. InnoDB has an even smaller limit (767 bytes) unless you're on MySQL 5.7.7+, in which case the limit is 3072 bytes by default.

Try to reduce your index size. Typically, the smaller your index size, the better your seek performance will be.

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

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

1 Answers1

Linked