5 SQL Primary Key Mistakes That Kill Database Performance (+ Fixes)

You’re building a user registration system. Everything works fine with 100 users. Then you hit 10,000 users and your database grinds to a halt. The culprit? A VARCHAR(255) primary key instead of an integer.

Let’s fix the primary key mistakes that crash databases in production.

Mistake 1: Using VARCHAR as Primary Key (When You Don’t Need To)

The Problem:

-- Bad: String primary key for no reason
CREATE TABLE users (
    email VARCHAR(255) PRIMARY KEY,
    username VARCHAR(50),
    created_at TIMESTAMP
);

Why It Fails:

• String comparisons are slower than integer comparisons
• Takes more storage (255 bytes vs 4-8 bytes for INT)
• Foreign key joins become painfully slow

The Fix:

-- Good: Integer primary key
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50),
    created_at TIMESTAMP
);

Real Impact: A client migrated from email-based PKs to integer PKs and saw query performance improve by 60% on a 2-million-row table.

Mistake 2: Forgetting to Make Your Primary Key AUTO_INCREMENT

The Problem:

-- Bad: Manual key management
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    total DECIMAL(10,2)
);

INSERT INTO orders VALUES (1, 101, 49.99);
INSERT INTO orders VALUES (2, 102, 89.99);
-- Developer forgets the next number... conflict incoming

The Fix:

-- Good: Automatic key generation
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    total DECIMAL(10,2)
);

INSERT INTO orders (customer_id, total) VALUES (101, 49.99);
-- Database handles the ID automatically

Mistake 3: Using Meaningful Data as Primary Keys

The Problem:

-- Bad: Social Security Number as PK
CREATE TABLE employees (
    ssn VARCHAR(11) PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

Why This Hurts:

• SSNs can change (identity theft recovery)
• Privacy concerns when visible in URLs
• Cannot reassign if entered incorrectly
• Cascading updates across foreign keys

The Fix:

-- Good: Surrogate key
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    ssn VARCHAR(11) UNIQUE,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

Mistake 4: Composite Keys When You Don’t Need Them

The Problem:

-- Bad: Overcomplicated composite key
CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    semester VARCHAR(20),
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id, semester)
);

Issues:

• Harder to reference in foreign keys
• More complex JOIN conditions
• Difficult to update individual columns

The Fix:

-- Good: Simple surrogate key with unique constraint
CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    course_id INT,
    semester VARCHAR(20),
    enrollment_date DATE,
    UNIQUE KEY unique_enrollment (student_id, course_id, semester)
);

Mistake 5: Not Indexing Foreign Keys That Reference Your Primary Key

The Problem:

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

-- Bad: No index on foreign key
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,  -- Missing index!
    order_date DATE
);

The Fix:

-- Good: Indexed foreign key
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    INDEX idx_customer (customer_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Real-World Example: E-Commerce Product System

Here’s a complete example showing these principles in action:

-- Products table
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL,  -- Business key, but not PK
    product_name VARCHAR(200),
    price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Categories table
CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) UNIQUE NOT NULL
);

-- Product-Category relationship
CREATE TABLE product_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- Simple surrogate key
    product_id INT NOT NULL,
    category_id INT NOT NULL,
    UNIQUE KEY unique_product_category (product_id, category_id),
    INDEX idx_product (product_id),
    INDEX idx_category (category_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE CASCADE
);

-- Insert example data
INSERT INTO products (sku, product_name, price) VALUES 
    ('LAPTOP-2024-001', 'Gaming Laptop Pro', 1299.99),
    ('MOUSE-2024-042', 'Wireless Mouse', 29.99);

INSERT INTO categories (category_name) VALUES 
    ('Electronics'),
    ('Computer Accessories');

INSERT INTO product_categories (product_id, category_id) VALUES 
    (1, 1),  -- Gaming Laptop -> Electronics
    (2, 1),  -- Wireless Mouse -> Electronics
    (2, 2);  -- Wireless Mouse -> Computer Accessories

The Bottom Line

Use integer auto-increment primary keys unless you have a compelling reason not to. Keep business logic in UNIQUE constraints, not primary keys. Your future self (and your database performance) will thank you.