MySQL interview questions grouped by junior, intermediate, advanced, and DBA level, with concise model answers, what interviewers are really testing, and the.
Most candidates preparing for a database interview don't have a knowledge problem. They have a sequencing problem. MySQL interview questions at the junior level test something completely different from what gets asked at the intermediate round — and the intermediate round tests something completely different from what a DBA screen cares about. Studying from a flat list of 50 questions treats all of those as equivalent, which is why candidates who've done hours of prep still get caught off guard when the conversation shifts from "what is a join?" to "why did this query stop using its index?"
This is a roadmap, not a cram sheet. The questions below are grouped by difficulty tier — junior, intermediate, advanced, and DBA-level — with model answers that include the mechanism or tradeoff the interviewer is actually listening for. If you're a bootcamp graduate preparing for your first backend role, start at the top and work through the intermediate section before touching anything else. If you're a mid-level developer stepping into a senior loop, the advanced and DBA sections are where your interview will actually be decided.
Why MySQL Interview Questions Get Harder Faster Than Most Candidates Expect
What Junior Screens Test Before Anyone Cares About Query Tuning
Entry-level MySQL interviews are almost entirely definitional, and that's not a criticism — it's the reality of what a hiring team needs to know before investing in a longer technical conversation. At this stage, interviewers are checking whether you understand the relational model at all: what a primary key does, how foreign keys enforce integrity, what a join actually produces in terms of rows, and whether you can write a GROUP BY without confusing it with a WHERE clause.
The concepts being tested are foundational, but the bar for answering well is higher than most candidates expect. A weak answer defines the term. A strong answer defines the term and names the behavior that makes it useful — why a primary key has to be non-null, why an INNER JOIN drops rows that don't match. That one extra sentence is what separates a candidate who studied definitions from one who has actually written queries.
Why Intermediate Rounds Suddenly Care About Tradeoffs, Not Just Syntax
The shift from junior to intermediate questions is a shift from "what is this?" to "why would you choose this?" An intermediate interviewer isn't impressed by knowing that an index speeds up reads — they want to know when you'd add one, when you wouldn't, and what the write overhead looks like on a high-insert table.
A practical example: imagine a reporting query that aggregates monthly sales by region across a large orders table. A junior candidate knows how to write the GROUP BY. An intermediate candidate can explain whether a composite index on (region, order_date) would help the optimizer avoid a full scan, and whether the query's selectivity makes that index worth the maintenance cost. That's the conversation intermediate rounds are designed to have.
The Line Where DBA Questions Stop Being Academic and Become Operational
DBA-level MySQL questions aren't harder because they use more obscure syntax. They're harder because they're framed as production failure scenarios. An interviewer asking about lock waits isn't testing whether you can define a deadlock — they're asking what you'd do at 2am when a checkout flow is hanging and you need to decide whether to kill a session without knowing what it was doing.
The same applies to replication lag and backup recovery. The question isn't "what is binary log replication?" — it's "your replica is 45 minutes behind and the primary is still taking writes. What do you check first?" That shift from definition to triage is the line where DBA questions live, and candidates who've only studied textbook answers fail it consistently.
Core MySQL Interview Questions Every Junior Candidate Should Nail First
What Is MySQL, and How Is It Different From SQL?
SQL is a language; MySQL is a database management system that implements it. SQL (Structured Query Language) is the standard for querying and manipulating relational data — it's a specification, not a product. MySQL is an open-source relational database that uses SQL as its query interface, adding its own storage engines, configuration layer, and operational tooling on top. The follow-up an interviewer often asks is where MySQL sits relative to PostgreSQL or SQLite — the honest answer is that MySQL optimized historically for web workloads and read-heavy traffic, while PostgreSQL has stronger standards compliance and extensibility.
What Is a Primary Key, and Why Does It Matter in Real Tables?
A primary key uniquely identifies every row in a table and cannot be null. MySQL enforces both constraints automatically: no two rows can share a primary key value, and the column (or columns) making up the key must always have a value. In InnoDB, the primary key also determines the physical order of rows on disk via the clustered index, which means queries that filter or range-scan on the primary key are faster than equivalent queries on a secondary column. Interviewers sometimes follow up by asking how a primary key differs from a unique key — the answer is that a unique key allows nulls (one per column in MySQL's implementation) and doesn't drive the clustered index.
What Is the Difference Between a Foreign Key and a Primary Key?
A primary key identifies a row in its own table; a foreign key references a primary key in another table to enforce a relationship. If you have an `orders` table with a `customer_id` column, a foreign key constraint on that column tells MySQL that every value in `customer_id` must exist in the `customers` table's primary key. This prevents orphaned rows — orders that reference customers who don't exist. The follow-up is almost always about cascade behavior: `ON DELETE CASCADE` removes child rows when the parent is deleted, `ON DELETE RESTRICT` blocks the deletion entirely. Knowing which one is appropriate depends on the data model, and interviewers are testing whether you've thought about that.
What Are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?
Each join type controls which rows survive when the two tables don't have a matching record on both sides. Using a customers-and-orders example: an INNER JOIN returns only customers who have at least one order — non-matching rows on either side are dropped. A LEFT JOIN returns all customers, with NULLs in the order columns for customers who haven't ordered anything. A RIGHT JOIN does the reverse — all orders, with NULLs for any order somehow missing a customer. MySQL doesn't support FULL OUTER JOIN natively, but you can emulate it with a UNION of a LEFT and RIGHT JOIN. The common mistake candidates make is confusing LEFT JOIN with "all rows from both tables" — it's all rows from the left table, not both.
What Is Normalization, and Why Do Interviewers Ask About It So Early?
Normalization is the process of structuring tables to eliminate redundant data and prevent update anomalies. In a denormalized design, storing a customer's city in every order row means a city name change requires updating hundreds of rows — and if one update is missed, the data is inconsistent. Normalization moves the city to a customers table and references it by ID. Interviewers ask about this early because it reveals whether a candidate understands why relational databases are designed the way they are, not just how to query them. The follow-up is nearly always about when denormalization is acceptable — the honest answer is when read performance on a specific query matters more than write consistency, usually in reporting or analytics workloads.
What Does GROUP BY Do, and How Is HAVING Different From WHERE?
GROUP BY collapses rows with the same value in a column into a single group so aggregate functions can operate on each group. WHERE filters individual rows before grouping happens. HAVING filters groups after aggregation. The practical difference: if you want total sales per region, you GROUP BY region and SUM the amounts. If you only want regions with more than $10,000 in sales, you add `HAVING SUM(amount) > 10000` — because at the point WHERE runs, the aggregated total doesn't exist yet. Using WHERE with an aggregate condition is a syntax error in MySQL, which is why interviewers ask this early — it's a clean test of whether the candidate understands query execution order.
MySQL Interview Questions on Joins, Subqueries, Indexes, and GROUP BY
When Would You Use a Subquery Instead of a Join?
Use a subquery when you need to filter or compute a derived set before the main query runs, especially when the relationship isn't a simple row match. A join works well when you're combining columns from two tables on a shared key. A subquery is more natural when you're asking "give me all customers who placed an order in the last 30 days" — you compute that filtered set of customer IDs first, then filter the customers table against it. Interviewers will probe whether the subquery is correlated (re-executed for every row in the outer query, which is expensive) or uncorrelated (executed once and reused). A correlated subquery on a large table is a common source of slow queries, and knowing when to rewrite it as a JOIN or a CTE is what the interviewer is actually testing.
Why Can an Index Make a Query Faster — and Sometimes Not?
An index lets MySQL locate matching rows without scanning the entire table, but the optimizer will skip it when the index isn't selective enough to be worth the overhead. If you index a `status` column that contains only three values — 'active', 'inactive', 'pending' — across a million-row table, the optimizer may decide a full table scan is cheaper than following the index to retrieve 30% of the rows anyway. Indexes also add overhead on every INSERT, UPDATE, and DELETE because the index structure has to be maintained. The practical implication is that indexes pay off on high-cardinality columns used frequently in WHERE clauses and joins, and become a liability on low-cardinality columns or heavily written tables.
How Do You Read a Basic EXPLAIN Plan?
The four fields that matter most in MySQL's EXPLAIN output are `type`, `key`, `rows`, and `Extra`. The `type` column tells you the access method: `const` and `ref` are fast (index lookups), `ALL` is a full table scan and almost always a problem. `key` shows which index the optimizer chose — a NULL here means no index was used. `rows` is the optimizer's estimate of how many rows it will examine, which is a proxy for cost. `Extra` shows supplemental behavior: "Using filesort" means MySQL is sorting in memory or on disk because no index covers the ORDER BY, and "Using temporary" means a temp table was created, usually for a GROUP BY. A plan where `type` is ALL and `key` is NULL on a large table is the clearest signal that an index is missing or the query needs a rewrite.
What Is the Difference Between WHERE, HAVING, and ON in a Join Query?
ON filters rows during the join itself; WHERE filters after the join produces its result set; HAVING filters after GROUP BY aggregates. The distinction between ON and WHERE matters most with outer joins. If you move a condition from ON to WHERE in a LEFT JOIN, you change the semantics: `ON orders.status = 'complete'` keeps all customers and NULLs out orders that don't match; `WHERE orders.status = 'complete'` eliminates customers with no complete orders entirely, effectively turning the LEFT JOIN into an INNER JOIN. In a report that's supposed to show all customers including those with no completed orders, that's a silent correctness bug — and interviewers ask about it precisely because it's easy to miss.
How Do Composite Indexes Work in MySQL?
A composite index on (col_a, col_b) can be used by queries that filter on col_a alone or on both col_a and col_b together, but not by queries that filter on col_b alone. This is the leftmost-prefix rule. If you have an index on (last_name, first_name), a query filtering on `last_name = 'Smith'` can use it. A query filtering only on `first_name = 'John'` cannot, because the index is sorted by last_name first. Column order in a composite index should reflect the most selective column first and the most common query pattern — getting it wrong means the index exists but the optimizer never uses it.
When Does GROUP BY Become Expensive?
GROUP BY becomes expensive when MySQL has to sort and aggregate a large number of rows without an index that covers the grouping columns. Without an index, MySQL performs a full scan, loads the rows into a temporary table or sort buffer, and then aggregates. On a table with millions of rows, this can be the most expensive step in a query. Adding an index on the GROUP BY column allows MySQL to read rows in order and aggregate on the fly without a sort pass. Interviewers may also ask about pre-aggregation strategies — maintaining a summary table that's updated incrementally rather than recomputing aggregates at query time — which is the right answer for reporting workloads where the raw table is too large to scan repeatedly.
Advanced MySQL Interview Questions on Transactions, Isolation, and Query Tuning
What Happens Inside a MySQL Transaction?
A transaction groups multiple SQL statements so they either all succeed or all roll back together — there's no partial state. The practical reason transactions exist is to keep related operations consistent. In an order-and-payment workflow, you need to insert the order row, decrement inventory, and record the payment in a single atomic unit. If the payment insert fails after the inventory decrement already ran, a rollback undoes the decrement too. Without a transaction, you'd have inventory reduced for an order that was never paid. InnoDB is MySQL's default storage engine and the one that supports transactions; MyISAM does not, which is why engine choice matters in schema design conversations.
What Are the MySQL Isolation Levels, and What Problems Are They Trying to Prevent?
MySQL's four isolation levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE — control how much one transaction can see of another's in-progress work. READ UNCOMMITTED allows dirty reads: you can see changes another transaction hasn't committed yet, which means you might act on data that gets rolled back. READ COMMITTED prevents dirty reads but allows non-repeatable reads — the same SELECT run twice in one transaction can return different rows if another transaction committed between them. REPEATABLE READ (InnoDB's default) prevents that by snapshotting the data at the start of the transaction, but phantom rows — new rows inserted by another transaction that match your WHERE clause — can still appear in some operations. SERIALIZABLE prevents all of these by effectively serializing access, at the cost of throughput. The follow-up interviewers ask is almost always "what's InnoDB's default and why?" — REPEATABLE READ, because it balances consistency with concurrency for most web workloads.
Why Do Deadlocks Happen Even When Both Queries Are Valid?
A deadlock occurs when two transactions each hold a lock the other needs, and neither can proceed. Transaction A locks row 1, then tries to lock row 2. Transaction B locks row 2, then tries to lock row 1. Both are waiting. MySQL detects this cycle and kills one transaction automatically, returning an error to the application. The queries themselves are perfectly valid — the problem is lock acquisition order. Interviewers testing this want to know whether you can diagnose a deadlock, not just define one. The diagnostic approach: check `SHOW ENGINE INNODB STATUS` for the last deadlock report, identify which transactions were involved and which rows they held, and then look at whether the application can be changed to acquire locks in a consistent order.
How Would You Tune a Slow Query Without Guessing?
The workflow is: run EXPLAIN first, identify the access pattern, check index coverage, reduce the rows examined, then consider a query rewrite. Guessing at indexes is the most common mistake — adding an index that the optimizer won't use because the query's WHERE clause doesn't match the index's leftmost prefix. For a slow reporting query that aggregates orders by region over a date range, EXPLAIN will show whether the optimizer is scanning the full table or using an index on (region, order_date). If the `rows` estimate is in the millions and `type` is ALL, the index is missing or the query is filtering too late. After indexing, if the query is still slow, look at whether the aggregation can be pushed into a subquery to reduce the row count before the join, or whether the result can be cached in a summary table.
What Are Window Functions, and When Are They Better Than GROUP BY?
Window functions compute aggregates across a set of rows related to the current row without collapsing the result into a single group per value. The difference is visible in a running total: `SUM(amount) OVER (PARTITION BY region ORDER BY order_date)` gives you a cumulative total per region on every row, with the original row data still intact. A GROUP BY SUM would collapse all rows for a region into one. Window functions are better when you need both the individual row and the aggregate in the same result — ranking salespeople within a region while keeping each sale's details, or calculating each order's percentage of its customer's total spend. They were added to MySQL in version 8.0, so asking about them is also an implicit version-awareness check.
How Do JSON Columns Change the Way You Design a MySQL Table?
JSON columns let you store semi-structured data without defining every field as a column, but they trade indexability and query clarity for flexibility. The useful case is event payloads or user profile data where the attributes vary significantly between rows — storing a `metadata` JSON column is cleaner than adding 20 nullable columns for fields that only 5% of rows use. The problem is that querying inside JSON requires functions like `JSON_EXTRACT()` or the `->` operator, which can't use a standard B-tree index. MySQL supports generated columns to index a specific JSON path, but this requires knowing in advance which paths you'll query. When a JSON column's fields start appearing in WHERE clauses and JOIN conditions regularly, that's the signal to promote them to proper columns.
MySQL Interview Questions DBA Candidates Get Asked When Production Is on the Line
How Do You Troubleshoot Lock Waits in a Busy Production System?
Start with `INFORMATION_SCHEMA.INNODB_TRX` and `INNODB_LOCK_WAITS` to identify which transaction is blocking and what it's holding. In a checkout flow where multiple sessions update the same inventory row, a long-running transaction that hasn't committed will block every other session trying to touch that row. The decision to kill the blocking session isn't automatic — you need to know whether it's mid-operation on something important, whether the application will retry correctly, and whether the lock contention is a symptom of a schema problem (like a missing index forcing row-level locks to escalate). Interviewers are testing triage logic: identify the blocker, understand its scope, decide whether to intervene.
What Would You Check First After a Replication Lag Alert?
Check whether the lag is growing or stable, then look at replica I/O and SQL thread status, then trace back to the primary's write workload. A sudden spike in replication lag usually means a large transaction or bulk operation on the primary — a multi-million-row UPDATE that generates a huge binary log event the replica has to replay serially. Sustained lag often points to I/O pressure on the replica or a hardware disparity. The order matters: `SHOW REPLICA STATUS` gives you binlog position and thread health; `SHOW PROCESSLIST` on the replica shows what the SQL thread is executing. Interviewers want to see that you triage before you act — killing threads or restarting replication without understanding the cause can make lag worse.
How Do Backups Fail Even When the Backup Job Says Success?
A backup job that reports success confirms the files were written — it doesn't confirm the data can be restored from them. The failure modes that matter are corruption in the backup files themselves, incomplete dumps from a long-running `mysqldump` that captured a table mid-write without a consistent snapshot, and backups that restore correctly but miss the binary logs needed to replay transactions up to the failure point. The only way to know a backup works is to restore it to a test instance and verify the data. "We have nightly backups" and "we can recover from last night's backup in under an hour" are two completely different claims, and interviewers asking this question are testing whether you understand the difference.
What Is Failover, and What Can Go Wrong During It?
Failover is the process of promoting a replica to primary when the original primary fails, and the risks are split brain, stale reads, and application misrouting. Split brain happens when the original primary recovers and starts accepting writes while the promoted replica is also accepting writes — you now have two primaries diverging. Stale reads happen when a replica promoted to primary is still seconds or minutes behind the original's last committed transactions. Application misrouting happens when connection strings or DNS haven't updated and some application instances are still pointing at the old primary. A well-designed failover process uses a single orchestration tool, fences the old primary before promoting the replica, and verifies replication position before redirecting traffic.
How Do You Decide Whether to Add an Index or Change the Query?
Adding an index is the right move when the query's access pattern is sound but the optimizer lacks a fast path to the data; rewriting the query is the right move when the query is doing unnecessary work that an index can't fix. A query that joins three large tables and filters late — after the join produces a huge intermediate result — won't be fixed by an index on the final filter column. The fix is to push the filter earlier, reduce the join's input size, or restructure the query entirely. Interviewers are looking for judgment about read/write balance: an index that speeds up a report run once a day but slows down ten thousand inserts per hour is usually the wrong trade.
How Do You Keep MySQL Healthy When the Schema and Workload Keep Changing?
The answer is continuous monitoring, scheduled index hygiene, schema change discipline, and regular restore drills — not a one-time setup. As workloads evolve, indexes that were valuable become unused (and pay write overhead for nothing), and new query patterns emerge that no index covers. Tools like `pt-index-usage` from Percona Toolkit identify indexes that haven't been used in a query plan recently. Schema changes on large tables require online DDL or tools like `pt-online-schema-change` to avoid locking the table. Restore drills should be a scheduled operational task, not something that happens the first time a real recovery is needed. The underlying principle is that a healthy MySQL instance requires active maintenance, not passive monitoring.
How to Answer MySQL Interview Questions in One to Three Sentences Without Rambling
Start With the Direct Answer, Then Add the Mechanism
The most common interview mistake is circling the answer instead of leading with it. An interviewer asks what a foreign key does; the candidate starts with "well, in relational databases, data integrity is important because..." — and the interviewer is already forming a negative impression before the actual answer arrives. The right shape is: state the answer in one sentence, then add the one mechanism or behavior that explains why it works that way. "A foreign key enforces referential integrity by requiring that every value in the child column exists in the parent table's primary key. MySQL will reject an insert that references a non-existent parent row." That's complete.
Use One Example, Not Three Half-Finished Ones
A single, specific example grounds an answer and signals that the candidate has actually used the concept. "Imagine a customers table and an orders table" gives the interviewer something concrete to follow. Three partial examples — "like in an e-commerce system, or maybe a CRM, or you could think about it in terms of..." — signals that the candidate is searching for the right framing in real time, which reads as uncertainty. Pick the example that makes the concept clearest and commit to it. One table name, one column name, one outcome.
Stop as Soon as You've Answered the Question They Actually Asked
Over-explaining is the most common way strong candidates undermine themselves. The question was about what HAVING does — not about the full GROUP BY execution order, not about window functions as an alternative, not about when you'd use a subquery instead. Answering the adjacent concepts unprompted buries the correct answer in noise and makes it harder for the interviewer to follow up productively. A crisp answer creates space for the follow-up question, which is where the real technical conversation happens. If the interviewer wants more depth, they'll ask — and that's the moment to add it.
How Verve AI Can Help You Prepare for Your Backend Developer Job Interview
The hardest part of MySQL interview prep isn't knowing the material — it's translating what you know into clear, confident answers under live questioning. When an interviewer follows up your GROUP BY answer with "how would that change if the table had 50 million rows?", the answer you give in the next ten seconds determines how the rest of the conversation goes. That's where Verve AI Interview Copilot works: it listens in real-time during your actual interview on Zoom, Google Meet, or Teams, tracking the conversation and helping you structure an answer as the question is still being asked. On the desktop app, Verve AI Interview Copilot stays invisible during screen share, so the support is there without changing how the interview looks to the interviewer. Before the real thing, the separate Mock Interviews feature lets you run full practice rounds against MySQL-specific question sets so that Verve AI Interview Copilot feels like a familiar tool rather than a new variable on the day that counts.
Bringing It Together
MySQL interview questions stop feeling endless the moment you organize them by level. Junior rounds are testing whether you understand the relational model. Intermediate rounds are testing whether you can reason about tradeoffs. Advanced rounds are testing whether you can diagnose, not just define. DBA rounds are testing whether you can keep production alive.
The practical next step: pick one question from each tier in this guide and write out a two-sentence answer without looking at the model answer. If the answer feels vague, that's the concept to study — not the whole list. One strong answer per tier, practiced until it's clean, is worth more than a shallow familiarity with all 32.
James Miller
Career Coach
