By Matthew Rathbone · October 30, 2026

MySQL interviews begin with general SQL (joins, aggregates, subqueries) and then test what is specific to MySQL: the InnoDB storage engine, index behavior, sql_mode, isolation levels and functions such as GROUP_CONCAT and IFNULL. Interviewers want to hear that you know why MySQL behaves the way it does, not only the syntax.

This page has 25 questions in that order. For database-neutral questions (keys, normalization, DELETE vs TRUNCATE), see our 30 SQL interview questions and answers. Interviewing for a different database? Try PostgreSQL interview questions or SQL Server interview questions.

Jump to: basics · queries · functions and data types · indexes and performance · InnoDB and transactions · interview tips · FAQ

Examples use a small employees table (id, name, department, salary, manager_id, hired, email) and an orders table, and were run on MySQL 8.0. To follow along, install MySQL and connect with mysql or a client such as Beekeeper Studio.

MySQL basics

1. What is MySQL, and why would you choose it?

MySQL is a free, open-source relational database management system (RDBMS), now owned by Oracle. It is popular for web applications because it is easy to install, fast for read-heavy workloads and well supported by hosting providers and frameworks. New to the language itself? Start with what is SQL.

2. How is MySQL different from PostgreSQL and SQL Server?

All three are relational databases that speak SQL. MySQL is often seen as the simplest to run and a good fit for web workloads. PostgreSQL is stricter and richer in features (RETURNING, partial indexes, more index types). SQL Server is Microsoft's commercial database with its own dialect, T-SQL. Syntax differs in small ways: MySQL uses LIMIT, SQL Server uses TOP, and MySQL quotes identifiers with backticks. Answer honestly: any of them can be the right choice.

3. What are storage engines? Which does MySQL use by default?

A storage engine is the layer that stores and retrieves table data. InnoDB is the default and the one to know: it supports transactions, row-level locking and foreign keys. MyISAM is an older engine with table-level locking, no transactions and no foreign keys. Others, like MEMORY and ARCHIVE, serve narrow cases. Use SHOW CREATE TABLE employees; to see which engine a table uses.

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

Both enforce uniqueness. A table has one primary key, and its columns cannot be NULL. A table can have many unique keys, and a unique key allows NULL values (in InnoDB, multiple NULLs). In InnoDB the primary key is also the clustered index, which means the table's rows are physically organized by it. See how to use a primary key in SQL.

5. What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes the rows you select with WHERE, and can be rolled back inside a transaction. TRUNCATE empties the whole table quickly, resets AUTO_INCREMENT and cannot be rolled back (it is DDL and commits implicitly). DROP TABLE removes the table itself. The auto-increment difference is a favorite follow-up. After deleting all rows and inserting again, the counter keeps going; after TRUNCATE it restarts:

DELETE FROM t;                -- t had ids 1, 2, 3
INSERT INTO t (v) VALUES (9);
-- id = 4

TRUNCATE TABLE t;
INSERT INTO t (v) VALUES (9);
-- id = 1

More in TRUNCATE vs DELETE vs DROP.

Writing queries

6. How do you find the highest-paid employee in each department?

Since MySQL 8.0 you can use a window function. RANK() numbers rows within each PARTITION, and ties share a rank.

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS r
FROM employees
WHERE department IS NOT NULL;
+------+-------------+---------+---+
| name | department  | salary  | r |
+------+-------------+---------+---+
| Cleo | Engineering | 7500.00 | 1 |
| Dev  | Engineering | 7100.00 | 2 |
| Eli  | Engineering | 7100.00 | 2 |
| Ana  | Sales       | 5200.00 | 1 |
| Ben  | Sales       | 4800.00 | 2 |
+------+-------------+---------+---+

Wrap it in a subquery or CTE and filter r = 1 to keep only the top earner. Before 8.0 there were no window functions, which is why older answers use self joins or user variables. Know the difference between RANK, DENSE_RANK and ROW_NUMBER: see how to use DENSE_RANK.

7. How do you find the second-highest salary?

The classic. The most portable answer uses a subquery; in MySQL you can also use LIMIT with OFFSET on distinct values.

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
+---------+
| salary  |
+---------+
| 7100.00 |
+---------+

