MySQL error 1071, "Specified key was too long", means an index needs more bytes than InnoDB will store in one index key. It almost never shows up on a fresh project. It shows up when someone converts a schema from utf8mb3 to utf8mb4, restores an old dump on a new server, or replays a migration on a replica that was created years ago with different defaults.
The fix is usually simple once you know which limit you hit. The trouble is that the simple fix on a 500 GB table can lock writes for hours. Below: how the limit is calculated, the four practical fixes, and what changes when the table is large.
The two messages and what they mean
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
Both are listed in the MySQL server error reference: 1071 is ER_TOO_LONG_KEY and 1709 is ER_INDEX_COLUMN_TOO_LONG. The number in the message tells you which limit you hit:
- 3072 bytes is the limit for InnoDB tables with the
DYNAMICorCOMPRESSEDrow format, according to the InnoDB limits page. You are trying to index a genuinely long value. - 767 bytes is the limit for
REDUNDANTandCOMPACTtables. This almost always means an old table, or a server whereinnodb_default_row_formatwas changed away from its default. - Smaller numbers such as 1536 or 768 bytes point to a server created with an 8KB or 4KB
innodb_page_size. The 3072-byte limit applies to the default 16KB page and shrinks proportionally.
How the bytes are counted
The confusing part is that limits are in bytes while lengths in your DDL are in characters. The CREATE INDEX documentation says prefix lengths for CHAR, VARCHAR and TEXT are counted in characters, while the limits are measured in bytes. MySQL reserves the worst case for every character, so for utf8mb4 each character counts as 4 bytes.
VARCHAR(255)inutf8mb3: 255 × 3 = 765 bytes. Fits under 767.- The same column in
utf8mb4: 255 × 4 = 1020 bytes. Too long for aCOMPACTtable, fine forDYNAMIC. VARCHAR(1000)inutf8mb4: 4000 bytes. Too long for any row format.
That is why 191 is such a common number in old schemas: 191 × 4 = 764 bytes, the largest utf8mb4 prefix that fits under 767. The InnoDB limits page uses exactly that example. It is also why the error appears during charset migrations: the column did not change length, but every character suddenly counts for more bytes. Keep in mind that utf8mb3 is deprecated and expected to be removed in a future major release, so this migration is one you will have to do eventually.
utf8mb4 columns of VARCHAR(500) in one index fail even though each one alone would fit. Add up every column in the index when you do the math.Find tables that are still on the 767-byte limit
Since innodb_default_row_format defaults to DYNAMIC (see the row format documentation), new tables are fine. Old ones are not, because an explicit ROW_FORMAT stays with the table through upgrades. This query lists them:
SELECT table_schema, table_name, row_format, table_collation
FROM information_schema.tables
WHERE engine = 'InnoDB'
AND row_format IN ('Compact', 'Redundant')
AND table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
ORDER BY table_schema, table_name;
Run it on the primary and on every replica. The documentation shows exactly this trap: a table with a 3070-character prefix index is created on a source with DYNAMIC as the default, and the same statement fails on a replica that uses COMPACT. Replication then stops with an error, not with a warning.
Four ways to fix it
1. Switch the table to DYNAMIC
If the message says 767 bytes, this is usually the right fix. It removes the old limit for every index on the table:
ALTER TABLE legacy_users ROW_FORMAT=DYNAMIC, ALGORITHM=INPLACE, LOCK=NONE;
The online DDL documentation lists changing ROW_FORMAT as in place, with concurrent DML allowed, but it rebuilds the table and calls it an expensive operation. Also note a quiet side effect: a table without an explicit ROW_FORMAT takes the current innodb_default_row_format on every rebuild, including OPTIMIZE TABLE.
2. Use a shorter column or a prefix index
Ask whether the column really needs 1000 characters. Email addresses, slugs and codes often do not, and VARCHAR(255) in utf8mb4 fits easily under 3072 bytes. Shortening a column with data in it needs ALGORITHM=COPY, so check the longest stored value first.
For a non-unique lookup index, a prefix is often enough. Most values differ in the first 100 to 200 characters, and the full value is still compared when rows are read:
-- Non-unique lookup index: index only the first 191 characters
ALTER TABLE pages ADD INDEX idx_url (url(191)), ALGORITHM=INPLACE, LOCK=NONE;
Do not use a prefix for a UNIQUE index unless you mean it. The documentation is clear that values then must be unique within the prefix length, so two different URLs that share the first 191 characters will collide. TEXT and BLOB columns always need a prefix.
3. Index a hash of the value
For uniqueness on long values such as URLs or file paths, index a fixed-size hash instead. A 32-byte SHA-256 fits under any limit. InnoDB supports secondary indexes on generated columns, including virtual ones:
ALTER TABLE pages
ADD COLUMN url_hash BINARY(32)
GENERATED ALWAYS AS (UNHEX(SHA2(url, 256))) VIRTUAL,
ADD UNIQUE INDEX uq_url_hash (url_hash);
-- Lookups must use the hash, then compare the full value
SELECT * FROM pages
WHERE url_hash = UNHEX(SHA2('https://example.com/a', 256))
AND url = 'https://example.com/a';
Two things to check before you choose this. The hash is byte-exact, while a utf8mb4 column with a case-insensitive collation treats A and a as equal, so the uniqueness rule changes. Normalise the value inside the expression (for example with LOWER()) if that matters. And range queries such as LIKE 'https://example.com/%' cannot use a hash index.
4. Rethink the index
Sometimes the honest answer is that a B-tree index on a 2000-character text column is the wrong tool. Full-text search, a separate lookup table with a numeric key, or an index on a shorter derived column often serves the real query better.
What changes on large tables
- Charset conversion blocks writes. According to the online DDL table,
CONVERT TO CHARACTER SETrebuilds the table when the encoding changes and does not permit concurrent DML. On a large, busy table use an online schema change tool instead; we compare the options in our guide to ALTER TABLE without downtime. - "Online" is not free. A
ROW_FORMATrebuild allows reads and writes, but it needs free disk space for a full copy of the table and its indexes, and changes made during the rebuild are kept in a temporary log limited byinnodb_online_alter_log_max_size(128 MB by default). If the log exceeds that limit, theALTERfails and uncommitted concurrent DML is rolled back. Test on a copy with production-like write load. - Replicas get the bill later. The
ALTERreaches the binary log only after it has finished on the source, so with standard replication the replica starts it then, and in our experience falls behind for roughly the length of the operation. Plan for that lag, or use a tool that copies rows in small batches. - Indexes grow. A
utf8mb4index can take more space than the old one, and a hash column adds 32 bytes per row plus the index. On a billion-row table that is real disk and buffer pool. - Restores and upgrades expose old tables. Dumps from old servers can carry explicit
ROW_FORMAT=COMPACT, and the target then refuses indexes the old server accepted. Run the query above before any major upgrade; our MySQL 8.0 to 8.4 upgrade guide covers the other checks. - Do one table at a time. Start with small tables to measure the rebuild rate in GB per hour on your hardware, then schedule the largest tables based on those numbers, not on hope.
Key takeaways
- Error 1071 means the index key exceeds the InnoDB limit: 3072 bytes for DYNAMIC and COMPRESSED, 767 bytes for COMPACT and REDUNDANT.
- Lengths in DDL are characters, limits are bytes, and
utf8mb4counts 4 bytes per character. - If the message says 767, convert the table to
ROW_FORMAT=DYNAMIC. - Use prefix indexes only for non-unique lookups; use a hash column for uniqueness on long values.
- Check row formats on every replica before running DDL that the source accepts.
- On large tables, charset conversion blocks writes; use an online schema change tool and plan for disk space and replica lag.