MySQL Interview Questions (2026): 30 Most Asked, With Answers

InnoDB is transaction-safe: it supports commit, rollback, and crash recovery through a combination of redo and undo logs, locks at the row level instead of the whole table, and enforces foreign key constraints. MyISAM has none of that. It locks the entire table for writes, has no transaction support at all, and skips crash recovery, a hard kill mid-write can corrupt a MyISAM table and force a manual REPAIR TABLE (MySQL 8.4 Reference Manual, storage engines).

MyISAM's one real advantage was always a smaller on-disk footprint and less bookkeeping overhead per write, which made it genuinely faster for pure read-heavy or bulk-insert workloads. That gap has narrowed a lot since InnoDB matured, and it's rarely worth the tradeoff anymore.

INT is 4 bytes and covers roughly negative 2.1 billion to positive 2.1 billion signed. BIGINT is 8 bytes with a ceiling around 9.2 quintillion. SMALLINT is 2 bytes, good for roughly plus or minus 32,000.

Reach for BIGINT on a primary key you genuinely expect to outlive 2.1 billion rows, a payment ledger, a high-volume event table, an auto-incrementing id on something that never gets purged. Defaulting every table to BIGINT out of caution doubles that column's storage cost and every index built on it, for zero benefit on a lookup table that will never hold more than 40 rows.

In InnoDB, the table's row data is the clustered index. Rows live physically in the leaf pages of a B+tree ordered by the primary key, not in a separate heap that the index points to. Every InnoDB table has exactly one clustered index.

If you don't declare a primary key, InnoDB picks the first UNIQUE NOT NULL index as the clustering key instead. If there isn't one of those either, it generates a hidden internal 6-byte row id and clusters on that, invisibly, whether you asked for it or not.

INNER JOIN returns only rows that have a match on both sides of the join condition. LEFT JOIN returns every row from the left table regardless of whether a match exists on the right, filling in NULL for the right side's columns when nothing matches.

Swap an INNER JOIN in where a LEFT JOIN was intended and rows silently disappear, customers with zero orders vanish from a "customers and their orders" report instead of showing up with a null order count. It's a quiet bug because the query still runs and still returns plausible-looking data.

Per table in the query: the access type (type), which index it picked if any (key), how many bytes of that key it's actually using (key_len), a rough estimate of rows it expects to examine (rows), and extra notes about what it's doing (Extra). type=ALL means a full table scan, no index used at all, and it's the value that should make you stop and look closer on anything past a small table.

The rows column is an estimate off table statistics, not an exact count, and those statistics can go stale after a large bulk load or delete. A wildly wrong rows estimate is itself a diagnostic clue, not just noise.

Atomicity: an undo log records enough to unwind any partially completed transaction, so a crash mid-transaction leaves either all its changes applied or none. Consistency: constraints and foreign keys keep the data in a valid state before and after every transaction. Isolation: concurrent transactions don't see each other's uncommitted changes, with the exact degree depending on the isolation level in use. Durability: a redo log gets flushed and fsynced to disk before a commit is acknowledged back to the client, so a committed transaction survives a crash right after.

InnoDB defaults to row-level locking for normal DML, UPDATE, DELETE, and SELECT... FOR UPDATE lock only the specific rows a statement actually touches, via index records. That last part matters: an UPDATE with a WHERE clause on an unindexed column has to examine every row to find matches, and it locks every row it examines during that scan, not just the ones that end up matching, which is a common source of "why did this unrelated row just get locked" confusion (MySQL 8.4 Reference Manual, InnoDB locking).

Table-level locks come from an explicit LOCK TABLES statement, certain ALTER TABLE operations, or MyISAM's whole-table-per-write model, which has no row-level option at all.

1NF: every column holds one atomic value, not a comma-separated list of three phone numbers crammed into a single field. 2NF (relevant only when the primary key is composite): every non-key column depends on the whole key, not just part of it. 3NF: a non-key column shouldn't depend on another non-key column instead of the primary key directly, customer_city depending on customer_zip, which depends on customer_id, means city belongs in a lookup table keyed by zip, not copied redundantly into every order row.

The primary writes every data change to its binary log (binlog). Each replica runs an IO thread that connects to the primary and copies new binlog events into its own local relay log, then a separate SQL thread, or multiple applier threads for parallel replication since MySQL 5.6, reads that relay log and applies the changes to the replica's own data.

Replication is asynchronous by default: the primary doesn't wait for any replica to confirm before returning a successful commit to the client, so a healthy-looking replica can still be seconds or minutes behind under load, with nothing on the primary's side even noticing.

