Fix MySQL Error 1071: Specified Key Was Too Long (767 bytes)

This error hits when creating an index on a column too long for InnoDB's 767-byte limit. Quick fix: use a shorter key prefix or switch to a larger row format.

Quick answer for people who know their way around

Change the column to a prefix index like INDEX `name` (`name`(191)) or switch the table to ROW_FORMAT=DYNAMIC and enable innodb_large_prefix. On MySQL 5.7.7+ and 8.0, that's already the default — so just update your table's row format.

Why this error happens

I've seen this error pop up most often when someone tries to create an index on a VARCHAR(255) column using UTF-8. Here's the math: UTF-8 can use up to 3 bytes per character (or 4 for utf8mb4). Multiply 255 characters by 3 bytes, that's 765 bytes. Throw in a second column in a compound index, and boom — you're over 767. InnoDB's old default limit is 767 bytes per index key. MySQL 5.6 and earlier, plus MariaDB up to 10.2, enforce this hard. If you're on MySQL 5.7.7+, the limit is 3072 bytes by default, but older tables still use the old 767 limit.

I know this error is infuriating — you just want to create a simple index and the database says no. But the fix is straightforward. Here's how.

Step-by-step fix

Step 1: Check your MySQL version and settings

SHOW VARIABLES LIKE 'innodb_large_prefix';
SHOW VARIABLES LIKE 'innodb_file_format';
SELECT @@version;

If you're on MySQL 5.5 or 5.6, innodb_large_prefix is OFF by default. On 5.7+, it's ON. MariaDB 10.2.2+ turned it ON by default.

Step 2: Shorten the index with a prefix

This is the quickest fix. Instead of indexing the whole column, index only the first N characters. For a VARCHAR(255) with utf8mb4, use 191 (191 * 4 = 764 bytes). For utf8 (3 bytes), use 255 (255 * 3 = 765 bytes — safe but tight).

ALTER TABLE `your_table` ADD INDEX `idx_name` (`your_column`(191));

If you're creating a new table, define it right the first time:

CREATE TABLE `your_table` (
    `id` INT NOT NULL,
    `name` VARCHAR(255),
    INDEX `idx_name` (`name`(191))
);

Step 3: Switch to DYNAMIC or COMPRESSED row format (MySQL 5.6+)

If prefix indexes aren't an option — say you need the full column indexed — switch the table to use a larger key limit. This assumes you're on MySQL 5.6.3+ or MariaDB 10.0+.

SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_large_prefix = ON;
ALTER TABLE `your_table` ROW_FORMAT = DYNAMIC;
ALTER TABLE `your_table` ADD INDEX `idx_name` (`your_column`);

Note: On MySQL 5.7.7+, innodb_large_prefix is ON and innodb_default_row_format is DYNAMIC — so you just need to run the ALTER TABLE to change the row format on existing tables.

Step 4: Change character set to a smaller one (last resort)

If you don't need Unicode, use latin1 (1 byte per char). This tripped me up the first time too — I didn't realize UTF-8 was the culprit.

ALTER TABLE `your_table` CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE `your_table` ADD INDEX `idx_name` (`your_column`);

Only do this if you're sure your data doesn't have accented characters or emoji. Test first.

Alternative fixes if the main one fails

Use a smaller column type

Do you really need VARCHAR(255)? Maybe VARCHAR(100) works. 100 * 3 = 300 bytes — well under 767.

Create a separate table for the long text and index on an ID

For really long text fields (like product descriptions), store the text in one table and use a foreign key in another. Index on the integer key, not the text.

Upgrade MySQL to 5.7.7+ or MariaDB 10.2.2+

These versions default to 3072-byte key limits. But old tables still need the row format change I showed in Step 3.

Prevention tip

When you create a new table with long VARCHAR columns, set ROW_FORMAT=DYNAMIC from the start. For example:

CREATE TABLE `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(255),
    `bio` VARCHAR(500),
    INDEX `idx_email` (`email`)
) ROW_FORMAT=DYNAMIC;

Also, avoid VARCHAR(255) unless you really need that many characters. Most fields like names and emails fit in fewer. Shorter columns mean smaller indexes and faster queries. Your future self will thank you.

If you're still stuck after these steps, check if your table uses utf8mb4 — that 4-byte character eats up your key space fast. Switch to utf8 (3 bytes) or latin1 (1 byte) if you can. That alone often fixes it.