/mysql

Data Types

Choose the smallest correct type — more rows per page, better cache, faster queries.

Numeric Sizes

Use BIGINT UNSIGNED for PKs — INT exhausts at ~4.3B rows. Use DECIMAL(19,4) for money, never FLOAT.

Strings

• VARCHAR(N) over TEXT when bounded — can be indexed directly.
• N matters: VARCHAR(255) vs VARCHAR(50) affects memory allocation for temp tables and sorts.

TEXT/BLOB Indexing

• You generally can't index TEXT/BLOB fully; use prefix indexes: INDEX(text_col(255)).
• Prefix length limits depend on InnoDB row format:
  • DYNAMIC/COMPRESSED: 3072 bytes max (≈768 chars with utf8mb4)
  • REDUNDANT/COMPACT: 767 bytes max (≈191 chars with utf8mb4)
• For keyword search, consider FULLTEXT indexes instead of large prefix indexes.

Date/Time

• TIMESTAMP: 4 bytes, auto-converts timezone, but 2038 limit. Use DATETIME for dates beyond 2038.

created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

JSON

Use for truly dynamic data only. Index JSON values via generated columns:

ALTER TABLE products
  ADD COLUMN color VARCHAR(50) GENERATED ALWAYS AS (attributes->>'$.color') STORED,
  ADD INDEX idx_color (color);

Prefer simpler types like integers and strings over JSON.

Generated Columns

Use generated columns for computed values, JSON extraction, or functional indexing:

-- VIRTUAL (default): computed on read, no storage
ALTER TABLE orders
  ADD COLUMN total_cents INT GENERATED ALWAYS AS (price_cents * quantity) VIRTUAL;

-- STORED: computed on write, can be indexed
ALTER TABLE products
  ADD COLUMN name_lower VARCHAR(255) GENERATED ALWAYS AS (LOWER(name)) STORED,
  ADD INDEX idx_name_lower (name_lower);

Choose VIRTUAL for simple expressions when space matters. Choose STORED when indexing is required or the expression is expensive.

ENUM/SET

Prefer lookup tables — ENUM/SET changes require ALTER TABLE, which can be slow on large tables.