A primary key is a NOT NULL plus UNIQUE constraint bundled together, and InnoDB backs it with the clustered index, so the check isn't just logical, it's structural. Try to insert a row with a primary key value that already exists and you get a 1062 duplicate entry error, the insert is rejected outright, nothing is written. Try to insert or leave a primary key column NULL, assuming AUTO_INCREMENT isn't filling it in for you, and you get 1048 column cannot be null.

A table can only have one PRIMARY KEY, but you can add other UNIQUE indexes on separate columns that also reject duplicates while still allowing NULLs, since UNIQUE doesn't imply NOT NULL. That's a common trap in interviews, candidates assume UNIQUE and PRIMARY KEY behave identically, but a UNIQUE column can hold multiple NULLs, MySQL treats each NULL as distinct for uniqueness purposes, while a primary key column can never be NULL at all.

DELETE is DML. It removes rows one at a time, subject to a WHERE clause if you supply one, fires any ON DELETE triggers, gets logged row by row in the redo and undo logs, and can be rolled back inside a transaction. Because it works row by row, deleting millions of rows with DELETE is slow and bloats the undo log until the transaction commits.

TRUNCATE is DDL. In InnoDB it effectively deallocates and recreates the table's underlying storage, so it's fast regardless of row count, it doesn't fire row-level triggers, and it resets AUTO_INCREMENT back to its starting value. It's still transactional in the sense that a rollback before commit undoes it, but you can't filter it with a WHERE clause and it takes an exclusive metadata lock on the table.

DROP removes the table definition entirely, indexes, constraints, and any privileges granted specifically on it. There's no recovering from DROP without a backup or binlog point-in-time recovery.

WHERE filters individual rows before any grouping happens. HAVING filters groups after GROUP BY has aggregated them. You can't reference an aggregate like COUNT(*) or SUM(total) in a WHERE clause, because at the point WHERE runs, no aggregation has happened yet.

Practically, use WHERE to cut down the row set as early as possible, since it can use an index, and HAVING generally can't. Use HAVING only for conditions on the aggregated result itself, like keeping only customers with more than five orders. Filtering with WHERE first and pushing the aggregate condition into HAVING keeps the query efficient, because you avoid aggregating rows you were going to discard anyway.

An AUTO_INCREMENT column pulls its next value from an in-memory counter InnoDB keeps for the table, seeded from the current max value on startup. Any INSERT that doesn't explicitly supply a value for that column grabs the next counter value and bumps the counter, using a lightweight internal lock held only long enough to reserve the number, not for the whole insert.

Gaps are expected, not a bug. A rolled-back transaction still keeps the ID it reserved, a failed insert, say a duplicate key or constraint violation, still consumed a number, and a DELETE never returns numbers to the pool. Older MySQL versions could even lose the in-memory counter on a crash and reuse IDs before this was fixed in 8.0, which is one more reason not to build business logic around IDs being consecutive.

NULL means unknown, not empty or zero, and any comparison against an unknown value is itself unknown, not true. So column = NULL evaluates to NULL for every row, and a WHERE clause only keeps rows where the condition evaluates to true, so it silently returns zero rows instead of throwing an error, which is exactly why this bug slips past code review so often.

The fix is IS NULL or IS NOT NULL, operators built specifically to test for the NULL state rather than compare values. It affects aggregates and DISTINCT differently than beginners expect too, COUNT(column) skips NULLs while COUNT(*) counts every row, and DISTINCT treats all NULLs as a single group even though NULL isn't equal to itself.

ON DELETE CASCADE tells InnoDB that when a row in the parent table is deleted, every child row referencing it through that foreign key gets deleted automatically, in the same transaction. Delete a customer with CASCADE set on their orders, and every order row tied to that customer_id disappears with it, no application code required.

RESTRICT, the default, blocks the delete outright if any child row references it, throwing a foreign key constraint error. SET NULL sets the child's foreign key column to NULL instead of deleting the row, which only works if that column is nullable. NO ACTION behaves like RESTRICT in InnoDB, checked immediately rather than deferred. CASCADE is convenient but risky, it's easy to chain cascades across several tables and delete far more data than intended, so many teams deliberately use RESTRICT or SET NULL and handle cleanup explicitly in application code instead.

Enforcing a foreign key means checking a referenced row exists, and doing something coherent (block, cascade, set null) inside a consistent transactional boundary when it changes. MyISAM has no transaction manager underneath it to hang that check on.

MySQL will actually let you write FOREIGN KEY syntax against a MyISAM table. It parses fine, creates without an error, and then silently does nothing at write time. No enforcement, no cascade, no rejected insert. That silent-acceptance behavior trips up more people than the missing feature itself, because nothing tells you it didn't work until orphaned rows show up months later.

