How to fix ERROR 1070: Too many key parts specified; max 16 parts allowed in MySQL

MySQLINTERMEDIATEMEDIUMUpdated Dec 22, 2025· 134 views

MySQL Error 1070 occurs when creating an index or primary key with more than 16 columns. MySQL enforces a hard limit of 16 key parts per index across all storage engines (InnoDB, MyISAM). Fix by reducing indexed columns or splitting into multiple indexes.

Quick fix

Fixes ERROR 1070: Too many key parts specified; max 16 parts allowed

-- WRONG: This fails with Error 1070
CREATE TABLE orders (
  col1 INT, col2 INT, col3 INT, col4 INT, col5 INT,
  col6 INT, col7 INT, col8 INT, col9 INT, col10 INT,
  col11 INT, col12 INT, col13 INT, col14 INT, col15 INT,
  col16 INT, col17 INT, col18 INT,
  PRIMARY KEY (col1, col2, col3, col4, col5, col6, col7, col8,
               col9, col10, col11, col12, col13, col14, col15,
               col16, col17, col18)  -- 18 parts! Exceeds 16
);

Identify which index exceeds the 16-column limit
Reduce indexed columns to 16 or fewer
Split into multiple indexes if necessary
Fix ORM auto-migrations (Prisma, Sequelize, etc.)

mysql — error

$-- WRONG: This fails with Error 1070
CREATE TABLE orders (
  col1 INT, col2 INT, col3 INT, col4 INT, col5 INT,
  col6 INT, col7 INT, col8 INT, col9 INT, col10 INT,
  col11 INT, col12 INT, col13 INT, col14 INT, col15 INT,
  col16 INT, col17 INT, col18 INT,
  PRIMARY KEY (col1, col2, col3, col4, col5, col6, col7, col8,
               col9, col10, col11, col12, col13, col14, col15,
               col16, col17, col18)  -- 18 parts! Exceeds 16
);
ERROR 1070: Too many key parts specified; max 16 parts allowed

What this error means

MySQL Error 1070 (SQLSTATE: 42000, ER_TOO_MANY_KEY_PARTS) is raised when you attempt to create an index or primary key with more than 16 columns (key parts). This is a hard limit enforced by MySQL and cannot be modified through configuration settings. The 16-column limit applies to all storage engines including InnoDB and MyISAM. It counts each column in the index as one "part," regardless of data type or column size. Even indexes with column prefixes (e.g., INDEX idx (col1(10))) count as one part per column. This error typically appears during CREATE TABLE, ALTER TABLE ADD INDEX, or CREATE INDEX statements. It can also occur during database migrations, ORM auto-migrations, or when importing database dumps from other systems that use different index limits.

Typical symptoms

Error 1070 appears immediately when creating a table with a composite index on more than 16 columns
ALTER TABLE ADD INDEX fails when adding an index spanning more than 16 columns
Database migrations or ORM schema synchronizations fail unexpectedly
Importing a SQL dump from another database system triggers this error
Forward Engineering in MySQL Workbench shows Error 1070 when processing multiple foreign keys as indexes
CREATE INDEX statements fail when specifying more than 16 columns

Common causes

Attempting to create a composite index (multiple columns) with more than 16 columns total
Creating a primary key that includes more than 16 columns
ORMs like Prisma, SQLAlchemy, or Sequelize automatically generating composite indexes from models with many attributes, exceeding the 16-part limit
Importing database schemas from systems with higher index column limits (PostgreSQL, SQL Server allow more parts)
Marking multiple columns (18+) as Foreign Keys in design tools like MySQL Workbench, which creates indexes on each FK
Incrementally adding columns to an existing index without tracking the current count

How to fix "ERROR 1070: Too many key parts specified; max 16 parts allowed"

1Identify which index exceeds the 16-column limit

Review your CREATE TABLE or ALTER TABLE statement to find the problematic index. The error message usually indicates the table name. Look for:

- PRIMARY KEY with many columns
- INDEX or UNIQUE KEY spanning many columns
- Composite constraints on multiple columns

