MySQL in Production: Real-World Patterns and Best Practices

Introduction

MySQL powers production systems across virtually every industry. This article explores real-world use cases with practical implementation patterns, covering web applications, e-commerce, content management, and multi-tenant architectures.

Web Applications

User Management Schema

-- Users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    avatar_url VARCHAR(500),
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    INDEX idx_email (email),
    INDEX idx_username (username),
    INDEX idx_created_at (created_at)
);

-- User sessions
CREATE TABLE user_sessions (
    session_id VARCHAR(64) PRIMARY KEY,
    user_id INT NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    expires_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_expires (expires_at)
);

-- Password reset tokens
CREATE TABLE password_resets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token VARCHAR(64) UNIQUE NOT NULL,
    used BOOLEAN DEFAULT FALSE,
    expires_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_token (token),
    INDEX idx_user (user_id)
);

Blog/Content System

-- Posts
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    author_id INT NOT NULL,
    title VARCHAR(500) NOT NULL,
    slug VARCHAR(500) UNIQUE NOT NULL,
    excerpt TEXT,
    content LONGTEXT,
    featured_image VARCHAR(500),
    status VARCHAR(20) DEFAULT 'draft',
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id),
    INDEX idx_slug (slug),
    INDEX idx_status_published (status, published_at),
    FULLTEXT idx_search (title, content)
);

-- Categories
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    parent_id INT NULL,
    description TEXT,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_slug (slug),
    INDEX idx_parent (parent_id)
);

-- Post categories (many-to-many)
CREATE TABLE post_categories (
    post_id INT NOT NULL,
    category_id INT NOT NULL,
    PRIMARY KEY (post_id, category_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

-- Tags
CREATE TABLE tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL,
    INDEX idx_slug (slug)
);

-- Post tags (many-to-many)
CREATE TABLE post_tags (
    post_id INT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

-- Comments
CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT,
    parent_id INT NULL,
    content TEXT NOT NULL,
    is_approved BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE,
    INDEX idx_post (post_id),
    INDEX idx_approved (is_approved)
);

E-commerce Platforms

Product Catalog

-- Products
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    category_id INT,
    brand_id INT,
    price DECIMAL(10, 2) NOT NULL,
    cost DECIMAL(10, 2),
    weight DECIMAL(8, 2),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id),
    FOREIGN KEY (brand_id) REFERENCES brands(id),
    INDEX idx_sku (sku),
    INDEX idx_category (category_id),
    INDEX idx_price (price)
);

-- Product inventory
CREATE TABLE inventory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    warehouse_id INT NOT NULL,
    quantity INT DEFAULT 0,
    reserved_quantity INT DEFAULT 0,
    available_quantity INT GENERATED ALWAYS AS (quantity - reserved_quantity) STORED,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    UNIQUE KEY uk_product_warehouse (product_id, warehouse_id)
);

-- Product images
CREATE TABLE product_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    url VARCHAR(500) NOT NULL,
    alt_text VARCHAR(255),
    sort_order INT DEFAULT 0,
    is_primary BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_product (product_id)
);

-- Product variants
CREATE TABLE product_variants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(255),
    price DECIMAL(10, 2),
    attributes JSON,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_product (product_id)
);

Order Management

-- Orders
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(20) UNIQUE NOT NULL,
    user_id INT,
    status VARCHAR(20) DEFAULT 'pending',
    subtotal DECIMAL(10, 2) NOT NULL,
    tax_amount DECIMAL(10, 2) DEFAULT 0,
    shipping_amount DECIMAL(10, 2) DEFAULT 0,
    discount_amount DECIMAL(10, 2) DEFAULT 0,
    total_amount DECIMAL(10, 2) NOT NULL,
    currency CHAR(3) DEFAULT 'USD',
    shipping_address JSON,
    billing_address JSON,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    shipped_at TIMESTAMP NULL,
    delivered_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_order_number (order_number),
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_created (created_at)
);

-- Order items
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    variant_id INT,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (variant_id) REFERENCES product_variants(id),
    INDEX idx_order (order_id)
);

-- Order status history
CREATE TABLE order_status_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INT,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_order (order_id)
);

Logging and Analytics

Application Logging

-- Application logs
CREATE TABLE logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    level ENUM('DEBUG', 'INFO', 'WARNING', 'ERROR', 'CRITICAL') NOT NULL,
    logger VARCHAR(100),
    message TEXT,
    context JSON,
    user_id INT,
    ip_address VARCHAR(45),
    user_agent VARCHAR(500),
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    INDEX idx_level (level),
    INDEX idx_logger (logger),
    INDEX idx_created (created_at),
    INDEX idx_user (user_id)
) ENGINE=ARCHIVE;

-- Query logs
CREATE TABLE query_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    query TEXT NOT NULL,
    duration_ms INT,
    rows_affected INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_created (created_at)
);

Analytics Tables