CHAR is fixed-length, padded with spaces up to the declared size, which makes sense for genuinely fixed-width data like a two-letter country code and wastes space on anything variable. VARCHAR is variable-length with a 1 or 2-byte length prefix and can carry a full index on the column, which is why it's the default choice for names, emails, and most short text.

TEXT stores large values off the main row page in InnoDB, only a prefix lives inline with a pointer to overflow pages for the rest, and you can't put a full unprefixed index on it. A TEXT column also costs the optimizer more to estimate row size against. If you actually know a field maxes out around 200 characters, a VARCHAR(255) beats a TEXT column that technically works but tells the optimizer nothing useful about the real size.

A secondary index's leaf nodes store the indexed column's value plus the primary key value of that row, not a direct pointer to a disk location. Finding a row through a secondary index is a two-step process: walk the secondary index's B+tree to collect matching primary key values, then look each one up in the clustered index to get the rest of the row.

That second step is random access, one clustered-index lookup per matching row, scattered across the table instead of read in one sequential pass. It's the reason a secondary-index scan that matches thousands of rows spread across the table can run noticeably slower than a comparable range scan on the clustered index itself, and it's the whole motivation behind covering indexes.

A B+tree composite index is sorted by its columns in the declared order, left to right, like a phone book sorted by last name, then first name. That structure only helps a query that filters on a left-to-right prefix of the declared columns.

CREATE INDEX idx_orders_status_date_total
 ON orders (status, order_date, total);

-- uses the index: filters on status, or status + order_date, or all three
SELECT * FROM orders WHERE status = 'shipped' AND order_date > '2026-01-01';

-- can't use this index efficiently: order_date alone skips the leading column
SELECT * FROM orders WHERE order_date > '2026-01-01';

Candidates who name the rule correctly still get the column order wrong two questions later when asked to design the index themselves. Reciting "leftmost prefix" and applying it aren't quite the same skill.

Historically, Block Nested Loop Join: for each row (or block of rows) in the outer table, scan the inner table for matches. Fast when the inner table has a usable index on the join column, since that turns the inner scan into an index lookup instead of a full scan. Slow without one, since every outer row triggers a fresh scan of the whole inner table.

MySQL 8.0.18, released in October 2019, added Hash Join for equi-joins that have no usable index, building an in-memory hash table on the smaller input instead of repeatedly scanning it. Before that release, an unindexed equi-join on a large table was genuinely one of the worse query shapes you could write.

Using filesort means MySQL couldn't satisfy an ORDER BY from an index's existing sort order, so it sorts the result set as a separate step, in memory if it fits inside sort_buffer_size, spilled to disk if it doesn't. The name is a bit dated; it doesn't necessarily touch an actual file on a modern install, but the label stuck.

Using temporary means the query needs an internal temp table, common with a GROUP BY the driving index doesn't cover, a DISTINCT, or an ORDER BY on different columns than the GROUP BY. Neither flag crashes anything by itself. Both get quietly more expensive as row count grows, which is exactly why a query that felt instant against 10,000 rows in staging turns into a support ticket once the table hits four million rows in production.

Under READ COMMITTED, each individual statement inside a transaction sees a fresh snapshot taken at the moment that statement starts. Run the same SELECT twice inside one transaction, and if another transaction committed a change in between, the second SELECT can return a different value than the first, a non-repeatable read.

Under REPEATABLE READ, InnoDB's default, the entire transaction sees one consistent snapshot fixed at the time of its first read, no matter how many statements run inside it after that. Two SELECTs of the same row inside the same transaction always match, even if another transaction commits a change to that row in between.

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If either UPDATE fails, or the connection drops before COMMIT runs, InnoDB rolls back everything done since START TRANSACTION using the undo log, both statements or neither. Without wrapping these two statements in a transaction, a crash between them leaves money deducted from account 1 and never credited to account 2, a half-finished transfer sitting in the data with no way to tell it apart from a correct one just by looking at the row.

Two transactions each hold a lock the other one needs next, and each is waiting on the other to release first, so neither can ever proceed on its own. InnoDB's deadlock detector isn't running on a fixed timer, it walks the wait-for graph as soon as a lock wait starts stacking up, and once it spots a cycle, it picks whichever transaction has done less work by InnoDB's own rough measure, usually fewer modified rows, as the victim.

That victim gets rolled back automatically and its connection gets error 1213 back. The other transaction just continues like nothing happened. Application code has to catch that specific error and retry the whole transaction; MySQL doesn't retry it on your behalf.

