MySQL-specific interview questions focus on InnoDB internals, replication, and the tuning knobs that come up most in real production systems.
InnoDB is the transactional engine and has been the default since MySQL 5.5 — it supports ACID transactions, row-level locking, foreign key constraints, and crash recovery through its redo and undo logs. MyISAM predates that shift and offers only table-level locking, no transactions, and no foreign keys, which made it faster for simple read-heavy workloads back when concurrency wasn't a big concern. InnoDB caches data and indexes in the `innodb_buffer_pool`, while MyISAM relies on the OS page cache plus its own smaller `key_buffer` for index blocks only. In practice you'd only reach for MyISAM today for legacy compatibility or very specific read-only archival tables, since InnoDB's MVCC and crash-safety make it the sane default everywhere else.
InnoDB tables are index-organized — the data itself lives in the leaf pages of a B+tree keyed on the primary key, which is why people call the primary key a `clustered index`. Every secondary index, by contrast, stores the indexed column plus the primary key value rather than a direct row pointer, so a lookup through a secondary index means traversing that index and then doing a second lookup into the clustered index to fetch the full row. That's why a wide or non-sequential primary key, like a UUID, hurts insert performance and causes page fragmentation, since random insert points force page splits across the whole tree. If you don't define a primary key, InnoDB picks the first `UNIQUE NOT NULL` index it finds, or failing that, generates a hidden 6-byte row ID internally.
MySQL's InnoDB defaults to `REPEATABLE READ`, meaning a transaction takes a consistent MVCC snapshot on its first read and every subsequent read within that transaction sees that same snapshot. Unusually for a database calling itself REPEATABLE READ, InnoDB also prevents phantom reads at that level by combining record locks with gap locks, so-called `next-key locking`, which the SQL standard doesn't actually require there. PostgreSQL, on the other hand, defaults to `READ COMMITTED`, where each statement, not each transaction, gets its own fresh snapshot, so a transaction can see different data across two statements if another transaction commits in between. If you want Postgres to match MySQL's per-transaction snapshot behavior you have to explicitly set `REPEATABLE READ`, or go all the way to `SERIALIZABLE`, which Postgres implements via serializable snapshot isolation rather than locking.
MySQL uses a pluggable storage engine architecture, so InnoDB, MyISAM, and others all sit behind the same SQL layer, whereas PostgreSQL has one storage engine baked into the core. Historically MySQL was easier to set up for straightforward replication and simple read-heavy web workloads, while Postgres has been stronger on standards compliance, complex queries, and richer data types like native arrays and `JSONB`. Postgres also has a more mature extension ecosystem, things like PostGIS or `pg_stat_statements`, and its MVCC implementation stores old row versions directly in the table, requiring periodic `VACUUM`, whereas InnoDB keeps old versions in a separate undo log. Neither is universally better — MySQL still tends to win on raw simplicity and replication tooling, Postgres tends to win once you need advanced SQL features or stricter correctness guarantees.
MySQL replication is built on the binary log, or `binlog`, which records every change made on the primary server either as the actual SQL statements (`statement-based`), the row-level changes (`row-based`, the default since 5.7), or a mix of both. A replica's I/O thread connects to the primary, streams binlog events into its own `relay log`, and a separate SQL thread, or multiple parallel worker threads in modern versions, applies those events to the replica's data. By default this is asynchronous, so the primary doesn't wait for a replica to catch up, which means replication lag and potential data loss on failover are real risks; `semi-sync replication` mitigates that by having the primary wait for at least one replica to acknowledge receipt before committing. Most production setups today also use `GTID`-based replication instead of binlog file-and-position coordinates, since it makes failover and replica reconfiguration far less error-prone.
The query cache stored the full result set of a `SELECT` keyed on the exact text of the query, so an identical subsequent query could be served straight from memory without touching the storage engine at all. The problem was invalidation — any write to a table, even one unrelated row insert, flushed every cached result depending on that table, and the whole cache was protected by a single global mutex. On write-heavy or highly concurrent workloads that mutex became a massive bottleneck, actually making things slower than having no cache at all, and it scaled terribly across multiple CPU cores. MySQL 8.0 dropped it entirely, pushing caching responsibility down to the `InnoDB buffer pool` and the OS filesystem cache, or up to application-level caching layers like Redis, which can make smarter invalidation decisions.
`EXPLAIN` shows you, row by row, how MySQL's optimizer intends to execute a query — which table it hits first, what index, if any, it uses via the `key` column, and the `type` column tells you the access method, ranging from `const` and `eq_ref` down through `range` and `index` to `ALL`, which means a full table scan. The `rows` column is the optimizer's estimate of how many rows it'll have to examine, and the `Extra` column flags things like `Using filesort` or `Using temporary`, which mean MySQL couldn't satisfy an ORDER BY or GROUP BY using an index alone. Since 8.0.18 you can also run `EXPLAIN ANALYZE`, which actually executes the query and gives you real timing and row counts per step instead of just the optimizer's estimates. In practice you're mostly hunting for full table scans on large tables and unnecessary filesorts or temp tables, then adding or restructuring indexes to eliminate them.
The buffer pool is InnoDB's main in-memory cache for data pages, index pages, and metadata like the adaptive hash index and change buffer, sized via `innodb_buffer_pool_size`. On a dedicated database server the common guidance is to set it to roughly 60-80% of available RAM, leaving room for connection thread memory, sort buffers, and the OS. Internally it manages pages with a modified LRU algorithm split into a young and old sublist, specifically so that a one-off full table scan doesn't flush out all your hot, frequently-accessed pages. On busier servers you'd also split it into multiple instances via `innodb_buffer_pool_instances` to reduce mutex contention from concurrent threads all fighting over the same pool.
InnoDB keeps the current auto-increment counter for a table and hands out the next value whenever a row is inserted without an explicit value for that column; since 8.0 that counter's state is written to the redo log so it survives a restart without resetting. Gaps show up because a value is reserved the moment an insert starts, not when it commits, so a rolled-back transaction or a failed insert due to a duplicate key still burns that value permanently. The `innodb_autoinc_lock_mode` setting controls how concurrent inserts get their values — mode 0 is the old table-level `AUTO-INC` lock held for the whole statement, while mode 2, the 8.0 default, lets simple inserts grab values without blocking each other at the cost of statement-based replication no longer being safe. Because of all this, you should never rely on auto-increment values being contiguous or reflecting insertion order under concurrency.
MySQL supports `FULLTEXT` indexes that let you run natural-language or boolean-mode searches with `MATCH() AGAINST()` instead of slow `LIKE '%term%'` scans. It was originally a MyISAM-only feature, but InnoDB gained full-text index support back in 5.6, so you no longer have to give up transactions just to get text search. Under the hood it builds an inverted index mapping tokens to the rows containing them, and you can tune things like the minimum indexed word length or the stopword list, with an `ngram` parser bundled in for languages like Chinese or Japanese that don't tokenize on whitespace. It's genuinely useful for basic search, but it doesn't do relevance tuning, faceting, or fuzzy matching anywhere near as well as a dedicated engine like Elasticsearch, so most people reach for that once search becomes a serious product feature.
Partitioning splits one logical table into multiple physical sub-tables based on a partitioning expression, with `RANGE`, `LIST`, `HASH`, and `KEY` being the main types. The optimizer can do partition pruning, meaning a query whose WHERE clause matches the partitioning column only has to scan the relevant partitions instead of the whole table, which is huge for time-series data partitioned by date where you're constantly querying recent ranges and archiving or dropping old ones. One real constraint in MySQL is that any unique key, including the primary key, must include the partitioning column, which sometimes forces awkward schema changes to make partitioning viable. It's most valuable for very large tables where you need fast bulk deletes of old data, since dropping a partition is far cheaper than a `DELETE`, rather than as a general-purpose performance fix.
Enabling `slow_query_log` and setting `long_query_time`, in seconds with fractional values allowed, tells MySQL to log any query that takes longer than that threshold to a file or table, giving you a concrete list of what's actually hurting production instead of guessing. Tools like `mysqldumpslow` or the more detailed `pt-query-digest` from Percona Toolkit aggregate that log by query pattern, so you can see which query shape is responsible for the most total time rather than chasing a single slow outlier. Once you've identified a culprit, the usual workflow is to run `EXPLAIN` on it, check whether it's doing a full table scan or filesort, and add or adjust an index accordingly. It's also worth turning on `log_queries_not_using_indexes` alongside it, since a query can be fast on a small table today and become your top offender the moment the table grows.
Because InnoDB indexes are B+trees, a composite index only helps a query if it uses the leftmost columns of that index in order — an index on `(a, b, c)` won't help a query filtering only on `b`, which is the single most common indexing mistake people make. A covering index, one that includes every column a query needs, lets MySQL satisfy the whole query from the secondary index's own B+tree without a second lookup into the clustered index, which can be a huge win on hot read paths. You want to avoid over-indexing too, since every index has to be maintained on every insert, update, and delete, so tables with heavy write traffic and a pile of rarely-used indexes pay a real cost for little benefit. The general loop is the same every time: profile with the slow query log, confirm the problem with `EXPLAIN`, add the narrowest index that satisfies the access pattern, and re-check rather than assuming more indexes are always better.
InnoDB runs automatic deadlock detection — when two transactions are each waiting on a lock the other holds, it identifies the cycle and rolls back whichever transaction has done less work, typically the one with fewer modified rows, returning a `Deadlock found when trying to get lock` error to that client. The other transaction proceeds normally, so applications need to catch that specific error and retry the rolled-back transaction rather than treating it as fatal. Deadlocks are more common than people expect on tables with heavy concurrent updates, especially when transactions touch rows in different orders or when gap locks from `REPEATABLE READ` create unexpected lock overlaps. Running `SHOW ENGINE INNODB STATUS` right after one occurs shows you the exact queries and locks involved in the last deadlock, which is usually enough to spot that you need to reorder operations consistently or shorten the transaction.
A covering index is one that contains every column a query needs — both the columns in the WHERE/JOIN clauses and the columns being selected — so MySQL can satisfy the entire query by reading the index's own B+tree without ever touching the underlying table data. This shows up as `Using index` in the `Extra` column of an `EXPLAIN` plan, and it's significantly faster than a normal index lookup because a secondary index is typically much smaller than the full row and more likely to already be cached in the buffer pool. A common technique is adding a few extra trailing columns to an existing index specifically so a hot, frequently-run query becomes covered, even if those columns aren't used for filtering. The tradeoff is the same as with any index: it adds size and slightly more write overhead, so covering indexes are usually reserved for a small number of genuinely hot queries rather than applied everywhere. It's one of the highest-leverage, lowest-risk optimizations once you've identified a query that runs often enough to matter.