MySQL "Specified key was too long" — Where Does the 3072-Byte Limit Come From?

Root causes of MySQL Error 1071, utf8mb4 4-byte multiplier, prefix indexing tradeoffs, generated hash columns for full uniqueness, and composite index limits.

Reviewed by:Senior Database Reliability Engineer

Tested Versions: MySQL 9.7.4 LTS & 8.4.13 LTS (Debian / Docker) | Tarihsel: MySQL 8.0.36, 5.7

Operational Verification & Reliability Metadata

Last Technical Review: 29 Ekim 2026

Tested VersionsMySQL 9.7.4 LTS & 8.4.13 LTS | Tarihsel: MySQL 8.0.36, 5.7.44

Required PrivilegesALTER TABLE or INDEX creation privileges

Risk Assessment

MediumPrefix indexes only guarantee uniqueness on the leading N characters, risking collisions

Expected OutputClean index creation via prefix indexing or SHA-256 generated columns within 3072-byte limit

Rollback Procedure:Drop prefix index and resize columns under DYNAMIC row format

MySQL 8.4 Reference Manual: InnoDB Limits

First 60 Seconds Emergency Protocol:

The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.

▸Symptoms

plaintext

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

This error surfaces when creating an index on utf8mb4 columns where total byte representation exceeds the InnoDB limit of 3,072 bytes (or 767 bytes under COMPACT/REDUNDANT row formats).

Character Set	Bytes / Char	3072-Byte Boundary
`latin1`	1 byte	3,072 characters
`utf8mb3`	3 bytes	1,024 characters
`utf8mb4`	4 bytes	768 characters

Composite indexes sum all column widths: two varchar(500) utf8mb4 columns equal 4,000 bytes, breaching 3,072 bytes.

▸Resolution Patterns

1. Prefix Indexing

sql

ALTER TABLE uygulamalar ADD INDEX idx_ad (ad(191));

CAUTION

Prefix indexes only enforce uniqueness across the specified length (191 characters), risking hash collisions for long values.

2. Generated SHA-256 Column for Full Uniqueness

sql

ALTER TABLE uygulamalar
  ADD COLUMN ad_hash BINARY(32) GENERATED ALWAYS AS (UNHEX(SHA2(ad, 256))) STORED,
  ADD UNIQUE INDEX uk_ad_hash (ad_hash);

•Companion Guide (PostgreSQL): PostgreSQL index row size exceeds maximum
•Related Error: MySQL Incorrect string value utf8mb4
•Engine Hub: MySQL Production Hub

Other Database Error Triage Playbooks

Other battle-tested diagnostic guides for high-severity database errors.

All Errors (50)

MYSQL4 min