MySQL Interview Questions and Answers
Last updated 2026-11-19
MySQL interviews mix general SQL with the behaviour that is specific to MySQL: storage engines, the default isolation level, sql_mode, upsert syntax, character sets and the way EXPLAIN reports a query. Interviewers use these to find out whether you have run MySQL in practice rather than just read about relational databases.
This page focuses on the MySQL-specific questions and on where MySQL differs from PostgreSQL. Version notes refer to MySQL 8.0 and later unless stated. For standard SQL topics see SQL interview questions, and for the comparison point see PostgreSQL interview questions. Round strategy is in how to crack a SQL interview.
| Version | MySQL 8.0 and later |
|---|---|
| Engine | InnoDB (default) |
| Compared with | PostgreSQL where it helps |
MySQL-specific areas interviewers return to
- InnoDB: clustered primary key, row locking, MVCC, foreign keys, crash recovery.
- sql_mode: ONLY_FULL_GROUP_BY and strict mode change whether bad queries fail or silently succeed.
- Character sets: utf8 in MySQL is a 3-byte subset, and utf8mb4 is real UTF-8.
- Replication and the binary log, and how they relate to point-in-time recovery.
- Query tuning through EXPLAIN, the slow query log and indexes.
How to answer comparison questions
When asked "MySQL or PostgreSQL", avoid declaring a winner. State a specific difference and what it implies: for example, MySQL clusters rows by primary key, so a random UUID primary key causes page splits and a large primary key enlarges every secondary index, while PostgreSQL stores a heap and indexes point to row locations.
MySQL Interview Questions and Answers: questions with worked answers
1 What is the difference between InnoDB and MyISAM?
easyInnoDB is the default engine since MySQL 5.5. It is transactional (ACID), uses row-level locking and MVCC, supports foreign keys and recovers after a crash using a redo log. MyISAM has no transactions or foreign keys, uses table-level locks, can be corrupted by a crash and is effectively legacy. MyISAM keeps an exact row count for COUNT(*) without a WHERE, which InnoDB cannot because MVCC makes the count depend on the transaction. System tables in MySQL 8 are InnoDB too.
2 What is the default transaction isolation level in MySQL, and how does InnoDB prevent phantoms?
mediumREPEATABLE READ, unlike PostgreSQL and Oracle which default to READ COMMITTED. Within a transaction, consistent reads use the snapshot taken at the first read, so the same query returns the same rows. For locking reads (SELECT ... FOR UPDATE, UPDATE, DELETE) InnoDB uses next-key locks, a record lock plus a gap lock, to stop other transactions inserting into the range, which prevents phantoms for those statements. Many production setups switch to READ COMMITTED to reduce gap-lock contention and deadlocks.
sqlSELECT @@transaction_isolation; -- REPEATABLE-READ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;3 What is the difference between utf8 and utf8mb4 in MySQL?
mediumMySQL's utf8 (an alias of utf8mb3) stores at most 3 bytes per character, so it cannot hold 4-byte characters such as emoji and some rarely used CJK characters: inserts fail in strict mode or are truncated otherwise. utf8mb4 is full UTF-8 and is the default since MySQL 8.0. Pick a utf8mb4 collation such as utf8mb4_0900_ai_ci (8.0 default, accent- and case-insensitive) or utf8mb4_bin for case-sensitive comparison. Remember that indexes on utf8mb4 columns use up to 4 bytes per character, which matters for index key length limits.
4 Explain ON DUPLICATE KEY UPDATE, INSERT IGNORE and REPLACE.
mediumAll three handle a unique-key conflict differently. INSERT ... ON DUPLICATE KEY UPDATE performs an upsert: it updates the existing row instead. INSERT IGNORE skips the conflicting row silently and also downgrades some other errors to warnings, which can hide data problems. REPLACE deletes the old row and inserts the new one, which fires delete and insert triggers, changes the auto-increment value and can break foreign keys. Prefer the first. PostgreSQL uses INSERT ... ON CONFLICT.
sqlINSERT INTO page_views (page_id, views) VALUES (7, 1) ON DUPLICATE KEY UPDATE views = views + 1;5 What is ONLY_FULL_GROUP_BY and why does it matter?
mediumIt is a sql_mode flag, on by default since MySQL 5.7.5, that rejects a query selecting a non-aggregated column that is not in GROUP BY and not functionally dependent on the grouped columns. With it off, MySQL returns an arbitrary row's value for that column, a silent bug that gives different results over time. If an interview query errors with "this is incompatible with sql_mode=only_full_group_by", the correct fix is to add the column to GROUP BY or aggregate it, rather than disabling the mode. ANY_VALUE() explicitly accepts an arbitrary value when that is truly intended.
6 How do AUTO_INCREMENT values behave? What are the gaps and limits?
mediumEach table can have one AUTO_INCREMENT column, which must be indexed (usually the primary key). Values are not reused after a DELETE or rollback, and failed inserts consume a value, so gaps are normal; do not rely on contiguous ids. TRUNCATE resets the counter, and DELETE does not. Before 8.0 the counter was held in memory and reset to MAX+1 after a restart, and 8.0 persists it. Use BIGINT for tables that may pass about 2.1 billion rows (signed INT limit), and UNSIGNED doubles the range.
7 What is the difference between DATETIME and TIMESTAMP?
mediumTIMESTAMP stores a UTC value as seconds since the Unix epoch (range 1970 to 2038 for 32-bit values, until 2038-01-19 03:14:07 UTC) and converts to and from the session time zone, using 4 bytes. DATETIME stores the date and time exactly as given, with no time zone conversion, over a range from year 1000 to 9999, using 5 bytes plus fractional seconds. Use TIMESTAMP for "when did this happen" audit fields and DATETIME for values that must not shift with the time zone, such as a scheduled local time.
8 Walk through reading an EXPLAIN output in MySQL.
hardKey columns: type shows the access method from best to worst (system, const, eq_ref, ref, range, index, ALL), where ALL is a full table scan. possible_keys and key show candidate and chosen indexes. rows is the optimiser's estimate of rows examined, and filtered the percentage kept by conditions. Extra gives hints: "Using index" means a covering index, "Using filesort" means an extra sort pass and "Using temporary" means a temporary table. EXPLAIN ANALYZE (8.0.18+) runs the query and shows real timings and row counts, which exposes bad estimates. See SQL indexes questions.
sqlEXPLAIN SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10; -- good: type=ref, key=idx_cust_created, no filesort CREATE INDEX idx_cust_created ON orders (customer_id, created_at);9 How does MySQL replication work?
mediumThe source writes every change to its binary log. A replica's I/O thread copies the binlog events into a relay log, and its SQL (applier) thread replays them. It is asynchronous by default, so a replica can lag and a source crash may lose committed transactions that were not yet shipped. Semi-synchronous replication waits for at least one replica to acknowledge receipt. GTIDs give each transaction a global identifier that simplifies failover. Binlog format ROW is the safe default, because STATEMENT can replicate non-deterministic functions incorrectly. The binary log also enables point-in-time recovery.
10 What features did MySQL 8.0 add that interviewers expect you to know?
mediumWindow functions, common table expressions including recursive ones, descending indexes, invisible indexes (test dropping an index without dropping it), utf8mb4 as default charset, atomic DDL, roles, JSON improvements, EXPLAIN ANALYZE, hash joins (from 8.0.18) and INTERSECT and EXCEPT (8.0.31). MySQL 5.7 reached end of life in October 2023, so older questions about 5.7 limitations are mostly historical. Check the exact version of the database in any test environment.
11 Does MySQL support FULL OUTER JOIN? How do you emulate it?
mediumNo. Emulate with a LEFT JOIN UNION a RIGHT JOIN, using UNION to remove the duplicated matches. Use UNION ALL with an anti-join (WHERE b.id IS NULL) on the second query if duplicates are legitimate. See the worked example in SQL joins questions.
sqlSELECT a.id, b.id FROM a LEFT JOIN b ON a.k = b.k UNION SELECT a.id, b.id FROM a RIGHT JOIN b ON a.k = b.k;12 Is string comparison case-sensitive in MySQL?
mediumIt depends on the column collation. Default collations ending in _ci (case-insensitive) make WHERE name = 'ravi' match 'Ravi'. Collations ending in _cs or _bin are case-sensitive. You can force a comparison with BINARY name = 'ravi' or COLLATE utf8mb4_bin, at the cost of ignoring a normal index in the first form. PostgreSQL comparisons are case-sensitive by default, which is a common surprise when moving queries between them. Trailing spaces are ignored in comparisons for PAD SPACE collations, but not for the 8.0 default utf8mb4_0900 collations (NO PAD).
13 How do you find and fix slow queries in MySQL?
mediumEnable the slow query log (slow_query_log, long_query_time) and aggregate it with pt-query-digest or the performance_schema statement digest tables, then run EXPLAIN on the worst queries. Typical fixes: add a composite index matching the filter and sort, avoid functions on indexed columns, replace SELECT * with needed columns so a covering index applies, avoid OFFSET with large offsets by using keyset pagination (WHERE id > last_id), and check that the buffer pool is sized properly (innodb_buffer_pool_size, commonly most of the memory on a dedicated server).
14 What happens when two transactions deadlock in InnoDB?
hardInnoDB detects the cycle of lock waits, picks a victim (usually the transaction that has done the least work), rolls it back and returns error 1213, so the application must retry the transaction. Reduce deadlocks by accessing tables and rows in a consistent order, keeping transactions short, using appropriate indexes (so fewer rows are locked), and considering READ COMMITTED to avoid gap locks. SHOW ENGINE INNODB STATUS prints the latest deadlock details.
15 How do you back up a MySQL database?
mediummysqldump creates a logical backup as SQL statements; --single-transaction gives a consistent snapshot for InnoDB tables without locking them. It is simple but slow to restore on large data. Physical backups (Percona XtraBackup, MySQL Enterprise Backup, or filesystem snapshots) copy the data files and are faster for large databases. Combine a full backup with the binary log to restore to a precise point in time, and test restores regularly, because an untested backup is not a backup.
Frequently asked questions
Is MySQL or PostgreSQL asked more in interviews?
Both are common. MySQL appears often in web-stack and service-company roles, PostgreSQL in newer product teams. Prepare the shared SQL first, then the specifics of the one in the job description.
Which MySQL version should I assume?
MySQL 8.0 or later, which has window functions and CTEs. Mention if you know a feature needs a newer version.
What is the difference between MySQL and MariaDB?
MariaDB is a fork that diverged after the Oracle acquisition of MySQL. Core SQL is similar, but features such as JSON, window functions and optimiser details differ by version.
What should I read next?
The PostgreSQL side in PostgreSQL interview questions, then SQL query practice.
Related guides
More in Programming Language Interview Questions by Language
- Nth Highest Salary in SQL: Interview Questions
- PL/SQL Interview Questions and Answers
- PostgreSQL Interview Questions and Answers
- Python Coding Interview Questions with Solutions
- Python Decorators Interview Questions
- Python Exception Handling Interview Questions
- Python File Handling Interview Questions
- Python Generators and Iterators Interview Questions
- Python Interview Questions and Answers
- Python Lambda, Map, Filter and Reduce Interview Questions
- Python List, Dict and Set Interview Questions
- Python Memory Management Interview Questions