How to Fix MySQL Error 1709: Index Column Size Too Large

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes. is raised when you attempt to create an index (or a unique constraint) on a column whose byte length exceeds the maximum key prefix that InnoDB allows for the active row format. The error symbol is ER_INDEX_COLUMN_TOO_LONG.

Impact

The CREATE TABLE, CREATE INDEX, or ALTER TABLE statement that triggered the error is rolled back and the index is not created. If the statement is part of a schema migration — whether run manually or by an ORM like Django, Rails, Hibernate, or Flyway — the entire migration step fails and may leave the migration history in a partial state requiring manual cleanup.

This error does not affect existing queries against already-created tables; it is a DDL-only error. However, the inability to create the index means the intended uniqueness constraint or query optimization will not be in place until the issue is resolved.

Common Causes

Indexing a long VARCHAR/VARBINARY column with COMPACT or REDUNDANT row format. The default InnoDB row format before MySQL 5.7.9 was COMPACT, which limits index key prefixes to 767 bytes. A VARCHAR(255) with utf8mb4 encoding requires up to 255 × 4 = 1020 bytes, which exceeds the limit.
Using utf8mb4 encoding on a column with a full-column index. Because utf8mb4 uses up to 4 bytes per character, columns that would be within limits under latin1 or utf8 (3 bytes/char) can exceed 767 bytes under utf8mb4. For example, VARCHAR(192) with utf8mb4 is exactly 768 bytes — one byte over the limit.
ORM-generated migrations creating indexes on string columns. Django, Rails, and other frameworks often add unique indexes to email or token columns defined as VARCHAR(254) or longer. With utf8mb4, these exceed 767 bytes under COMPACT row format.
Composite indexes where the combined prefix length exceeds the limit. When creating a multi-column index, the sum of the prefix bytes across all indexed columns must not exceed the limit for the active row format.
Explicit innodb_large_prefix=OFF in older MySQL 5.6 configurations. MySQL 5.6 introduced innodb_large_prefix (default OFF in early 5.6, ON in later patches) to enable up to 3072-byte prefixes for DYNAMIC/COMPRESSED row formats. If this variable is OFF, even DYNAMIC tables are restricted to 767 bytes.

Troubleshooting and Resolution Steps

Check the column definition and encoding that triggered the error.
```
SHOW CREATE TABLE your_table\G
```
Look at the column type, character set, and collation. Multiply the character length by the bytes-per-character for the encoding (utf8mb4 = 4, utf8 = 3, latin1 = 1) to determine the byte length.
Switch the table to DYNAMIC row format (recommended fix for MySQL 5.7.9+).

DYNAMIC row format supports index key prefixes up to 3072 bytes, which accommodates VARCHAR(768) in utf8mb4.
```
ALTER TABLE your_table ROW_FORMAT=DYNAMIC;
```
On MySQL 5.7.9+, innodb_default_row_format defaults to DYNAMIC, so new tables will use it automatically. For older 5.6 instances, also set:
```
SET GLOBAL innodb_large_prefix = ON;
SET GLOBAL innodb_file_format = Barracuda;
```
Then recreate the table or run the ALTER TABLE above to change the row format.
Use a prefix index to index only the first N characters of the column.

If you do not need full-column uniqueness or cannot change the row format, index only a prefix:
```
-- Instead of:
ALTER TABLE users ADD INDEX idx_token (token);

-- Use a prefix index (first 191 characters = 764 bytes in utf8mb4):
ALTER TABLE users ADD INDEX idx_token (token(191));
```
Note that prefix indexes cannot enforce uniqueness for the full column value. If uniqueness is required, DYNAMIC row format is the better solution.
Reduce the column length if the data model allows it.
```
ALTER TABLE users MODIFY COLUMN token VARCHAR(191) CHARACTER SET utf8mb4;
```
191 characters is the commonly-cited "magic number" for utf8mb4 columns that need a full index under COMPACT row format: 191 × 4 = 764 bytes ≤ 767.

Verify the row format and InnoDB settings after the change.

SELECT TABLE_NAME, ROW_FORMAT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'your_table';

SHOW VARIABLES LIKE 'innodb_large_prefix';
SHOW VARIABLES LIKE 'innodb_default_row_format';

Additional Information

The 767-byte limit applies to COMPACT and REDUNDANT row formats. DYNAMIC and COMPRESSED row formats raise the limit to 3072 bytes, provided innodb_large_prefix=ON (MySQL 5.6) or MySQL 5.7.9+ where this is the default.
MyISAM has a separate index key length limit of 1000 bytes, but this error code is specific to InnoDB.
In MySQL 8.0, innodb_large_prefix and innodb_file_format were removed entirely; DYNAMIC is the only supported row format and the 3072-byte limit always applies.
Related error: ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes — this is the equivalent error for some contexts and older MySQL versions. Error 1709 is the InnoDB-specific variant with the same root cause.
When using Django, setting DEFAULT_AUTO_FIELD and database character set to utf8mb4 commonly triggers this error on AbstractUser's email field. The fix is to ensure the MySQL database uses innodb_default_row_format=DYNAMIC in my.cnf.
Alembic and Flyway migrations will halt on this error without rolling back already-applied statements in the same migration file. Always test schema migrations against a production-equivalent MySQL configuration.

Frequently Asked Questions

Why does this only happen with utf8mb4 and not utf8? MySQL's utf8 encoding uses a maximum of 3 bytes per character, so VARCHAR(255) requires up to 765 bytes — just under the 767-byte COMPACT limit. utf8mb4 uses up to 4 bytes per character, pushing VARCHAR(255) to 1020 bytes. This is why switching from utf8 to utf8mb4 (required to support emoji and full Unicode) commonly triggers this error on existing schemas.

Can I just use a prefix index everywhere to avoid this error? A prefix index solves the error, but it cannot enforce a UNIQUE constraint on the full column value — two rows with the same first 191 characters but different suffixes would not be detected as duplicates. For unique constraints on long string columns, switching to DYNAMIC row format is the correct solution.

Will changing the row format lock the table? In MySQL 5.6 and 5.7, ALTER TABLE ... ROW_FORMAT=DYNAMIC performs an in-place rebuild of the table, which acquires a metadata lock and can be slow on large tables. On MySQL 8.0, Online DDL improvements reduce locking. Consider using pt-online-schema-change or gh-ost for large production tables.

Is this error the same as ERROR 1071? They share the same root cause (index key prefix too long) but appear in different contexts. ERROR 1071 (ER_TOO_LONG_KEY) is the general message emitted by certain storage engine paths. ERROR 1709 (ER_INDEX_COLUMN_TOO_LONG) is InnoDB-specific and explicitly names the 767-byte column size limit. Both are resolved the same way.