Skip to content
Documentation

MySQL Schema Design and Indexing Essentials

FieldValue
TypeSkill Resource
Source~/.copilot/skills/database/references/mysql.md
DescriptionNot specified

Source Content

MySQL Schema Design and Indexing Essentials

This is a lighter reference for MySQL basics; Postgres is the primary engine in this skill. Expand this reference as MySQL workloads appear.

Schema Design Basics

Naming Conventions

  • Tables: plural, lowercase, snake_case (orders, customer_payments)
  • Columns: lowercase, snake_case (customer_id, created_at)
  • Foreign keys: <table>_id pattern (orders.customer_id, payments.order_id)
  • Indexes: idx_<table>_<column(s)> (idx_orders_customer_date)

Character Sets and Collation

-- Use utf8mb4 for full Unicode support (emoji, etc.)
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Always Use InnoDB

  • InnoDB: ACID, transactions, crash recovery — use this for everything.
  • MyISAM: deprecated; use InnoDB only.

Indexing Strategy

Single-Column B-Tree Index

-- Equality and range queries
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Composite Indexes

Rule: Most selective columns first, then range/sort columns.

-- Query: SELECT * FROM orders WHERE customer_id = ? AND status = 'shipped' ORDER BY created_at DESC
CREATE INDEX idx_orders_customer_status_date
ON orders (customer_id, status, created_at DESC);

Covering Indexes

Include non-key columns to avoid table lookups (MySQL 5.7+):

-- SELECT order_total, status FROM orders WHERE customer_id = ?
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id, order_total, status);

Partial Indexes (MySQL 5.7+, via WHERE clause)

-- Index only active rows
CREATE INDEX idx_active_orders
ON orders (customer_id, created_at)
WHERE status != 'cancelled';

Full-Text Indexes

-- Full-text search on documents
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content LONGTEXT,
    FULLTEXT INDEX ft_title_content (title, content)
);


-- Query
SELECT * FROM articles
WHERE MATCH (title, content) AGAINST ('database performance' IN BOOLEAN MODE);

Foreign Keys and Referential Integrity

CREATE TABLE customers (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    total DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE
) ENGINE=InnoDB;

ON DELETE policies:

  • CASCADE — delete child rows when parent is deleted
  • RESTRICT — block delete if children exist
  • SET NULL — set FK to NULL on parent delete (column must allow NULL)

Normalization (3NF)

Design for normalization first, denormalize only with measured justification.

Bad (denormalized, data redundancy):

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100),      -- Redundant; depends on customer_id
    customer_email VARCHAR(100),     -- Redundant
    order_date DATE,
    total DECIMAL(10, 2)
);

Good (normalized):

CREATE TABLE customers (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_date DATE,
    total DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers (id)
);


-- Composite index on common queries
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

Query Optimization Basics

EXPLAIN FORMAT=JSON

EXPLAIN FORMAT=JSON
SELECT * FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 10\G

Look for:

  • "type": “ref” (good, index) vs “ALL” (bad, full table scan)
  • "rows": estimated rows — if wildly off, run ANALYZE TABLE orders;
  • "key": which index was used (or null if none)

Common Query Patterns

Equality + range:

-- Use index for both filter and sort
SELECT * FROM orders
WHERE customer_id = ? AND created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY created_at DESC;


CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

Top-N per group (LIMIT … OFFSET):

-- Slow: materializes all rows then sorts
SELECT * FROM products
ORDER BY category, price DESC
LIMIT 10 OFFSET 100;


-- Better: cursor pagination
SELECT * FROM products
WHERE (category, price) < (?, ?)  -- Keyset
ORDER BY category DESC, price DESC
LIMIT 11;

Pagination: Keyset Over OFFSET

-- First page: use NULL or 0 for cursor
SELECT * FROM orders
WHERE customer_id = ?
  AND created_at < NOW()  -- Cursor position
ORDER BY created_at DESC
LIMIT 21;  -- Fetch one extra to detect "more"


-- Next page: use last row's created_at as cursor
SELECT * FROM orders
WHERE customer_id = ?
  AND created_at < (last row's created_at)
ORDER BY created_at DESC
LIMIT 21;

Performance Tuning Parameters

-- Check current settings
SHOW VARIABLES LIKE 'innodb_buffer_pool%';
SHOW VARIABLES LIKE 'query_cache%';


-- Typical production recommendations:
-- innodb_buffer_pool_size: 50–80% of available RAM
-- innodb_log_file_size: 1–2 GB for large tables
-- max_connections: adjust per workload (default 151)
-- query_cache_size: 0 (disabled in modern MySQL; use app-level caching)

Monitoring and Maintenance

Table Statistics

-- Refresh statistics for query planner
ANALYZE TABLE orders;


-- Check table size
SELECT
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
FROM information_schema.TABLES
WHERE table_schema = 'your_database'
ORDER BY size_mb DESC;

Slow Query Log

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- Log queries > 2 seconds


-- View slow queries
SHOW GLOBAL STATUS LIKE 'Slow_queries';

Key Differences from PostgreSQL

FeatureMySQLPostgres
IndexesB-tree, Full-text, SPATIALB-tree, GIN, GIST, BRIN, Hash
TransactionsInnoDB only; implicit commit in other enginesDefault; all operations are transactional
JSONLimited; use JSON_EXTRACT, JSON_SETRich JSONB with operators, GIN indexing
Auto-incrementAUTO_INCREMENT columnSERIAL / GENERATED AS IDENTITY
SequencesN/ANative support
Full-textFULLTEXT INDEXto_tsvector, GIN indexes
PartitioningRANGE, LIST, HASHRANGE, LIST, HASH, plus native time partitioning

References