Most interview answers treat normalization like a moral obligation. Plenty of production schemas break 3NF on purpose, and not as a shortcut: reporting tables, cached running totals, an order_line that stores product_name as it existed at purchase time instead of joining live to the products table.

That last one isn't really an optimization at all, it's correctness. An order from eighteen months ago should show the product name the customer actually saw when they bought it, not whatever someone renamed the product to last week. Interviewers who ask "when do you break the rules" are usually checking whether a candidate can tell the difference between denormalizing for speed and denormalizing because the normalized version is quietly wrong.

Statement-based replication (SBR) replicates the literal SQL statement, and the replica re-executes it. Row-based replication (RBR) replicates the actual row changes that happened, and the replica just applies those directly.

SBR is more compact on the wire for a bulk UPDATE touching a million rows, one statement instead of a million row-change events, but it breaks the moment a statement isn't deterministic: NOW(), UUID(), RAND(), or an AUTO_INCREMENT value that gets assigned differently depending on the replica's own table state. That silent-divergence risk is exactly why ROW became the default binlog_format starting in MySQL 5.7.7, trading some extra bandwidth for an actual correctness guarantee instead of a hopeful one.

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

GROUP BY collapses rows sharing the same email into one bucket, COUNT(*) counts how many collapsed into each bucket, and HAVING filters those bucket totals after the grouping happens, which is why it's HAVING and not WHERE here, WHERE can't filter on an aggregate that doesn't exist until after grouping runs. Deleting the actual extra rows is a different, slightly trickier query: rank duplicates with ROW_NUMBER() partitioned by email, then delete anything past rank 1, usually through a join back to the same table rather than a direct subquery, since MySQL won't let you SELECT from the table you're currently deleting from in the same statement without wrapping it in a derived table first.

UNION combines the result sets of two queries and removes duplicate rows. UNION ALL combines them and keeps everything, duplicates included. The performance gap comes from that dedup step, to remove duplicates MySQL has to sort or hash the entire combined result set and compare rows against each other, which is extra CPU and often a temporary table on disk if the result doesn't fit in memory.

If you already know the two queries can't produce overlapping rows, they're pulling from mutually exclusive partitions, or one filters status='active' and the other status='inactive', UNION ALL gives identical results with none of the dedup overhead. The common mistake is defaulting to UNION out of habit even when duplicates are structurally impossible, quietly adding a sort step to a query that runs thousands of times a day.

SELECT a.name AS employee_1, b.name AS employee_2, a.hire_date
FROM employees a
JOIN employees b
 ON a.hire_date = b.hire_date
 AND a.id < b.id
ORDER BY a.hire_date;

This is a self-join, the same table joined to itself under two aliases so rows can be compared to each other within one table. The a.id < b.id condition does two jobs at once, it stops an employee from pairing with themselves, since a.id = b.id would always match on hire_date, and it stops each pair from appearing twice, once as (a,b) and once as (b,a). Drop that inequality and you'd get a self-match for every row plus a mirrored duplicate for every real pair.

The general pattern, joining a table to itself with an inequality on the primary key, shows up anywhere you're comparing rows to their peers: employees sharing a manager, products in the same category, or transactions within seconds of each other on the same account.

All three assign a position to each row within a partition ordered by some column, and they differ only in how they handle ties. ROW_NUMBER() gives every row a unique, strictly increasing number regardless of ties, so two rows with identical values still get different numbers, broken arbitrarily by the underlying sort. RANK() gives tied rows the same number but then skips ahead, so if two rows tie for rank 2, the next row gets rank 4, not 3. DENSE_RANK() also ties equally but doesn't skip, the next distinct value gets rank 3.

SELECT name, score,
 ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
 RANK()    OVER (ORDER BY score DESC) AS rnk,
 DENSE_RANK() OVER (ORDER BY score DESC) AS drnk
FROM contestants;

Picking the wrong one is a classic bug source. Use ROW_NUMBER() when you need exactly one row per group, deduping or pagination, RANK() when gaps after ties matter, like competition-style standings, and DENSE_RANK() when you want a compact tier number, say top 3 distinct salary bands, regardless of how many employees fall in each band.

A stored function must return exactly one value and can be used inline inside a SQL statement, like SELECT get_discount(customer_id) FROM orders, anywhere an expression is legal. A stored procedure can return zero, one, or many result sets, or none at all, can take OUT and INOUT parameters, and can run DDL and multi-step transaction logic, but it has to be invoked with CALL, it can't be embedded inside a SELECT.