The DISTINCT matters: two people earn 7100, so without it the "second row" would still be a 7100 but for the wrong reason. Mention ties and what should happen when there is no second value (the query returns no rows; wrap it in another SELECT to get NULL instead). See how to limit rows in MySQL.

8. What is the difference between INNER JOIN, LEFT JOIN and RIGHT JOIN? Does MySQL have FULL OUTER JOIN?

An inner join returns only matching rows. A left join returns every row from the left table, with NULL where there is no match. A right join does the same for the right table. MySQL has no FULL OUTER JOIN. The workaround is a LEFT JOIN combined with a RIGHT JOIN through UNION. Practice them with the interactive joins lesson and how to use LEFT JOIN.

SELECT e.name, COUNT(o.id) AS orders
FROM employees e
LEFT JOIN orders o ON o.employee_id = e.id
GROUP BY e.id, e.name;
+------+--------+
| name | orders |
+------+--------+
| Ana  |      2 |
| Ben  |      1 |
| Cleo |      0 |
| Dev  |      0 |
| Eli  |      0 |
| Fay  |      0 |
+------+--------+

Counting o.id rather than * is what makes employees with no orders show 0 instead of 1.

9. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters groups after aggregation, so it can use aggregate functions.

SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING AVG(salary) > 5000;
+-------------+-------------+
| department  | avg_sal     |
+-------------+-------------+
| Engineering | 7233.333333 |
+-------------+-------------+

Note that Sales (average 5000) is excluded because 5000 is not greater than 5000. See how to use HAVING and how to use GROUP BY.

10. Why does SELECT name, department, COUNT(*) ... GROUP BY department fail?

Because of ONLY_FULL_GROUP_BY, which is part of the default sql_mode in MySQL 5.7 and later. Every selected column must be in the GROUP BY or inside an aggregate, otherwise MySQL cannot know which name to show for a group:

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'demo.employees.name' which is not
functionally dependent on columns in GROUP BY clause

Fix the query (group by name too, or aggregate it, or use ANY_VALUE(name) if you truly do not care which one). Do not "fix" it by turning the mode off, because the old behavior returns arbitrary values silently.

11. How do you find duplicate rows?

Group by the columns that define a duplicate and keep the groups with more than one row:

SELECT email, COUNT(*) AS n
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;

On our sample data this returns no rows, because email has a unique key. To delete duplicates, join the table to itself or use ROW_NUMBER() in a CTE and delete rows numbered above 1.

12. What is a CTE, and what is a subquery?

A subquery is a query nested inside another. A common table expression (CTE, available since MySQL 8.0) is a named result defined with WITH that reads top-to-bottom and can be referenced more than once. A correlated subquery references the outer query and runs once per row, as here, where we find people paid above their own department average:

SELECT name, salary
FROM employees e
WHERE salary > (SELECT AVG(salary)
                FROM employees
                WHERE department = e.department);
+------+---------+
| name | salary  |
+------+---------+
| Ana  | 5200.00 |
| Cleo | 7500.00 |
+------+---------+

MySQL 8.0 also supports WITH RECURSIVE for hierarchies such as org charts.

Functions and data types

13. What is the difference between CHAR and VARCHAR?

CHAR(n) is fixed-length and pads shorter values with spaces; it suits values of constant length such as country codes. VARCHAR(n) is variable-length and stores only what is needed plus a one or two byte length prefix. Use VARCHAR for most text, and TEXT for long free-form content.

14. Which data type should you use for money?

DECIMAL(p, s), which is exact. FLOAT and DOUBLE are approximate and can give results like 0.30000000000000004. Our salary column is DECIMAL(10,2), which is why results above show 7500.00. Other types to know: INT/BIGINT, DATE, DATETIME vs TIMESTAMP (TIMESTAMP is stored in UTC and converted to the session time zone), BOOLEAN (an alias for TINYINT(1)) and JSON.

15. How does MySQL handle NULL? What are IFNULL and COALESCE?

