Skip to content
Tutorials Center
My learning

Topics

Lesson 74 of 75 ยท Practice and Review

MySQL Interview Questions

Prepare for MySQL interviews with clear answers on InnoDB vs MyISAM, indexing, isolation levels, replication, EXPLAIN, locks and query optimization.

A MySQL interview rarely asks you to recite syntax. It asks why something behaves the way it does: why a query ignores an index, what an isolation level promises, what happens when a replica falls behind. Good answers are short, concrete and honest about trade-offs. This lesson gives you the questions that come up most, with answers you can say out loud in under a minute, grouped by topic.

Every answer links to the lesson that teaches the topic in depth. A MySQL server cannot run in the browser, so server details such as replication and EXPLAIN output are read-only. Where an idea can be demonstrated with portable SQL, a live example runs in SQLite, and the lesson says which parts are stand-ins.

What interviewers probe

Engine basics InnoDB, rows, pages, logs.
Indexes Order, covering, selectivity.
Concurrency Isolation, locks, deadlocks.
Operations Replication, backups, EXPLAIN.

Explain the trade-off, not just the definition.

1 / 2

Engines and architecture

What is the difference between InnoDB and MyISAM? InnoDB is transactional (ACID), uses row-level locking, supports foreign keys, and recovers after a crash from its redo log. MyISAM has no transactions, locks the whole table for writes, has no foreign keys, and can be left corrupt after a crash. InnoDB is the default since MySQL 5.5, and MySQL's own system tables moved to it in 8.0. Use MyISAM almost never; it survives only in old systems. See storage engines.

Why is the primary key important in InnoDB? InnoDB stores a table as a B-tree ordered by the primary key (the clustered index), so the rows live in the leaves. Every secondary index entry stores the primary key value to find its row. Two consequences: a long primary key (such as a 36-character UUID string) makes every secondary index bigger, and random keys cause page splits on insert. A short, increasing key such as a BIGINT AUTO_INCREMENT is the usual good choice. See InnoDB architecture.

What are the redo log and the undo log? The redo log records changes so that committed work can be replayed after a crash (durability). The undo log keeps previous versions of rows so a rollback can restore them and so other transactions can read a consistent snapshot (MVCC). The binary log is separate and lives at the server level: it records changes for replication and point-in-time recovery.

What is the buffer pool? InnoDB's memory cache of data and index pages. It is the most important performance setting; on a dedicated database server innodb_buffer_pool_size is commonly set to roughly 50 to 75 percent of RAM. See server tuning.

Indexes

How does a B-tree index speed up a query? It keeps values sorted in a shallow tree, so finding a value takes a few page reads instead of scanning every row, and ranges and ORDER BY can follow the sorted leaves. A hash-style lookup could not do range scans. See how indexes work.

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 a condition on b alone, because the entries are sorted by a first. Put equality columns first, then a range column, and remember that columns after a range column are not used for narrowing. See composite indexes.

You can watch the rule with the query planner. SQLite's planner follows the same logic, so this live demo is a fair stand-in for the idea (MySQL's EXPLAIN output looks different, but the choices are the same):

SQL The leftmost prefix rule in a query plan

Press Run (or Ctrl + Enter) to see the result here.

The first two plans search the index; the third scans the table. In MySQL, the third would show type: ALL or index instead of ref.

What is a covering index? An index that contains every column a query needs, so MySQL answers from the index without touching the table rows. EXPLAIN shows Using index in Extra. Because InnoDB secondary indexes already contain the primary key, you get that column for free.

When does MySQL not use an index? When a function wraps the indexed column (WHERE YEAR(created_at) = 2026), when types mismatch (a number compared with a string column), when a LIKE pattern starts with a wildcard ('%abc'), when the optimizer decides the condition matches too many rows so a scan is cheaper, or when the index columns do not match the leftmost prefix. Rewrite the condition to be sargable: created_at >= '2026-01-01' AND created_at < '2027-01-01'.

Why not index every column? Each index costs disk, memory and write time, and many are redundant. Index for real queries and check for unused ones (see the data dictionary lesson).

Transactions, isolation and locking

