📋

Description

Error 1071 indicates that a specified key (index) in a MySQL table definition has exceeded the maximum allowed length. This commonly occurs during table creation or alteration when defining primary keys, unique keys, or other indexes, preventing the table or index from being successfully created.

💬

Error Message

Specified key was too long; max key length is %d bytes

🔍

Known Causes

3 known causes

⚠️

InnoDB Row Format Limitations

Certain InnoDB row formats (e.g., COMPACT, DYNAMIC in older versions) impose a 767-byte limit on index prefixes, which can be exceeded by long VARCHAR or TEXT columns.

⚠️

Multi-byte Character Sets

Using multi-byte character sets such as `utf8mb4` increases the byte length of string columns, making it easier to exceed key length limits for indexes.

⚠️

Composite Key Exceeds Limit

When creating a composite index on multiple columns, the combined byte length of all indexed columns can easily exceed the maximum allowed key length.

🛠️

Solutions

5 solutions available

1. Reduce Column Length easy

Shorten VARCHAR length to fit index limit

1

Calculate max key length based on charset

-- InnoDB limits:
-- utf8mb4: 3072 bytes max / 4 bytes per char = 768 chars
-- utf8: 3072 bytes max / 3 bytes per char = 1024 chars
-- latin1: 3072 bytes max / 1 byte per char = 3072 chars

2

Reduce VARCHAR length

-- Wrong:
CREATE TABLE posts (
  title VARCHAR(1000),
  INDEX idx_title (title)  -- Too long for utf8mb4
);

-- Right:
CREATE TABLE posts (
  title VARCHAR(191),
  INDEX idx_title (title)
);

2. Use Prefix Index easy

Index only first N characters

1

Create index on prefix

CREATE TABLE posts (
  content TEXT,
  INDEX idx_content (content(100))  -- Index first 100 chars
);

2

For VARCHAR columns

ALTER TABLE users ADD INDEX idx_email (email(50));

3. Enable Large Prefix (MySQL 5.6/5.7) medium

Allow up to 3072 bytes for InnoDB

1

Check current settings

SHOW VARIABLES LIKE 'innodb_large_prefix';
SHOW VARIABLES LIKE 'innodb_file_format';

2

Enable large prefix (my.cnf)

[mysqld]
innodb_large_prefix=ON
innodb_file_format=Barracuda
innodb_file_per_table=ON

3

Use ROW_FORMAT=DYNAMIC for the table

CREATE TABLE posts (
  title VARCHAR(500),
  INDEX idx_title (title)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

4. Use latin1 for ASCII-Only Data medium

Single-byte charset allows longer keys

1

Use latin1 for specific columns

CREATE TABLE codes (
  code VARCHAR(500) CHARACTER SET latin1,
  INDEX idx_code (code)
);

5. Use Hash Index Alternative advanced

Store hash of long values

1

Add hash column and index that

ALTER TABLE posts 
  ADD COLUMN title_hash CHAR(32) AS (MD5(title)) STORED,
  ADD INDEX idx_title_hash (title_hash);

2

Query using hash

SELECT * FROM posts WHERE title_hash = MD5('Your Search Title');

🔗

Related Errors

5 related errors

MySQL Error 1071: Key Length Exceeds Limit