Functions carry restrictions procedures don't. A function marked DETERMINISTIC generally can't run statements that write data in a non-deterministic way, and depending on binlog format, functions that modify data can bump into statement-based replication safety checks. In practice, reach for a function when you need a computed value reused across many queries, and a procedure for a multi-step operation, a batch job, conditional branching, or anything that manages its own transaction.

A trigger is a block of SQL that fires automatically BEFORE or AFTER an INSERT, UPDATE, or DELETE on a table. You can use it to validate data, maintain a denormalized summary column, write an audit log row, or enforce a business rule at the database layer instead of the application layer.

The risk is that triggers are invisible from the application code path. A developer reading the app's ORM calls has no way to know that updating one row silently cascades into three other writes unless they go digging in the schema, which makes debugging harder, "why did this row change, nothing in the code touched it" is a real support ticket. Triggers run inside the same transaction as the triggering statement, so an expensive or buggy trigger adds latency and lock time to every write, and a trigger that throws an error rolls back the whole original statement, which surprises people expecting a side effect rather than a gate. Most teams reserve triggers for narrow, well-documented cases like audit trails, and push actual business logic into the application layer where it stays visible and testable.

The buffer pool is InnoDB's in-memory cache for table and index pages, along with a few related structures like the change buffer and adaptive hash index. Every read that hits the buffer pool avoids a disk seek, every write applies to the in-memory page first and gets flushed to disk later by the background page cleaner, so buffer pool size is probably the single biggest lever for InnoDB performance.

Standard guidance is to set innodb_buffer_pool_size to roughly 60 to 70 percent of available RAM on a dedicated database server, leaving room for the OS, connection thread memory, and other per-connection buffers like sort_buffer_size and join_buffer_size, which aren't shared. If your working data set is smaller than the buffer pool, you get close to a 100 percent cache hit rate and disk I/O mostly disappears from read paths. If it's bigger, you'll see it in SHOW ENGINE INNODB STATUS and the buffer pool hit rate, and the fix is more RAM, sharding, or accepting that part of your working set will always require disk reads. Undersizing this is one of the most common and most impactful misconfigurations on self-managed MySQL.

Pessimistic locking assumes conflicts are likely, so it takes a lock upfront and holds it for the duration of the operation. In MySQL that's SELECT... FOR UPDATE, which locks the selected rows so no other transaction can modify or lock them until yours commits or rolls back. It's straightforward and safe but holds locks for as long as your transaction runs, so a slow application thread that grabs a lock and then makes a network call before releasing it can back up every other transaction waiting on those rows.

Optimistic locking assumes conflicts are rare, so it takes no database lock at all. It reads a row along with a version number, does the work in application memory, and on write checks the version hasn't changed.

UPDATE accounts
SET balance = 150, version = version + 1
WHERE id = 42 AND version = 7;

If that UPDATE affects zero rows, someone else modified the row in between, and the application retries or surfaces a conflict to the user. Optimistic locking scales better under low contention because it never blocks other transactions, but it pushes retry logic into application code and it's a worse fit when conflicts are frequent, since you'll be retrying constantly instead of just waiting for a lock.

The N+1 problem happens when code fetches a list of N parent records with one query, then loops over them and fires a separate query per row to get related data, ending up with 1 plus N total round trips instead of two. It's extremely common with ORMs, fetch 50 orders, then access order.customer.name inside a loop, and if the ORM lazy-loads relationships, that's 50 additional SELECT statements, each a full network round trip even though the actual data returned is tiny.

You spot it by watching query logs or the slow query log during a request and counting near-identical queries that differ only by an ID in the WHERE clause, or by enabling general_log temporarily in staging and watching the query count for a single page load. APM tools like New Relic or Datadog usually flag "N similar queries" directly.

The fix is almost always to fetch related data in bulk, either with a JOIN that pulls everything in one query, or a single WHERE customer_id IN (...) query issued once for all the IDs already in hand, stitching results together in application memory afterward. Most ORMs have an explicit eager-loading mechanism specifically to avoid this, but it has to be turned on deliberately, it isn't the default since eager-loading everything all the time wastes bandwidth on data you don't need.

A regular B-tree index is built for equality, range lookups, and prefix matching. It can find WHERE name = 'Smith' or WHERE name LIKE 'Sm%' efficiently but it's essentially useless for WHERE description LIKE '%wireless%', that leading wildcard forces a full table scan because a B-tree can't be traversed from the middle of a string.

