阅读中文版本

MySQL ERROR 1071 — Specified Key Was Too Long

Fix MySQL 1071 by prefix-indexing long columns or reducing key byte length, since InnoDB limits the index key size.

mysql -e "CREATE TABLE t (a VARCHAR(1000), INDEX(a))"

mysql -e "SHOW FULL COLUMNS FROM t\G" | grep -i collation

Symptoms

The error “MySQL ERROR 1071 — Specified Key Was Too Long” points at a specific failure mode. This guide documents the symptoms you can observe, the commands that isolate the cause, the root cause, the fix, and the checks that prove the fix works.

Observed symptoms include:

• ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
• Adding a composite unique index on VARCHAR columns fails
• Works with latin1 but fails after switching to utf8mb4

Reproduce and Diagnose

Reproduce the failure in isolation before changing any code. Work through the checks below in order; each one narrows the search space.

Step 1: Read the max key length in the error

mysql -e "CREATE TABLE t (a VARCHAR(1000), INDEX(a))"

The error states the byte limit, which depends on row format and column charset.

Step 2: Check the charset byte multiplier

mysql -e "SHOW FULL COLUMNS FROM t\G" | grep -i collation

utf8mb4 explains a 4x multiplier; a smaller charset or prefix index reduces the size.

Likely contributors, in order of frequency:

• A VARCHAR column large enough to exceed the index key byte limit is indexed fully
• utf8mb4 uses up to 4 bytes per character, quadrupling the key size
• A composite index across several long columns exceeds the limit collectively

Root Cause

InnoDB limits the index key to 3072 bytes (with DYNAMIC row format). A full-column index on a wide utf8mb4 VARCHAR can exceed this, so CREATE/ALTER fails with 1071.

The Fix

Index a prefix (INDEX(col(191))), choose a smaller charset where appropriate, use a hash column for long values, and keep composite keys within the byte budget.

Watch out: Prefix indexes cannot enforce full-column uniqueness or support sorting by the full value; verify semantics before using them.

ALTER TABLE users
  ADD INDEX idx_email (email(191));    -- prefix within byte budget

-- unique on full value via a generated hash column
ALTER TABLE users
  ADD COLUMN email_hash CHAR(64) AS (SHA2(email, 256)) STORED,
  ADD UNIQUE KEY uq_email_hash (email_hash);

-- check the resulting index
SHOW INDEX FROM users;

Verify and Prevent

Recreate the index with a prefix and confirm success; run EXPLAIN and confirm the index is used for the intended queries.

Once verified, add a regression test that reproduces the original symptom so the failure cannot silently return.

This class of failure is usually environmental rather than pure logic, so capture the exact command, the environment variables, and the working directory in your bug report. Reproduce it in the smallest possible script, then widen the scope only once the minimal case is understood. A short written checklist for this failure mode saves hours the next time it appears.