Schema Design Best Practices

MySQL Schema Design Guide

  Based on MySQL 8.0 / 8.4
  15 Topics
  ~35 min read

Schema design is the foundation of database performance. Wrong data type choices can waste tens of gigabytes of storage, inappropriate normalization may require dozens of JOINs per query, and over-denormalization leads to data inconsistency. This chapter starts from storage internals to systematically cover the memory/disk cost of each data type, practical application of normalization theory, the benefits and costs of denormalization, and complete schema design case studies for e-commerce and social network scenarios.

1. Schema Design Principles Overview

Before discussing specific data types, we need to understand a few core principles. These principles run through the entire schema design process, and violating any of them can lead to serious performance or maintenance issues.

1.1 Smaller is Better

Smaller data types mean: less disk I/O, more rows fit in a Buffer Pool page, faster sorting and comparison, and smaller index trees. The more rows a 16KB InnoDB page can hold, the more useful data a single random I/O retrieves. This yields the highest return of all optimizations.

1.2 Simpler is Better

Integer comparison is cheaper than string comparison. Use MySQL's built-in DATE/DATETIME types for dates, not strings. Use integers for IP addresses (INET_ATON()/INET_NTOA()), not VARCHAR(15). Simple types not only save space but also let the optimizer make better execution plans.

1.3 Avoid NULL (Unless Truly Needed)

NULL columns require extra bitmap space in InnoDB to mark which columns are NULL. Nullable columns make index statistics and value comparisons more complex. If a column always has a value in your business logic, declare it NOT NULL with a meaningful default. But don't replace NULL with empty strings or magic numbers (-1, 0, 9999) — that causes worse semantic confusion.

1.4 Design for Query Patterns, Not Storage Patterns

First clarify the read/write ratio and core query paths, then decide how to organize table structures. A write-heavy logging system and a read-heavy product display page should have completely different schema designs even if the underlying data model is the same.

Core Mindset: Schema design is a trade-off between "storage efficiency", "query performance", "development convenience", and "data consistency". There is no universal solution, only optimal solutions under given constraints.

2. Integer Types: INT vs BIGINT vs TINYINT

Integer types are the most commonly used data types in MySQL. Choosing the correct integer width directly affects storage space and index size.

2.1 INT vs BIGINT: When to Upgrade?

For auto-increment primary keys, INT UNSIGNED maxes out at ~4.29 billion, which is sufficient for most businesses. But BIGINT is required in these scenarios:

• Distributed IDs (Snowflake): 64-bit IDs exceed INT range
• Log/event tables: thousands of writes per second, will exceed 4.29B over years
• Merged shards: multiple shards each with 4.29B ID space, may collide after merge

Storage Cost Calculation: Upgrading from INT (4B) to BIGINT (8B) adds 4 bytes per row for the PK. If you have 5 secondary indexes (each leaf stores the PK), the actual overhead is 4 x (1 + 5) = 24 bytes per row. For 100 million rows, that is 2.4 GB extra storage. Do this calculation at design time.

2.2 What Does the Number in INT(11) Mean?

The 11 in INT(11) does not affect storage or value range. It only specifies display width when ZEROFILL is used. MySQL 8.0.17+ deprecated this syntax. Just write INT or INT UNSIGNED when creating tables — no width needed.

-- Bad: misleading width specifiers (deprecated in 8.0.17+)
CREATE TABLE bad_example (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  status TINYINT(1),  -- does NOT mean boolean; still stores -128..127
  PRIMARY KEY (id)
);

