MySQL interview questions and answers for MySQL 8.4 LTS

Most backend and full-stack interviews include SQL, and for PHP and Node.js roles that usually means MySQL. Interviewers want to see that you can write correct queries, design tables with sensible keys, explain how indexes and transactions work, and read an execution plan when a query is slow.

This set has 21 questions grouped by level, with model answers checked against the MySQL 8.4 Reference Manual. MySQL 8.4 is the current long-term support (LTS) series; the latest release at the time of writing is 8.4.12, published on 18 August 2026. MySQL also ships faster-moving Innovation releases, so it helps to know the difference.

Run the examples on a local MySQL 8.4 server as you go. SQL answers are easier to remember once you have seen the result yourself.

Beginner questions

1. What is the difference between the LTS and Innovation release tracks?

LTS series, currently 8.4, receive only necessary fixes; features are not removed during the series, and support follows Oracle's lifetime policy (five years of premier and three years of extended support). Innovation releases add features and behaviour changes more often and are supported only until the next Innovation release. Most production systems run the LTS track.

2. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates. Put every condition that does not need an aggregate in WHERE, because filtering early means less data to group.

SELECT customer_id, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(total) > 1000;

3. Explain the main join types.

• INNER JOIN returns only rows with a match in both tables.
• LEFT JOIN returns every row from the left table, with NULL columns where the right table has no match. Filtering on right.id IS NULL finds rows with no match.
• RIGHT JOIN is the mirror image and is usually rewritten as a LEFT JOIN for readability.
• CROSS JOIN returns every combination of rows.

MySQL does not support FULL OUTER JOIN; emulate it with a LEFT JOIN and a RIGHT JOIN combined with UNION.

4. DELETE, TRUNCATE or DROP?

DELETE removes selected rows, can be rolled back in a transaction and fires triggers. TRUNCATE TABLE empties the whole table quickly by recreating it, resets AUTO_INCREMENT, causes an implicit commit and does not fire delete triggers. DROP TABLE removes the table and its definition.

5. CHAR or VARCHAR? And which character set?

CHAR(n) is fixed length and suits short values of constant length, such as country codes. VARCHAR(n) stores only the actual length plus one or two bytes. Use utf8mb4 for text: it stores all of Unicode, including emoji. It is the server default in MySQL 8.4, with the default collation utf8mb4_0900_ai_ci.

6. What is the difference between a primary key and a unique key?

A table has at most one primary key; its columns cannot be NULL, and in InnoDB the table's rows are physically stored in primary key order (the clustered index). A table can have many unique keys, and a unique key allows multiple NULL values.

Intermediate questions

7. How does a B-tree index speed up queries, and when is it not used?

An index keeps column values sorted in a B-tree, so MySQL can find matching rows without scanning the whole table. It may not be used when a function wraps the column (WHERE YEAR(created_at) = 2026), when a LIKE pattern starts with a wildcard, when types or collations do not match, or when the optimizer estimates that a large share of the table matches and a scan is cheaper. Rewrite the first example as a range: created_at >= '2026-01-01' AND created_at < '2027-01-01'.

8. What is a composite index and the leftmost-prefix rule?

An index on several columns, such as (customer_id, status, created_at), can be used for conditions on customer_id, on customer_id and status, or on all three, but not on status alone, because the index is sorted by its first column first. Put columns used for equality before columns used for ranges or sorting.

9. What is a covering index?

An index that contains every column a query needs, so MySQL answers from the index without reading the table rows. EXPLAIN shows "Using index" in the Extra column. In InnoDB every secondary index also contains the primary key columns, which helps.

10. How do you investigate a slow query?

Enable the slow query log to find it, then run EXPLAIN to see the plan (access type, chosen index, estimated rows) and EXPLAIN ANALYZE to run the query and compare estimates with actual rows and timings. Look for full table scans (type: ALL), "Using filesort" and "Using temporary" on large tables.

EXPLAIN ANALYZE
SELECT o.id, o.total
FROM orders AS o
WHERE o.customer_id = 42
  AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

EXPLAIN ANALYZE always uses the tree output format and, because it executes the statement, should be used with care on production data.

11. What are ACID properties?

Atomicity: a transaction succeeds completely or not at all. Consistency: it moves the database from one valid state to another, respecting constraints. Isolation: concurrent transactions do not see each other's uncommitted work, to the degree set by the isolation level. Durability: committed changes survive a crash. InnoDB, the default storage engine, provides all four.

