MySQL Interview Questions

A curated set of MySQL interview questions, ordered roughly from fundamentals to more advanced — the kind you'll actually be asked in real screens and on-sites.

Storage engines#

Q: What's the difference between InnoDB and MyISAM, and which should you use? InnoDB supports transactions, row-level locking, and foreign key constraints; MyISAM supports none of those, locking at the table level instead of the row level and offering no crash-safe transaction log. InnoDB has been the default since MySQL 5.5 and is the right choice for essentially every new table — MyISAM only shows up today in legacy systems or specific historical use cases that predate InnoDB's current feature set.

Q: Why does row-level locking matter for concurrency? It lets two transactions modify different rows of the same table at the same time without blocking each other. Table-level locking (MyISAM's approach) serializes all writes to a table — one write blocks every other read and write against the whole table until it commits — which becomes a severe bottleneck under concurrent write load.

Transactions and isolation#

Q: What is MySQL's default isolation level, and how does it differ from READ COMMITTED? MySQL's InnoDB defaults to REPEATABLE READ, which takes a consistent snapshot at the start of a transaction — every SELECT within that transaction sees the same data, even if other transactions commit changes in between. READ COMMITTED (PostgreSQL's and SQL Server's default) takes a fresh snapshot on every individual statement, so two reads in the same transaction can see different data if something else committed in between. Neither is strictly better; it's a trade-off between predictable in-transaction consistency and reading the freshest committed data.

Q: What causes a deadlock, and how should an application handle one? A deadlock occurs when two transactions each hold a lock the other is waiting for, so neither can proceed — commonly caused by two transactions updating the same rows in a different order. InnoDB detects the cycle automatically and rolls back one transaction with a deadlock error. The application should treat that error as expected under concurrency and retry the rolled-back transaction, and the best prevention is always acquiring locks (updating rows) in a consistent order across all code paths.

Indexing#

Q: When should you add an index to a column, and when is it not worth it? Index columns used frequently in WHERE clauses, JOIN conditions, and ORDER BY, especially ones with high cardinality (many distinct values), where an index dramatically narrows the rows scanned. It's not worth it for low-cardinality columns queried alone (a boolean flag, for instance) or for columns rarely queried at all — every index adds write overhead on every insert/update/delete and consumes storage, so indexing something that's never actually filtered on is pure cost with no benefit.

Q: In a composite index on (a, b), why can't a query filtering only on b use it efficiently? A composite index is a single B-tree sorted first by a, then by b within each a value — the "leftmost prefix rule." Without a value for a, MySQL has no way to jump to the relevant b values inside that structure, so the index is only usable for queries that filter on a alone, or on a and b together. A query filtering purely on b would need its own separate index on b.

Data types#

Q: Why should you never store monetary values as FLOAT or DOUBLE? FLOAT/DOUBLE store binary floating-point approximations, and many ordinary decimal fractions (like 0.1) have no exact binary representation — the resulting tiny rounding errors compound across many transactions and can even break = comparisons on values that look identical. DECIMAL(p,s) stores an exact fixed-point value with a defined number of digits before and after the decimal point, so it's the correct type for any money, pricing, or account-balance column.

Replication and backups#

Q: How does MySQL replication actually work under the hood? The primary records every data-changing statement or row change in its binary log (binlog); a replica connects to the primary, streams that binlog, and replays the same changes locally, arriving at the same data by re-executing the same history. Modern setups use GTIDs (globally unique transaction ids) rather than tracking raw binlog file/position pairs by hand, which makes failover and reconnecting a replica far less error-prone.

Q: What's the practical risk of asynchronous replication, and how would you mitigate it? Because the primary commits and acknowledges a write without waiting for any replica to receive it, a replica can genuinely lag behind — a query against it can return data that's already stale, and a user reading immediately after their own write might not see it yet on the replica they happen to be routed to. Semi-synchronous replication (the primary waits for at least one replica to acknowledge receipt before returning success) narrows this window, and routing a user's read-your-own-write queries back to the primary for a short period is a common application-level mitigation.

Q: Why isn't a replica a substitute for a real backup strategy? Replication propagates every change to the data just as faithfully as it propagates a legitimate one — an accidental DROP TABLE or a DELETE missing its WHERE clause replicates to every replica right along with everything else. A replica protects against a server dying, not against application or human error; recovering from a mistake like that needs a point-in-time backup (a mysqldump or physical snapshot) combined with binlog replay up to just before the mistake happened.