MySQL Interview Questions

Note: Interviewers use this to find out whether you have actually carried an on-call pager or only worked on a laptop copy of a database. Give concrete routines, not adjectives.

Structure your answer around the three things every DBA is judged on:

• Backups you have actually restored. Say how often you took them (nightly logical dump plus binary logs for point-in-time recovery, for example) and — more importantly — the last time you rehearsed a restore. A backup nobody has restored is a guess.
• What you monitored. Replication lag, slow query log, connection count, buffer pool hit rate, disk headroom. Name the threshold that pages you.
• How you handled schema changes. Whether you used online DDL, pt-online-schema-change, or a maintenance window, and how you rolled back.

Close with an incident: what broke, how you noticed, how long recovery took, and the change you made afterwards so it could not happen twice.

This is the single most common MySQL behavioural question, because it maps directly onto the job. Walk through it as an investigation, in order:

• How you noticed. An alert, a user complaint, or a spike in the slow query log — say which.
• How you isolated it. SHOW FULL PROCESSLIST to see what was running, the slow query log or Performance Schema to find the worst offenders by total time rather than by single-run time.
• What EXPLAIN told you. The usual culprits: a full table scan, a missing composite index, an index that could not be used because of a function wrapped around the column, or a bad join order.
• What you changed and what it bought you. Give the before and after number.

Note: Mention that you fixed the worst query by total execution time, not the slowest individual one. A query that takes 200ms and runs 50,000 times an hour costs far more than a 4-second report nobody runs.

Note: Do not answer "never". Everyone who has worked at scale has a story, and claiming otherwise reads as inexperience or dishonesty. Pick a real incident with a clean recovery.

A good answer has four beats:

• The mistake, stated plainly. An UPDATE without a WHERE clause, a migration that locked a large table during peak traffic, a DROP on the wrong environment.
• How fast you owned it. Interviewers care much more about whether you told someone immediately than about the mistake itself.
• The recovery. Point-in-time recovery from binary logs, a restore into a scratch schema and a targeted re-insert, or a failover to a replica.
• The guardrail you added. Running in a transaction so you can ROLLBACK, enabling sql_safe_updates, requiring a peer to review every migration, or removing write access to production entirely.

The last beat is what actually gets you hired.

Translate the decision into the currency your listener cares about — time, money, or risk — and keep the internals out of it unless you are asked.

• For developers, frame it as behaviour they will see: "this index means the search endpoint stops scanning 4 million rows, so the page loads in 40ms instead of 3 seconds — but every INSERT into this table now costs a little more."
• For managers, frame it as cost and risk: "this change lets us stay on the current instance size for another year" or "without it, checkout starts timing out during a sale."

Always state the trade-off out loud. Indexes speed up reads and slow down writes; denormalising speeds up reads and creates a consistency problem you now have to own. Presenting only the upside is what makes non-specialists distrust database advice.

Note: If you have ever written a short design document or run a lunch-and-learn on this, mention it. It signals that you scale your knowledge rather than hoarding it.

Answer in two halves — how you learn, and how you filter.

How you learn: the official MySQL release notes and worklogs, the Percona and PlanetScale engineering blogs, and — most persuasively — a local instance you deliberately break. Reading about EXPLAIN ANALYZE is not the same as running it against a badly written query and watching the row estimates drift.

How you filter: a new version or feature has to clear three bars before you would put it in production:

• Does it solve a problem we actually have, measured, rather than one we might have?
• Is it out of the earliest releases and running somewhere at comparable scale?
• Can we roll back if it disappoints, and have we tested that rollback?

Note: Naming one feature you chose NOT to adopt, and why, is more convincing than listing ten you like.

They are two different implementations sitting under the same SQL layer, and they differ in the guarantees they give you.

• Transactions. InnoDB is fully ACID and supports COMMIT and ROLLBACK. MyISAM has no transactions at all — a half-finished multi-statement change stays half-finished.
• Locking. InnoDB locks individual rows, so concurrent writers to different rows do not block each other. MyISAM locks the whole table on write, which collapses under concurrent traffic.
• Crash recovery. InnoDB replays its redo log and comes back consistent. MyISAM tables must be repaired after an unclean shutdown and can lose data.
• Foreign keys. Supported by InnoDB, silently ignored by MyISAM.

