MySQL Interview Questions

Summary

InnoDB stores table rows inside the primary key B-tree (the clustered index), ordered by primary key. Every secondary index stores the primary key value as its row pointer, so lookups through a secondary index do a second search in the primary key.

Longer Answer

Consequences:

• Short primary keys keep every secondary index small, because each secondary entry contains the PK.
• Monotonic keys (AUTO_INCREMENT) append to the end of the B-tree. Random keys (UUIDv4) insert all over the tree, causing page splits, fragmentation and many more random I/Os once the table outgrows the buffer pool.
• Range scans on the primary key are fast because rows are physically ordered.
• If you define no primary key, InnoDB uses the first NOT NULL unique index, or creates a hidden 6-byte row ID, which you cannot use in queries and which is a shared counter.

If you need globally unique IDs, use time-ordered ones (UUIDv7, ULID, Snowflake-style) stored as BINARY(16), or keep an AUTO_INCREMENT PK and a separate unique public ID.

Key Points

• Rows live in the PK B-tree.
• Secondary indexes store the PK.
• Prefer short, increasing PKs.
• Random UUIDs cause page splits.

Summary

A composite index on (a, b, c) is sorted by a, then b, then c. It can serve queries that filter on a, on a and b, or on all three, but not on b or c alone. A range condition on a column stops later columns from being used to narrow the search.

Longer Answer

Think of a phone book sorted by last name, then first name. You can find "Smith", or "Smith, John", but not every "John".

Design rules:

• Put equality columns first, then the range or sort column: for WHERE customer_id = ? AND created_at > ? ORDER BY created_at, index (customer_id, created_at).
• An index can also satisfy ORDER BY (avoiding filesort) if the order matches the index after the equality columns.
• Column order by selectivity alone is a myth; order by how queries use the columns.
• One good composite index often beats several single-column indexes (MySQL can merge indexes, but it is usually slower).

Code Example

CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);

-- Uses the index for both filtering and ordering
SELECT id, total FROM orders
WHERE customer_id = 42 AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

Key Points

• Sorted by columns left to right.
• Leftmost prefix must be used.
• Equality columns first, then range/sort.
• Ranges stop use of later columns for seeking.

Summary

Look at type (access method, from best const/eq_ref/ref/range to worst ALL = full scan), key (the index chosen), rows (estimated rows examined), filtered, and Extra (Using index, Using where, Using filesort, Using temporary).

Longer Answer

• type = ALL on a large table means a full table scan. index means a full index scan (better, but still everything).
• possible_keys vs key: an index exists but was not chosen, often because of low selectivity or a type mismatch.
• rows x filtered estimates how many rows flow to the next join step.
• Using filesort: an extra sort step (not necessarily on disk); often fixed with an index matching ORDER BY.
• Using temporary: an internal temporary table, common with GROUP BY or DISTINCT on non-indexed columns.

EXPLAIN FORMAT=TREE shows the plan as a tree, and EXPLAIN ANALYZE (8.0.18+) actually runs the query and reports real row counts and timings per step, which reveals wrong estimates.

Key Points

• type: const > eq_ref > ref > range > index > ALL.
• key, rows, filtered.
• Using filesort / Using temporary are warning signs.
• EXPLAIN ANALYZE shows actual timings.

Summary

Common causes: a function or expression on the column (WHERE DATE(created_at) = ...), implicit type conversion (comparing a VARCHAR column to a number), a leading wildcard (LIKE '%abc'), not using the leftmost prefix of a composite index, OR across different columns, low selectivity, or stale statistics.

Longer Answer

Fixes:

• Rewrite to a range: created_at >= '2026-10-01' AND created_at < '2026-10-02' instead of DATE(created_at) = ...; or index the expression (functional indexes, 8.0.13+).
• Compare with the right type: WHERE phone = '0123', not = 123 (string-to-number conversion disables the index and can match wrong rows).
• Different collations or character sets between joined columns also prevent index use.
• OR across columns: use UNION ALL of two indexed queries, or rely on index merge.
• If the optimiser estimates that a large share of the table matches, a full scan can genuinely be faster.
• Refresh statistics with ANALYZE TABLE; as a last resort use FORCE INDEX (and document why).

Key Points

• Functions on columns, type conversion, leading wildcards.
• Leftmost prefix not used; OR across columns.
• Collation mismatches in joins.
• Low selectivity can make a scan cheaper.

