Top 50 MySQL Interview Questions and Answers (2026)

MySQL questions show up in interviews for backend, full-stack, data, and DevOps roles. They are rarely about syntax. Interviewers want to know if you understand what the database actually does: how InnoDB stores rows, why an index gets ignored, what REPEATABLE READ really guarantees, and what breaks when a table hits 500 million rows.

This guide covers 50 questions in the order most interviews follow: fundamentals and data types, queries and joins, indexes and performance, transactions and locking, replication and scaling, schema design, and hands-on SQL exercises with solutions. Every answer is short enough to say out loud in an interview. If you're building a broader prep plan, start with our guide to coding interviews and use Interview Coder to practice the live parts.

The canonical reference for everything here is the official MySQL documentation. When an interviewer pushes past these answers, that's where the details live.

SQL is the query language. MySQL is a relational database management system that implements it. You write SQL; MySQL parses it, plans it, executes it, and manages storage, transactions, replication, and access control underneath. MySQL is open source, owned by Oracle, and ships in two main lines: Community and Enterprise. It speaks a dialect of SQL with its own extensions (backtick quoting, LIMIT, ON DUPLICATE KEY UPDATE, storage engine selection). Other systems like PostgreSQL and SQL Server implement the same standard with their own dialects.

Why they ask it: it filters out candidates who say "I know SQL" but have never thought about what the server itself does.

Use InnoDB. It has been the default since MySQL 5.5 and it is the right answer for almost everything. InnoDB gives you transactions, row-level locking, crash recovery via the redo log, foreign keys, and MVCC for non-blocking reads. MyISAM has none of that: no transactions, table-level locking, and a real risk of corruption on crash. Its historical advantages (smaller footprint, fast COUNT(*)) stopped mattering years ago. In an interview, name one legitimate MyISAM trait (table-level COUNT(*) is O(1) because it stores the row count), then say you'd still pick InnoDB.

Why they ask it: it's a quick check that you know MySQL has pluggable storage engines and that you understand what InnoDB buys you.

CHAR(n) is fixed-length: it always uses n characters of storage, padded with spaces. Good for values that are genuinely fixed, like country codes or hashes. VARCHAR(n) is variable-length with a 1-2 byte length prefix, and the declared length counts toward the row size limit of 65,535 bytes. TEXT types store large content, can be held off-page, and can only be indexed with a prefix length. Practical rule: CHAR for short fixed values, VARCHAR for almost everything else, TEXT only when values can genuinely exceed what a VARCHAR comfortably holds.

TIMESTAMP converts values to UTC on storage and back to the session time zone on retrieval. Its range ends in January 2038, which is now close enough to matter. DATETIME stores the literal value with no time zone conversion and supports years 1000 through 9999. Both support fractional seconds. Common practice in 2026: store DATETIME in UTC explicitly and handle time zones in the application, which avoids both the 2038 ceiling and surprise conversions when a connection's time zone differs from what you expect.

Why they ask it: time zone bugs are a classic production incident, and the 2038 limit is a fact many candidates have never heard of.

Use DECIMAL, never FLOAT or DOUBLE. Floating-point types store binary approximations, so 0.1 + 0.2 doesn't equal 0.3 and rounding errors accumulate across sums and aggregations. DECIMAL(13,4) stores exact values with fixed precision. The other accepted answer is storing amounts as integers in the smallest unit (cents), which sidesteps decimal handling entirely and is common in payment systems. Either is fine in an interview; saying FLOAT is an instant red flag.

MySQL's historical utf8 is an alias for utf8mb3: a maximum of 3 bytes per character. That cannot store emoji or any character outside the Basic Multilingual Plane, and inserts of such characters fail or get mangled. utf8mb4 is real UTF-8 with up to 4 bytes per character and has been the server default since MySQL 8.0. The interview answer: always use utf8mb4, and if you inherit a legacy utf8 table, plan a charset migration before users paste emoji into it.

NULL means "unknown," not zero or empty string. Any comparison with =, <, or <> against NULL returns NULL, not true or false, so WHERE col = NULL matches nothing. Use IS NULL and IS NOT NULL, or the null-safe operator <=> when comparing two nullable columns. Aggregates like SUM and AVG ignore NULLs, and COUNT(col) counts only non-NULL values while COUNT(*) counts rows. One myth worth killing: InnoDB indexes do include NULL values, so WHERE col IS NULL can use an index.