-- Page views
CREATE TABLE page_views (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    session_id VARCHAR(64),
    page_url VARCHAR(500) NOT NULL,
    referrer_url VARCHAR(500),
    user_agent VARCHAR(500),
    ip_address VARCHAR(45),
    country VARCHAR(2),
    device_type ENUM('desktop', 'tablet', 'mobile'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_page (page_url),
    INDEX idx_user (user_id),
    INDEX idx_session (session_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- Daily aggregations
CREATE TABLE daily_metrics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    page_url VARCHAR(500),
    views INT DEFAULT 0,
    unique_visitors INT DEFAULT 0,
    bounce_count INT DEFAULT 0,
    avg_session_duration INT DEFAULT 0,
    UNIQUE KEY uk_date_url (date, page_url),
    INDEX idx_date (date)
);

-- Update daily metrics (scheduled job)
INSERT INTO daily_metrics (date, page_url, views, unique_visitors)
SELECT 
    DATE(created_at) as date,
    page_url,
    COUNT(*) as views,
    COUNT(DISTINCT user_id) as unique_visitors
FROM page_views
WHERE DATE(created_at) = CURRENT_DATE
ON DUPLICATE KEY UPDATE 
    views = VALUES(views),
    unique_visitors = VALUES(unique_visitors);

Multi-Tenant SaaS

Tenant Isolation

-- Tenants (for multi-tenant apps)
CREATE TABLE tenants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    domain VARCHAR(255),
    plan VARCHAR(50) DEFAULT 'free',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_slug (slug),
    INDEX idx_domain (domain)
);

-- Tenant-specific tables using tenant_id
CREATE TABLE tenant_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    INDEX idx_tenant (tenant_id)
);

-- View for current tenant (application sets this)
-- Application uses: SET @current_tenant_id = 1;

-- Row-level security via triggers
DELIMITER //
CREATE TRIGGER tenant_isolation_insert
BEFORE INSERT ON tenant_data
FOR EACH ROW
BEGIN
    IF @current_tenant_id IS NOT NULL THEN
        SET NEW.tenant_id = @current_tenant_id;
    END IF;
END //
DELIMITER ;

Sharding Strategy

-- Application-level sharding
-- Shard key: user_id

-- User shard 1 (users 1-1000000)
CREATE TABLE users_1 (
    id INT PRIMARY KEY,
    -- user data
) ENGINE=InnoDB;

-- User shard 2 (users 1000001-2000000)
CREATE TABLE users_2 (
    id INT PRIMARY KEY,
    -- user data
) ENGINE=InnoDB;

-- Shard routing (application logic)
def get_shard(user_id):
    shard_num = (user_id - 1) // 1000000 + 1
    return f"users_{shard_num}"

Time-Series Data

IoT Data Collection

-- Time-series data for IoT
CREATE TABLE sensor_data (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sensor_id VARCHAR(50) NOT NULL,
    timestamp DATETIME(6) NOT NULL,
    value DECIMAL(10, 4),
    unit VARCHAR(20),
    quality ENUM('good', 'uncertain', 'bad') DEFAULT 'good',
    INDEX idx_sensor (sensor_id),
    INDEX idx_timestamp (timestamp),
    INDEX idx_sensor_time (sensor_id, timestamp)
) ENGINE=InnoDB;

-- Hourly aggregations
CREATE TABLE hourly_sensor_aggregates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sensor_id VARCHAR(50) NOT NULL,
    hour DATETIME NOT NULL,
    min_value DECIMAL(10, 4),
    max_value DECIMAL(10, 4),
    avg_value DECIMAL(10, 4),
    count INT,
    UNIQUE KEY uk_sensor_hour (sensor_id, hour)
);

-- Calculate aggregates (scheduled)
INSERT INTO hourly_sensor_aggregates (sensor_id, hour, min_value, max_value, avg_value, count)
SELECT 
    sensor_id,
    DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00') as hour,
    MIN(value),
    MAX(value),
    AVG(value),
    COUNT(*)
FROM sensor_data
WHERE timestamp >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')
GROUP BY sensor_id, hour
ON DUPLICATE KEY UPDATE
    min_value = VALUES(min_value),
    max_value = VALUES(max_value),
    avg_value = VALUES(avg_value),
    count = VALUES(count);

Best Practices Summary

Use Case	Key Features	MySQL Solution
Web Apps	User management, sessions	InnoDB, indexes
E-commerce	Inventory, orders	Transactions, constraints
CMS	Content, search	FULLTEXT, JSON
Logging	High write volume	ARCHIVE engine
Analytics	Aggregations	Summary tables
Multi-tenant	Isolation	tenant_id + RLS
Time-series	IoT, metrics	Datetime indexes

Performance Optimization

Query Optimization

-- Use covering indexes
CREATE INDEX idx_products_cover 
ON products (category_id, is_active, price) 
INCLUDE (name, description);

-- Partial indexes (MySQL 8.0.20+)
CREATE INDEX idx_active_users 
ON users(email) WHERE is_active = TRUE;

-- Composite index order
-- Put equality conditions first, range last
CREATE INDEX idx_orders_status_user 
ON orders(status, user_id, created_at);

Caching Strategy

-- Query result caching
-- Note: Query cache removed in MySQL 8.0

-- Use application-level caching (Redis, Memcached)
-- Or use MySQL HeatWave for analytics

Conclusion

MySQL’s versatility makes it suitable for virtually any production use case. From web applications handling millions of users to e-commerce platforms processing transactions, from content management systems to IoT data collection, MySQL delivers reliability, performance, and flexibility.

Combined with its ecosystem of tools (MySQL InnoDB Cluster, ProxySQL, MySQL Shell), MySQL continues to be a top choice for production database deployments in 2026.