learn deep. ship safe.Practice testsFollow ByteMunSubscribe
SQL and MySQL · Senior

Advanced SQL and MySQL Interview Questions

Advanced MySQL interview questions for senior engineers and DBAs: replication lag, partitioning vs sharding, online schema changes, UUID keys and the clustered index, SKIP LOCKED queues, redo logs, next-key locks and MVCC.

CoversIndexesMySQLQuery optimisationReplicationPartitioning and shardingSchema designTransactions

Practice questions with answers

Tap an option: green is right, red is wrong, and the explanation says why.

0/10 answered · ✓ 0 ✗ 0
Question 1 of 101 / 10
Q1Choose one answermedium

Your app writes to a MySQL primary and reads from an asynchronous replica. A user updates their profile and the next page shows the old value. Why?

Show answer and explanation

Answer: D. Replication lag: the replica hasn't applied the change yet; route that user's reads to the primary for a while, or wait for the replica to catch up

With standard asynchronous replication the primary commits and writes the binary log; the replica fetches and applies events a little later, sometimes seconds later under load. Reads that must see your own writes should go to the primary, use a session flag after writes, or wait for the replica to apply a known GTID (WAIT_FOR_EXECUTED_GTID_SET). Replicas never roll back the primary's commits and don't sync daily. Browser caching can cause stale pages too, but read-after-write on a replica is the classic cause.

Q2Choose one answermedium

What is the difference between partitioning and sharding?

Show answer and explanation

Answer: B. Partitioning splits one table into parts inside the same database server; sharding spreads data across several independent servers

MySQL partitioning (by RANGE, LIST, HASH or KEY) keeps all parts in one instance; it helps prune scans and drop old data quickly (drop a monthly partition), and every unique key, including the primary key, must contain the partition column. Sharding puts different rows on different servers, usually chosen by a shard key such as customer id, which scales writes but makes cross-shard joins and transactions the application's problem. Both split rows, not columns, and the server counts are the other way round.

Q3Choose one answerhard

A users table has 400 million rows and you need to add a column without blocking writes. What is the safest approach in MySQL 8?

Show answer and explanation

Answer: D. Use ALTER TABLE ... ADD COLUMN with ALGORITHM=INSTANT where supported, otherwise an online schema-change tool such as gh-ost or pt-online-schema-change

MySQL 8.0.12+ can add a column instantly (only metadata changes; from 8.0.29 at any position), and stating ALGORITHM=INSTANT makes the statement fail instead of silently copying the table. When an instant change isn't possible, gh-ost or pt-online-schema-change copy rows into a shadow table in small chunks and swap tables at the end, keeping writes flowing. A blocking ALTER at peak risks an outage, changing only the replica breaks replication, and a CSV reload means long downtime.

Q4Choose one answerhard

Why can random UUIDv4 primary keys hurt InnoDB performance, and what is a common fix?

Show answer and explanation

Answer: A. Random keys insert all over the clustered B+tree, causing page splits and poor cache use; use AUTO_INCREMENT, or time-ordered IDs stored as BINARY(16)

