MySQL interview questions: answers checked against the docs
The database questions developers and DBAs get about MySQL, answered in plain words and checked against the MySQL 8.4 Reference Manual.
MySQL interviews cover InnoDB versus MyISAM, the clustered primary key, CHAR versus VARCHAR, isolation levels, composite indexes, slow queries and EXPLAIN, utf8mb4, foreign keys, gap and next-key locks, deadlocks and replication with the binary log and GTIDs. Below are 11 questions; each answer lists the MySQL 8.4 manual pages it was checked against.
Basic MySQL interview questions
Basic1. What is the difference between InnoDB and MyISAM?
InnoDB, the default engine in MySQL 8.4 (an LTS series), supports transactions with commit, rollback and crash recovery, row-level locking and foreign keys. MyISAM has no transactions or crash-safe recovery and locks whole tables, which limits it under mixed read and write workloads. New tables should use InnoDB unless there is a specific reason not to.
Checked against: MySQL 8.4 manual: storage engines, MySQL 8.4 manual: introduction to InnoDB, MySQL 8.4 manual: MySQL releases (Innovation and LTS)
Basic2. What is a clustered index in InnoDB?
InnoDB stores each table's rows in the order of its primary key: the primary key is the clustered index. Every secondary index stores the primary key value with each entry and uses it to find the row. That is why a short primary key keeps every secondary index smaller, and why sequential keys insert more efficiently than random ones such as random UUIDs.
SQL snippet · illustrative, not run on this pageCREATE TABLE applications (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
candidate_id BIGINT UNSIGNED NOT NULL,
job_id BIGINT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL,
KEY idx_candidate (candidate_id, created_at)
) ENGINE=InnoDB;Checked against: MySQL 8.4 manual: clustered and secondary indexes
Basic3. What is the difference between CHAR and VARCHAR?
CHAR(n) is fixed-length: stored values are padded with spaces to n characters, and trailing spaces are removed when you read them. VARCHAR(n) stores only the characters given plus a length prefix. Use CHAR for values that always have the same length, such as two-letter country codes, and VARCHAR for everything else.
Checked against: MySQL 8.4 manual: CHAR and VARCHAR
Intermediate MySQL interview questions
Intermediate4. What is the default isolation level in MySQL, and how does it differ from PostgreSQL?
InnoDB defaults to REPEATABLE READ: a transaction's plain SELECTs read from a consistent snapshot taken at its first read. PostgreSQL defaults to READ COMMITTED. Both use multiversioning, so ordinary reads do not block writes. Know all four standard levels and which anomalies each allows.
SQL snippet · illustrative, not run on this pageSET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;Checked against: MySQL 8.4 manual: transaction isolation levels, MySQL 8.4 manual: InnoDB multi-versioning
Intermediate5. How do composite indexes work in MySQL?
A multiple-column index can serve queries that use its leftmost columns: an index on (last_name, first_name) helps queries on last_name, or on both, but not on first_name alone. Put the columns used for equality first and the column used for a range or sort after them.
SQL snippet · illustrative, not run on this pageCREATE INDEX idx_name ON people (last_name, first_name);
SELECT * FROM people WHERE last_name = 'Lee' AND first_name LIKE 'A%';Checked against: MySQL 8.4 manual: multiple-column indexes
Intermediate6. How do you find and fix a slow query?
Turn on the slow query log to find queries above long_query_time, then run EXPLAIN on them. Check which index the key column shows (none means a full scan), how many rows MySQL estimates it must examine, and the Extra column for Using filesort or Using temporary. EXPLAIN ANALYZE also runs the query and reports actual times. Then add or adjust an index, or rewrite the query.
SQL snippet · illustrative, not run on this pageEXPLAIN SELECT * FROM applications WHERE candidate_id = 42 ORDER BY created_at DESC;Checked against: MySQL 8.4 manual: EXPLAIN output format, MySQL 8.4 manual: EXPLAIN statement, MySQL 8.4 manual: slow query log
Intermediate7. Why use utf8mb4 instead of utf8mb3?
utf8mb3 stores at most three bytes per character, so it cannot store characters outside the Basic Multilingual Plane, such as many emoji. utf8mb4 is full UTF-8 with up to four bytes per character. Use utf8mb4 for new schemas, and check index length limits when converting.
SQL snippet · illustrative, not run on this pageALTER TABLE candidates CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;Checked against: MySQL 8.4 manual: utf8mb4
Intermediate8. How do foreign keys work?
A foreign key on the child table must reference values that exist in the parent table, and InnoDB enforces this on every insert, update and delete. ON DELETE and ON UPDATE choose what happens to child rows when the parent changes: restrict, cascade or set null. InnoDB requires an index on the referencing columns and creates one if it is missing.
SQL snippet · illustrative, not run on this pageALTER TABLE applications
ADD CONSTRAINT fk_app_job FOREIGN KEY (job_id) REFERENCES jobs (id) ON DELETE CASCADE;Checked against: MySQL 8.4 manual: foreign keys
Advanced MySQL interview questions
Advanced9. What are gap locks and next-key locks?
Besides locking index records, InnoDB can lock the gaps between them. A next-key lock combines a record lock with a lock on the gap before the record. Under REPEATABLE READ, locking reads and index scans use next-key locks so other transactions cannot insert rows into a range you scanned, which prevents phantom rows. They are also a common cause of unexpected lock waits on range updates.
SQL snippet · illustrative, not run on this pageSTART TRANSACTION;
SELECT * FROM jobs WHERE salary BETWEEN 80000 AND 90000 FOR UPDATE; -- also locks the gaps in this range
COMMIT;Checked against: MySQL 8.4 manual: InnoDB locking
Advanced10. How does InnoDB handle deadlocks?
With deadlock detection on (the default), InnoDB notices a deadlock and rolls back one transaction, the victim, so the others can continue; the application gets an error and should retry. Reduce deadlocks by keeping transactions short, accessing tables and rows in a consistent order, and having indexes that keep locks narrow.
Checked against: MySQL 8.4 manual: deadlocks
Advanced11. How does MySQL replication work?
The source writes changes to its binary log; replicas read the binary log and apply the changes. Replication is asynchronous by default, so a replica can lag behind and a failover can lose the latest transactions; semi-synchronous replication reduces that risk. With GTIDs, every committed transaction gets a global ID, which makes failover and re-pointing replicas much simpler.
Checked against: MySQL 8.4 manual: binary log, MySQL 8.4 manual: GTID concepts, MySQL 8.4 manual: replication
How to practice with this page
- Read EXPLAIN output for a slow query and propose an index.
- Explain the primary key choice for a large InnoDB table.
- Describe what happens when two transactions update the same rows in a different order.
- Review generic SQL on the SQL interview page.
Related interview prep and tools
MVCC, VACUUM and index types.
Open →QuestionsMongoDB interview questionsDocuments, sharding and write concern.
Open →QuestionsSQL interview questionsThe SQL every MySQL role tests, with real output.
Open →QuestionsSystem design interview questionsScaling reads and writes in larger systems.
Open →Free toolResume summary generatorDraft a summary for a MySQL Database Administrator role; the title is filled in, you add your experience.
Open →Free toolLinkedIn headline generatorWrite a headline for a MySQL Database Administrator search.
Open →Disclosure: TryHired makes HIRED, a paid job-search program, so we have a conflict of interest when we mention it. This question bank is free, with no TryHired signup.
Questions people ask
What MySQL topics come up often in interviews?
Which MySQL version do these answers follow?
Were these queries run?
How this page is built
TryHired wrote these questions and answers. Statements about the technology were checked against the official documentation pages listed below, saved when the page was built (2026-11-16). The code snippets are short illustrations and were not run on this page. The question list is general practice: it does not predict what a particular employer will ask.
- Interviewers ask different questions for different roles and levels.
- Answers are short explanations, not complete references.
- No MySQL server was used; the answers follow the MySQL 8.4 Reference Manual and generally apply to 8.0.
- MySQL 8.4 manual: binary log (read 2026-11-15)
- MySQL 8.4 manual: CHAR and VARCHAR (read 2026-11-15)
- MySQL 8.4 manual: clustered and secondary indexes (read 2026-11-15)
- MySQL 8.4 manual: multiple-column indexes (read 2026-11-15)
- MySQL 8.4 manual: deadlocks (read 2026-11-15)
- MySQL 8.4 manual: storage engines (read 2026-11-15)
- MySQL 8.4 manual: EXPLAIN output format (read 2026-11-15)
- MySQL 8.4 manual: EXPLAIN statement (read 2026-11-15)
- MySQL 8.4 manual: foreign keys (read 2026-11-15)
- MySQL 8.4 manual: GTID concepts (read 2026-11-15)
- MySQL 8.4 manual: introduction to InnoDB (read 2026-11-15)
- MySQL 8.4 manual: transaction isolation levels (read 2026-11-15)
- MySQL 8.4 manual: InnoDB locking (read 2026-11-15)
- MySQL 8.4 manual: InnoDB multi-versioning (read 2026-11-15)
- MySQL 8.4 manual: MySQL releases (Innovation and LTS) (read 2026-11-15)
- MySQL 8.4 manual: replication (read 2026-11-15)
- MySQL 8.4 manual: slow query log (read 2026-11-15)
- MySQL 8.4 manual: utf8mb4 (read 2026-11-15)