NULL means unknown, so NULL = NULL is not true and you must use IS NULL. IFNULL(a, b) returns b when a is NULL (MySQL-specific); COALESCE(a, b, c, ...) is the standard version that returns the first non-null argument.

SELECT IFNULL(department, 'Unassigned') AS d,
       COALESCE(department, 'n/a')       AS d2
FROM employees WHERE id = 6;
+------------+------+
| d          | d2   |
+------------+------+
| Unassigned | n/a  |
+------------+------+

Related: COUNT(*) counts all rows (6 here) but COUNT(department) skips nulls (5). More in how to use NULL values in SQL.

16. How do you work with dates in MySQL?

Key functions: NOW(), CURDATE(), DATE_FORMAT(), STR_TO_DATE(), DATE_ADD(), DATEDIFF() and TIMESTAMPDIFF().

SELECT name,
       DATE_FORMAT(hired, '%b %e, %Y')        AS hired_on,
       TIMESTAMPDIFF(YEAR, hired, '2026-10-30') AS years
FROM employees LIMIT 3;
+------+--------------+-------+
| name | hired_on     | years |
+------+--------------+-------+
| Ana  | Mar 1, 2021  |     5 |
| Ben  | Jul 15, 2022 |     4 |
| Cleo | Jan 10, 2020 |     6 |
+------+--------------+-------+

Guides: current date and time, difference between two dates and group by month.

17. What does GROUP_CONCAT do?

A MySQL aggregate that joins the values of a group into one string. It supports its own ORDER BY and SEPARATOR.

SELECT department,
       GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS names
FROM employees
GROUP BY department;
+-------------+----------------+
| department  | names          |
+-------------+----------------+
| NULL        | Fay            |
| Engineering | Cleo, Dev, Eli |
| Sales       | Ana, Ben       |
+-------------+----------------+

The result is truncated at group_concat_max_len (1024 bytes by default), a classic gotcha.

18. How do you query a JSON column?

MySQL has a native JSON type. JSON_EXTRACT(col, '$.path') returns JSON (strings keep their quotes); the ->> operator extracts and unquotes in one step; JSON_CONTAINS tests membership.

SELECT name,
       profile->>'$.city'                         AS city,
       JSON_EXTRACT(profile, '$.city')            AS raw,
       JSON_CONTAINS(profile, '"sql"', '$.skills') AS has_sql
FROM employees WHERE id = 1;
+------+--------+----------+---------+
| name | city   | raw      | has_sql |
+------+--------+----------+---------+
| Ana  | Austin | "Austin" |       1 |
+------+--------+----------+---------+

JSON columns cannot be indexed directly. Index a generated column that extracts the value you filter on.

Indexes and performance

19. What is an index, and what are the trade-offs?

An index is a sorted structure (a B-tree in InnoDB) that lets MySQL find rows without scanning the whole table. Indexes speed up WHERE, JOIN and ORDER BY but cost disk space and slow down INSERT, UPDATE and DELETE, because every index must be maintained. Types you should name: primary, unique, regular, composite, full-text and spatial. See how to create an index in SQL.

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

A composite index covers several columns, such as (department, salary). MySQL can use it for queries that filter on the leftmost columns (department, or department and salary) but not for a query on salary alone. Column order should reflect how you filter: equality columns first, range columns after. A covering index contains every column the query needs, so MySQL never touches the table (EXPLAIN shows Using index).

21. How do you find out why a query is slow?

Use EXPLAIN to see the plan, and EXPLAIN ANALYZE (MySQL 8.0.18+) to also run it and see actual timings. After CREATE INDEX idx_dept ON employees (department);:

EXPLAIN SELECT * FROM employees WHERE department = 'Sales';
+----+-------------+-----------+------+---------------+----------+------+------+
| id | select_type | table     | type | possible_keys | key      | ref  | rows |
+----+-------------+-----------+------+---------------+----------+------+------+
|  1 | SIMPLE      | employees | ref  | idx_dept      | idx_dept | const |    2 |
+----+-------------+-----------+------+---------------+----------+------+------+