Summary

READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. InnoDB defaults to REPEATABLE READ: a transaction's consistent reads see a snapshot taken at its first read, and locking reads use next-key locks to prevent phantoms.

Longer Answer

• READ UNCOMMITTED: dirty reads possible; almost never used.
• READ COMMITTED: each statement sees the latest committed data; fewer gap locks, so less locking contention; common in high-concurrency OLTP and the default in PostgreSQL and SQL Server.
• REPEATABLE READ (InnoDB default): a stable snapshot for plain SELECTs within the transaction; SELECT ... FOR UPDATE and writes lock index ranges (next-key locks).
• SERIALIZABLE: plain SELECTs become LOCK IN SHARE MODE (when autocommit is off), reducing concurrency.

A subtle point: in REPEATABLE READ, plain reads see the snapshot but UPDATE and locking reads act on the latest committed version, so read-then-update logic can still be wrong unless you lock or update atomically.

Key Points

• Four levels; InnoDB default REPEATABLE READ.
• READ COMMITTED reduces gap locking.
• Snapshot reads vs locking reads differ.
• Read-then-write still needs locks or atomic updates.

Deep dive: Transactions, Locking & Concurrency in Code

Summary

A deadlock happens when two transactions each hold a lock the other needs. InnoDB detects it and rolls back one transaction (the 'victim') with error 1213. Applications must retry the whole transaction, and reduce deadlocks by locking in a consistent order and keeping transactions short.

Longer Answer

Typical causes: two transactions updating the same rows in opposite order (transfer A to B while B to A); gap or next-key locks from concurrent inserts into the same range; unindexed lookups that lock many rows; foreign-key checks.

Diagnosis: SHOW ENGINE INNODB STATUS shows the latest deadlock with both statements and the locks involved; innodb_print_all_deadlocks=ON logs every deadlock. performance_schema.data_locks shows current locks.

Prevention: access rows in a consistent order (for example by primary key), keep transactions small and fast, index the columns in WHERE clauses of updates, and avoid user think-time inside transactions. Deadlocks can never be eliminated entirely in a busy system, so retry with a small backoff.

Key Points

• Circular waits; InnoDB rolls back one (error 1213).
• Retry the whole transaction.
• Consistent lock order, short transactions, good indexes.
• SHOW ENGINE INNODB STATUS for diagnosis.

Deep dive: Code Clinic: review a PHP money transfer

Summary

LIMIT 20 OFFSET 100000 makes MySQL read and discard 100,000 rows before returning 20, so deep pages get linearly slower. Keyset (seek) pagination remembers the last row's sort key and asks for WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20, which uses the index directly at any depth.

Longer Answer

Keyset pagination is also stable when rows are inserted while users page (no duplicates or skipped rows). Its limitation is that you cannot jump to an arbitrary page number; it suits infinite scroll, APIs and exports. The sort must be on a unique combination (add the primary key as a tiebreaker) backed by an index.

If you need page numbers, a deferred join helps: select only primary keys with the offset (covered by an index), then join back for the full rows.

Code Example

-- next page after the last row the client saw
SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < ('2026-10-10 12:00:00', 98231)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Key Points

• OFFSET reads and discards rows.
• Keyset: WHERE on last-seen sort key.
• Needs a unique, indexed sort order.
• Deferred join if page numbers are required.

Summary

InnoDB is transactional (ACID), uses row-level locking and MVCC, supports foreign keys, and recovers automatically after a crash. MyISAM has none of these: it locks whole tables on writes and can be corrupted by a crash.

Longer Answer

InnoDB writes changes to a redo log before data pages (write-ahead logging), so committed transactions survive crashes. Readers do not block writers thanks to MVCC, and writers lock individual rows (plus gaps under REPEATABLE READ). Data is stored in a clustered index on the primary key.

MyISAM stores data and indexes separately, has table-level locks (a long write blocks all reads), no transactions and no foreign keys. Its historical advantages (fast COUNT(*), full-text search) no longer matter: InnoDB has had full-text indexes since 5.6. Use InnoDB for everything except special cases (MEMORY for temporary lookup data, ARCHIVE).

Key Points

• InnoDB: ACID, row locks, MVCC, FKs, crash recovery.
• MyISAM: table locks, no transactions.
• InnoDB clusters data by primary key.

Summary