12. What are the isolation levels, and what is InnoDB's default?

The four levels are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. InnoDB's default is REPEATABLE READ: plain SELECT statements in a transaction read from a snapshot taken at the first read, and locking reads on ranges use gap or next-key locks to block inserts into the range. Under READ COMMITTED, each read takes a fresh snapshot and gap locking is mostly disabled.

13. How do you lock rows safely when updating stock or balances?

Use a transaction with a locking read, so two sessions cannot both read the same value and overwrite each other.

START TRANSACTION;

SELECT quantity
FROM inventory
WHERE product_id = 7
FOR UPDATE;

UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 7 AND quantity > 0;

COMMIT;

Keep such transactions short, always access tables in the same order to reduce deadlocks, and retry the transaction when MySQL reports a deadlock.

14. Normalisation versus denormalisation?

Normalisation (usually to third normal form) removes duplicated data so each fact is stored once, which avoids update anomalies. Denormalisation deliberately duplicates data, such as a stored order_count on a customer, to make reads faster. Start normalised, and denormalise specific hot paths when measurements justify it and you can keep the copies consistent.

Advanced questions

15. What are window functions?

Functions that calculate across a set of rows related to the current row without collapsing them into one row, as GROUP BY does. Available since MySQL 8.0.

SELECT customer_id, id, total,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn,
       SUM(total)   OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

Wrap it in a subquery or CTE and filter WHERE rn = 1 to get each customer's latest order, a classic interview task.

16. What is a common table expression (CTE)?

A named temporary result defined with WITH that makes complex queries readable. A recursive CTE (WITH RECURSIVE) can walk hierarchies such as category trees or organisation charts.

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 0 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories AS c
  JOIN tree AS t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth, name;

17. Offset pagination or keyset pagination?

LIMIT 20 OFFSET 100000 still reads and discards 100,000 rows, so it slows down on deep pages. Keyset (seek) pagination remembers the last row's sort key and asks for rows after it: WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20, supported by an index on (created_at, id). It stays fast at any depth but cannot jump to an arbitrary page number.

18. How does MySQL replication work?

The source writes changes to its binary log; each replica copies the log into a relay log and applies it. GTIDs give every transaction a unique ID, which simplifies failover. MySQL 8.4 removed the old terminology, so use CHANGE REPLICATION SOURCE TO, START REPLICA and SHOW REPLICA STATUS, and SHOW BINARY LOG STATUS instead of SHOW MASTER STATUS. Replication is asynchronous by default, so replicas can lag.

19. What changed for authentication in MySQL 8.4?

The mysql_native_password plugin is disabled by default, and the default_authentication_plugin variable was removed in favour of authentication_policy. Accounts should use caching_sha2_password; old client libraries may need upgrading. Other 8.4 changes worth mentioning: FLUSH HOSTS was removed (use TRUNCATE TABLE performance_schema.host_cache), and several InnoDB defaults changed, such as innodb_adaptive_hash_index now OFF and innodb_io_capacity now 10000.

20. How do you back up a MySQL database?

Logical backups (mysqldump, or MySQL Shell's dump utilities) export SQL or data files and are portable but slow to restore for large databases. Physical backups copy the data files and suit large databases. For point-in-time recovery, keep the binary logs from the last full backup and replay them. A backup only counts once you have tested restoring it.

21. How do you add an index to a large table without downtime?

InnoDB supports online DDL for most index operations: ALTER TABLE orders ADD INDEX idx_customer_created (customer_id, created_at), ALGORITHM=INPLACE, LOCK=NONE; lets reads and writes continue. Specifying the algorithm and lock makes the statement fail instead of silently blocking if online execution is not possible. Test on a copy of production data first, watch replica lag, and schedule it for a quiet period.

Practice checklist

• Create customers, orders and categories tables and write each query above against them.
• Run EXPLAIN before and after adding a composite index and explain the difference aloud.
• Open two sessions and reproduce a lock wait with SELECT ... FOR UPDATE.
• Write the "latest order per customer" query in two ways: with a window function and with a join.

Next steps

Pair this set with our PHP, Laravel or Node.js interview questions, depending on the role. The MySQL 8.4 Reference Manual is the authority for anything version-specific; check it when an answer depends on defaults.