Why they ask it: the NOT IN plus NULL trap (see question 14) breaks real queries, and this is the foundation for it.

DELETE is DML: it removes rows one at a time, optionally with a WHERE clause, fires triggers, writes undo for rollback, and can run inside a transaction. TRUNCATE is DDL: it drops and recreates the table, resets AUTO_INCREMENT, fires no row triggers, causes an implicit commit, and cannot be rolled back. DROP removes the table itself, data and definition. Speed follows from mechanics: TRUNCATE is near-instant on huge tables where DELETE would grind through millions of rows of undo logging.

ENUM stores one value from a fixed list, internally as a small integer, so it's compact and self-documenting. The downsides: adding a new value requires an ALTER TABLE, sorting follows the definition order rather than alphabetical order, and invalid inserts can silently become empty strings under loose SQL modes. Many teams prefer a TINYINT with constants in application code, or a lookup table with a foreign key. A good answer names the tradeoff instead of declaring ENUM categorically bad.

INNER JOIN returns rows that match in both tables. LEFT JOIN returns all rows from the left table, with NULLs where the right side has no match. RIGHT JOIN is the mirror image. CROSS JOIN produces the Cartesian product. A self join is an ordinary join of a table to itself, useful for hierarchies like employees and managers. MySQL also has STRAIGHT_JOIN, a hint forcing the optimizer to join tables in the written order. Notably absent: FULL OUTER JOIN, which MySQL does not implement.

Combine a LEFT JOIN and a RIGHT JOIN with UNION ALL, excluding matched rows from the second query so they don't appear twice:

SELECT a.id, b.id
FROM a LEFT JOIN b ON a.id = b.a_id
UNION ALL
SELECT a.id, b.id
FROM a RIGHT JOIN b ON a.id = b.a_id
WHERE a.id IS NULL;

The first query covers all left rows including matches; the second adds right rows with no left match. Using plain UNION instead also works but silently deduplicates identical rows, which may not be what you want.

Why they ask it: it tests whether you actually know MySQL's limits or just assume every database supports the full standard.

WHERE filters rows before grouping; HAVING filters groups after aggregation. So WHERE cannot reference aggregate results, and HAVING exists precisely for conditions like HAVING COUNT(*) > 5. Performance angle worth saying out loud: push every condition you can into WHERE, because filtering early means fewer rows get grouped and aggregated. Using HAVING for a plain column filter that belongs in WHERE works in MySQL, but it wastes work and reads as sloppy in an interview.

UNION removes duplicate rows across the combined result; UNION ALL keeps everything. Deduplication isn't free: MySQL has to sort or hash the combined result to find duplicates, often via a temporary table. UNION ALL just streams the results back to back. So the rule is: use UNION ALL unless you specifically need duplicates removed. Saying that unprompted, and explaining the temporary-table cost, signals real-world experience.

Functionally: IN checks membership against a list or subquery, EXISTS checks whether a correlated subquery returns any row, and a JOIN combines rows. The modern MySQL optimizer rewrites many IN and EXISTS subqueries into semi-joins, so the old "EXISTS is always faster" advice is outdated; check EXPLAIN instead. The trap interviewers fish for: NOT IN against a subquery that can return NULL matches nothing at all, because the comparison with NULL is never true. NOT EXISTS doesn't have that problem and is the safer anti-join.

A common table expression is a named subquery defined with WITH that you can reference like a table in the rest of the statement. It replaces deeply nested derived tables and makes queries readable. A recursive CTE references itself: it starts from an anchor row set and repeatedly applies the recursive part until no new rows appear. Standard use cases are hierarchies and series: walking an org chart from one manager down through every report, or generating a sequence of dates. MySQL has supported both since 8.0.

Window functions compute a value across a set of related rows without collapsing them the way GROUP BY does. You define the window with OVER (PARTITION BY ... ORDER BY ...). For ranking ties, the three differ: ROW_NUMBER assigns unique sequential numbers, breaking ties arbitrarily; RANK gives tied rows the same rank and then skips (1, 1, 3); DENSE_RANK gives ties the same rank without gaps (1, 1, 2). Window functions are the clean answer to "top N per group" problems, which we solve in the exercises section below.

Why they ask it: top-N-per-group is the single most common practical SQL exercise, and window functions are the expected tool.

