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

-- 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
);

-- 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)
);

-- 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
);

-- 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);

// 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
}

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

-- 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;

-- 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;