mysql-patterns

MySQL / MariaDB Patterns

This is the query- and schema-design layer for MySQL (InnoDB) and MariaDB, written for a team whose default database is PostgreSQL (see postgres-patterns, postgres-hotel-partitioning) but that also operates MySQL/MariaDB-backed systems (commonly paired with CodeIgniter/Laravel legacy stacks — see php-codeigniter-patterns, laravel-patterns). Every section below calls out where MySQL's real, documented behavior differs from Postgres, because that difference — not the syntax — is where migrated assumptions actually break.

Primary keys: the clustered-index difference that changes everything else

InnoDB stores a table's rows physically ordered by primary key — the PK is the clustering key, and every secondary index stores the PK value as its row pointer (not a physical row address, the way Postgres's heap-based secondary indexes work). Two direct consequences:

• A non-monotonic primary key (e.g. a random UUID) causes page splits and index fragmentation on every insert, because new rows aren't appended at the end of physical storage — they're inserted wherever the PK value's ordering says they belong. This is a far more serious performance concern in InnoDB than in Postgres, where a random UUID PK costs only a (already-real) B-tree-locality penalty on that one index, not the clustering order of the whole table.
• Every secondary index lookup requires a second lookup by PK value (unless the query is covered), because a secondary index only stores the PK, not the full row — a wide/expensive PK type inflates every secondary index's storage cost, not just the primary key's own.

Prefer an auto-increment (or otherwise monotonically increasing) BIGINT primary key for InnoDB tables. If a UUID is required for external referencing, keep it as a separate unique-indexed column rather than the clustering PK, or use a time-ordered UUID variant (UUIDv7-style) specifically to preserve insertion order.

Charset: utf8mb4, never utf8