A gap lock locks the space between index records so other transactions cannot insert into it; a next-key lock is a record lock plus the gap before it. InnoDB uses them under REPEATABLE READ for locking reads and writes, to prevent phantom rows.

Longer Answer

SELECT * FROM orders WHERE customer_id = 7 FOR UPDATE locks the matching index records and the gaps around them, so another transaction inserting an order for customer 7 (or a neighbouring value) waits. If the condition is not on an index, InnoDB may lock every row it scans, which is effectively the whole table.

Surprises: inserts blocked by a range nobody seems to touch; deadlocks between transactions that insert into the same gap; UPDATE ... WHERE non_indexed_col = ? locking far more than expected. Mitigations: index the columns in locking conditions, keep transactions short, and consider READ COMMITTED (gap locking is mostly disabled there, except for foreign-key and duplicate-key checks).

Key Points

• Gap lock: blocks inserts into a range.
• Next-key = record + preceding gap.
• Unindexed conditions lock everything scanned.
• READ COMMITTED reduces gap locks.

Deep dive: Transactions, Locking & Concurrency in Code

Summary

The source writes changes to the binary log; each replica's I/O thread copies events into its relay log, and SQL (applier) threads replay them. By default this is asynchronous; semi-synchronous replication waits until at least one replica has received the event before the commit returns. GTIDs give every transaction a global ID, making failover and re-pointing replicas much simpler.

Longer Answer

• Asynchronous: lowest latency; a source crash can lose transactions not yet copied.
• Semi-synchronous: the commit waits for a replica acknowledgement (received, not applied), reducing data loss at the cost of latency.
• GTIDs (server_uuid:transaction_number) let a replica ask for "everything I have not executed" instead of tracking file names and positions.
• Replicas can apply transactions in parallel (multi-threaded replicas) to reduce lag.

Replication is not a backup: a DROP TABLE replicates too. Group Replication / InnoDB Cluster adds consensus-based, automatic failover.

Key Points

• Binlog, relay log, applier threads.
• Async vs semi-sync durability trade-off.
• GTIDs simplify failover.
• Replication is not a backup.

Deep dive: Scaling Databases & Data Consistency

Summary

Route reads that must see the user's own recent writes to the primary (read-your-writes), monitor lag, and only use replicas for reads that tolerate staleness. Reduce lag with parallel appliers, smaller transactions and adequate replica hardware.

Longer Answer

Symptoms: a user saves a profile, the next page reads from a replica and shows old data. Strategies:

• Sticky primary after write: for a few seconds after a write (or for the rest of the request), read from the primary. Laravel's sticky database option does this per request.
• GTID-based waits: wait until the replica has applied the GTID of the write (WAIT_FOR_EXECUTED_GTID_SET) before reading.
• Lag-aware routing: proxies such as ProxySQL stop sending reads to replicas that are too far behind.

Causes of lag: huge transactions (a single DELETE of millions of rows replays as one unit), single-threaded applying, DDL, and weaker replica hardware.

Key Points

• Read-your-writes from the primary.
• Lag-aware routing.
• Avoid huge transactions; parallel appliers.
• Monitor lag.

Deep dive: Scaling Databases & Data Consistency

Summary

Use physical backups (Percona XtraBackup, MySQL Enterprise Backup, snapshots) for large databases and logical backups (mysqldump, MySQL Shell dump utilities) for smaller ones or migrations. Point-in-time recovery restores the last full backup and then replays the binary logs up to just before the incident.

Longer Answer

• mysqldump --single-transaction takes a consistent logical dump of InnoDB tables without long locks, but restoring large dumps is slow (indexes rebuild).
• MySQL Shell's util.dumpInstance() / loadDump() are parallel and much faster.
• Physical hot backups copy data files plus redo, restoring quickly.
• Keep binary logs (and their retention) long enough to cover the gap between backups.

A backup is only proven by a restore: test restores regularly and measure how long they take against your recovery time objective (RTO).

Key Points

• Physical for big, logical for small/migrations.
• --single-transaction for consistency.
• PITR = full backup + binlog replay.
• Test restores and measure RTO.

Summary

Check whether the change can run with ALGORITHM=INSTANT or INPLACE (online DDL) in MySQL 8; otherwise use an online schema-change tool (gh-ost or pt-online-schema-change) that copies the table in the background and swaps it in. Always test on a copy and watch replication lag.

Longer Answer

