Skip to content
TryHired.
Interview prep · Technical · checked against the official docs

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.

Updated Nov 16, 2026 · Facts checked against the official documentation · questions: TryHired
Quick answer

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

Basic MySQL interview questions

Advice

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 page
CREATE 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

Intermediate MySQL interview questions

Advice

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 page
SET 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 page
CREATE 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 page
EXPLAIN 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 page
ALTER 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 page
ALTER 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

Advanced MySQL interview questions

Advice

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 page
START 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

Practice

How to practice with this page

Advice
  • 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.
Next

Related interview prep and tools

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.

FAQ

Questions people ask

What MySQL topics come up often in interviews?
InnoDB versus MyISAM, primary keys and the clustered index, composite indexes and EXPLAIN, isolation levels and locking, deadlocks, character sets, foreign keys, replication with the binary log and GTIDs, and backups.
Which MySQL version do these answers follow?
The MySQL 8.4 Reference Manual. MySQL 8.4 is a long-term support (LTS) series; MySQL also publishes Innovation releases with newer features. The answers generally also apply to MySQL 8.0; check version-specific features in your version's manual.
Were these queries run?
No. There is no MySQL server on the build machine, so the SQL snippets are illustrations and no output is shown. Table names are examples.
Method

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.

What this page can't tell you
  • 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.
Sources
  1. MySQL 8.4 manual: binary log (read 2026-11-15)
  2. MySQL 8.4 manual: CHAR and VARCHAR (read 2026-11-15)
  3. MySQL 8.4 manual: clustered and secondary indexes (read 2026-11-15)
  4. MySQL 8.4 manual: multiple-column indexes (read 2026-11-15)
  5. MySQL 8.4 manual: deadlocks (read 2026-11-15)
  6. MySQL 8.4 manual: storage engines (read 2026-11-15)
  7. MySQL 8.4 manual: EXPLAIN output format (read 2026-11-15)
  8. MySQL 8.4 manual: EXPLAIN statement (read 2026-11-15)
  9. MySQL 8.4 manual: foreign keys (read 2026-11-15)
  10. MySQL 8.4 manual: GTID concepts (read 2026-11-15)
  11. MySQL 8.4 manual: introduction to InnoDB (read 2026-11-15)
  12. MySQL 8.4 manual: transaction isolation levels (read 2026-11-15)
  13. MySQL 8.4 manual: InnoDB locking (read 2026-11-15)
  14. MySQL 8.4 manual: InnoDB multi-versioning (read 2026-11-15)
  15. MySQL 8.4 manual: MySQL releases (Innovation and LTS) (read 2026-11-15)
  16. MySQL 8.4 manual: replication (read 2026-11-15)
  17. MySQL 8.4 manual: slow query log (read 2026-11-15)
  18. MySQL 8.4 manual: utf8mb4 (read 2026-11-15)