A FULLTEXT index builds an inverted index, mapping individual words to the rows that contain them, so it answers "which rows contain this word anywhere" without scanning every row. You query it with MATCH(description) AGAINST('wireless headphones' IN NATURAL LANGUAGE MODE) rather than LIKE, and InnoDB has supported FULLTEXT since 5.6, it isn't exclusive to MyISAM anymore. It also supports boolean mode for required and excluded word syntax plus relevance scoring, neither of which LIKE searches can give you.

The tradeoff is that FULLTEXT indexes are heavier to maintain on write, every insert or update has to tokenize the text and update the inverted index, and they aren't a real substitute for a dedicated search engine like Elasticsearch once you need fuzzy matching, typo tolerance, or complex relevance tuning across a large corpus. For a product catalog or a comments table on a small to mid-size app, MySQL's FULLTEXT is usually good enough and much simpler to operate than standing up a separate search cluster.

No, MySQL has regular views, a stored SELECT statement re-executed against live data every time you query it, but no built-in materialized view that persists computed results to disk and refreshes on a schedule, which Postgres and Oracle both have natively. A MySQL view adds zero storage and zero staleness, but that also means a view built on a heavy aggregate query is exactly as slow as running that aggregate directly, the view just saves you retyping it.

To get materialized-view behavior, teams roll their own. Create a real table with the desired result shape, populate it with an INSERT... SELECT, and refresh it on a schedule with a cron job, an event scheduler job created with CREATE EVENT, or a trigger-maintained summary table that updates incrementally as underlying rows change. Some also lean on read replicas plus a scheduled ETL into a reporting table specifically so expensive analytical queries don't contend with the OLTP workload on the primary. It's more manual than a native materialized view, but more explicit about when the data refreshes, which some teams actually prefer for auditability.

Plain EXPLAIN shows MySQL's estimated execution plan, which index it intends to use, roughly how many rows it expects to examine, whether it expects a filesort or temporary table, all based on table statistics gathered before the query actually runs. Those estimates can be badly wrong, especially on tables with stale statistics or heavily skewed data, so the plan can look fine on paper while the query is actually slow.

EXPLAIN ANALYZE, added in MySQL 8.0.18, actually executes the query and reports real timing and real row counts alongside the plan, so you can see that a step expected to examine 200 rows but actually examined 400,000, telling you immediately that statistics are stale or the query is structured in a way the optimizer can't estimate well. It also shows actual time spent per operation in the tree, so you can tell whether the slow part is the join, the sort, or the filter, rather than guessing from row-count estimates alone. The tradeoff is that EXPLAIN ANALYZE runs the query for real, including any subquery side effects, so you don't want to point it at a multi-minute production query on a whim.

A prepared statement separates the SQL structure from the data values at the protocol level. The client sends the query template with placeholders, SELECT * FROM users WHERE email = ?, to the server first, the server parses and compiles that structure into an execution plan, and only afterward does the client send the actual parameter values in a separate message, tagged explicitly as data rather than SQL text.

Because the values are transmitted as typed data and bound directly into the already-compiled plan, they're never re-parsed as SQL, so a malicious string like ' OR '1'='1 can't be interpreted as anything other than a literal value compared against the email column. Contrast that with building a query by string concatenation, where the user's input becomes part of the SQL text itself before it's ever parsed, so crafted input can close the quote and inject new SQL. That's the actual mechanism, not "the driver sanitizes it," structure and data simply never share the same parsing pass.

LIMIT 20 OFFSET 100000 looks cheap because it only returns 20 rows, but MySQL still has to scan and discard the first 100,000 matching rows before it can start returning the 20 you actually want, the OFFSET doesn't let it skip ahead, it has to count through every row in order. As the offset grows, that scan grows linearly, so a deep page can take orders of magnitude longer than page one even though both return the same number of rows.

Keyset pagination, sometimes called seek pagination, avoids that by remembering the last row's sort key from the previous page and using it as a WHERE condition instead of an OFFSET.

SELECT id, created_at, title
FROM posts
WHERE created_at < '2026-07-01 10:15:00'
ORDER BY created_at DESC
LIMIT 20;

If created_at is indexed, this jumps straight to the right spot in the index instead of scanning past everything before it, so performance stays flat regardless of how deep into the result set you're paging. The tradeoff is you lose the ability to jump to an arbitrary page number, you can only move forward and backward from a known cursor, which is exactly why infinite-scroll feeds tend to use keyset pagination while admin-style page-number tables often stick with OFFSET despite the cost.

Barely one I'd actually defend. Maybe a strictly read-only, single-writer archival table where corruption risk from a crash is close to zero because nothing ever writes to it after the initial load. Even then, InnoDB with a compressed row format gets you most of the footprint savings without giving up crash safety.