• INSTANT (8.0.12+, extended in 8.0.29): adding a column (also in the middle since 8.0.29), dropping a column, renaming a column or changing a default only changes metadata, so it is immediate.
• INPLACE: adding a secondary index, many other changes; concurrent DML is allowed, but the operation still takes brief metadata locks and replicas apply it serially (lag).
• COPY: rebuilds the table and blocks writes; changing a column's data type usually needs it.

Specify ALGORITHM=INSTANT or INPLACE, LOCK=NONE explicitly so MySQL errors instead of silently falling back to a blocking copy. Beware metadata locks: a long-running query on the table makes the ALTER wait, and everything queued behind the ALTER waits too, which can take the application down. Set lock_wait_timeout low for DDL.

Key Points

• INSTANT for metadata-only changes.
• INPLACE allows concurrent DML.
• COPY blocks writes; use gh-ost/pt-osc.
• Watch metadata locks and replica lag.

Summary

MySQL's old utf8 (an alias of utf8mb3) stores at most 3 bytes per character, so it cannot store emoji or some CJK characters; inserts fail or are truncated. utf8mb4 is real UTF-8 and is the default in MySQL 8.0, with the collation utf8mb4_0900_ai_ci.

Longer Answer

Use utf8mb4 for everything, including the connection character set (SET NAMES utf8mb4 or the driver option); a mismatch between connection and column charset leads to mojibake. Collations decide comparison and sorting: _ai_ci is accent- and case-insensitive (so 'resume' = 'résumé'), _as_cs is sensitive, _bin compares bytes. Unique indexes follow the collation, so case-insensitive collations make [email protected] and [email protected] duplicates. Joining columns with different collations can prevent index use.

Key Points

• utf8 = 3-byte utf8mb3; use utf8mb4.
• Default in 8.0: utf8mb4_0900_ai_ci.
• Collation affects equality, sorting and uniqueness.
• Match connection charset.

Summary

Use DECIMAL(p, s) (exact decimal arithmetic) or an integer number of minor units (cents). Never use FLOAT or DOUBLE, which are binary approximations: 0.1 + 0.2 is not exactly 0.3.

Longer Answer

DECIMAL(19, 4) is a common choice for amounts that need sub-cent precision (FX rates, unit prices); integers in cents are simple and fast. Store the currency next to the amount, and do arithmetic with decimal types in the application too (PHP bcmath or a money library, Python Decimal, Java BigDecimal).

Key Points

• DECIMAL or integer minor units.
• Never FLOAT/DOUBLE.
• Store the currency code.
• Decimal arithmetic in code too.

Deep dive: Payments & Money APIs

Summary

AUTO_INCREMENT BIGINT keys are compact and insert in order, which suits InnoDB's clustered index. Random UUIDv4 keys are 16+ bytes, insert randomly (page splits, poor cache locality) and enlarge every secondary index. If you need globally unique or non-guessable IDs, use time-ordered IDs (UUIDv7, ULID) in BINARY(16), or keep an integer PK plus a separate public ID.

Longer Answer

Integer keys leak information (counts, growth) and are guessable, which matters for public URLs; but authorization, not obscure IDs, should protect data. AUTO_INCREMENT values have gaps (rollbacks, INSERT IGNORE, bulk inserts) and are allocated per server, which complicates multi-primary setups. UUID_TO_BIN(uuid, 1) swaps time fields of UUIDv1 to make them more sequential.

Store UUIDs as BINARY(16), not CHAR(36) (which is 36+ bytes and slower to compare).

Key Points

• Increasing keys suit the clustered index.
• Random UUIDs: page splits, larger indexes.
• UUIDv7/ULID in BINARY(16) if needed.
• IDs are not authorization.

Summary

Window functions (MySQL 8.0+) compute values across a set of rows related to the current row without collapsing them like GROUP BY: running totals, rankings, moving averages, previous/next values (LAG, LEAD).

Longer Answer

Latest order per customer:

SELECT * FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t
WHERE rn = 1;

Other uses: SUM(amount) OVER (ORDER BY day) for a running total, RANK() for leaderboards, LAG(value) OVER (...) for day-over-day change. They replace awkward self-joins and correlated subqueries. Window functions run after WHERE and GROUP BY, so filter on their result in an outer query or a CTE.

Key Points

• Aggregate without collapsing rows.
• ROW_NUMBER / RANK / LAG / LEAD / running sums.
• Filter results in an outer query.
• Available from MySQL 8.0.

Summary