LIMIT 20 OFFSET 100000 makes MySQL read and discard 100,000 rows before returning 20. The cost grows linearly with the offset, so deep pages get slower and slower. The fix is keyset pagination (also called seek method): remember the last value seen and filter on it.

SELECT id, title FROM articles
WHERE id < 1057
ORDER BY id DESC
LIMIT 20;

This is an indexed range scan regardless of how deep you are. The tradeoff: no jumping to an arbitrary page number, which most infinite-scroll UIs don't need anyway.

It rejects queries that select a non-aggregated column not functionally dependent on the GROUP BY columns. Without it, MySQL would silently return an arbitrary value from the group, which is a correctness bug waiting to happen. It has been on by default since MySQL 5.7. If you intentionally don't care which value comes back, wrap the column in ANY_VALUE(). This question often comes from interviewers who got burned migrating an app that relied on the old loose behavior.

InnoDB indexes are B+trees: balanced trees where internal nodes guide the search and all values live in leaf pages, which are linked together in order. Lookups descend from the root in O(log n) page reads — typically 3 or 4 for even very large tables. Because leaves are ordered and linked, the same structure serves equality lookups, range scans (BETWEEN, <, >), prefix matches (LIKE 'abc%'), and ORDER BY on the indexed columns without a sort. That ordering property is what separates B-trees from hash indexes, which only handle equality.

In InnoDB, the clustered index is the table. Rows are physically stored in the leaf pages of the primary key's B+tree, ordered by primary key. Every other index is a secondary index whose leaf entries hold the indexed columns plus the primary key value. A secondary index lookup therefore does two traversals: one in the secondary index to find the PK, then one in the clustered index to fetch the row. Two consequences worth stating: primary key lookups are the fastest access path, and a large primary key (like a long string) inflates every secondary index on the table.

An index that contains every column a query needs, so MySQL answers the query entirely from the index without touching the clustered index. EXPLAIN shows Using index in the Extra column when this happens. Example: with an index on (user_id, created_at, status), the query SELECT status FROM orders WHERE user_id = 7 ORDER BY created_at DESC never reads a full row. Covering indexes are one of the highest-leverage optimizations for hot read queries because they eliminate the secondary-to-clustered double lookup from the previous question.

A composite index on (a, b, c) sorts entries by a, then b within a, then c within b. MySQL can use it for conditions on a, on a, b, or on a, b, c — any leftmost prefix. A query filtering only on b can't seek into the index because entries aren't ordered by b alone. One more subtlety that separates strong candidates: once a column is used for a range condition (a = 5 AND b > 10), columns after it in the index (c) can no longer be used for seeking. Order your columns equality-first, ranges last.

Why they ask it: most real-world indexing mistakes are composite indexes with columns in the wrong order.

The common cases: a function or expression wrapped around the column (WHERE YEAR(created_at) = 2026), a leading-wildcard pattern (LIKE '%term'), an implicit type conversion (comparing a string column to a number), mismatched character sets or collations between joined columns, and skipping the leftmost column of a composite index. The optimizer may also choose a full table scan deliberately when the condition matches a large fraction of the table, because scanning sequentially beats millions of secondary-index row lookups. Fixes: rewrite predicates as ranges (created_at >= '2026-01-01' AND created_at < '2027-01-01'), match types, or add a functional index, which MySQL 8.0 supports.

Start with type, the access method, from best to worst: const, eq_ref, ref, range, index, ALL. ALL is a full table scan. Then check key (which index was chosen), rows (estimated rows examined), and Extra: Using index is good (covering index), Using filesort and Using temporary are sort and temp-table work worth investigating. EXPLAIN FORMAT=JSON adds cost estimates. EXPLAIN ANALYZE actually executes the query and reports real timings and row counts per step, which is the fastest way to see where an estimate diverged from reality.

Three layers. The slow query log records statements exceeding long_query_time; aggregate it with pt-query-digest to rank by total time, not single worst case. The Performance Schema tracks statement digests live — events_statements_summary_by_digest shows which normalized queries consume the most total latency, and the sys schema wraps it in readable views. Then take the top offenders through EXPLAIN ANALYZE. The point to land in an interview: optimize by total time consumed across all executions, because a 50ms query running 10,000 times per minute beats a 5-second report query as the real problem.