I haven't personally run a MyISAM table in a production system since around 2016, so treat this answer as informed rather than current. If you're inheriting a legacy schema that still has MyISAM tables in it, the honest interview answer is "I'd plan a migration to InnoDB, not defend keeping it."

A query is covered when every column it references, in the SELECT list and the WHERE clause both, already lives inside one index. InnoDB answers the whole query straight from that index's B+tree and never has to do the second clustered-index lookup at all.

CREATE INDEX idx_orders_covering ON orders (status, order_date, total);

EXPLAIN SELECT order_date, total
FROM orders
WHERE status = 'shipped';
-- Extra: Using index

"Using index" in the Extra column is the confirmation. Don't confuse it with "Using index condition," a different optimization (index condition pushdown) that only pushes part of the filtering down into the index scan, it still fetches the full row afterward.

A self-join, the same table joined to itself under two aliases, one representing the employee row and one representing that employee's manager row.

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;

An index on manager_id matters more here than it looks. Without one, that join is a full nested-loop scan of the whole table for every employee row instead of an index lookup back to each manager.

Start with EXPLAIN, not a guess.

EXPLAIN SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'completed'
GROUP BY c.id;

If the row for orders shows type=ALL and a rows estimate near the table's full size, there's no usable index on the columns the WHERE and JOIN actually filter on. Adding a composite index on orders(status, customer_id) usually turns that full scan into a range scan, since it lets MySQL filter to completed orders and walk straight to each customer's rows without touching the rest of the table. Re-run EXPLAIN after adding it. The rows estimate dropping from a few million to a few thousand is the confirmation, not a guess that it probably helped.

Yes, and it's a fair thing to flag as odd, because InnoDB's REPEATABLE READ is stronger than the SQL standard technically requires at that level. The standard only obligates REPEATABLE READ to stop non-repeatable reads of existing rows. New rows appearing in a re-run range query, phantoms, are allowed to slip through under the standard's definition.

InnoDB closes that gap anyway using gap locks and next-key locks on locking reads (SELECT... FOR UPDATE, UPDATE, DELETE): a next-key lock covers both an index record and the gap immediately before it, so another transaction can't insert a new row into that gap until the first transaction commits (MySQL 8.4 Reference Manual, InnoDB transaction isolation levels). For a plain non-locking SELECT, phantoms get avoided differently, through the MVCC snapshot fixed at the start of the transaction rather than through locking at all.

-- Session A
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- A now holds the lock on row 1

-- Session B (running at the same time)
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- B now holds the lock on row 2

-- Session A
UPDATE accounts SET balance = balance + 50 WHERE id = 2; -- blocks, waiting on B

-- Session B
UPDATE accounts SET balance = balance + 50 WHERE id = 1; -- blocks, waiting on A
-- neither can proceed: InnoDB detects the cycle, kills one session, error 1213

The fix isn't retry logic, retry logic is a band-aid you still need. The actual fix is making every part of the codebase lock rows in the same order, always update the lower account id first, say, which turns this exact scenario into a normal lock wait, one transaction pauses briefly, instead of a cycle neither side can escape.

Replication lag isn't instant, and under load or a slow network it can stretch from milliseconds to real seconds. A read immediately after a write can hit a replica that hasn't applied that write yet, the classic read-your-own-write bug: a user updates their profile, reloads the page, and sees the old value because the read got routed to a replica that's a beat behind.

Common mitigations: pin read-after-write traffic to the primary for a short window right after a write, or check replica lag before routing a read at all. I don't have a clean number for how often this specific bug shows up across the codebases I've seen, only that "why does my data look wrong for a second" tickets trace back to it often enough to be worth naming out loud.

-- Nth highest salary with a window function (MySQL 8.0 and later)
SELECT salary
FROM (
 SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
 FROM employees
) ranked
WHERE rnk = 2;

-- second-highest without window functions, the older interview answer
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

DENSE_RANK handles ties correctly: two employees tied for 2nd place both rank 2, and the next distinct salary is ranked 3, not bumped to 4 the way ROW_NUMBER would bump it. Window functions have made the correlated-subquery version mostly a historical curiosity, most shops run 8.0 or later by now, but keep the older answer ready anyway. Some interviewers ask for it deliberately, banning window functions on purpose, just to see whether you actually know a second way to get there.

SELECT department_id, name, salary
FROM (
 SELECT department_id, name, salary,
     ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
 FROM employees
) ranked
WHERE rn = 1;

PARTITION BY resets the ranking within each department instead of across the whole table, so rn = 1 gives you exactly one row per department. ROW_NUMBER picks an arbitrary winner on a tie unless you add a tiebreaker column to the ORDER BY, swap in RANK() or DENSE_RANK() if a tie between two employees at the same salary should show both rows instead of picking one at random.

