mysql-expert

MySQL Expert

MySQL differs from PostgreSQL in ways that matter for design, not just syntax: the clustered primary key, the storage-engine boundary, and a replication model built on the binary log.

Core Concepts

InnoDB Is the Database

Everything below assumes InnoDB. Rows are stored inside the primary key index — the table is the primary key B-tree. Three consequences drive most design decisions:

1. The primary key is present in every secondary index, so a wide primary key inflates every index on the table.
2. A secondary index lookup costs two traversals: the index, then the primary key. Unless the index covers the query.
3. Inserts in primary key order are cheap; random primary keys cause page splits and fragmentation.

This is why a BIGINT AUTO_INCREMENT or a time-ordered UUID (UUIDv7) beats a random UUIDv4 primary key by a wide margin on large tables.

Character Sets

Use utf8mb4 and nothing else. MySQL's utf8 is a three-byte subset that cannot store emoji or some CJK characters, and it fails by truncating or erroring at insert time.

CREATE TABLE orders (…) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Collation determines comparison and sorting. utf8mb4_0900_ai_ci is accent-insensitive and case-insensitive; use utf8mb4_0900_as_cs or _bin when you need exact matching, for example on tokens or hashes.

Isolation and Locking

The default is REPEATABLE READ, which is stricter than PostgreSQL's default and uses gap locks that surprise people migrating across.

SELECT @@transaction_isolation;
SET SESSION transaction_isolation = 'READ-COMMITTED';   -- often the better default

READ COMMITTED reduces gap locking and deadlocks for typical OLTP workloads. Change it deliberately and test — it also changes replication semantics for statement-based binlog formats.

Schema Design

CREATE TABLE orders (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id     BINARY(16) NOT NULL,                    -- UUID stored compactly
  customer_id   BIGINT UNSIGNED NOT NULL,
  status        ENUM('pending','paid','shipped','cancelled') NOT NULL,
  total_minor   BIGINT NOT NULL,                        -- money as integer minor units
  currency      CHAR(3) NOT NULL,
  created_at    TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_orders_public_id (public_id),
  KEY idx_orders_customer_created (customer_id, created_at),
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;