Every index must be maintained on every INSERT, UPDATE of indexed columns, and DELETE, so writes pay a tax per index. Indexes also consume disk and buffer pool memory, crowding out actual data pages from cache. Too many similar indexes can confuse the optimizer into picking the wrong one. Hygiene: drop redundant indexes (an index on (a) is redundant if (a, b) exists), and use invisible indexes — MySQL 8.0 lets you hide an index from the optimizer to test whether anything misses it before you drop it for real.

Atomicity: a transaction fully commits or fully rolls back; InnoDB uses undo logs to reverse incomplete work. Consistency: constraints, foreign keys, and the other three properties together keep data valid. Isolation: concurrent transactions don't trample each other, implemented with MVCC and locking. Durability: once committed, data survives a crash; InnoDB writes the redo log before flushing data pages (write-ahead logging) and uses the doublewrite buffer to guard against torn page writes. Tie each letter to its mechanism — that's what distinguishes a real answer from a memorized acronym.

Four levels, weakest to strongest. READ UNCOMMITTED: can read uncommitted changes (dirty reads). READ COMMITTED: only committed data, but two reads in one transaction can see different values (non-repeatable reads). REPEATABLE READ: consistent snapshot for the whole transaction — this is InnoDB's default. SERIALIZABLE: transactions behave as if executed one at a time. The MySQL-specific point worth making: InnoDB's REPEATABLE READ also blocks most phantom reads, using next-key locks for locking reads, which is stronger than the SQL standard requires at that level.

Multi-version concurrency control: instead of making readers wait for writers, InnoDB keeps old row versions in undo logs and shows each transaction a consistent snapshot. When a transaction starts reading under REPEATABLE READ, it gets a read view; rows modified after that point are reconstructed from undo history as they looked before. Plain SELECTs therefore take no row locks at all. Writers still lock rows against other writers. This is why MySQL handles read-heavy concurrency well, and why long-running transactions are a problem — they force undo history to be kept alive (question 34).

All three are InnoDB row-level locks taken on index records. A record lock locks one index entry. A gap lock locks the open interval between index entries, preventing inserts into that range. A next-key lock is the combination: the record plus the gap before it. Under REPEATABLE READ, locking reads use next-key locks to stop phantom rows from appearing in a range you've locked. Under READ COMMITTED, gap locking is mostly disabled, which reduces deadlocks but allows phantoms. Gap locks surprise people: an UPDATE on a range can block inserts of rows that don't even exist yet.

Why they ask it: gap locks cause real deadlocks in production, and most candidates have never heard of them.

Both are locking reads. FOR UPDATE takes exclusive locks on the rows read: other transactions can't modify them or take their own locking reads until you commit. FOR SHARE takes shared locks: others can also read-share, but nobody can modify. Use FOR UPDATE for read-then-write flows like decrementing inventory. MySQL 8.0 added two modifiers that come up in queue-worker designs: NOWAIT fails immediately instead of waiting for a lock, and SKIP LOCKED skips locked rows — the standard pattern for multiple workers pulling jobs from the same table.

Two transactions hold locks the other needs, in a cycle: A locks row 1 and waits for row 2; B locks row 2 and waits for row 1. InnoDB detects the cycle immediately and rolls back the cheaper transaction with error 1213. Handling, in order: design the application to retry the failed transaction (deadlocks are a normal event, not a bug); acquire locks in a consistent order across code paths; keep transactions short; index your lookups so locking reads don't lock more rows than intended. To diagnose, SHOW ENGINE INNODB STATUS prints the latest deadlock with both transactions' statements and locks.

The redo log is write-ahead durability: every change is recorded there and flushed at commit, before the modified data pages reach disk. After a crash, InnoDB replays the redo log to recover committed changes. The undo log is the opposite direction: it stores previous row versions, used to roll back uncommitted transactions and to serve MVCC snapshots to readers. Compact framing: redo replays the committed, undo unwinds the uncommitted — and undo doubles as the version store for consistent reads.

Three reasons. First, MVCC: as long as an old transaction holds a read view, InnoDB cannot purge the undo history any snapshot might need, so undo grows and every consistent read gets slower reconstructing old versions. Watch history list length in SHOW ENGINE INNODB STATUS. Second, locks: rows stay locked until commit, blocking other writers for the duration. Third, operations: huge transactions replicate as one unit, stalling replicas, and they block DDL through metadata locks. The practical rule: keep transactions seconds long, and batch large backfills into chunks of a few thousand rows.