The pre-8.0 fallback is a correlated NOT EXISTS subquery checking that no higher salary exists in the same department, which works but re-scans the department's rows for every candidate row, uglier and slower on a large table without a supporting index. Window functions genuinely made this one of the questions that got easier to answer well, not just shorter.

This is next-key locking, and it's one of the more counterintuitive parts of InnoDB. To prevent phantom rows under REPEATABLE READ, InnoDB doesn't just lock the existing rows a range query touches, it locks the gaps between index records too. If transaction A runs SELECT * FROM orders WHERE customer_id = 42 FOR UPDATE and no rows currently match, InnoDB still takes a gap lock on the space in the index where a matching row would go, specifically so transaction B can't sneak in an INSERT that would make a phantom row appear if A re-ran the same query later in the same transaction.

If transaction B tries to INSERT a new order with customer_id = 42 while A's gap lock is held, B blocks, not because of a conflicting row, but because it's inserting into a gap A has locked. Gap locks plus the record lock on the next index record after the gap together form a "next-key lock." This only applies at REPEATABLE READ and above, READ COMMITTED disables gap locking almost entirely and keeps only record locks, which is one practical reason some high-concurrency insert-heavy workloads deliberately run at READ COMMITTED. The debugging tell is checking SHOW ENGINE INNODB STATUS for the latest detected deadlock section, or the lock wait rows in performance_schema.data_locks, where you'll see a lock_type of GAP rather than a plain record lock, confirming it's the gap, not an actual row, causing the wait.

InnoDB's crash-safety story rests on write-ahead logging. Before any data page is modified on disk, the change is first written to the redo log and fsynced, assuming innodb_flush_log_at_trx_commit is set to 1. Data pages themselves get modified in the buffer pool and flushed to the actual tablespace files lazily, on the page cleaner's schedule, not synced to disk on every commit. That's the entire point, redo log writes are small and sequential and fast, data page flushes are comparatively large and random and can be deferred.

On an unclean shutdown, whatever data pages made it to disk before the crash are potentially behind the redo log, so on restart InnoDB replays the redo log from the last checkpoint forward, reapplying every logged change to bring data pages back up to the state they should have been in at the moment of the crash. That gets you durability even though most committed changes hadn't actually been flushed to their real on-disk pages yet.

There's a second layer to this. InnoDB writes changed pages to the doublewrite buffer before writing them to their real location, guarding against a torn page, where an OS or disk write is interrupted mid-page, since MySQL pages are typically 16KB while disk writes commonly happen in smaller blocks. If InnoDB detects a page checksum mismatch during recovery, it reconstructs the page from the doublewrite buffer instead of trying to recover a corrupted target from redo-log replay alone. After redo log replay and any doublewrite recovery, InnoDB rolls back any transactions that were active but uncommitted at the moment of the crash, using the undo log, so recovery ends with the database in exactly the state it was in at the last committed transaction, nothing more and nothing less.

The first step is figuring out whether the replica is applying slowly or just not receiving fast enough. SHOW REPLICA STATUS separates these, Seconds_Behind_Source gives you the lag number, but you want to compare Read_Source_Log_Pos against the primary's current binlog position to see if the IO thread is behind on fetching, versus checking whether the SQL thread, or the applier workers if multi-threaded replication is enabled, is behind on applying what it already received.

If it's the IO thread, it's usually a network problem, a slow link or a saturated NIC, and it's the easier case to fix. If it's the apply side, which is the far more common culprit, the classic cause is that replication historically applies changes from the binlog in commit order, single-threaded, per schema, by default. If the primary is running many small concurrent transactions across different tables and the replica is stuck on single-threaded replication, or multi-threaded replication with too few applier workers, or workers that end up serializing anyway because the writes hit the same table under row-level locks, the replica can't parallelize the apply step and falls behind even though the box itself has plenty of spare CPU.

Concretely, check innodb_replica_parallel_workers (formerly slave_parallel_workers) and confirm parallel apply is actually enabled with a sane worker count. Check whether a single large transaction, a bulk UPDATE or a big DELETE, hit the primary right before the lag started, since one giant transaction has to fully apply as one unit on the replica regardless of parallelism settings. Check whether the replica itself is under unrelated load, a backup job or a reporting query holding a long-running transaction that's starving the buffer pool and slowing the replica's own apply-side writes. And check disk I/O on the replica specifically, since it typically has to both write the relay log and then apply it as real writes, roughly doubling the I/O compared to the primary doing the same work once.