InnoDB became the default in MySQL 5.5 because durability and row-level concurrency matter for essentially every real application. MyISAM survives only in legacy schemas and in a few read-only cases where its smaller footprint and full-text history were useful — and even that argument disappeared once InnoDB gained full-text indexes.

An InnoDB index is a B+ tree: keys are held in sorted order, all values live in the leaf nodes, and the leaves are linked so a range scan can walk sideways without returning to the root. Because it is sorted, the engine can find a value in roughly log(n) page reads instead of scanning every row.

Two kinds matter:

• The clustered index is the primary key, and the full row is stored in its leaves. There is exactly one per table.
• A secondary index stores the indexed columns plus the primary key value, so using one usually costs a second lookup back into the clustered index unless the index covers every column the query needs.

An index will be ignored when:

• You wrap the column in a function — WHERE YEAR(created_at) = 2024 cannot use an index on created_at, but WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01' can.
• You skip a leading column of a composite index. An index on (a, b, c) serves WHERE a = ? and WHERE a = ? AND b = ?, but not WHERE b = ? alone.
• The pattern is leading-wildcard, like LIKE '%term'.
• There is an implicit type conversion between the column and the value.
• The optimiser estimates the query will match a large fraction of the table, in which case a sequential scan is genuinely cheaper.

Isolation levels decide how much of other people's uncommitted or in-flight work your transaction is allowed to see. From weakest to strongest:

• READ UNCOMMITTED — you can read another transaction's uncommitted changes. Those are dirty reads, and they may be rolled back out from under you. Almost never appropriate.
• READ COMMITTED — you only see committed data, but two identical reads inside one transaction can return different values because someone committed in between. This is the default in PostgreSQL and Oracle.
• REPEATABLE READ — MySQL's default with InnoDB. Your transaction takes a consistent snapshot at its first read, so the same query returns the same rows all the way through. InnoDB additionally uses gap locks so phantom rows are largely prevented too, which is stronger than the SQL standard requires.
• SERIALIZABLE — transactions behave as if they ran one after another. Correct, but the extra locking costs concurrency.

Note: A very common follow-up is why READ COMMITTED is often preferred for high-write workloads: it takes fewer gap locks and therefore deadlocks less.

A join decides which rows survive when two tables are matched on a condition.

• INNER JOIN returns only rows that match on both sides. Non-matching rows from either table disappear.
• LEFT JOIN returns every row from the left table, with NULLs filled in where the right table has no match.
• RIGHT JOIN is the mirror image — every row from the right table. It is rarely used in practice because you can always rewrite it as a LEFT JOIN with the tables swapped, which reads more naturally.
• FULL OUTER JOIN returns every row from both sides. MySQL does not support it, so you emulate it with a LEFT JOIN UNIONed to a RIGHT JOIN.

Note: The classic trap is putting a filter on the right-hand table in the WHERE clause of a LEFT JOIN. WHERE b.status = 'active' throws away the NULL rows and silently turns your LEFT JOIN back into an INNER JOIN. Put that condition in the ON clause instead.

Replication copies changes from a source server to one or more replicas. The source writes every change into its binary log; each replica has an I/O thread that pulls those events into a local relay log, and an applier thread that replays them.

The three modes differ in how long the source waits before telling the client the commit succeeded:

• Asynchronous (the default) — the source commits and replies immediately, without waiting for any replica. Fastest, but if the source dies before a replica catches up, those transactions are lost.
• Semi-synchronous — the source waits until at least one replica confirms it has written the event to its relay log. It does not wait for the replica to apply it. This trades a little latency for a much smaller window of data loss.
• Group Replication — a group of servers agree on transactions using a consensus protocol, giving you automatic failover and either single-primary or multi-primary writes. This is the foundation of InnoDB Cluster.

Binary log formats also matter: ROW is the safe default, STATEMENT is compact but unsafe for non-deterministic functions, and MIXED switches between them.

EXPLAIN prints the plan the optimiser intends to use. The columns worth reading in order:

• type — the access method, and the first thing to check. Roughly best to worst: const, eq_ref, ref, range, index, ALL. Seeing ALL on a large table means a full scan.
• key — which index was actually chosen. NULL here alongside a large rows value is the classic missing-index signature.
• rows — the optimiser's estimate of rows examined at this step. Multiply across joined tables to see the real cost.
• filtered — the percentage of those rows expected to survive the WHERE clause.
• Extra — where the useful warnings live. Using filesort and Using temporary mean work is spilling out of the index; Using index is the good case, a covering index that never touches the table.

Note: EXPLAIN shows the plan; EXPLAIN ANALYZE actually runs the query and shows real timings and real row counts next to the estimates. When the estimate and the actual differ wildly, your table statistics are stale — run ANALYZE TABLE.

Normalisation organises a schema so that every fact is stored exactly once, which removes update anomalies.

• First normal form (1NF) — every column holds a single atomic value. No comma-separated lists, no repeating groups like phone1, phone2, phone3.
• Second normal form (2NF) — 1NF, and every non-key column depends on the whole primary key. This only bites with composite keys: if a table keyed on (order_id, product_id) also stores product_name, that column depends on half the key and belongs in the products table.
• Third normal form (3NF) — 2NF, and no non-key column depends on another non-key column. Storing city and state alongside pincode breaks it, because state is a fact about the pincode, not about the row.

When to denormalise: when a read path is measurably too slow and the join is the proven cause. Typical cases are a cached aggregate such as comment_count on a post, or a reporting table built for one dashboard. The price is that you now own the consistency problem — you must update the copy whenever the source changes, usually in the same transaction or through a scheduled rebuild.

All three remove data, but at different levels and with very different costs.

• DELETE is DML. It removes rows one at a time, accepts a WHERE clause, fires triggers, is fully logged, and can be rolled back inside a transaction. Because each row is logged, deleting millions of rows is slow and produces a large binary log.
• TRUNCATE is DDL. It drops and recreates the table, so it is far faster, takes no WHERE clause, fires no row triggers, resets AUTO_INCREMENT to 1, and causes an implicit commit — you cannot roll it back.
• DROP is DDL that removes the table itself: rows, structure, indexes, and permissions all go.

Note: A frequent follow-up is why DELETE does not release disk space back to the operating system. InnoDB marks the pages reusable inside the tablespace but does not shrink the file. To actually reclaim it you need OPTIMIZE TABLE, which rebuilds the table.

A deadlock is two transactions each holding a lock the other needs. InnoDB detects the cycle automatically, picks the transaction that has done less work, and rolls it back with error 1213. Nothing hangs forever.

How to diagnose: run SHOW ENGINE INNODB STATUS and read the LATEST DETECTED DEADLOCK section. It shows both transactions, the exact statements, and which locks each was holding and waiting for.

How to prevent:

• Take locks in a consistent order. Most deadlocks come from one code path updating table A then B while another does B then A. Agreeing an order across the codebase removes the cycle entirely.
• Keep transactions short. Never hold a transaction open across a network call or user input.
• Index the columns you filter on. Without an index, InnoDB locks far more rows than you intended, which widens the window enormously.
• Consider READ COMMITTED for write-heavy workloads, since it takes fewer gap locks.

Note: The most important point to make is that your application must retry a deadlocked transaction. Deadlocks are normal at concurrency and are not by themselves a bug.

CHAR(n) is fixed width — every value occupies n characters and shorter values are padded with spaces that are stripped on retrieval. VARCHAR(n) is variable width and stores a one or two byte length prefix plus the actual characters.

Use CHAR only when values really are a constant length: a country code, a hash of known width, an MD5 digest. Use VARCHAR for anything with a spread of lengths — names, emails, titles.

Choosing types generally, in priority order:

• Pick the smallest type that comfortably fits. Narrower rows mean more rows per page, which means fewer disk reads and a more effective buffer pool. Do not use BIGINT for a column that will never exceed a few million.
• Never store money in FLOAT or DOUBLE. Use DECIMAL, which is exact.
• Prefer NOT NULL where the value is genuinely mandatory. Nullable columns cost an extra bit and make every query author reason about three-valued logic.
• Store dates in DATE, DATETIME or TIMESTAMP, never in a VARCHAR. TIMESTAMP converts to UTC and is range-limited to 2038; DATETIME does not convert and has a much wider range.