backend / databases / sql / 17_mysql.md

MySQL

5 interview angles 4 min read source

MySQL

Most of this repo assumes Postgres. This file covers what differs, because “we’re on MySQL” is a common interview follow-up and the differences are real.

Storage engines

InnoDB is the only one that matters: ACID, row-level locking, foreign keys, crash recovery. MyISAM is legacy — table-level locks, no transactions, no FK enforcement. If someone says “MySQL doesn’t support transactions”, they’re remembering MyISAM.

The clustered index — the biggest structural difference

InnoDB stores the table in primary key order, and the primary key is the table. Secondary indexes store the primary key value, not a row pointer.

Consequences:

  • A secondary index lookup does two lookups: index → primary key → row. Postgres goes straight to a heap tuple.
  • A wide primary key inflates every secondary index, since each entry embeds it. A UUID primary key is measurably worse here than in Postgres.
  • Random primary key inserts cause page splits. Sequential IDs append cleanly; random UUIDs fragment the table. UUIDv7 (time-ordered) fixes this and is the right choice if you need UUIDs.
  • Covering indexes are more valuable — including the columns you select avoids the second lookup entirely.

That first point drives most MySQL index design, and it’s the thing to say when asked how MySQL differs.

Where MySQL differs from Postgres

MySQL Postgres
Default isolation REPEATABLE READ READ COMMITTED
Table storage clustered by PK heap
DDL in transactions no — implicit commit yes, fully transactional
UPSERT INSERT ... ON DUPLICATE KEY UPDATE INSERT ... ON CONFLICT
Case sensitivity collation-dependent, often insensitive sensitive
JSON JSON type, functional indexes JSONB with GIN — richer
Arrays, custom types no yes
Window functions, CTEs 8.0+ long-standing
Full-text search built in, adequate built in, or Elasticsearch

DDL is not transactional. A failed migration in MySQL leaves the schema half-changed and you cannot roll back. In Postgres you can wrap a migration in a transaction and abort cleanly. This changes how you write and test migrations — see 16_alembic.md.

REPEATABLE READ by default means MySQL takes consistent snapshots for the whole transaction, so you won’t see non-repeatable reads that Postgres’s READ COMMITTED allows. It also makes some lock behaviour surprising — gap locks on range scans are a common source of deadlocks people don’t expect. See 08_transactions_isolation.md.

Practical notes

-- Upsert
INSERT INTO users (id, email, seen) VALUES (1, 'a@b.com', NOW())
ON DUPLICATE KEY UPDATE seen = VALUES(seen);

-- Explain
EXPLAIN ANALYZE SELECT ...;    -- 8.0.18+; before that, EXPLAIN FORMAT=JSON
  • Character set: use utf8mb4, never utf8. MySQL’s utf8 is three bytes and cannot store emoji or some CJK characters. This is a real, still-common bug.
  • ONLY_FULL_GROUP_BY is on by default in 8.0. Older code relying on selecting non-aggregated columns will break, which is usually a good thing.
  • Online DDL is better in 8.0, but for large tables the ecosystem still uses gh-ost or pt-online-schema-change to avoid long locks.
  • Connection handling: MySQL threads are cheaper than Postgres processes, so connection pressure is less acute — but pool anyway.

Python

# SQLAlchemy - the driver is the choice
create_engine("mysql+pymysql://...")      # pure Python, portable
create_engine("mysql+mysqldb://...")      # C extension, faster
create_async_engine("mysql+aiomysql://...")

SQLAlchemy abstracts most differences. What leaks through: ON CONFLICT vs ON DUPLICATE KEY, RETURNING support (MySQL 8.0.31+ has it only for some statements), and array/JSONB-specific operators.

Interview angle

  • “How does MySQL differ from Postgres?” — lead with the clustered index: InnoDB stores the table in primary key order and secondary indexes hold the primary key, so secondary lookups cost two traversals and a wide primary key inflates every index. Then non-transactional DDL and REPEATABLE READ as the default isolation.
  • “Why does UUID primary key choice matter more in MySQL?” — random UUIDs cause page splits in the clustered index and are embedded in every secondary index. Use UUIDv7 or a sequential surrogate key.
  • “What happens if a migration fails halfway?” — in MySQL the schema is left partially changed, because DDL causes an implicit commit and can’t be rolled back. In Postgres you can wrap it in a transaction and abort cleanly.
  • “What’s the utf8 trap?” — MySQL’s utf8 is a three-byte encoding that can’t store emoji or some CJK characters. Always use utf8mb4.
  • “How do you alter a huge table without downtime?” — 8.0’s online DDL where it applies, otherwise gh-ost or pt-online-schema-change, which build a shadow table and swap.
Previous
Alembic