Expect InnoDB, indexes/EXPLAIN, replication lag, charset, AUTO_INCREMENT, ON DUPLICATE KEY, and when to choose Postgres instead.
Top themes
- InnoDB vs MyISAM, transactions, isolation
- Index design and EXPLAIN (type, key, rows)
- utf8mb4, collation, and ORM connection charset
- Replication, backups, least-privilege users
- SQL mode and ONLY_FULL_GROUP_BY
Story to prepare
60 seconds: WordPress/Laravel app slow list endpoint → EXPLAIN showed ALL scan → composite index on (status, created_at) → latency drop.
Practice: Review prior lessons, then try interview questions without notes.
Important interview questions and answers
- Q: When pick Postgres over MySQL?
A: Heavy analytics, JSONB indexing, geospatial, strict SQL—MySQL when stack/hosting is MySQL. - Q: LAST_INSERT_ID race?
A: Per session—safe within one connection; not global across servers.
Self-check
- Name three EXPLAIN columns.
- Why utf8mb4?
Tip: Prepare one EXPLAIN fix story from a real slow endpoint.
Interview prep
- InnoDB why?
Transactions, FK, row-level locking.
- EXPLAIN columns?
type, key, rows, Extra are core four.