What does ACID mean? Atomicity (all statements succeed or none do), Consistency (constraints hold before and after), Isolation (concurrent transactions do not see each other's half-finished work, to a degree set by the isolation level), Durability (committed data survives a crash). This part runs live, because SQLite is also transactional:

SQL Atomicity with ROLLBACK

Press Run (or Ctrl + Enter) to see the result here.

After the rollback both balances are unchanged (100 and 50). In MySQL you would write START TRANSACTION instead of BEGIN, and in InnoDB the same all-or-nothing rule holds. See transactions and ACID.

List the isolation levels and the default. From weakest to strongest: READ UNCOMMITTED (dirty reads possible), READ COMMITTED (each statement sees the latest committed data; non-repeatable reads possible), REPEATABLE READ (the InnoDB default; a transaction reads from one snapshot taken at its first read) and SERIALIZABLE (plain reads become locking reads). The standard says REPEATABLE READ may allow phantoms, but InnoDB prevents them for locking reads using next-key locks and for plain reads using the snapshot.

What is MVCC? Multi-version concurrency control: InnoDB keeps old row versions in the undo log, so readers do not block writers and writers do not block readers. A plain SELECT reads the version that matches its snapshot. See isolation levels and MVCC.

What is a deadlock and how do you handle it? Two transactions each hold a lock the other needs. InnoDB detects the cycle immediately and rolls back one transaction (error 1213). The application must catch that error and retry the whole transaction. Reduce them by touching rows in a consistent order, keeping transactions short and indexing the columns used in WHERE, so fewer rows are locked. See locking and deadlocks.

What is a gap lock? Under REPEATABLE READ, a locking read or write on a range locks the gaps between index records as well as the records, preventing other transactions from inserting into the range. This is how phantoms are blocked, and also why inserts can unexpectedly wait. READ COMMITTED mostly disables gap locking.

What does SELECT ... FOR UPDATE SKIP LOCKED do? It locks the rows it returns and skips rows that other transactions have locked, instead of waiting. It is the building block for job queues: each worker claims different rows.

Replication and operations

How does MySQL replication work? The source writes changes to its binary log. A replica's I/O thread copies those events into a relay log, and its SQL (applier) threads replay them. Replication is asynchronous by default, so a replica can lag. With row-based logging (the default) the events carry the changed row images, which is safe for nondeterministic statements. GTIDs give every transaction a global identifier, which makes failover and re-pointing replicas simple.

SQL
-- On a replica (MySQL 8.4 names)
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'db-primary', SOURCE_USER = 'repl',
  SOURCE_PASSWORD = '...', SOURCE_AUTO_POSITION = 1;
START REPLICA;
SHOW REPLICA STATUS\G

What causes replication lag and what can you do? Large transactions, a single-threaded applier, slow disks on the replica, or heavy reads on the replica. Use multi-threaded replication, split big jobs into batches, and never read your own writes from a replica without checking it has caught up. See binary log and replication.

How would you take a consistent backup? For logical backups, mysqldump --single-transaction takes a consistent snapshot of InnoDB tables without blocking writes. For large databases, use a physical backup (Percona XtraBackup, MySQL Enterprise Backup or the Clone plugin). Keep binary logs to replay changes after the backup for point-in-time recovery, and test restores regularly. See backup and restore.

EXPLAIN and optimization

How do you read EXPLAIN? Look at the columns in this order. type shows how rows are accessed, from best to worst: const, eq_ref, ref, range, index, ALL (full scan). key is the index actually chosen. rows is the estimated rows to examine, and filtered the estimated percent kept by the remaining conditions. Extra adds warnings: Using filesort (an extra sort pass), Using temporary (a temporary table), Using index (covering index, good). EXPLAIN ANALYZE (8.0.18 and later) runs the query and adds actual timings and row counts. See EXPLAIN.

A query is slow. What do you do? Measure first: find it in the slow query log or performance_schema. Run EXPLAIN (or EXPLAIN ANALYZE). Look for full scans, filesorts and large rows. Check that predicates are sargable and types match. Add or adjust an index for the filter and order columns. Select fewer columns, replace deep OFFSET with keyset pagination, and avoid per-row queries. Re-measure, because the optimizer's choice can surprise you. See query optimization and finding slow queries.

COUNT(*) versus COUNT(column)? COUNT(*) counts rows. COUNT(column) counts rows where the column is not NULL. On InnoDB neither is instant on a big table, because of MVCC the engine has to scan an index to count visible rows.

DELETE, TRUNCATE and DROP? DELETE removes selected rows, is logged row by row and can be rolled back. TRUNCATE empties the table quickly by recreating it, resets AUTO_INCREMENT, and commits implicitly because it is DDL. DROP TABLE removes the table itself. Those are server statements, so they are shown only. A related favorite runs live, because counting and NULL behave the same way in SQLite:

SQL NULL, COUNT and DISTINCT in one query

Press Run (or Ctrl + Enter) to see the result here.

The result is 4, 3, 2 and 3. This is a favorite follow-up: COUNT(*) counts all rows, COUNT(col) skips NULL and COUNT(DISTINCT col) skips NULL and duplicates.

When you do not know an answer, reason from the data structure. "InnoDB stores rows in a B-tree by primary key, so..." is a better start than a guess, and interviewers value the method more than the memorized fact.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which statement about InnoDB's clustered index is correct?

    InnoDB organizes the table by its primary key. Secondary index entries store the primary key value, which is why a long primary key enlarges every index.
  2. 2. With an index on (a, b, c), which condition can NOT use the index to seek?

    Entries are sorted by a first, so a condition on b alone cannot narrow the search. This is the leftmost prefix rule.
  3. 3. What is MySQL's default isolation level, and what should an application do after a deadlock error?

    InnoDB defaults to REPEATABLE READ. A deadlock rolls one transaction back with error 1213, and the application must run the transaction again.
  4. 4. In EXPLAIN, which Extra value usually indicates a covering index?

    Using index means the query was answered from the index alone. Filesort and temporary are signs of extra work.
  5. 5. Why can a replica show data older than the source?

    The source does not wait for replicas, so a replica can lag. Semi-synchronous replication or reading from the source avoids stale reads when needed.

Summary

  • InnoDB is transactional with row locks and a clustered primary key; MyISAM is not and is rarely the right choice.
  • Indexes are sorted B-trees: respect the leftmost prefix, keep conditions sargable, and use covering indexes for hot queries.
  • Isolation levels trade consistency for concurrency; REPEATABLE READ is the default, MVCC avoids read-write blocking, and deadlocks must be retried.
  • Replication is binlog-based and asynchronous by default, so plan for lag; back up with consistent snapshots and test restores.
  • For slow queries, measure, read EXPLAIN (type, key, rows, Extra), then fix the index or the query.

Next: MySQL Cheat Sheet.