The source server records every committed change in its binary log. A replica connects with a receiver (I/O) thread that streams those events into its local relay log, and applier (SQL) threads execute them against the replica's data — in parallel since MySQL 5.7/8.0. Replication is asynchronous by default: the source commits without waiting for any replica, so replicas can lag. Typical uses: scaling reads across replicas, failover targets, and isolating analytics or backups from production traffic. Operating this is a routine topic in DevOps interviews too.

Three formats. STATEMENT logs the SQL text — compact, but nondeterministic statements (NOW(), UUID(), LIMIT without ORDER BY) can produce different results on replicas. ROW logs the actual before/after row images — safe and the default since MySQL 5.7, at the cost of bigger logs for wide updates. MIXED uses statement format but switches to row for unsafe statements. The standard answer in 2026 is ROW: it's the default, it's the only safe choice for correctness, and row-based events feed change data capture tools like Debezium, which is a nice bonus point to mention.

A global transaction identifier is a unique ID (source_uuid:sequence) stamped on every transaction. With GTIDs, a replica says "I have executed this set of transactions" instead of "I'm at byte offset X in binlog file Y." That makes failover dramatically simpler: point a replica at a new source with MASTER_AUTO_POSITION/auto-positioning and it works out what it's missing on its own. Without GTIDs, you have to compute file-and-position coordinates manually during a failover, which is exactly the moment you least want to be doing arithmetic. New deployments should use GTIDs.

Asynchronous (the default): the source commits and acknowledges the client without waiting for any replica. Fastest, but if the source dies before replicas catch up, those transactions are lost on failover. Semi-synchronous: the commit waits until at least one replica acknowledges it has received (not applied) the events. That bounds data loss at the cost of added commit latency. For stronger guarantees, Group Replication / InnoDB Cluster uses a consensus protocol across a group of servers with automatic failover. Know which guarantee each level actually gives — semi-sync confirms receipt, not execution.

First measure it: SHOW REPLICA STATUS reports seconds behind source, and heartbeat tables give a more honest number. Then attack causes: enable parallel appliers, break up huge transactions (one 10-million-row UPDATE replicates as one unit), and fix replica-side long queries. For correctness, handle read-your-own-writes: a user who just posted must see their post, so route post-write reads to the source, pin the session to the source briefly, or use GTID-based waits (WAIT_FOR_EXECUTED_GTID_SET) so a replica read blocks until it has caught up to that write.

Why they ask it: stale-read bugs from lagging replicas are among the most common distributed-system bugs in CRUD apps.

Partitioning splits one table into pieces inside a single MySQL server, by RANGE, LIST, HASH, or KEY. Queries that filter on the partition key only touch relevant partitions (pruning), and dropping a partition is an instant way to expire old data — great for time-series tables. But it doesn't add capacity: one server still does the work. Sharding splits data across multiple servers, usually by hashing a key like user_id, with routing handled in the application or by a layer like Vitess or ProxySQL. Sharding scales writes and storage, at the cost of losing cross-shard transactions and joins. Reads scale with replicas; writes scale with shards. This distinction comes up constantly in system design interviews.

1NF: atomic values — no comma-separated lists or repeating column groups. 2NF: no partial dependencies — every non-key column depends on the whole composite key, not part of it. 3NF: no transitive dependencies — non-key columns depend on the key only, so city and zip_code deriving from each other belong in their own table. Normalize by default: it eliminates update anomalies. Denormalize deliberately when a measured read path hurts — duplicating a username onto posts to avoid a hot join, or maintaining a counter column instead of COUNT(*). Name the cost: every denormalization adds an update path you must keep consistent.

Auto-increment integers are small (8 bytes as BIGINT), ordered, and append to the right edge of the clustered index, so inserts are fast and pages stay full. Their downside: they're guessable and awkward to generate across shards. Random UUIDs (v4) are the opposite: 16 bytes, globally unique, but random inserts scatter across the clustered index causing page splits and cold-cache reads, and they inflate every secondary index. The 2026 middle ground: time-ordered UUIDv7 (RFC 9562) stored as BINARY(16), or MySQL's UUID_TO_BIN(uuid, 1) trick for v1 UUIDs. Strong answer: internal auto-increment PK plus a public UUID with a unique index.