(Some columns omitted for width.) Read the type column: const (primary key lookup) and ref are good, range is fine, index scans a whole index, and ALL is a full table scan. Also check key (which index was used), rows (estimated rows examined) and Extra (watch for Using filesort and Using temporary). Why might MySQL ignore an index? A function on the column (WHERE LOWER(email) = ...), a leading wildcard (LIKE '%son'), a type mismatch, or a query that returns most of the table. More in how to improve SQL query performance.

InnoDB and transactions

22. How do you do an upsert in MySQL?

Use INSERT ... ON DUPLICATE KEY UPDATE. If the insert would violate a primary or unique key, MySQL updates the existing row instead. (REPLACE INTO also works but deletes and re-inserts the row, which can break foreign keys and resets columns you did not list.)

INSERT INTO employees (name, department, salary, email)
VALUES ('Ana2', 'Sales', 5300, '[email protected]')
ON DUPLICATE KEY UPDATE salary = VALUES(salary);

SELECT ROW_COUNT();   -- 2

Here ROW_COUNT() returns 2 when an existing row is updated (1 for a fresh insert, 0 if nothing changed). That oddity trips people up. In MySQL 8.0.20+, VALUES() is deprecated in favor of a row alias (INSERT ... AS new ON DUPLICATE KEY UPDATE salary = new.salary).

23. What is a transaction, and what are the ACID properties?

A transaction groups statements so that they all succeed or all fail. ACID: Atomicity (all or nothing), Consistency (data moves from one valid state to another), Isolation (concurrent transactions do not see each other's half-finished work) and Durability (committed data survives a crash). In MySQL, InnoDB provides them, MyISAM does not.

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

24. What are the transaction isolation levels? What is MySQL's default?

From weakest to strongest: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. MySQL's InnoDB default is REPEATABLE READ (we confirmed with SELECT @@transaction_isolation;), unlike PostgreSQL and SQL Server where the default is READ COMMITTED. Name the anomalies each level allows: dirty reads, non-repeatable reads and phantom reads. InnoDB uses MVCC snapshots plus gap locks to avoid most phantoms at REPEATABLE READ.

25. How do you back up, scale and secure a MySQL database?

mysqldump makes logical backups (SQL text files), while tools such as Percona XtraBackup or MySQL Enterprise Backup take physical, hot backups; the binary log enables point-in-time recovery. To scale reads, use source-replica replication and send reads to replicas; for bigger loads, add caching, partitioning or sharding. For security, grant users the least privilege they need (GRANT SELECT ON shop.* TO 'app'@'%'), use parameterized queries to prevent SQL injection (how to prevent SQL injection attacks) and require TLS connections.

How to prepare for a MySQL interview

  • Run everything yourself. Install MySQL 8.0 and try EXPLAIN, GROUP BY errors and isolation levels. Hands-on stories beat memorized definitions.
  • Practice writing queries by hand. Many interviews use a shared editor without autocomplete. Rehearse with SQL practice problems and the 38 SQL query examples.
  • Know the version. Window functions, CTEs and EXPLAIN ANALYZE only exist in MySQL 8.0+. Say which version you have used.
  • Prepare one performance story. Describe a slow query, what EXPLAIN showed and which index fixed it.
  • State assumptions. Ask about duplicates, nulls and ties before writing the query.

Where to go next

FAQ

What are the most common MySQL interview questions?

Joins, GROUP BY vs HAVING, indexes and when MySQL uses them, EXPLAIN, InnoDB vs MyISAM, CHAR vs VARCHAR, DELETE vs TRUNCATE, and transaction isolation levels.

What MySQL questions are asked of experienced candidates?

Expect questions on query plans, composite and covering indexes, InnoDB locking and isolation, replication, partitioning, backups and slow query tuning. Questions 19 to 25 above are the core of it.

How should a beginner prepare for a MySQL interview?

Get comfortable with SELECT, joins, GROUP BY and subqueries first, then learn indexes and transactions. Our free interactive lessons cover the fundamentals in your browser.

Is MySQL the same as SQL?

No. SQL is the query language, and MySQL is a database that implements it, with extra features of its own. See what is SQL.

How long does it take to prepare?

With basic SQL knowledge, one to two weeks of daily practice covers this list. Starting from zero, plan for a month. See is SQL easy to learn?