-- Good: clean, modern syntax
CREATE TABLE good_example (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  status TINYINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

2.3 UNSIGNED Pitfalls

Subtraction on UNSIGNED columns may produce overflow errors. For example, subtracting two UNSIGNED columns where the result would be negative causes an error when NO_UNSIGNED_SUBTRACTION SQL mode is off. MySQL 8.0+ default SQL mode includes this protection, but be aware of cross-version compatibility.

-- Potential issue with UNSIGNED subtraction
SELECT col_a - col_b FROM t WHERE col_a < col_b;
-- If both are UNSIGNED and col_a < col_b → ERROR 1690 or huge number

-- Safe approach: CAST to SIGNED
SELECT CAST(col_a AS SIGNED) - CAST(col_b AS SIGNED) FROM t;

3. String Types: CHAR vs VARCHAR vs TEXT

3.1 Storage Mechanism Differences

3.2 When to Use CHAR

CHAR is ideal for storing values of fixed or nearly fixed length:

• MD5 hashes: CHAR(32)
• ISO country codes: CHAR(2)
• UUID (without hyphens): CHAR(32) or better BINARY(16)
• Y/N flags: CHAR(1)

3.3 VARCHAR Length Selection Strategy

Although VARCHAR(255) and VARCHAR(1000) consume the same disk space for a 10-character string, the declared length still matters for these reasons:

1. Memory allocation: MySQL's in-memory temp tables (MEMORY engine) allocate fixed space based on VARCHAR's max declared length. VARCHAR(1000) uses 4000 bytes/row in utf8mb4 temp tables
2. Sort buffer: sort_buffer also allocates based on declared length
3. Length prefix overhead: N ≤ 255 uses 1-byte prefix, N > 255 uses 2-byte prefix

Best Practice: Set VARCHAR to the actual maximum business length, keeping it under 255 when possible for the 1-byte length prefix. Username VARCHAR(50), email VARCHAR(254) (RFC 5321), URL VARCHAR(2083) (IE max URL length).

3.4 TEXT vs VARCHAR: When to Use TEXT?

The core difference between TEXT and VARCHAR is overflow behavior. When data exceeds InnoDB's inline storage limit (DYNAMIC format: ~768 byte prefix + overflow pointer), both overflow to external pages. But TEXT columns cannot have default values (before MySQL 8.0.13) and cannot be part of in-memory temp tables (forcing on-disk temp tables).

Choose VARCHAR When...

• You need to index the entire column
• Length is within 65,535 and predictable
• You need default values
• Column participates in ORDER BY / GROUP BY

Choose TEXT When...

• Storing articles, logs, comments
• Length varies greatly (few bytes to KBs)
• Rarely involved in sorting
• Can be queried separately (avoid SELECT *)

3.5 ENUM vs VARCHAR

ENUM is stored internally as 1-2 byte integers, more compact than VARCHAR. But modifying ENUM (adding/removing members) requires ALTER TABLE, which is expensive on large tables.

-- ENUM: compact but inflexible
CREATE TABLE orders (
  status ENUM('pending','paid','shipped','delivered','cancelled') NOT NULL DEFAULT 'pending'
);
-- Storage: 1 byte (up to 255 members) or 2 bytes (up to 65535 members)

-- Alternative: TINYINT + application-level mapping (more flexible)
CREATE TABLE orders (
  status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0=pending,1=paid,2=shipped,3=delivered,4=cancelled'
);

Recommendation: If enum values rarely change (gender, continents), ENUM is a good choice. If members may be added/removed frequently (order status, payment methods), TINYINT + application-layer mapping is more flexible. Never use VARCHAR for enumerable values — it wastes space and is error-prone.

4. Date & Time Types: DATETIME vs TIMESTAMP

4.1 The Year 2038 Problem (Y2038)

TIMESTAMP uses a 32-bit Unix timestamp, maxing out at 2038-01-19 03:14:07 UTC. When business data may involve dates beyond 2038 (contract expiry, warranty periods, birthdays), DATETIME is required. MySQL is discussing 64-bit TIMESTAMP extensions, but none are officially implemented yet.

4.2 Time Zone Best Practices

-- Verify current time zone settings
SELECT @@global.time_zone, @@session.time_zone;

-- Best practice: store all times in UTC
SET GLOBAL time_zone = '+00:00';

-- TIMESTAMP auto-converts; DATETIME does not
CREATE TABLE events (
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  -- ↑ Stored as UTC, displayed in session time zone
  event_date  DATETIME NOT NULL,
  -- ↑ Stored exactly as inserted, no conversion
  updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

4.3 Fractional Seconds Precision (FSP)

MySQL 5.6.4+ supports fractional seconds precision, range 0-6 (microseconds). Each level of precision requires additional storage:

-- Millisecond precision for API logs
CREATE TABLE api_logs (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  endpoint    VARCHAR(200) NOT NULL,
  latency_ms  SMALLINT UNSIGNED NOT NULL,
  created_at  TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_created (created_at)
);

Alternative Date/Time Storage: Some teams store Unix millisecond timestamps in BIGINT. The benefits are avoiding time zone conversion and faster comparisons; the drawbacks are poor readability and inability to use DATE_FORMAT etc. directly. Recommended only when cross-language/cross-system consistency is needed.

5. Exact & Floating Point: DECIMAL vs FLOAT vs DOUBLE

5.1 Why FLOAT Cannot Be Used for Money

IEEE 754 binary representation cannot exactly store decimal fractions. Classic example: 0.1 + 0.2 = 0.30000000000000004. In financial scenarios, these errors become non-negligible after many accumulations.

-- Demonstration: FLOAT precision loss
CREATE TABLE float_test (amount FLOAT);
INSERT INTO float_test VALUES (0.1), (0.2);
SELECT SUM(amount) FROM float_test;
-- Result: 0.30000001192092896  (not exactly 0.3)

-- Correct approach for money: DECIMAL
CREATE TABLE decimal_test (amount DECIMAL(10,2));
INSERT INTO decimal_test VALUES (0.10), (0.20);
SELECT SUM(amount) FROM decimal_test;
-- Result: 0.30  (exact)

5.2 DECIMAL Storage Space Calculation

DECIMAL(M,D) storage rules: integer part (M-D) digits and fractional part D digits are each calculated as "every 9 digits = 4 bytes", with remainders per this table:

Example: DECIMAL(19,4): integer part 15 digits = 9 digits (4B) + 6 digits (3B) = 7 bytes; fractional part 4 digits = 2 bytes. Total: 7 + 2 = 9 bytes.

5.3 Alternative for Money: Integer Cents

-- Store money as integer cents (avoids DECIMAL overhead)
CREATE TABLE payments (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  amount_cents INT UNSIGNED NOT NULL COMMENT 'Amount in cents, e.g. 1999 = $19.99',
  currency     CHAR(3) NOT NULL DEFAULT 'USD',
  PRIMARY KEY (id)
);

-- Display: divide by 100 in application layer
-- Advantage: INT is 4 bytes, DECIMAL(10,2) is 5 bytes
-- Advantage: integer arithmetic is faster, no precision issues
-- Limitation: max $42,949,672.95 with INT UNSIGNED

6. JSON Column: Flexibility vs Performance Trade-off

MySQL 5.7.8 introduced native JSON type, significantly enhanced in 8.0. JSON columns are stored in binary format (not text), supporting path queries and partial updates.

6.1 JSON Advantages

• Schema flexibility: different rows can have different JSON structures without ALTER TABLE
• Data validation: auto-validates JSON format on insert, safer than storing JSON strings in TEXT
• Path queries: -> and ->> operators efficiently extract nested fields
• Partial updates: MySQL 8.0's JSON_SET(), JSON_REPLACE() support in-place modification without rewriting the entire JSON document

6.2 JSON Performance Costs

• Cannot directly index JSON columns: must use virtual generated columns to create indexes
• Storage overhead: binary JSON typically uses more space than equivalent relational columns (stores key names, type markers, offset arrays)
• Optimizer limitations: JSON path queries cannot fully leverage optimizer statistics

-- JSON column with generated column index
CREATE TABLE products (
  id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name      VARCHAR(200) NOT NULL,
  attrs     JSON NOT NULL,
  -- Virtual generated column: extracts 'color' from JSON
  color     VARCHAR(30) GENERATED ALWAYS AS (attrs->>'$.color') VIRTUAL,
  -- Virtual generated column: extracts 'weight' from JSON
  weight_kg DECIMAL(8,2) GENERATED ALWAYS AS (attrs->>'$.weight_kg') VIRTUAL,
  PRIMARY KEY (id),
  KEY idx_color (color),
  KEY idx_weight (weight_kg)
);

-- Insert: only fill 'attrs', generated columns auto-populate
INSERT INTO products (name, attrs) VALUES
  ('Widget A', '{"color":"red","weight_kg":1.5,"material":"steel"}'),
  ('Widget B', '{"color":"blue","weight_kg":0.8,"sizes":[10,20,30]}');

-- Query: use generated column (indexed!)
SELECT * FROM products WHERE color = 'red';

-- Query: JSON path query (no index, full scan)
SELECT * FROM products WHERE attrs->>'$.material' = 'steel';

6.3 Multi-Valued Index (MySQL 8.0.17+)

MySQL 8.0.17 introduced multi-valued indexes, creating index entries for each element in a JSON array, supporting MEMBER OF(), JSON_CONTAINS(), and JSON_OVERLAPS() queries.

-- Multi-valued index on JSON array
CREATE TABLE articles (
  id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(200) NOT NULL,
  tags  JSON NOT NULL,
  PRIMARY KEY (id),
  KEY idx_tags ((CAST(tags AS UNSIGNED ARRAY)))
);

INSERT INTO articles (title, tags) VALUES
  ('Intro to MySQL', '[1, 3, 5]'),
  ('Advanced SQL',   '[2, 3, 7]');

-- Uses multi-valued index:
SELECT * FROM articles WHERE 3 MEMBER OF(tags);
SELECT * FROM articles WHERE JSON_CONTAINS(tags, '[1, 3]');
SELECT * FROM articles WHERE JSON_OVERLAPS(tags, '[5, 7]');

JSON Column Guidelines: (1) Only use JSON when flexible schema is truly needed; (2) extract frequently queried fields as generated columns with indexes; (3) keep JSON documents within a few KB to avoid frequent overflow page access; (4) don't treat JSON as a "catch-all" — fields that can be relational columns should be.

7. Normalization: 1NF through BCNF

Normalization aims to eliminate data redundancy and update anomalies. Each normal form adds additional constraints on top of the previous one. Understanding what problem each normal form solves is more important than memorizing definitions.

7.1 First Normal Form (1NF): Atomicity

Requires each column value to be an indivisible atomic value. A typical 1NF violation:

-- Violates 1NF: multiple values in one column
CREATE TABLE students_bad (
  id    INT PRIMARY KEY,
  name  VARCHAR(50),
  phone VARCHAR(200)  -- '13800001111,13900002222,15000003333'
);

-- Correct: separate table for multi-valued attribute
CREATE TABLE students (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE student_phones (
  student_id INT UNSIGNED NOT NULL,
  phone      VARCHAR(20) NOT NULL,
  is_primary TINYINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (student_id, phone),
  FOREIGN KEY (student_id) REFERENCES students(id)
);

7.2 Second Normal Form (2NF): Eliminate Partial Dependencies

Building on 1NF, all non-key columns must depend on the entire primary key, not just part of it. 2NF only applies to tables with composite primary keys.

-- Violates 2NF: student_name depends only on student_id, not on (student_id, course_id)
CREATE TABLE enrollments_bad (
  student_id   INT NOT NULL,
  course_id    INT NOT NULL,
  student_name VARCHAR(50),  -- ← partial dependency on student_id only
  course_name  VARCHAR(100), -- ← partial dependency on course_id only
  grade        CHAR(2),
  PRIMARY KEY (student_id, course_id)
);
-- Problem: if a student changes name, must update ALL rows for that student

-- Correct 2NF: split into three tables
CREATE TABLE students     (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE courses      (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE enrollments  (student_id INT, course_id INT, grade CHAR(2),
                           PRIMARY KEY (student_id, course_id));

7.3 Third Normal Form (3NF): Eliminate Transitive Dependencies

Building on 2NF, non-key columns cannot depend on other non-key columns. All non-key columns must directly depend on the primary key, with no transitive chains like A → B → C.

-- Violates 3NF: city depends on zip_code, not directly on id
CREATE TABLE customers_bad (
  id       INT PRIMARY KEY,
  name     VARCHAR(50),
  zip_code CHAR(6),
  city     VARCHAR(50),    -- ← transitive: id → zip_code → city
  province VARCHAR(30)     -- ← transitive: id → zip_code → province
);
-- Problem: if a zip code's city name changes, must update all matching rows

-- Correct 3NF: separate zip code lookup
CREATE TABLE customers (
  id       INT PRIMARY KEY,
  name     VARCHAR(50),
  zip_code CHAR(6),
  FOREIGN KEY (zip_code) REFERENCES zip_codes(code)
);
CREATE TABLE zip_codes (
  code     CHAR(6) PRIMARY KEY,
  city     VARCHAR(50) NOT NULL,
  province VARCHAR(30) NOT NULL
);

7.4 Boyce-Codd Normal Form (BCNF)

BCNF is a stronger version of 3NF. In 3NF, only non-prime attributes must not transitively depend on candidate keys; BCNF requires that in every non-trivial functional dependency X → Y, X must be a superkey. BCNF handles certain anomalies that 3NF cannot, but in practice 3NF satisfies the vast majority of needs.

-- Example where 3NF is satisfied but BCNF is violated
-- Scenario: Students choose courses taught by specific professors
-- FDs: {student_id, course} → professor
--      {professor} → course  (each professor teaches only one course)

CREATE TABLE schedule_bad (
  student_id INT,
  course     VARCHAR(50),
  professor  VARCHAR(50),
  PRIMARY KEY (student_id, course)
);
-- professor → course means professor is a determinant but NOT a superkey
-- This violates BCNF

-- BCNF decomposition:
CREATE TABLE prof_courses (
  professor VARCHAR(50) PRIMARY KEY,
  course    VARCHAR(50) NOT NULL
);
CREATE TABLE student_profs (
  student_id INT,
  professor  VARCHAR(50),
  PRIMARY KEY (student_id, professor)
);

Practical Advice: In OLTP systems, designing to 3NF is usually sufficient. BCNF decomposition may cause more table joins and more complex queries. In analytical systems (OLAP), normalization is often intentionally broken for query performance (see denormalization in the next section).

8. Denormalization Strategies

Denormalization is a conscious trade-off between "query performance" and "data consistency maintenance cost". It is not ignorant design, but a rational choice made after fully understanding normalization, based on specific query patterns and performance requirements.

8.1 Redundant Columns (Most Common Denormalization)

-- Normalized: need JOIN to get user name with every order query
SELECT o.id, o.amount, u.name
FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2025-01-01';

-- Denormalized: add redundant user_name to orders table
ALTER TABLE orders ADD COLUMN user_name VARCHAR(50) NOT NULL DEFAULT '';
-- Trade-off: no JOIN needed, but must update orders.user_name when user changes name

-- Maintenance approach: trigger or application-layer sync
CREATE TRIGGER trg_sync_user_name
AFTER UPDATE ON users FOR EACH ROW
BEGIN
  IF OLD.name != NEW.name THEN
    UPDATE orders SET user_name = NEW.name WHERE user_id = NEW.id;
  END IF;
END;

8.2 Summary / Aggregation Tables

Pre-compute and store COUNT, SUM, AVG and other aggregate results, avoiding full scans of raw data on every query.

-- Summary table: daily sales statistics
CREATE TABLE daily_sales_summary (
  summary_date DATE NOT NULL,
  category_id  INT UNSIGNED NOT NULL,
  order_count  INT UNSIGNED NOT NULL DEFAULT 0,
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  avg_amount   DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (summary_date, category_id)
);

-- Refresh: scheduled job or triggered after each order
INSERT INTO daily_sales_summary (summary_date, category_id, order_count, total_amount, avg_amount)
SELECT DATE(created_at), category_id,
       COUNT(*), SUM(amount), AVG(amount)
FROM orders
WHERE DATE(created_at) = CURDATE()
GROUP BY DATE(created_at), category_id
ON DUPLICATE KEY UPDATE
  order_count = VALUES(order_count),
  total_amount = VALUES(total_amount),
  avg_amount = VALUES(avg_amount);

8.3 Cache Tables (Materialized Intermediate Results)

For complex multi-table JOIN query results, materialize them into a wide table, refreshed periodically or event-driven.

-- Cache table: product listing with pre-joined data
CREATE TABLE product_listing_cache (
  product_id    INT UNSIGNED PRIMARY KEY,
  product_name  VARCHAR(200) NOT NULL,
  brand_name    VARCHAR(100) NOT NULL,
  category_path VARCHAR(300) NOT NULL,  -- 'Electronics > Phones > Smartphones'
  avg_rating    DECIMAL(2,1) NOT NULL DEFAULT 0.0,
  review_count  INT UNSIGNED NOT NULL DEFAULT 0,
  lowest_price  DECIMAL(10,2),
  in_stock      TINYINT UNSIGNED NOT NULL DEFAULT 1,
  updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_category (category_path(50)),
  KEY idx_rating (avg_rating DESC),
  KEY idx_price (lowest_price)
);

-- Rebuild: nightly or triggered by product/review/inventory changes

8.4 Counter Table Pattern

Under high concurrency, directly using UPDATE SET count = count + 1 on the main table causes row lock contention. Counter tables distribute update pressure across multiple rows.

-- Counter table: distribute writes across N slots
CREATE TABLE post_counters (
  post_id    BIGINT UNSIGNED NOT NULL,
  slot       TINYINT UNSIGNED NOT NULL COMMENT '0..99',
  like_count INT UNSIGNED NOT NULL DEFAULT 0,
  view_count INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (post_id, slot)
) ENGINE=InnoDB;

-- Initialize 100 slots per post:
-- INSERT INTO post_counters (post_id, slot) VALUES (123, 0), (123, 1), ... (123, 99);

-- Increment: pick a random slot to reduce contention
UPDATE post_counters
SET like_count = like_count + 1
WHERE post_id = 123 AND slot = FLOOR(RAND() * 100);

-- Read: sum all slots
SELECT SUM(like_count) AS total_likes, SUM(view_count) AS total_views
FROM post_counters
WHERE post_id = 123;

-- Periodically consolidate: merge all slots into slot 0, reset others
-- This keeps the read query efficient (fewer rows to sum)

When to Use Counter Tables: Only needed when single-row updates show obvious lock waits. In most systems, count = count + 1 on the main table with short transactions is sufficient. Over-engineering counter tables adds complexity. Typical scenarios needing counter tables: social media likes (thousands of updates per second on the same row), real-time view counters.

8.5 When to Denormalize, When Not?

Denormalize When...

• Read-heavy, queries far outnumber updates
• JOIN cost is high: large tables, unindexable JOIN columns
• Redundant data consistency can be ensured asynchronously
• Frequent aggregations over large datasets

Avoid Denormalization When...

• Write-heavy, redundancy update cost exceeds JOIN cost
• Data consistency is critical (finance, healthcare)
• JOINs can be eliminated via covering indexes
• Team lacks proper data sync mechanisms

9. NULL Deep Dive

9.1 NULL Storage Cost

In InnoDB's row format, each nullable column occupies 1 bit in the NULL bitmap in the row header. A table with 8 nullable columns adds 1 byte to the row header; 16 nullable columns add 2 bytes. While the overhead is small, it accumulates on tables with billions of rows.

9.2 NULL and Indexes

InnoDB's B+Tree indexes can contain NULL values (unlike Oracle, which does not index all-NULL keys). But NULL has special handling in indexes:

• NULL values are considered "less than all non-NULL values" and sorted at the beginning
• WHERE col IS NULL can use the index
• But COUNT(col) does not count NULL rows; COUNT(*) counts all rows
• Unique indexes allow multiple NULLs (NULL != NULL)

-- NULL surprises in queries
SELECT * FROM t WHERE col != 'A';
-- ↑ Does NOT return rows where col IS NULL!
-- NULL != 'A' evaluates to NULL (not TRUE), so those rows are excluded.

-- To include NULLs:
SELECT * FROM t WHERE col != 'A' OR col IS NULL;

-- NULL in aggregations
SELECT COUNT(col)  FROM t;  -- counts non-NULL values only
SELECT COUNT(*)    FROM t;  -- counts all rows
SELECT AVG(col)    FROM t;  -- ignores NULLs in both SUM and COUNT

-- NULL in UNIQUE constraints
CREATE TABLE t (email VARCHAR(254) UNIQUE);
INSERT INTO t VALUES (NULL), (NULL);  -- Both succeed! NULL != NULL

9.3 Correct and Incorrect Use of NULL

Appropriate NULL Usage

• Optional fields: middle name, secondary phone
• Events that haven't occurred: deleted_at, completed_at
• Optional FK relationships: manager_id (CEO has no manager)

Avoid NULL For

• Required fields using NULL for "unfilled" — validate at app layer
• NULL meaning 0 or empty string — different semantics
• Boolean columns — use TINYINT NOT NULL DEFAULT 0
• Columns frequently used in ORDER BY / GROUP BY / aggregation

10. Character Sets & Collations: utf8mb4 Complete Guide

10.1 Why utf8mb4, Not utf8?

MySQL's utf8 (also called utf8mb3) is an incomplete implementation, supporting only up to 3-byte UTF-8 characters (BMP plane). It cannot store 4-byte characters like emoji, some CJK Extension B characters, or mathematical symbols. utf8mb4 is the complete UTF-8 implementation. MySQL 8.0 changed the default charset to utf8mb4 and default collation to utf8mb4_0900_ai_ci.

Migration Warning: After migrating from utf8 to utf8mb4, a VARCHAR(255) index key length grows from 765 to 1020 bytes. InnoDB's max index key length is 3072 bytes (innodb_large_prefix=ON), but combined index total length is still limited. Check all index lengths before migration.

10.2 Collation Selection

-- Recommended server-level configuration (my.cnf)
[mysqld]
character-set-server = utf8mb4
collation-server     = utf8mb4_0900_ai_ci

[client]
default-character-set = utf8mb4

-- Per-table override for case-sensitive column
CREATE TABLE users (
  id       INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs NOT NULL,
  email    VARCHAR(254) NOT NULL,
  UNIQUE KEY uk_username (username)
);

10.3 Character Set Impact on Storage

Each utf8mb4 character occupies at most 4 bytes, but actual storage depends on the character: ASCII is 1 byte, most Chinese characters 3 bytes, emoji 4 bytes. N in VARCHAR(N) refers to character count, not byte count. Thus VARCHAR(100) under utf8mb4 needs up to 400 bytes of inline space.

10.4 Connection-Level Character Set

Even if the server and tables are set to utf8mb4, garbled characters or data truncation can occur if the client connection uses the wrong charset. Ensure every link in the connection uses utf8mb4:

-- Check current connection charset settings
SHOW VARIABLES LIKE 'character_set%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| character_set_client             | utf8mb4|  ← client sends in this charset
| character_set_connection         | utf8mb4|  ← server converts to this for processing
| character_set_results            | utf8mb4|  ← server converts results to this
| character_set_database           | utf8mb4|  ← default for new tables in current DB
| character_set_server             | utf8mb4|  ← default for new databases
+----------------------------------+--------+

-- JDBC connection string (Java)
-- jdbc:mysql://host:3306/db?useUnicode=true&characterEncoding=utf8mb4

-- Go (go-sql-driver/mysql)
-- user:pass@tcp(host:3306)/db?charset=utf8mb4&collation=utf8mb4_0900_ai_ci

-- PHP (PDO)
-- new PDO('mysql:host=host;dbname=db;charset=utf8mb4', $user, $pass);

10.5 utf8 to utf8mb4 Migration Steps

1. Check all index lengths: SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH FROM information_schema.COLUMNS WHERE CHARACTER_SET_NAME = 'utf8' AND TABLE_SCHEMA = 'mydb'
2. Shorten VARCHAR length on over-long indexed columns (e.g. VARCHAR(255) → VARCHAR(191) to stay within 767-byte limit) or enable innodb_large_prefix
3. Change database default charset: ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci
4. Convert table by table: ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci
5. Update connection strings to ensure clients use utf8mb4
6. Verify: insert emoji characters to test

11. Schema Design Patterns

11.1 Soft Delete Pattern

-- Approach A: deleted_at timestamp (NULL = active, non-NULL = deleted)
CREATE TABLE posts (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title      VARCHAR(200) NOT NULL,
  content    TEXT,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_active (deleted_at, id)  -- filter active rows efficiently
);

-- Query active posts: WHERE deleted_at IS NULL
-- Query deleted posts: WHERE deleted_at IS NOT NULL
-- Undelete: UPDATE posts SET deleted_at = NULL WHERE id = ?

-- Approach B: is_deleted flag (better for partitioning)
CREATE TABLE posts (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title      VARCHAR(200) NOT NULL,
  is_deleted TINYINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_active (is_deleted, id)
);

11.2 Polymorphic Association Pattern

When multiple entity types (articles, products, videos) can all have comments:

-- Anti-pattern: polymorphic FK (no referential integrity)
CREATE TABLE comments_bad (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  commentable_type VARCHAR(20) NOT NULL, -- 'article', 'product', 'video'
  commentable_id   BIGINT UNSIGNED NOT NULL,
  body             TEXT NOT NULL
  -- Cannot create a proper FOREIGN KEY here!
);

-- Better pattern: shared parent table (exclusive arc)
CREATE TABLE commentables (
  id   BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  type ENUM('article','product','video') NOT NULL
);
CREATE TABLE articles (
  commentable_id BIGINT UNSIGNED PRIMARY KEY,
  title          VARCHAR(200) NOT NULL,
  FOREIGN KEY (commentable_id) REFERENCES commentables(id)
);
CREATE TABLE products (
  commentable_id BIGINT UNSIGNED PRIMARY KEY,
  name           VARCHAR(200) NOT NULL,
  FOREIGN KEY (commentable_id) REFERENCES commentables(id)
);
CREATE TABLE comments (
  id              BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  commentable_id  BIGINT UNSIGNED NOT NULL,
  body            TEXT NOT NULL,
  FOREIGN KEY (commentable_id) REFERENCES commentables(id)
);

11.3 Tree Structure Patterns

Four approaches for storing hierarchical data (category trees, org charts, nested comments):

-- Adjacency List + Recursive CTE (MySQL 8.0+ recommended)
CREATE TABLE categories (
  id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_id INT UNSIGNED,
  name      VARCHAR(100) NOT NULL,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_parent (parent_id),
  FOREIGN KEY (parent_id) REFERENCES categories(id)
);

-- Query: all descendants of category 1
WITH RECURSIVE cte AS (
  SELECT id, parent_id, name, 0 AS depth
  FROM categories WHERE id = 1
  UNION ALL
  SELECT c.id, c.parent_id, c.name, cte.depth + 1
  FROM categories c JOIN cte ON c.parent_id = cte.id
)
SELECT * FROM cte ORDER BY depth, name;

-- Path Enumeration pattern
CREATE TABLE categories_path (
  id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  path VARCHAR(500) NOT NULL COMMENT 'e.g. /1/5/23/',
  name VARCHAR(100) NOT NULL,
  KEY idx_path (path)
);
-- All descendants of node 5:
SELECT * FROM categories_path WHERE path LIKE '/1/5/%';

11.4 Audit Trail Pattern

Record data change history for compliance auditing or data recovery. Two common implementations:

-- Approach A: separate audit log table (generic)
CREATE TABLE audit_log (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  table_name   VARCHAR(64) NOT NULL,
  record_id    BIGINT UNSIGNED NOT NULL,
  action       ENUM('INSERT','UPDATE','DELETE') NOT NULL,
  old_values   JSON DEFAULT NULL,
  new_values   JSON DEFAULT NULL,
  changed_by   INT UNSIGNED NOT NULL COMMENT 'user id who made the change',
  changed_at   TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_table_record (table_name, record_id),
  KEY idx_changed_at (changed_at),
  KEY idx_changed_by (changed_by)
) ENGINE=InnoDB;

-- Approach B: history table per entity (typed, queryable)
CREATE TABLE users_history (
  history_id  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     INT UNSIGNED NOT NULL,
  username    VARCHAR(50) NOT NULL,
  email       VARCHAR(254) NOT NULL,
  status      TINYINT UNSIGNED NOT NULL,
  valid_from  TIMESTAMP(3) NOT NULL,
  valid_to    TIMESTAMP(3) NOT NULL DEFAULT '9999-12-31 23:59:59.999',
  changed_by  INT UNSIGNED NOT NULL,
  PRIMARY KEY (history_id),
  KEY idx_user_valid (user_id, valid_from),
  KEY idx_valid_range (valid_from, valid_to)
) ENGINE=InnoDB;

-- "What was user 42's email on 2025-06-15?"
SELECT email FROM users_history
WHERE user_id = 42
  AND valid_from   '2025-06-15';

Selection Advice: Generic audit log (Approach A) suits compliance needs — who changed what and when; typed history tables (Approach B) suit "time travel query" scenarios — querying data snapshots at a point in time. Both can coexist.

11.5 EAV Pattern (Entity-Attribute-Value)

EAV uses a generic table to store arbitrary attributes, common in e-commerce SKU attributes and CMS custom fields. But it has serious performance and type safety issues — JSON columns should usually be preferred as an alternative.

-- EAV anti-pattern (still seen in legacy systems)
CREATE TABLE product_attrs (
  product_id INT UNSIGNED NOT NULL,
  attr_name  VARCHAR(50) NOT NULL,
  attr_value VARCHAR(500),
  PRIMARY KEY (product_id, attr_name)
);
-- Finding products with color='red' AND size='L' requires self-join!
-- No type safety: price '19.99' stored as string

-- Modern alternative: JSON column with generated column indexes
CREATE TABLE products (
  id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(200) NOT NULL,
  attrs JSON NOT NULL DEFAULT ('{}'),
  color VARCHAR(30) GENERATED ALWAYS AS (attrs->>'$.color') VIRTUAL,
  KEY idx_color (color)
);

12. Schema Design Anti-Patterns

12.1 God Table

Stuffing all information into a single "super wide table", resulting in many NULL columns, overly wide rows hurting Buffer Pool efficiency, and extremely slow ALTER TABLE operations.

-- Anti-pattern: God Table
CREATE TABLE everything (
  id INT PRIMARY KEY,
  user_name VARCHAR(50), user_email VARCHAR(254), user_phone VARCHAR(20),
  order_id INT, order_date DATE, order_amount DECIMAL(10,2),
  product_name VARCHAR(200), product_price DECIMAL(10,2),
  shipping_address TEXT, shipping_city VARCHAR(50),
  payment_method VARCHAR(20), payment_status VARCHAR(20),
  -- ... 50+ more columns, most NULL for any given row
);
-- Problems: rows are huge, Buffer Pool holds fewer rows per page,
-- no clear entity boundaries, impossible to maintain.

12.2 Multi-Column Attribute

-- Anti-pattern: phone1, phone2, phone3 columns
CREATE TABLE contacts_bad (
  id     INT PRIMARY KEY,
  name   VARCHAR(50),
  phone1 VARCHAR(20),
  phone2 VARCHAR(20),
  phone3 VARCHAR(20)  -- What if someone has 4 phones?
);
-- Fix: separate phone table (1NF)

12.3 Implicit Type Conversion

When column types don't match in JOIN or WHERE conditions, MySQL performs implicit type conversion, causing index invalidation. This is one of the most common "performance bugs caused by schema design".

-- Table A: user_id is INT
CREATE TABLE orders (user_id INT UNSIGNED, ...);

-- Table B: user_id is VARCHAR (design mistake!)
CREATE TABLE user_logs (user_id VARCHAR(20), ...);

-- This JOIN silently converts VARCHAR to INT, index on user_logs.user_id is IGNORED:
SELECT * FROM orders o
JOIN user_logs l ON o.user_id = l.user_id;  -- FULL TABLE SCAN on user_logs!

-- Fix: ensure matching types across all related tables

12.4 Over-Reliance on Foreign Keys

InnoDB foreign keys introduce additional lock overhead in high-concurrency systems (shared lock check on parent table for every child INSERT/UPDATE/DELETE). In microservice architectures, cross-service data integrity is better enforced at the application layer.

Foreign Key Advice: (1) Monoliths where data consistency is critical (finance) — use FK. (2) High-concurrency OLTP, microservices — app-layer validation + indexes, no FK. (3) Regardless of FK usage, indexes on JOIN columns are always necessary.

12.5 Over-Indexing

Every index requires additional disk space, and every write operation (INSERT/UPDATE/DELETE) must maintain all related indexes. Too many indexes significantly degrades write performance.

-- Over-indexed table (real example from production audit)
CREATE TABLE orders_overindexed (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id     INT UNSIGNED NOT NULL,
  status      TINYINT UNSIGNED NOT NULL,
  amount      INT UNSIGNED NOT NULL,
  created_at  TIMESTAMP NOT NULL,
  KEY idx_user (user_id),
  KEY idx_status (status),
  KEY idx_amount (amount),
  KEY idx_created (created_at),
  KEY idx_user_status (user_id, status),        -- ← covers idx_user
  KEY idx_user_created (user_id, created_at),    -- ← redundant if idx_user_status_created exists
  KEY idx_status_created (status, created_at),
  KEY idx_user_status_created (user_id, status, created_at),  -- ← covers idx_user_status
  KEY idx_user_amount (user_id, amount)
  -- 9 secondary indexes! Each INSERT updates 9 B+Trees
);

-- After analysis: only 3 indexes needed
CREATE TABLE orders_optimized (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id     INT UNSIGNED NOT NULL,
  status      TINYINT UNSIGNED NOT NULL,
  amount      INT UNSIGNED NOT NULL,
  created_at  TIMESTAMP NOT NULL,
  KEY idx_user_status_created (user_id, status, created_at),
  KEY idx_status_created (status, created_at),
  KEY idx_user_amount (user_id, amount)
  -- 3 indexes: covers all actual query patterns
);

Index Audit Method: Use sys.schema_unused_indexes to find never-used indexes, and sys.schema_redundant_indexes to find redundant indexes (one is a prefix of another). Regular audit and removal of useless indexes can improve write performance by 20-50%.

12.6 VARCHAR for Fixed-Length Data

Fixed-length data like MD5 hashes (32 chars), UUIDs (36 chars with hyphens), ISO country codes (2 chars) should use CHAR or BINARY to avoid VARCHAR's length prefix overhead. UUID storage should especially use BINARY(16) with UUID_TO_BIN() / BIN_TO_UUID().

-- UUID storage: BINARY(16) vs CHAR(36)
CREATE TABLE sessions (
  id BINARY(16) PRIMARY KEY,  -- 16 bytes
  user_id INT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Insert with UUID_TO_BIN (MySQL 8.0+, swap flag for time-ordered UUID)
INSERT INTO sessions (id, user_id) VALUES
  (UUID_TO_BIN(UUID(), 1), 42);

-- Query: convert back for display
SELECT BIN_TO_UUID(id, 1) AS uuid, user_id FROM sessions;
-- Savings: 16 bytes (BINARY) vs 36 bytes (CHAR) = 56% smaller indexes

13. Real-World Case: E-Commerce Schema Design

Using a medium-scale e-commerce system as an example, showing the complete process from requirements analysis to schema design. Core entities: users, products, orders, payments.

13.1 User Table Design

CREATE TABLE users (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username     VARCHAR(50) NOT NULL,
  email        VARCHAR(254) NOT NULL,
  password_hash CHAR(60) NOT NULL COMMENT 'bcrypt hash, always 60 chars',
  phone        VARCHAR(20) DEFAULT NULL,
  avatar_url   VARCHAR(500) DEFAULT NULL,
  status       TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '0=disabled,1=active,2=suspended',
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_username (username),
  UNIQUE KEY uk_email (email),
  KEY idx_phone (phone),
  KEY idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Design decisions:
-- 1. INT (not BIGINT): a single DB unlikely to have >4B users
-- 2. password_hash CHAR(60): bcrypt is always exactly 60 chars
-- 3. email VARCHAR(254): RFC 5321 max local+domain length
-- 4. phone nullable: some users register via email only
-- 5. status TINYINT, not ENUM: easier to add new states

13.2 Product & SKU Table Design

CREATE TABLE products (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name         VARCHAR(200) NOT NULL,
  slug         VARCHAR(200) NOT NULL COMMENT 'URL-friendly identifier',
  brand_id     INT UNSIGNED NOT NULL,
  category_id  INT UNSIGNED NOT NULL,
  description  TEXT,
  attrs        JSON NOT NULL DEFAULT ('{}') COMMENT 'flexible product attributes',
  status       TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0=draft,1=active,2=archived',
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_slug (slug),
  KEY idx_brand (brand_id),
  KEY idx_category_status (category_id, status),
  KEY idx_created (created_at)
) ENGINE=InnoDB;

CREATE TABLE product_skus (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id   INT UNSIGNED NOT NULL,
  sku_code     VARCHAR(50) NOT NULL,
  price        INT UNSIGNED NOT NULL COMMENT 'price in cents',
  cost         INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cost in cents',
  stock        INT UNSIGNED NOT NULL DEFAULT 0,
  attrs        JSON NOT NULL DEFAULT ('{}') COMMENT '{"color":"red","size":"L"}',
  weight_g     MEDIUMINT UNSIGNED COMMENT 'weight in grams',
  is_active    TINYINT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uk_sku (sku_code),
  KEY idx_product_active (product_id, is_active),
  KEY idx_price (price)
) ENGINE=InnoDB;

-- Why BIGINT for SKU id: a product may have hundreds of SKU variants,
-- and with millions of products, INT could be reached.
-- Why INT for price (cents): avoids DECIMAL overhead, max $42.9M per item

13.3 Order Table Design

CREATE TABLE orders (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_no      VARCHAR(32) NOT NULL COMMENT 'business order number',
  user_id       INT UNSIGNED NOT NULL,
  status        TINYINT UNSIGNED NOT NULL DEFAULT 0
                COMMENT '0=created,1=paid,2=shipped,3=delivered,4=cancelled,5=refunded',
  total_cents   INT UNSIGNED NOT NULL,
  discount_cents INT UNSIGNED NOT NULL DEFAULT 0,
  shipping_cents INT UNSIGNED NOT NULL DEFAULT 0,
  pay_amount    INT UNSIGNED NOT NULL COMMENT 'total - discount + shipping',
  currency      CHAR(3) NOT NULL DEFAULT 'USD',
  -- Denormalized: avoids JOIN to get address on every order page
  ship_name     VARCHAR(100) NOT NULL,
  ship_address  VARCHAR(500) NOT NULL,
  ship_city     VARCHAR(100) NOT NULL,
  ship_country  CHAR(2) NOT NULL,
  ship_zip      VARCHAR(20) NOT NULL,
  paid_at       TIMESTAMP NULL DEFAULT NULL,
  shipped_at    TIMESTAMP NULL DEFAULT NULL,
  delivered_at  TIMESTAMP NULL DEFAULT NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_user_status (user_id, status),
  KEY idx_created (created_at),
  KEY idx_status_created (status, created_at)
) ENGINE=InnoDB;

CREATE TABLE order_items (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id    BIGINT UNSIGNED NOT NULL,
  sku_id      BIGINT UNSIGNED NOT NULL,
  -- Snapshot: price at time of order (not current price)
  sku_name    VARCHAR(200) NOT NULL,
  unit_price  INT UNSIGNED NOT NULL COMMENT 'cents, snapshot at order time',
  quantity    SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  KEY idx_order (order_id),
  KEY idx_sku (sku_id)
) ENGINE=InnoDB;

Design Highlights - Shipping address denormalized into orders: after creation, the order address should not change when the user updates their address — this redundancy is both a performance optimization and a business correctness requirement - order_items.unit_price is a snapshot at order time, not the current product price - order_no separated from auto_increment id: expose order_no externally, use integer id internally for JOINs

14. Real-World Case: Social Network Schema Design

14.1 Follow/Follower Relationship Table

CREATE TABLE user_follows (
  follower_id  INT UNSIGNED NOT NULL,
  followee_id  INT UNSIGNED NOT NULL,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (follower_id, followee_id),
  KEY idx_followee (followee_id, follower_id)
) ENGINE=InnoDB;

-- "Who does user 42 follow?"  → PRIMARY KEY scan on follower_id=42
-- "Who follows user 42?"      → idx_followee scan on followee_id=42
-- "Does user 42 follow user 99?" → PRIMARY KEY point lookup

-- Denormalized counters (avoid COUNT(*) on millions of rows)
ALTER TABLE users
  ADD COLUMN follower_count  INT UNSIGNED NOT NULL DEFAULT 0,
  ADD COLUMN following_count INT UNSIGNED NOT NULL DEFAULT 0;

-- Maintain via application layer:
-- On follow:  UPDATE users SET following_count = following_count + 1 WHERE id = ?
--             UPDATE users SET follower_count  = follower_count  + 1 WHERE id = ?
-- On unfollow: decrement both

14.2 Feed / Timeline Table

Social network feeds have two classic architecture patterns: push model (fanout on write) and pull model (fanout on read). Schema design directly determines which model is used.

-- Pull model: query followers' posts at read time
CREATE TABLE posts (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id    INT UNSIGNED NOT NULL,
  content    TEXT NOT NULL,
  media_urls JSON DEFAULT NULL,
  like_count INT UNSIGNED NOT NULL DEFAULT 0,
  reply_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_user_created (user_id, created_at DESC)
) ENGINE=InnoDB;

-- Pull: get feed for user 42 (query all followees' posts)
SELECT p.* FROM posts p
JOIN user_follows f ON p.user_id = f.followee_id
WHERE f.follower_id = 42
ORDER BY p.created_at DESC
LIMIT 20;
-- Problem: if user 42 follows 1000 people, this becomes expensive

-- Push model: pre-materialize feed for each user
CREATE TABLE user_feed (
  user_id    INT UNSIGNED NOT NULL,
  post_id    BIGINT UNSIGNED NOT NULL,
  author_id  INT UNSIGNED NOT NULL,
  created_at TIMESTAMP(3) NOT NULL,
  PRIMARY KEY (user_id, post_id),
  KEY idx_user_time (user_id, created_at DESC)
) ENGINE=InnoDB;

-- On new post: fan out to all followers' feeds
-- Fast read: SELECT * FROM user_feed WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20
-- Problem: celebrities with millions of followers → huge write amplification

-- Hybrid: push for normal users, pull for celebrities (>100K followers)

14.3 Like Table Design

CREATE TABLE post_likes (
  user_id    INT UNSIGNED NOT NULL,
  post_id    BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, post_id),
  KEY idx_post (post_id, created_at DESC)
) ENGINE=InnoDB;

-- "Has user 42 liked post 999?"  → PRIMARY KEY point lookup (very fast)
-- "Who liked post 999?"          → idx_post scan
-- Like count: maintained in posts.like_count (denormalized)

-- Like action (idempotent)
INSERT IGNORE INTO post_likes (user_id, post_id) VALUES (42, 999);
UPDATE posts SET like_count = like_count + ROW_COUNT() WHERE id = 999;
-- ROW_COUNT() = 1 if inserted, 0 if duplicate (already liked)

14.4 Message Table Design

CREATE TABLE conversations (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  type         TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0=direct,1=group',
  last_msg_at  TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_last_msg (last_msg_at DESC)
) ENGINE=InnoDB;

CREATE TABLE conversation_members (
  conversation_id BIGINT UNSIGNED NOT NULL,
  user_id         INT UNSIGNED NOT NULL,
  last_read_msg   BIGINT UNSIGNED DEFAULT NULL COMMENT 'last read message id',
  joined_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (conversation_id, user_id),
  KEY idx_user (user_id, conversation_id)
) ENGINE=InnoDB;

CREATE TABLE messages (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  conversation_id BIGINT UNSIGNED NOT NULL,
  sender_id       INT UNSIGNED NOT NULL,
  content         TEXT NOT NULL,
  msg_type        TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0=text,1=image,2=file',
  created_at      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_conv_created (conversation_id, created_at DESC)
) ENGINE=InnoDB;

-- Unread count: compare last_read_msg with max message id per conversation
SELECT m.conversation_id,
       COUNT(*) AS unread
FROM messages m
JOIN conversation_members cm
  ON m.conversation_id = cm.conversation_id
WHERE cm.user_id = 42
  AND m.id > COALESCE(cm.last_read_msg, 0)
GROUP BY m.conversation_id;

15. Schema Migration Strategies

15.1 Online DDL

MySQL 8.0 significantly improved online DDL capabilities. Understanding three DDL algorithms is crucial for choosing the right migration approach:

-- INSTANT: add column at the end (MySQL 8.0.12+)
ALTER TABLE users ADD COLUMN bio TEXT DEFAULT NULL, ALGORITHM=INSTANT;
-- Completes in milliseconds regardless of table size!

-- INPLACE: add index without blocking writes
ALTER TABLE orders ADD INDEX idx_amount (pay_amount), ALGORITHM=INPLACE, LOCK=NONE;

-- Verify which algorithm MySQL will use:
ALTER TABLE orders ADD COLUMN note TEXT, ALGORITHM=INSTANT;
-- If INSTANT is not possible, MySQL returns an error instead of falling back

15.2 pt-online-schema-change / gh-ost

For large table changes that native Online DDL cannot handle (column type changes, PK modifications), use third-party tools:

-- gh-ost example: change column type on a 500M row table
gh-ost \
  --host=db-primary.example.com \
  --database=mydb \
  --table=orders \
  --alter="MODIFY COLUMN order_no VARCHAR(64) NOT NULL" \
  --chunk-size=1000 \
  --max-load="Threads_running=25" \
  --critical-load="Threads_running=100" \
  --throttle-control-replicas=db-replica.example.com \
  --postpone-cut-over-flag-file=/tmp/gh-ost-cutover.flag \
  --execute

15.3 Large Table Migration Checklist

1. Assess impact: table size, write QPS, replica lag tolerance
2. Choose approach: INSTANT > INPLACE > gh-ost/pt-osc > COPY
3. Test environment: run on production-scale test env, record duration
4. Backup: take a full backup before migration
5. Off-peak execution: choose low-traffic windows
6. Monitor: watch replication lag, Threads_running, I/O utilization
7. Rollback plan: define rollback steps for failure scenarios

15.4 Safe Paths for Data Type Changes

Not all data type changes require table rebuilds. These changes can be done INPLACE:

• Extending VARCHAR: from VARCHAR(50) to VARCHAR(200) (as long as original and target are both within 255, or both above 255)
• Extending ENUM: adding new members at the end
• Changing defaults: purely metadata operation

These changes require table rebuild (COPY or gh-ost):

• INT → BIGINT
• VARCHAR → TEXT (or reverse)
• Character set changes (e.g. utf8 → utf8mb4)
• Crossing VARCHAR 255 boundary (e.g. VARCHAR(200) → VARCHAR(500))

16. Frequently Asked Questions

Q1: Should the primary key be auto-increment INT or UUID?

Performance perspective: Auto-increment INT/BIGINT is optimal. InnoDB uses a clustered index (data sorted by PK), and auto-increment guarantees sequential inserts — new rows always append to the end of the B+Tree, avoiding page splits. UUID v4 is random, causing random inserts and page splits, potentially degrading write performance by 2-5x on large tables. Distributed scenarios: If multiple nodes need to independently generate non-conflicting IDs, auto-increment INT cannot work. Three alternatives: - UUID v7 / ULID: timestamp prefix ensures rough ordering; store as BINARY(16) for near auto-increment performance - Snowflake IDs: 64-bit time-ordered IDs, fits in BIGINT - MySQL UUID_TO_BIN(uuid, 1): swaps timestamp high/low bits, improving ordering Recommendation: Monolith apps use auto-increment INT/BIGINT; distributed systems use Snowflake (BIGINT) or UUID v7 (BINARY(16)).

Q2: Why is VARCHAR(255) so popular?

Because VARCHAR's length prefix is 1 byte when N ≤ 255 and 2 bytes when N > 255. 255 is the boundary for "maximum flexibility with minimum storage overhead". But this doesn't mean you should default to VARCHAR(255). Set it based on actual business maximum length: username VARCHAR(50), email VARCHAR(254), product name VARCHAR(200). Blindly using 255 causes: (1) wasted space in in-memory temp tables; (2) code reviews cannot determine the field's actual meaning and constraints.

Q3: When should tables be split vertically?

When a table's columns can be clearly divided into "hot data" and "cold data", consider vertical splitting: - User table split: users (id, name, email, status) + user_profiles (user_id, bio, avatar_url, preferences JSON) — list pages query only users, detail pages JOIN user_profiles - Product table split: products (id, name, price, status) + product_details (product_id, description TEXT, spec JSON) — list pages don't need description and spec Splitting criteria: (1) Large columns (TEXT, JSON, BLOB) not needed by core queries (lists, search) should be split out; (2) column groups with different update frequencies can be separated (frequently updated stats vs rarely changing base info).

Q4: Can ENUM members be added online?

In MySQL 8.0, adding new members at the end of an ENUM can be done with the INPLACE algorithm without table rebuild: ALTER TABLE orders MODIFY status ENUM('pending','paid','shipped','delivered','cancelled','returned'), ALGORITHM=INPLACE; However: (1) inserting in the middle changes internal numeric mapping, requiring COPY; (2) removing members is not supported (only adding); (3) reordering also requires COPY. This is why TINYINT + app-layer mapping is more flexible for frequently changing enums.

Q5: What's the difference between JSON column and TEXT storing JSON strings?

Four key differences: 1. Validation: JSON type auto-validates on write; TEXT accepts any string 2. Storage format: JSON uses optimized binary format, supporting direct access to nested elements without parsing the entire document; TEXT stores raw text, requiring full parsing on every query 3. Index support: JSON supports virtual generated column indexes and multi-valued indexes; TEXT only supports prefix indexes (and cannot index internal structure) 4. Partial updates: MySQL 8.0 JSON supports in-place partial updates via JSON_SET() (under certain conditions, no full document rewrite needed); TEXT must rewrite the entire value Recommendation: If the content is JSON format, always use the JSON type, not TEXT.

Q6: How to choose the right primary key strategy?

Core considerations for primary key selection: | Scenario | Recommended PK | Reason | | --- | --- | --- | | Scenario | Recommended PK | Reason | | Single DB, single table | INT UNSIGNED AUTO_INCREMENT | Most compact, fastest, sequential | | May exceed 4.2B rows | BIGINT UNSIGNED AUTO_INCREMENT | Space for capacity | | Distributed / multi-writer | BIGINT (Snowflake) | Time-ordered + globally unique | | Client-generated IDs | BINARY(16) (UUID v7) | Time-ordered + no central service | | Natural business key exists | Surrogate + business key | Surrogate as PK, business key as UNIQUE |

Q7: How far should you normalize?

Practical rules of thumb: - OLTP (Online Transaction Processing): Design to 3NF, then selectively denormalize based on performance testing. Most production systems end up as "3NF + limited strategic denormalization" - OLAP (Online Analytical Processing): Typically uses star/snowflake schemas, intentionally denormalized to reduce JOINs - Don't over-normalize from the start: If 90% of queries need JOINs to reassemble, you've split too aggressively - Don't denormalize from the start either: First establish correct normalized structure, then denormalize targeted bottlenecks found during performance testing

Q8: How to handle multi-currency amount storage?

Multi-currency amount storage requires attention to: - Always store currency code: Use CHAR(3) for ISO 4217 currency codes (USD, CNY, EUR) - Store in smallest units: USD uses cents, JPY has no decimals, some Middle Eastern currencies have 3 decimal places (dinar = 1000 fils). Use BIGINT to store integer values of the smallest unit - Exchange rate table: Separate exchange rate table recording point-in-time rates for historical tracing - Don't do currency conversion in the database: Handle in application layer; database only stores original amount and currency CREATE TABLE payments ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, amount_minor BIGINT NOT NULL COMMENT 'amount in smallest currency unit', currency CHAR(3) NOT NULL, exponent TINYINT UNSIGNED NOT NULL DEFAULT 2 COMMENT 'decimal places: USD=2, JPY=0, BHD=3', exchange_rate DECIMAL(16,8) DEFAULT NULL COMMENT 'rate to base currency at payment time' );

Q9: Is soft delete or hard delete better?

Each suits different scenarios: Soft delete (deleted_at / is_deleted) is suitable for: data recovery (undelete), audit requirements (financial regulations requiring historical records), complex relationships (deleting a user affects orders, comments, and dozens of other tables). Downside: all queries must include WHERE deleted_at IS NULL, and the table grows over time. Hard delete is suitable for: logs/temporary data, GDPR-mandated complete user data removal, rapidly growing tables needing size control. Hybrid approach: Soft delete + archiving. After soft deletion, periodically migrate deleted data to archive tables (or archive database), keeping the main table lean.

Q10: Why are foreign key constraints not recommended in MySQL?

Strictly speaking, "not recommended" applies to large-scale, high-concurrency systems. FK costs in InnoDB include: - Extra locks: Child INSERT requires shared lock (S-lock) on parent row, increasing lock waits under high concurrency - Cascade risks: ON DELETE CASCADE may delete thousands of child rows when one parent row is removed, causing long transactions and performance jitter - Cross-DB/sharding difficulties: FKs don't support cross-database or cross-shard references; they become obstacles when horizontal scaling is needed - DDL complexity: Tables with FKs have extra dependency handling during DDL But in small systems, development environments, or scenarios where data consistency is paramount, FKs are still valuable. The key is making an informed choice after understanding the costs, rather than dogmatic "never use" or "always use".

[← Prev: InnoDB Internals](/books/high-performance-mysql/innodb-internals)
[Back to Contents](/books/high-performance-mysql)
[Next: Index Optimization →](/books/high-performance-mysql/index-guide)