Introduction

MySQL error 1071 means the index you are trying to create would require more bytes than the storage engine or table format allows. The important word is bytes, not characters, so character set choice and indexed column lengths matter just as much as the number of columns in the key.

Why This Error Happens

Consider a table definition like this:

1CREATE TABLE users (
2    email VARCHAR(255) NOT NULL,
3    tenant VARCHAR(255) NOT NULL,
4    provider VARCHAR(255) NOT NULL,
5    INDEX idx_user_lookup (email, tenant, provider)
6) CHARACTER SET utf8mb4;

With utf8mb4, each character can use up to 4 bytes. That means a long VARCHAR index can consume far more space than it appears to from the column length alone.

A single VARCHAR(255) under utf8mb4 can require up to 1020 bytes of index space in the worst case. A multi-column key can therefore exceed the limit quickly.

The Limit Depends on Engine and Format

The exact maximum key length depends on your MySQL or MariaDB version, storage engine, row format, and character set. The error message tells you the effective limit that applied in your case.

So if the message says 1000 bytes, that is the constraint you need to solve against for that table and environment.

This is why advice copied from another blog post can be misleading if it assumes a different engine or a different InnoDB configuration.

Common Fix: Reduce Indexed Length

The first and most common fix is to shorten the indexed columns or use index prefixes for long strings.

Example:

1CREATE TABLE users (
2    email VARCHAR(255) NOT NULL,
3    tenant VARCHAR(255) NOT NULL,
4    provider VARCHAR(255) NOT NULL,
5    INDEX idx_user_lookup (email(100), tenant(50), provider(50))
6) CHARACTER SET utf8mb4;

A prefix index stores only the first part of each string for the index. That can bring the total byte count under the limit.

The tradeoff is that shorter prefixes may reduce index selectivity, so test query performance rather than assuming the change is free.

Another Fix: Use Shorter or More Appropriate Data Types

Sometimes the real issue is schema design. If a column stores short codes, slugs, or normalized identifiers, using VARCHAR(255) everywhere may be excessive.

Possible improvements include:

• shorter VARCHAR lengths
• integer surrogate keys for relationships
• separate lookup tables for long textual values
• indexes only on the columns actually needed by queries

A smaller and more intentional schema often fixes the error without relying on prefix indexing at all.

Character Set Choice Matters

Changing from utf8mb4 to a narrower character set reduces index byte usage, but that should be a conscious functional decision, not just an index workaround.

If your application needs full Unicode support, especially emoji and supplementary characters, changing away from utf8mb4 may not be acceptable.

In most modern systems, it is better to keep the correct character set and redesign the index than to weaken text support just to satisfy a byte limit.

Composite Indexes Need Special Attention

Error 1071 often appears on composite indexes because the total indexed bytes across all indexed columns count toward the limit.

That means three medium-sized string columns can be worse than one long column. Review whether every column in the composite index is necessary and whether the ordering matches real query patterns.

Sometimes splitting one oversized composite index into two better-targeted indexes is the more useful fix.

Common Pitfalls

The most common mistake is thinking in characters instead of bytes. In MySQL indexing, byte size is what matters.

Another issue is applying prefix lengths blindly without checking whether the resulting index is still selective enough for the queries you care about.

People also assume the same key-length limit exists everywhere. It varies across versions and storage configurations, so always trust the limit reported by the actual environment.

Finally, do not respond by dropping useful indexes entirely without checking the query plans. The goal is to reshape the index, not necessarily to remove it.

Summary

• Error 1071 means the index definition exceeds the byte limit for that environment.
• Character set and VARCHAR length strongly affect index size.
• Prefix indexes, shorter columns, and better schema design are common fixes.
• Composite indexes can exceed limits even when individual columns look reasonable.
• Treat the reported byte limit in the error message as the real constraint to solve.