InnoDB stores rows in primary-key order. Sequential keys append to the right-most page; random UUIDv4 values land on random pages, so the buffer pool must hold the whole index hot, pages split often and fill only partly, and every secondary index carries the larger key. Fixes: an AUTO_INCREMENT key (with a separate public UUID if needed), or time-ordered IDs such as UUIDv7 (or MySQL's own UUID() values packed with UUID_TO_BIN(uuid, 1), which moves the time part first), stored as BINARY(16) rather than CHAR(36). UUIDs are unique and generating them is cheap.

Q5Choose one answerhard

Several workers pick jobs from a jobs table. Which query lets each worker claim a different job without blocking on the others?

Show answer and explanation

Answer: C. SELECT id FROM jobs WHERE status = 'queued' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED, then update it in the same transaction

FOR UPDATE SKIP LOCKED (MySQL 8.0.1+) locks the first unlocked matching row and silently skips rows other workers have locked, so workers don't queue behind each other. Claim and mark the job in one short transaction. Reading without a lock lets two workers grab the same job, LOCK TABLES serialises everything, and a shared lock lets several workers read the same job and then deadlock when they try to update it.

Q6Choose one answerhard

What does InnoDB's redo log protect against?

Show answer and explanation

Answer: A. Losing committed transactions in a crash: changes are logged before dirty pages are flushed, and the log is replayed on restart

InnoDB changes pages in the buffer pool and writes a compact record of each change to the redo log. With innodb_flush_log_at_trx_commit=1 (the default) the log is flushed to disk at every commit, so after a crash InnoDB replays the log and committed work survives: the D in ACID. Undo logs, not redo, provide rollback and the old row versions that MVCC readers see. It does nothing for disk space or query caching.

Q7Choose one answerhard

Under REPEATABLE READ, transaction A runs SELECT ... WHERE amount > 100 FOR UPDATE. What stops transaction B from inserting a new row with amount 150 until A finishes?

Show answer and explanation

Answer: B. Next-key locks: InnoDB locks the matching index records and the gaps between them, so inserts into that range wait

For locking reads under REPEATABLE READ, InnoDB uses next-key locks (a record lock plus a lock on the gap before it) over the scanned index range. B's insert of 150 needs an insert-intention lock in a locked gap, so it waits, which prevents phantom rows for A. Under READ COMMITTED most gap locking is turned off and the insert would go ahead. Plain SELECTs take no table lock, and MySQL 8 removed the query cache.

Q8Choose one answerhard

Monthly reports scan a huge orders table and slow down the primary. Which approach is usually best?

Show answer and explanation

Answer: D. Run reports on a replica or a separate analytics store, and pre-aggregate into summary tables when the same totals are read often

Heavy analytical scans compete with the transactional workload for buffer pool, I/O and CPU. Moving them to a read replica or a columnar warehouse isolates the load, and summary tables refreshed on a schedule turn repeated full scans into small lookups. Indexing every column slows every write and won't help aggregate scans much, SERIALIZABLE adds locking, and turning off the binary log breaks replication and point-in-time recovery.

Q9Choose one answerhard

What is the main risk of semi-synchronous replication compared with asynchronous replication?

Show answer and explanation

Answer: D. Commits wait for at least one replica to acknowledge receiving the change, so write latency goes up; in return less data is lost if the primary fails

With semi-sync, the primary waits after commit until at least one replica confirms it has received and logged the transaction (not necessarily applied it). That narrows the window of committed-but-lost transactions on failover, at the cost of a network round trip per commit; if no replica answers within the timeout, MySQL falls back to asynchronous. Replicas still don't accept writes, there is no shared disk, and it relies on the binary log rather than disabling it.

Q10Select all that applyhard

Which of these help make a MySQL schema change safe in production?

Show answer and explanation

Answer: C. Testing the migration on a recent copy of production data to measure its time and locking + D. Setting a short lock_wait_timeout so the ALTER gives up instead of queuing behind a long transaction + E. Making the change backward compatible first (expand), deploying code, then removing the old column later (contract)

Expand-and-contract keeps old and new code working during a rollout. Rehearsing on production-sized data shows whether an ALTER is instant, in place or a full copy. A short lock_wait_timeout matters because even instant DDL needs a brief metadata lock, and waiting behind a long-running query blocks every query after it. DDL in MySQL commits implicitly, so it can't be rolled back inside a transaction, and dropping a column in the same deploy breaks instances still running old code.

Take it as a timed test

  • 12 questions
  • 18 min
  • Pass mark 60%
  • Answers shown at the end

Sign in with your email to take the test: we send a 6-digit code, no password. Your scores and reports are saved to your account. The test also uses 10 questions that aren't shown above, so practise for understanding, not memory.

About this paper

Senior SQL interviews are about running MySQL under real load. This paper covers the production questions that come up for senior backend, platform and DBA roles: read-after-write problems with asynchronous replicas, partitioning versus sharding, adding a column to a 400-million-row table without downtime, why random UUID primary keys hurt InnoDB, building a job queue with FOR UPDATE SKIP LOCKED, what the redo log and undo log are for, how next-key locks stop phantoms, and safe expand-and-contract schema changes. Ten questions are on this page with explanations; the timed test draws 12 from a bank of 20.

The explanations name the MySQL 8 version where a feature arrived, because interviewers often ask what changed between 5.7 and 8.0.

Written by Published Answers checked by Deepak

More SQL and MySQL quizzes

All SQL and MySQL quizzes →