sql

-- WRONG: This fails with Error 1070
CREATE TABLE orders (
  col1 INT, col2 INT, col3 INT, col4 INT, col5 INT,
  col6 INT, col7 INT, col8 INT, col9 INT, col10 INT,
  col11 INT, col12 INT, col13 INT, col14 INT, col15 INT,
  col16 INT, col17 INT, col18 INT,
  PRIMARY KEY (col1, col2, col3, col4, col5, col6, col7, col8,
               col9, col10, col11, col12, col13, col14, col15,
               col16, col17, col18)  -- 18 parts! Exceeds 16
);

If using an ORM, check the generated SQL in your migration files.

2Reduce indexed columns to 16 or fewer

The most straightforward fix is to remove unnecessary columns from the index. Keep only the columns essential for your queries:

sql

-- CORRECT: Limit index to 16 or fewer columns
CREATE TABLE orders (
  col1 INT, col2 INT, col3 INT, col4 INT, col5 INT,
  col6 INT, col7 INT, col8 INT, col9 INT, col10 INT,
  col11 INT, col12 INT, col13 INT, col14 INT, col15 INT,
  col16 INT, col17 INT, col18 INT,
  -- Primary key with only 10 essential columns
  PRIMARY KEY (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10)
);

For composite indexes on existing tables:

sql

-- Drop the problematic index
ALTER TABLE orders DROP INDEX idx_composite;

-- Recreate with fewer columns
ALTER TABLE orders ADD INDEX idx_composite (
  col1, col2, col3, col4, col5, col6, col7, col8, col9, col10
);

Choose columns that are:
- Frequently used in WHERE clauses
- Used in JOIN conditions
- Provide good selectivity (filtering power)

3Split into multiple indexes if necessary

If you need to index many columns for different queries, create separate indexes instead of one massive composite index:

sql

-- Instead of one index with 18 columns:
-- CREATE INDEX idx_big ON orders (col1, col2, ..., col18); -- FAILS

-- Create multiple targeted indexes:
CREATE INDEX idx_search ON orders (col1, col2, col3, col4, col5);
CREATE INDEX idx_reporting ON orders (col6, col7, col8, col9, col10);
CREATE INDEX idx_audit ON orders (col11, col12, col13, col14, col15);
CREATE INDEX idx_archive ON orders (col16, col17);

Multiple targeted indexes often perform better than one large index because:
- MySQL can choose the best index for each query
- Smaller indexes consume less memory and disk space
- Write operations (INSERT, UPDATE, DELETE) are faster with fewer indexes
- Index maintenance is more efficient

4Fix ORM auto-migrations (Prisma, Sequelize, etc.)

If using an ORM that auto-generates indexes, limit the number of indexed columns in your model definition:

Prisma example (remove unnecessary @index decorators):

typescript

// WRONG: Too many indexed fields
model Order {
  col1  String @db.VarChar(255) @index
  col2  String @db.VarChar(255) @index
  col3  String @db.VarChar(255) @index
  // ... up to 18 indexed fields
  col18 String @db.VarChar(255) @index
}

// CORRECT: Reduce to essential fields
model Order {
  col1  String @db.VarChar(255) @index
  col2  String @db.VarChar(255) @index
  col3  String @db.VarChar(255) @index
  col4  String @db.VarChar(255)
  col5  String @db.VarChar(255)
  // Only index fields used in frequent queries
}

For composite indexes, explicitly limit parts:

typescript

model Order {
  col1 String
  col2 String
  col3 String
  // ... more fields
  col18 String
  
  @@index([col1, col2, col3, col4, col5]) // Composite index: 5 parts
}

5Review and remove unnecessary foreign key indexes

MySQL automatically creates indexes on foreign key columns for performance. If you have many FKs, consider whether all are necessary:

sql

-- View all indexes on your table
SHOW INDEXES FROM your_table_name;

-- Drop unnecessary FK constraints and their indexes
ALTER TABLE orders DROP FOREIGN KEY fk_unnecessary_relation;

