How to fix ERROR 1071: Specified key was too long in MySQL

-- See full version information
SELECT VERSION();

-- See if large index support is built-in
SHOW VARIABLES LIKE 'innodb_large_prefix';

-- View table information
SHOW CREATE TABLE your_table_name\G

-- Or check row format directly
SELECT TABLE_NAME, ENGINE, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'your_table_name';

-- Original failing table (with utf8mb4)
-- CREATE TABLE users (
--   id INT PRIMARY KEY,
--   email VARCHAR(255) UNIQUE,  -- 255 × 4 = 1020 bytes, exceeds 767 limit
--   name VARCHAR(255),
--   UNIQUE KEY idx_email (email)  -- FAILS
-- );

-- Fixed version using prefix indexes
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255),
  name VARCHAR(255),
  UNIQUE KEY idx_email (email(191))  -- Index only first 191 chars
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- For composite indexes (multiple columns)
CREATE TABLE posts (
  id INT PRIMARY KEY,
  title VARCHAR(255),
  slug VARCHAR(255),
  category VARCHAR(100),
  UNIQUE KEY idx_slug (slug(100)),  -- First 100 chars of slug
  INDEX idx_title_cat (title(75), category(50))  -- Composite index with prefixes
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Or create indexes separately
CREATE UNIQUE INDEX idx_email ON users (email(191));
CREATE INDEX idx_title_category ON posts (title(75), category(50));

-- After creating table successfully, check the index
SHOW CREATE TABLE users\G

-- See actual index information
SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_NAME = 'users' AND COLUMN_NAME = 'email';

-- Create table with DYNAMIC row format
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  name VARCHAR(255),
  UNIQUE KEY idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

-- Create another table
CREATE TABLE posts (
  id INT PRIMARY KEY,
  title VARCHAR(255),
  content TEXT,
  slug VARCHAR(255) UNIQUE,
  UNIQUE KEY idx_slug (slug)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4;

-- Change existing table row format
ALTER TABLE users ROW_FORMAT=DYNAMIC;

-- This may take time on large tables
-- Check if it completed
SHOW CREATE TABLE users\G

-- Check row format was applied
SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'users';

# Linux - find and edit my.cnf
sudo nano /etc/mysql/my.cnf
# or
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# macOS (Homebrew)
nano /usr/local/etc/my.cnf

# Windows - edit my.ini
# Typically in: C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

[mysqld]
innodb_file_format=barracuda
innodb_file_per_table=on
innodb_large_prefix=on

[mysqld]
# Performance settings
max_connections=100
default-storage-engine=InnoDB

# Large index support
innodb_file_format=barracuda      # Enable Barracuda file format
innodb_file_per_table=on          # Create separate tablespace per table
innodb_large_prefix=on            # Enable large prefix indexes (3072 bytes)

# Character set
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

# Linux/systemd
sudo systemctl restart mysql

# macOS (Homebrew)
brew services restart mysql

# Windows (Command Prompt as Administrator)
net stop MySQL57
net start MySQL57

# Or direct startup
mysqld --console

-- Check if large prefix is enabled
SHOW VARIABLES LIKE 'innodb_large_prefix';

-- Check file format
SHOW VARIABLES LIKE 'innodb_file_format';

-- Check file per table
SHOW VARIABLES LIKE 'innodb_file_per_table';

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  name VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

-- See actual max length used in email column
SELECT MAX(CHAR_LENGTH(email)) as max_email_length FROM users;

-- For other columns
SELECT MAX(CHAR_LENGTH(name)) as max_name_length FROM users;
SELECT MAX(CHAR_LENGTH(category)) as max_category_length FROM products;

-- If emails are typically 50-100 chars, reduce from 255
ALTER TABLE users MODIFY email VARCHAR(100) CHARSET utf8mb4;

-- Calculate byte size: 100 × 4 = 400 bytes (under 767 limit)

-- For composite indexes, reduce both columns
ALTER TABLE posts
  MODIFY title VARCHAR(150) CHARSET utf8mb4,
  MODIFY slug VARCHAR(100) CHARSET utf8mb4;

-- Byte calculation: (150 × 4) + (100 × 4) = 1000 bytes
-- Still over 767 limit! Need to reduce further or use prefix indexes

-- Before (fails with error 1071)
CREATE TABLE ecommerce_products (
  id INT PRIMARY KEY,
  sku VARCHAR(255) UNIQUE,           -- Max SKU length in practice: 50 chars
  category VARCHAR(255),              -- Max category: 80 chars
  description VARCHAR(255),           -- Not indexed
  UNIQUE KEY idx_sku (sku),           -- sku: 255 × 4 = 1020 bytes - FAILS
  KEY idx_category (category)         -- category: 255 × 4 = 1020 bytes - FAILS
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- After (uses actual data requirements)
CREATE TABLE ecommerce_products (
  id INT PRIMARY KEY,
  sku VARCHAR(50) UNIQUE,             -- 50 × 4 = 200 bytes
  category VARCHAR(80),                -- 80 × 4 = 320 bytes
  description VARCHAR(255),            -- Not indexed, so size doesn't matter
  UNIQUE KEY idx_sku (sku),           -- sku: 200 bytes - OK
  KEY idx_category (category)         -- category: 320 bytes - OK
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create with latin1 (no UTF-8 support)
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  name VARCHAR(255),
  UNIQUE KEY idx_email (email)      -- 255 × 1 = 255 bytes - works fine
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- This converts all text columns
ALTER TABLE users CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;

-- Or for specific columns
ALTER TABLE users MODIFY email VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci;

# If using a migration tool
npm run migrate
# or
python manage.py migrate
# or
php artisan migrate

# Or run your SQL directly
mysql -u username -p database_name < schema.sql

-- View table structure
SHOW CREATE TABLE your_table_name\G

-- Check all indexes on the table
SHOW INDEX FROM your_table_name;

-- View detailed index information
SELECT
  TABLE_NAME,
  INDEX_NAME,
  COLUMN_NAME,
  SEQ_IN_INDEX,
  KEY_LEN,
  NULL_FLAG,
  INDEX_TYPE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_NAME = 'your_table_name'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

-- KEY_LEN shows bytes used by index
-- For email(191) with utf8mb4: should show 764 or less
-- For email(255) with utf8mb4 and DYNAMIC: should show 1020

-- Example output interpretation:
-- KEY_LEN = 764 means 191 characters × 4 bytes
-- KEY_LEN = 1020 means 255 characters × 4 bytes
-- KEY_LEN = 767 means 255 characters × 3 bytes utf8 (not utf8mb4)

-- Insert test data
INSERT INTO users (id, email, name) VALUES (1, '[email protected]', 'Test User');

-- Verify unique constraint works
INSERT INTO users (id, email, name) VALUES (2, '[email protected]', 'Another User');
-- Should fail: Duplicate entry for key 'idx_email'

-- Check query uses the index
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- Should show index in "possible_keys" and "key" columns

-- Check table statistics
SHOW TABLE STATUS LIKE 'users'\G

-- Check row count
SELECT COUNT(*) FROM users;

-- Test query performance
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = '[email protected]'\G

-- Double-check your fix was applied
SHOW CREATE TABLE your_table_name\G

-- Verify MySQL variables
SHOW VARIABLES LIKE 'innodb_%';
SHOW VARIABLES LIKE 'character_set%';

-- Check server version
SELECT VERSION();