Give both sides. For: the database guarantees referential integrity no matter which code path writes, which catches whole classes of bugs. Against, at scale: FK checks add locking on parent rows during writes, they complicate online schema-change tools like gh-ost and pt-online-schema-change, and they don't work across shards — which is why many large MySQL shops (famously, much of the high-scale web world) drop FKs and enforce integrity in the application plus periodic consistency checks. Reasonable default: keep FKs until you have a measured reason not to, and know exactly what you're giving up when you remove them.

Use JSON for genuinely variable attributes: third-party API payloads, per-integration settings, sparse product attributes where columns would mostly be NULL. MySQL validates JSON on insert, stores it in an efficient binary format, and lets you index inside it through generated columns or multi-valued indexes. Don't use JSON for fields you regularly filter, join, or aggregate on — those belong in real columns with real indexes. The one-line answer interviewers like: relational core, JSON edges. If you find yourself writing JSON_EXTRACT in every query's WHERE clause, that field should be a column.

Layered answer. First, native online DDL: many operations run with ALGORITHM=INPLACE without blocking reads/writes, and MySQL 8.0 made common cases like adding a column ALGORITHM=INSTANT — a metadata-only change that finishes immediately. Second, when the operation requires a full table rebuild, use an external tool: gh-ost or pt-online-schema-change builds a shadow copy, backfills it while tracking ongoing changes (via binlog or triggers), then swaps names atomically. Third, the trap to mention: every ALTER needs a metadata lock, so one long-running query can stall the ALTER and queue every other query behind it.

Why they ask it: it cleanly separates people who've operated MySQL from people who've only queried it.

Interviewers use these to watch you write SQL live, not to test memorization. Talk through your approach before typing. The schema for all five: employees(id, name, salary, department_id, manager_id) and users(id, email).

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

This returns NULL when no second salary exists, which is usually the wanted behavior. Alternative with DISTINCT and offset:

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Note the difference: the second version returns an empty result set rather than NULL when there's no second value. Mention DENSE_RANK as the generalization to "nth highest" — it handles ties correctly where OFFSET arithmetic gets fragile.

SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Group by the candidate column, then filter groups with HAVING, since WHERE can't see aggregate results (question 12). If the interviewer extends to "show the full duplicate rows," join this back:

SELECT u.*
FROM users u
JOIN (
  SELECT email FROM users
  GROUP BY email HAVING COUNT(*) > 1
) d ON u.email = d.email;

DELETE u1
FROM users u1
JOIN users u2
  ON u1.email = u2.email
 AND u1.id > u2.id;

This is MySQL's multi-table delete: self-join the table on the duplicate key, and delete every row that has a twin with a smaller id. Each row with a duplicate matches at least one row with a lower id and gets removed; the lowest id in each group matches nothing and survives. Follow up unprompted with the prevention: add a unique index on email so duplicates can't come back, using INSERT ... ON DUPLICATE KEY UPDATE or INSERT IGNORE at write time.

SELECT department_id, name, salary
FROM (
  SELECT e.*,
         RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employees e
) ranked
WHERE rnk = 1;

Window functions can't appear in WHERE directly, hence the derived table (or a CTE). RANK keeps ties — two people sharing the top salary both appear; switch to ROW_NUMBER to force exactly one per department. The pre-8.0 version joins against a GROUP BY department_id, MAX(salary) subquery; knowing both shows range.

SELECT e.name AS employee, e.salary,
       m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

A self join: the table appears twice under different aliases, once as the employee and once as the manager. The join condition links each employee to their manager's row; the WHERE compares across the two aliases. Top-level employees with manager_id IS NULL drop out naturally with the inner join — say that out loud, because handling the NULL case unprompted is exactly the kind of edge-case awareness interviewers score.

Don't memorize 50 answers. Build a local MySQL instance, load a few million rows of fake data, and verify the claims yourself: run EXPLAIN ANALYZE before and after adding an index, trigger a deadlock on purpose, watch replication lag grow under a big transaction. One evening of that beats a week of flashcard review, because interviewers probe one level deeper than whatever you say first. Round it out with the rest of your stack — most MySQL-heavy roles also test Java or another backend language, and Microsoft-stack roles lean on SQL Server questions instead. For the overall process, our software engineer interview prep guide covers how to sequence it all.

For the live rounds themselves, Interview Coder is a desktop app that assists you in real time during technical interviews, with coding answers powered by Claude Sonnet 4.6, Anthropic's latest Sonnet model. There's a free tier to try it, and Pro is $299/month or a one-time $799 for lifetime access.