How to fix ERROR 1069: Too many keys specified; max 64 keys allowed in MySQL

Run this query to see how many indexes your table currently has:

SHOW INDEX FROM your_table_name;

This output shows:
- Each index (including PRIMARY, UNIQUE, FOREIGN KEY indexes)
- The column(s) each index covers
- The index type and size

Count the number of distinct index names. If you see 64 or more unique index names, you have hit the limit.

Review the index list and look for:
- Duplicate indexes covering the same columns
- Overlapping indexes where one is a prefix of another (e.g., INDEX(a) and INDEX(a, b))
- Indexes on foreign key columns that already have indexes through the foreign key constraint itself

For example, if you have both:

INDEX idx_user_id ON orders(user_id);
FOREIGN KEY (user_id) REFERENCES users(id);

The foreign key already creates an index, so the explicit index is redundant.

Drop indexes that are duplicated or unnecessary:

DROP INDEX index_name ON your_table_name;

After dropping each index, verify it was removed:

SHOW INDEX FROM your_table_name;

Typically, you can safely remove:
- Duplicate indexes
- Single-column indexes that are subsets of composite indexes
- Indexes created by the ORM that you don't actually query on

Instead of creating separate indexes for each column you query, combine them:

Before (4 separate indexes):

CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_created_at ON orders(created_at);
CREATE INDEX idx_amount ON orders(amount);

After (1-2 composite indexes):

CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);
CREATE INDEX idx_amount ON orders(amount);

Composite indexes are more efficient—they reduce total index count while still supporting your queries (as long as query columns match the index column order).

If you are using Django, Laravel, Prisma, or another ORM:

Prisma example:
Instead of creating an index for every foreign key:

model Order {
  id          String  @id
  userId      String  @db.VarChar(255)
  user        User    @relation(fields: [userId], references: [id])
  // Don't add: @@index([userId]) — the foreign key index is enough
}

Django example:
In your model, use db_index=False on foreign keys that don't need explicit indexing:

class Order(models.Model):
    user = ForeignKey(User, on_delete=models.CASCADE, db_index=False)

Edit your migration files to remove redundant index declarations before applying them.

Once you have removed or consolidated indexes, recount them:

SHOW INDEX FROM your_table_name;

The unique index count should now be below 64. If you are still above 64 and cannot remove more indexes without breaking application queries, consider the advanced options below.