INNER JOIN returns only matching rows; LEFT JOIN returns every row from the left table with NULLs where there is no match; RIGHT JOIN is the mirror image. The classic mistake is filtering the right table in WHERE, which removes the NULL rows and silently turns the LEFT JOIN into an INNER JOIN.

Longer Answer

-- Wrong: customers without 2026 orders disappear
SELECT c.id, COUNT(o.id) FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2026-01-01'
GROUP BY c.id;

-- Right: put the condition in ON
SELECT c.id, COUNT(o.id) FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.created_at >= '2026-01-01'
GROUP BY c.id;

Also remember that COUNT(o.id) counts matches while COUNT(*) counts the NULL row too, and that joining two one-to-many relations multiplies rows (fan-out), inflating sums.

Key Points

• INNER vs LEFT vs RIGHT.
• WHERE on the right table negates the LEFT JOIN.
• COUNT(col) vs COUNT(*).
• Fan-out when joining two child tables.

Summary

The buffer pool is InnoDB's in-memory cache of data and index pages. Reads and writes go through it, so most performance depends on the working set fitting in it. On a dedicated database server it is commonly set to around 50-75% of RAM.

Longer Answer

Pages are managed with a modified LRU (new pages enter at a midpoint so a one-off full scan does not evict the hot set). Dirty pages are flushed in the background. Watch the buffer pool hit rate (Innodb_buffer_pool_reads vs read_requests) and free pages. Leave memory for connections, temporary tables, the OS and other processes; swapping is far worse than a smaller buffer pool. innodb_dedicated_server=ON sizes it automatically. In 8.0+ the size can be changed online.

Key Points

• Cache for data and index pages.
• Working set should fit.
• ~50-75% of RAM on dedicated servers.
• Never let the server swap.

Summary

Enable the slow query log (long_query_time, optionally log_queries_not_using_indexes) and summarise it with pt-query-digest, or query performance_schema.events_statements_summary_by_digest (and the sys schema views) to rank normalised queries by total time.

Longer Answer

Rank by total time (calls x average), not just the single slowest execution: a 5 ms query called 10 million times a day matters more than one 10-second report. sys.statement_analysis and sys.statements_with_full_table_scans are quick starting points. Then EXPLAIN the top offenders, fix indexes or queries, and compare before and after. APM tools show the same data per endpoint.

Key Points

• Slow query log + pt-query-digest.
• performance_schema digests / sys schema.
• Rank by total time.
• Fix the top few, measure again.

Summary

In small batches (for example 1,000 to 10,000 rows) by primary-key ranges, each in its own short transaction, with a pause between batches and monitoring of replication lag. Never in one giant statement.

Longer Answer

A single DELETE FROM logs WHERE created_at < '2025-01-01' on 50 million rows holds locks for a long time, bloats undo, writes an enormous binlog event, stalls replicas (the event is applied as one unit) and cannot be stopped cleanly. Batching:

DELETE FROM logs WHERE created_at < '2025-01-01' ORDER BY id LIMIT 5000;
-- repeat until 0 rows affected, sleeping briefly between batches

Walking by primary-key ranges (WHERE id BETWEEN ? AND ?) avoids rescanning. Tools like pt-archiver do this, and for recurring retention, partitioning with DROP PARTITION is better still.

Key Points

• Batch by PK range, short transactions.
• Throttle and watch replica lag.
• pt-archiver for automation.
• Partitioning for recurring retention.

Summary

Both are mature and handle most OLTP workloads well. PostgreSQL offers richer SQL and types (JSONB with GIN indexes, arrays, partial and expression indexes, extensions such as PostGIS and pgvector, transactional DDL). MySQL is simple to operate, very well understood at web scale, and has strong replication tooling and managed offerings. Team experience and ecosystem often decide.

Longer Answer

Lean to PostgreSQL for complex queries and analytics, rich data types, geospatial, strict standards behaviour, or many extensions. Lean to MySQL when the team and tooling are MySQL-centric, for very read-heavy web workloads with familiar replication, or for compatibility with existing systems (Vitess for sharding).

Operational differences matter: PostgreSQL needs VACUUM tuning and uses a process per connection (pooling with PgBouncer is common); MySQL's clustered primary key makes PK choice more critical.

Key Points

• Both are excellent for OLTP.
• PostgreSQL: richer SQL, types, extensions.
• MySQL: operational simplicity, ecosystem.
• Team experience often decides.