ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

What it means

InnoDB limits how many bytes one index entry may take: 3072 bytes for tables with the DYNAMIC or COMPRESSED row format (the default since MySQL 5.7 and MariaDB 10.2, with the usual 16 KB pages), and 767 bytes for COMPACT and REDUNDANT tables.

The server counts the most a column could hold, not what’s in it. With utf8mb4, the default character set, each character may take 4 bytes, so:

That’s where the familiar varchar(191) in older Laravel schemas comes from: it was the longest utf8mb4 column a 767-byte index could hold.

Common causes

1. A unique index on a long varchar: URLs, paths, external ids declared varchar(1000) or longer.
2. A composite index whose columns add up to more than 3072 bytes.
3. Converting a table from utf8 (3 bytes) or latin1 (1 byte) to utf8mb4, which raises every string column’s count without changing its declared length.
4. An old table with ROW_FORMAT=COMPACT, created on MySQL 5.6 or earlier, or with an explicit row format, where the limit is still 767 bytes and a varchar(255) index fails.

How to fix it

Index a prefix

For a plain (non-unique) index, the first few hundred characters are usually enough to find rows:

CREATE INDEX ix_pages_url ON pages (url(255));

A prefix index can’t enforce uniqueness of the whole value, and can’t serve ORDER BY url alone or cover a query.

Index a hash for uniqueness

To keep long values unique, store a hash in a generated column and put the unique index on that:

ALTER TABLE pages
  ADD COLUMN url_hash binary(32) AS (UNHEX(SHA2(url, 256))) STORED,
  ADD UNIQUE KEY uq_pages_url_hash (url_hash);

Look rows up with WHERE url_hash = UNHEX(SHA2(?, 256)) AND url = ?, which uses the index. A second copy of a URL then fails with duplicate entry, with the hash’s bytes as the value. On MariaDB you don’t need to do this yourself: from 10.4 a UNIQUE key on a long column is accepted and stored as a hash (see below).

Declare the column shorter

If a real value never needs 1000 characters, say so: varchar(255) fits a DYNAMIC index, and varchar(191) fits even the 767-byte limit.

Move an old table to DYNAMIC

SELECT table_name, row_format FROM information_schema.tables WHERE table_schema = DATABASE();
ALTER TABLE users ROW_FORMAT=DYNAMIC;

That rebuilds the table; on a large one, see adding an index for how long-running ALTER TABLE statements behave.

Single-byte columns

An ASCII-only column, such as a hash or a code, can use CHARACTER SET ascii or latin1 and take one byte a character: varchar(1000) CHARACTER SET latin1 indexed whole in the test below.

Reproduce it

On MySQL 8.4.11 (innodb_default_row_format is dynamic, innodb_page_size 16384, the server’s character set utf8mb4):

CREATE TABLE k1 (id int PRIMARY KEY, url varchar(1000), UNIQUE KEY uq_url (url));
CREATE TABLE k2 (id int PRIMARY KEY, a varchar(500), b varchar(500), KEY ab (a, b));
CREATE TABLE k5 (id int PRIMARY KEY, email varchar(255), UNIQUE KEY uq_email (email)) ROW_FORMAT=COMPACT;
CREATE TABLE k10 (id int PRIMARY KEY, url text, UNIQUE KEY uq_url (url(3000)));

ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

varchar(768) with a unique key, KEY (url(255)) on a varchar(1000), varchar(191) on COMPACT and varchar(1000) CHARACTER SET latin1 were all accepted. An index on a text column without a length is a different error:

ERROR 1170 (42000): BLOB/TEXT column 'body' used in key specification without a key length

MariaDB 11.4.13 refused the composite key with the same 1071, but accepted the long unique keys and stored them as hashes:

UNIQUE KEY `uq_url` (`url`) USING HASH

For a non-unique key that was too long it created a prefix index instead (KEY ix_url (url(768))) with Note 1071, and it indexed a text column without a length the same way. On COMPACT it gave a different number:

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes

In Inlet

Inlet’s structure editor adds indexes as ADD INDEX in one ALTER TABLE, shows the DDL before it runs, and notes that MySQL may copy the table while it runs. When it fails, Inlet shows the error and links to this page.