These are the questions that come up again and again in interviews for developer and data roles that use MySQL. Each answer is short on purpose: say it in your own words, then give an example. Where an answer needs proof, the example below was run on a real MySQL 8.4 server. Each answer links to the lesson that explains the topic in full.
Interviewers care less about reciting definitions than about whether you understand why. When you answer, add one sentence with a real situation.
Part 1: Basics
1. What is MySQL? An open-source relational database management system (RDBMS), now owned by Oracle (community edition under the GPL, plus commercial editions). It stores data in tables and uses SQL. Its default storage engine is InnoDB, which supports transactions and foreign keys. (What is MySQL)
2. What is the difference between SQL and MySQL? SQL is the language, standardised across databases. MySQL is one database server that implements SQL, with its own extensions (such as LIMIT, AUTO_INCREMENT, ON DUPLICATE KEY UPDATE). PostgreSQL, SQL Server and Oracle are other servers that also speak SQL, each in a slightly different dialect.
3. What are DDL, DML, DQL, DCL and TCL? DDL defines structure (CREATE, ALTER, DROP, TRUNCATE). DML changes data (INSERT, UPDATE, DELETE). DQL reads data (SELECT). DCL controls access (GRANT, REVOKE). TCL controls transactions (COMMIT, ROLLBACK, SAVEPOINT). (SQL Categories)
4. What is the difference between DELETE, TRUNCATE and DROP? DELETE removes rows (optionally with a WHERE), can be rolled back, fires triggers and keeps the AUTO_INCREMENT counter. TRUNCATE removes all rows quickly, resets the counter, and is DDL (it commits implicitly and cannot be rolled back). DROP removes the whole table including its structure.
CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, v VARCHAR(5));
INSERT INTO t (v) VALUES ('a'), ('b'), ('c');
DELETE FROM t;
INSERT INTO t (v) VALUES ('d');
SELECT id, v FROM t;
TRUNCATE TABLE t;
INSERT INTO t (v) VALUES ('e');
SELECT id, v FROM t;+----+------+
| id | v |
+----+------+
| 4 | d |
+----+------+
1 row in set
+----+------+
| id | v |
+----+------+
| 1 | e |
+----+------+
1 row in setAfter DELETE, the next id continues at 4. After TRUNCATE, it starts again at 1. (DELETE, TRUNCATE)
5. What is the difference between CHAR and VARCHAR? CHAR(n) is fixed length and padded with spaces (trailing spaces are removed on reading). VARCHAR(n) is variable length and uses one or two extra bytes to store the length. Use CHAR for values of a fixed size (country codes, hashes) and VARCHAR for the rest. (CHAR and VARCHAR)
6. What is NULL, and how do you compare with it? NULL means “unknown or missing”, not zero and not an empty string. Any comparison with it gives unknown, so = NULL is never true. Use IS NULL, IS NOT NULL, or the NULL-safe <=>. Aggregates (except COUNT(*)) skip NULL. (NULL)
7. What is the difference between WHERE and HAVING? WHERE filters rows before grouping, so it cannot use aggregates. HAVING filters groups after GROUP BY, so it can. (HAVING)
8. What is the difference between UNION and UNION ALL? UNION combines two results and removes duplicates (which costs a sort or hash). UNION ALL keeps everything and is faster. Use UNION ALL unless you need the de-duplication.
SELECT 'x' AS v UNION SELECT 'x' UNION SELECT 'y';
SELECT 'x' AS v UNION ALL SELECT 'x' UNION ALL SELECT 'y';+---+
| v |
+---+
| x |
| y |
+---+
2 rows in set
+---+
| v |
+---+
| x |
| x |
| y |
+---+
3 rows in set**9. What is the difference between COUNT(\*), COUNT(column) and COUNT(DISTINCT column)?** COUNT(*) counts rows. COUNT(column) counts rows where the column is not NULL. COUNT(DISTINCT column) counts different non-NULL values. (Aggregates)
10. What is a primary key, and how is it different from a unique key? A primary key identifies each row: it is unique and not NULL, and a table has only one. A unique key is also unique, but a table can have many, and a UNIQUE column can hold several NULLs, because NULL is never equal to NULL.
CREATE TABLE people (id INT PRIMARY KEY, email VARCHAR(30) UNIQUE);
INSERT INTO people VALUES (1, NULL), (2, NULL), (3, 'a@example.com');
SELECT id, email FROM people ORDER BY id;+----+---------------+
| id | email |
+----+---------------+
| 1 | NULL |
| 2 | NULL |
| 3 | a@example.com |
+----+---------------+
3 rows in set11. What is a foreign key? A column that must match a value in another table’s key, so that a child row cannot exist without its parent. ON DELETE and ON UPDATE choose what happens to children: RESTRICT (refuse), CASCADE (follow), SET NULL. (Foreign Keys)
12. What is a view? A saved SELECT that behaves like a table. It simplifies queries, hides columns, and can add a security layer. It stores no data of its own, and a complex view can be slow. (Views)
13. What is the difference between a stored procedure, a function and a trigger? A procedure is called with CALL and can return result sets and change data. A function returns one value and is used inside expressions. A trigger runs automatically before or after an INSERT, UPDATE or DELETE. (Stored Programs, Triggers)
14. What is normalization? Why denormalize? Normalization organises tables so that each fact is stored once (1NF: atomic values, 2NF: no partial dependencies, 3NF: no transitive dependencies), which prevents update, insert and delete anomalies. Denormalization duplicates data on purpose (snapshots, counters, summaries) for speed or history, with a plan to keep copies correct. (Normalization, Denormalization)
15. How do collations affect comparisons? The default collation in MySQL 8 is utf8mb4_0900_ai_ci: accent- and case-insensitive, and “NO PAD”, so trailing spaces matter. Older collations such as utf8mb4_general_ci treat 'a' and 'a ' as equal (PAD SPACE).
SELECT 'Maya' = 'maya' AS case_insensitive, 'a' = 'a ' AS no_pad_default, 'a' COLLATE utf8mb4_general_ci = 'a ' AS pad_space_old;+------------------+----------------+---------------+
| case_insensitive | no_pad_default | pad_space_old |
+------------------+----------------+---------------+
| 1 | 0 | 1 |
+------------------+----------------+---------------+
1 row in set(Character Sets and Collations)
Part 2: Joins and queries
16. What types of joins are there? INNER JOIN (matches only), LEFT JOIN (all rows from the left, matched where possible), RIGHT JOIN (the mirror), CROSS JOIN (every combination), and a self join (a table with itself). MySQL has no FULL OUTER JOIN: combine a LEFT JOIN and a RIGHT JOIN with UNION.
CREATE TABLE a (id INT, label VARCHAR(5));
CREATE TABLE b (id INT, label VARCHAR(5));
INSERT INTO a VALUES (1, 'a1'), (2, 'a2');
INSERT INTO b VALUES (2, 'b2'), (3, 'b3');
SELECT a.id AS a_id, b.id AS b_id FROM a LEFT JOIN b ON b.id = a.id
UNION
SELECT a.id, b.id FROM a RIGHT JOIN b ON b.id = a.id
ORDER BY COALESCE(a_id, 99), b_id;+------+------+
| a_id | b_id |
+------+------+
| 1 | NULL |
| 2 | 2 |
| NULL | 3 |
+------+------+
3 rows in set17. How do you find rows in one table with no match in another? An anti-join: LEFT JOIN ... WHERE right.id IS NULL, or NOT EXISTS. Beware NOT IN when the subquery can return NULL: it then returns nothing. (Practice: Find the Bug)
18. Subquery or join? Both can express many questions. Joins are often clearer for combining columns. EXISTS is clearest for “is there a related row?”. The optimizer in MySQL 8 handles both well, so choose readability, and check EXPLAIN if it is slow. A correlated subquery refers to the outer row and runs once per row (the optimizer may rewrite it). (Subqueries)
19. What is the difference between ROW_NUMBER, RANK and DENSE_RANK? All number rows in a window. For ties, ROW_NUMBER gives different numbers, RANK gives the same number and skips the following ones (1, 1, 3), and DENSE_RANK gives the same number and does not skip (1, 1, 2). (Rank Functions)
20. What is a CTE? What is a recursive CTE? A common table expression (WITH) names a subquery to use like a temporary table inside one statement, which makes long queries readable. A recursive CTE refers to itself to walk trees and generate sequences. (CTEs, Recursive CTEs)
21. How do you find duplicates, and delete them? Find with GROUP BY ... HAVING COUNT(*) > 1. Delete by joining to a numbered derived table (ROW_NUMBER()) and removing the rows after the first. Prevent them with a UNIQUE index. (Practice 3)
22. How do you get the Nth highest salary? DENSE_RANK() OVER (ORDER BY salary DESC) and filter on the rank, or SELECT DISTINCT salary ... ORDER BY salary DESC LIMIT 1 OFFSET N-1. (SQL Problems)
23. What is the logical order of execution of a SELECT? FROM and JOIN, then WHERE, GROUP BY, HAVING, window functions, SELECT (aliases are created here), DISTINCT, ORDER BY, and finally LIMIT. That is why WHERE cannot use a select alias or an aggregate, while ORDER BY can use an alias. (Execution Order)
24. What does ONLY_FULL_GROUP_BY do? It is part of the default sql_mode in MySQL 8. A query that selects a column that is neither grouped nor aggregated is rejected (error 1055), because the value would be arbitrary. (GROUP BY)
25. How do you page through results? LIMIT n OFFSET m is simple, but deep offsets are slow (MySQL reads and discards the skipped rows). For long lists, use keyset pagination: WHERE id > :last_seen ORDER BY id LIMIT n. Always add a unique tie-breaker to the ORDER BY. (Pagination)
Part 3: Indexes and performance
26. What is an index, and how does InnoDB store it? A separate sorted structure (a B+tree) that lets MySQL find rows without scanning the table. In InnoDB the table itself is stored as a clustered index on the primary key, so rows are physically ordered by it. A secondary index stores the indexed columns plus the primary key value, and a lookup through it then fetches the row from the clustered index. This is why a short primary key keeps every index smaller. (What is an Index, InnoDB)
27. When is an index not used? A function on the column (WHERE YEAR(date) = 2026), a leading wildcard (LIKE '%abc'), a type or collation mismatch (comparing a string column with a number), OR across different columns, a condition that matches most of the table (a scan is cheaper), or a composite index used from the wrong column. (Why Index Is Not Used)
28. What is the leftmost-prefix rule? A composite index on (a, b, c) can serve conditions on a, on a, b, and on a, b, c, but not on b alone or c alone. Put equality columns first, then the range or sort column. (Composite Indexes)
29. What is a covering index? An index that contains all the columns a query needs, so MySQL answers from the index without touching the table (Using index in EXPLAIN). (Covering Indexes)
30. How do you find a slow query? Enable the slow query log (slow_query_log, long_query_time), look at performance_schema / sys digests for the worst statements, then run EXPLAIN or EXPLAIN ANALYZE on the suspect. (Slow Query Log, Performance Schema)
31. How do you read EXPLAIN? Look at type (best to worst: const, eq_ref, ref, range, index, ALL), key (the index used), rows (the estimated rows examined) and Extra (Using filesort and Using temporary are things to examine, Using index is good). EXPLAIN ANALYZE runs the query and shows real times. (EXPLAIN, EXPLAIN ANALYZE)
32. What is the N+1 query problem? Running one query for a list and then one more query per row (for related data). 100 rows means 101 queries. Fix with a join or one IN (...) query. (N+1)
33. What is the difference between partitioning and sharding? Partitioning splits one table into pieces inside one server (useful to drop old data quickly or prune scans). Sharding spreads data across several servers, and the application (or a proxy) must route queries. (Partitioning)
34. How would you speed up a slow report? Check the plan, add or fix the right index, select only the columns you need, filter before aggregating, avoid functions on indexed columns, pre-aggregate into a summary table, and run heavy reports on a replica. (Query Tuning Workflow, Summary Tables)
Part 4: Transactions and locking
35. What is ACID? Atomicity: all statements of a transaction succeed or none. Consistency: constraints hold before and after. Isolation: concurrent transactions do not see each other’s half-done work. Durability: committed data survives a crash (InnoDB redo log). (ACID)
36. What are the isolation levels, and what is MySQL’s default? READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. InnoDB’s default is REPEATABLE READ: a transaction sees the same snapshot for its whole life (MVCC), and next-key locks prevent phantom rows for locking reads.
SELECT @@transaction_isolation AS default_isolation_level, @@autocommit AS autocommit;+-------------------------+------------+
| default_isolation_level | autocommit |
+-------------------------+------------+
| REPEATABLE-READ | 1 |
+-------------------------+------------+
1 row in set37. What is a deadlock, and how do you handle it? Two transactions each hold a lock the other needs. InnoDB detects it, rolls one back (error 1213), and the other continues. The application must retry the whole transaction. To reduce deadlocks: access rows in a consistent order, keep transactions short, and use good indexes (fewer rows locked). (Deadlocks, Transactions in PHP)
38. What is the difference between optimistic and pessimistic locking? Pessimistic locking locks the row first (SELECT ... FOR UPDATE) and others wait. Optimistic locking does not lock: a version column is checked at update time (UPDATE ... WHERE id = ? AND version = ?), and if no row changed, someone else got there first, and you retry or tell the user. (Update Concurrency)
39. What do FOR UPDATE, NOWAIT and SKIP LOCKED do? FOR UPDATE locks the selected rows until the transaction ends. NOWAIT fails immediately if a row is locked. SKIP LOCKED skips locked rows, which is the standard way to build a work queue where each worker gets a different job. (Locking)
40. InnoDB or MyISAM? InnoDB: transactions, row-level locking, foreign keys, crash recovery, the default. MyISAM: no transactions, table locks, no foreign keys, not crash-safe. Use InnoDB for everything new. (InnoDB, MyISAM)
41. What does autocommit do? With autocommit = 1 (the default), every statement is its own transaction, committed at once. START TRANSACTION switches it off until COMMIT or ROLLBACK. DDL statements commit implicitly. (Autocommit)
Part 5: Design and operations
42. How do you model a many-to-many relationship? With a junction table that holds the two foreign keys, with a composite primary key, and extra columns if the relationship has data (an enrollment’s score). (Many-to-Many)
43. How do you store money? With DECIMAL(p, s) (or an integer count of the smallest unit), never FLOAT or DOUBLE, which cannot represent most decimal fractions exactly. (Schema Anti-Patterns)
44. Auto-increment or UUID for a primary key? Auto-increment is small, sequential and fast for InnoDB’s clustered index. Random UUIDs scatter inserts across the index and make every secondary index larger. If you need UUIDs, store them as BINARY(16) (UUID_TO_BIN(uuid, 1) reorders time-based UUIDs to be nearly sequential). (Data Types)
45. JSON column or normalized columns? Use real columns for data you filter, join or constrain. Use JSON for flexible, rarely queried attributes, and index a generated column or a functional index when you do search it. (JSON vs Columns)
46. What is soft delete, and what are its costs? Marking a row as deleted (deleted_at) instead of removing it. It allows undo and keeps history, but every query must filter it, unique keys get awkward, and it does not satisfy a data-erasure request. (Soft Delete)
47. How do you prevent SQL injection? Prepared statements with bound parameters for every value, a whitelist for identifiers (column names, ASC/DESC), least-privilege database accounts, and no string-built SQL. Escaping and input filtering are not enough alone. (SQL Injection)
48. How do you back up MySQL, and how do you recover to a point in time? mysqldump --single-transaction (logical) or a physical tool (CLONE, XtraBackup, snapshots), plus the binary log. To recover: restore the last full backup, then replay the binary log with mysqlbinlog up to just before the mistake. Test restores regularly. (Backup Strategy, Point-in-Time Recovery)
49. What is replication, and what is replication lag? The source writes changes to its binary log, replicas copy and apply them. Lag is how far a replica is behind, caused by heavy writes, large transactions or slow replicas. Replication gives read scaling and failover, but it is not a backup. (Replication Basics)
50. How do you change a big table without downtime? Prefer online DDL (ALGORITHM=INSTANT or INPLACE), the expand and contract pattern for renames, batches for backfills, and an online schema-change tool (gh-ost, pt-online-schema-change) for very large tables. Test on production-sized data first. (Schema Migrations, Online DDL)
How to prepare
- Run the examples. An answer you have typed and seen run is easy to remember.
- Practise the SQL problems under time: SQL Problems, Practice 1, Practice 2, Practice 3.
- Prepare one design story: a schema you built, the trade-offs, and what you would change (Design Questions).
- Say what you do not know. “I have not used that, but I would look at X” is a better answer than a guess.