Alternatively, in design tools like MySQL Workbench:
- Don't mark every column as a Foreign Key just for data integrity
- Only create FKs for critical relationships
- Accept that some referential integrity checks happen at the application level instead of the database

This reduces both the index count and the composite key parts problem.

6Test your fix before deploying to production

After modifying your indexes, verify the CREATE TABLE or ALTER TABLE succeeds:

sql

-- Test in development or staging first
CREATE TABLE orders_new (
  id INT PRIMARY KEY AUTO_INCREMENT,
  col1 INT,
  col2 INT,
  -- ... your columns
  INDEX idx_composite (col1, col2, col3, col4, col5)
);

-- If no error, you're good to proceed
DROP TABLE orders_new;

For migrations using tools like Flyway or Liquibase:
- Run migrations against a staging database first
- Monitor execution time (large index reductions may speed up DDL operations)
- Plan for downtime if altering large production tables (consider pt-online-schema-change or gh-ost)

Advanced notes

The 16-part limit is absolute: Unlike some MySQL configuration variables (max_connections, innodb_buffer_pool_size), the 16-part limit cannot be increased by recompiling MySQL or modifying source code in a supported manner. It's a fundamental constraint of the storage engine architecture.

Prefix indexes still count as one part: If you use column prefixes like INDEX idx (col1(10), col2(20)), each column—regardless of prefix length—counts as one part toward the 16-part limit. The prefix only affects the byte length limit (3072 bytes for DYNAMIC row format), not the part count.

NULL handling: The NULL value in a key column takes 1 byte of storage but doesn't affect the part count. Unique indexes can contain multiple NULL values.

Performance impact: Composite indexes can improve query performance only if queries use the leftmost columns first (left-to-right principle). A 16-part index where queries only reference columns 5-8 won't be used efficiently. Benchmark before and after index changes.

Secondary index limit: A table can have a maximum of 64 secondary indexes (Error 1069). Combined with the 16-part limit per index, this means you cannot index every combination of columns in a large schema. Choose indexes strategically based on actual query patterns.

InnoDB key length limit: Even with fewer than 16 parts, the total key length is limited to 3072 bytes (DYNAMIC/COMPRESSED row format) or 767 bytes (COMPACT/REDUNDANT). Overly large VARCHAR columns in a composite index may hit this byte limit before hitting the 16-part limit.

Frequently asked questions

What does "ERROR 1070: Too many key parts specified; max 16 parts allowed" mean?: MySQL Error 1070 (SQLSTATE: 42000, ER_TOO_MANY_KEY_PARTS) is raised when you attempt to create an index or primary key with more than 16 columns (key parts). This is a hard limit enforced by MySQL and cannot be modified through configuration settings. The 16-column limit applies to all storage engines including InnoDB and MyISAM.
How do I fix "ERROR 1070: Too many key parts specified; max 16 parts allowed"?: MySQL Error 1070 occurs when creating an index or primary key with more than 16 columns. MySQL enforces a hard limit of 16 key parts per index across all storage engines (InnoDB, MyISAM). Fix by reducing indexed columns or splitting into multiple indexes.
What causes "ERROR 1070: Too many key parts specified; max 16 parts allowed"?: Attempting to create a composite index (multiple columns) with more than 16 columns total Creating a primary key that includes more than 16 columns ORMs like Prisma, SQLAlchemy, or Sequelize automatically generating composite indexes from models with many attributes, exceeding the 16-part limit
Is the "ERROR 1070: Too many key parts specified; max 16 parts allowed" error serious?: It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading

📚MySQL Official Documentation
📖
MySQL 8.0 Reference Manual - Multiple-Column Indexes(Official Docs)
📖
MySQL 8.0 Reference Manual - InnoDB Limits(Official Docs)
📖
MariaDB Knowledge Base - Too many key parts specified(Official Docs)
Galaxy Error Guide - MySQL Error 1070
MySQL Forums Discussion - Error 1070