MySQL Interview Questions and Answers

Answer

InnoDB has been the default since MySQL 5.5 because it provides the four things production systems cannot live without: ACID transactions, row-level locking, crash recovery via redo and undo logs, and foreign key enforcement. MyISAM has none of these. It locks entire tables on write, does not support transactions, and after an unclean shutdown its tables can be left corrupted, needing a REPAIR TABLE that may silently lose rows.

In MySQL 8.0 the system tables themselves (the data dictionary) moved from MyISAM to InnoDB, which is why 8.0 finally has atomic DDL: a CREATE TABLE that crashes halfway no longer leaves an orphaned .frm file, because .frm files are gone entirely. In interviews, MyISAM comes up in two ways. First, as a legacy migration story: older PHP-era codebases at services companies like TCS and Infosys still have MyISAM tables, and you should be able to explain the migration (ALTER TABLE t ENGINE=InnoDB, watch disk space because InnoDB is larger, and re-test COUNT(*) queries since MyISAM stored an exact row count and InnoDB does not).

Second, as a trick question about COUNT(*): people claim MyISAM is faster, which was only true for unfiltered counts, and in 8.0 InnoDB parallel scan made even that gap mostly irrelevant. The other engines worth naming are MEMORY (rarely used now that the TempTable engine handles internal temp tables) and ARCHIVE for append-only logs. If an interviewer asks which engine to pick in 2026, the honest answer is that you do not pick: everything is InnoDB unless a very specific archival or federation need says otherwise.

-- See engines available and the default
SHOW ENGINES;

-- Find lingering MyISAM tables in a legacy schema
SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE engine = 'MyISAM'
  AND table_schema NOT IN ('mysql', 'information_schema');

-- Convert one (locks vary by version; test on a replica first)
ALTER TABLE legacy_orders ENGINE=InnoDB;

Answer

CHAR(n) is fixed length: it always stores n characters, right-padded with spaces that are stripped on retrieval. VARCHAR(n) is variable length: it stores the actual string plus a 1 or 2 byte length prefix (2 bytes once the column can exceed 255 bytes). TEXT types (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT) are stored more like blobs: with InnoDB's default DYNAMIC row format, long values are pushed off-page onto overflow pages with only a 20 byte pointer kept in the row.

Practical consequences interviewers listen for: first, VARCHAR length limits are in characters but the row size limit (roughly 65,535 bytes shared across all columns) is in bytes, so a VARCHAR(20000) with utf8mb4 can fail with error 1118 because each character may take 4 bytes. Second, TEXT columns cannot have a DEFAULT literal (until 8.0.13 added expression defaults) and an index on them requires a prefix length, e.g. INDEX(body(100)). Third, sorting large TEXT/VARCHAR values can force on-disk temporary tables; max_sort_length caps how many bytes of each value are compared.

Choose CHAR only for genuinely fixed-width data such as country codes CHAR(2) or MD5 hex CHAR(32); it avoids fragmentation from in-place updates. Use VARCHAR for almost everything user-facing. Reserve TEXT for content where 16 KB+ values are genuinely possible, and keep such columns out of hot tables when you can, because they inflate row size and reduce how many rows fit per 16 KB InnoDB page, which directly reduces buffer pool efficiency.

CREATE TABLE articles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  country CHAR(2) NOT NULL,          -- fixed width, e.g. 'IN'
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(191) NOT NULL,
  body MEDIUMTEXT,
  UNIQUE KEY uk_slug (slug),
  KEY idx_body_prefix (body(100))    -- prefix index on TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Answer

TIMESTAMP stores an epoch-based UTC value: MySQL converts from the session time_zone on write and back to it on read. DATETIME stores the literal wall-clock value with no conversion at all. That single difference drives everything else.

TIMESTAMP historically had the 2038 problem because it was a 32-bit value capped at 2038-01-19; MySQL 8.0.28 extended supported ranges on many platforms, but the safe interview answer is that classic TIMESTAMP ranges from 1970 to 2038 while DATETIME covers years 1000 to 9999. Both support fractional seconds up to microseconds, declared as DATETIME(6) or TIMESTAMP(6), and both work with DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP (since 5.6, DATETIME gained this too). The production pattern most Indian product companies follow: run every server with time_zone set to UTC (and app servers in UTC as well), store DATETIME, and convert to IST only at the presentation layer.

This avoids the classic bug where a replica or a new app pod has a different session time zone and TIMESTAMP values silently shift by 5 hours 30 minutes. The counter-argument for TIMESTAMP is that the conversion is automatic and it is 4 bytes versus DATETIME's 5 plus fractional storage, but implicit conversion is exactly what causes surprises during DST-observing deployments abroad. Also mention that NOW() returns session-zone time while UTC_TIMESTAMP() is explicit, and that CONVERT_TZ() requires the time zone tables to be loaded via mysql_tzinfo_to_sql, which is often forgotten on self-managed EC2 instances.

CREATE TABLE payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  amount_paise BIGINT UNSIGNED NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
    ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB;

-- Inspect and set zone handling explicitly
SELECT @@global.time_zone, @@session.time_zone;
SET time_zone = '+00:00';
SELECT NOW(), UTC_TIMESTAMP();

Answer

MySQL's legacy utf8 charset (an alias for utf8mb3) stores at most 3 bytes per character, so it cannot store emoji, many Indic conjuncts in supplementary planes, or any code point above U+FFFF. Inserting one under strict mode fails with 'Incorrect string value' (error 1366); under non-strict mode the string is silently truncated at the first 4-byte character, which is far worse. utf8mb4 stores up to 4 bytes and covers all of Unicode, which is why MySQL 8.0 made utf8mb4 the server default with utf8mb4_0900_ai_ci as the default collation ('0900' refers to Unicode 9.0 rules, 'ai' accent-insensitive, 'ci' case-insensitive). The 0900 collations are both more correct and faster than the old utf8mb4_general_ci and utf8mb4_unicode_ci, and unlike them they have no-pad semantics, meaning trailing spaces are significant in comparisons, which can surprise code migrating from 5.7.

Two operational gotchas worth stating in an interview. First, index length: with 4-byte characters, the old 767-byte index limit under the COMPACT row format capped unique indexes at VARCHAR(191); with the DYNAMIC row format (default since 5.7) and large prefixes the limit is 3072 bytes, so VARCHAR(255) unique keys are fine on any modern setup. Second, mixing collations across joined columns causes 'Illegal mix of collations' (error 1267) or, more insidiously, kills index usage because MySQL must coerce one side.

Character set must match end to end: column charset, connection charset (charset in the client config or connection string), and the HTML layer. For an Indian consumer app, utf8mb4 is non-negotiable: names, chat, and reviews all contain emoji.

-- Check current defaults
SELECT @@character_set_server, @@collation_server;

-- Table pinned to modern defaults
CREATE TABLE reviews (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  body VARCHAR(1000) NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

-- Find columns still on legacy utf8mb3
SELECT table_name, column_name, character_set_name
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND character_set_name = 'utf8mb3';

Answer

In InnoDB the primary key is not just a constraint, it is the physical layout of the table. InnoDB stores every table as a clustered index: a B+tree ordered by primary key where the leaf nodes contain the full rows. A unique key is a separate B+tree that enforces uniqueness (allowing multiple NULLs, unlike some databases) and whose leaves store the indexed columns plus the primary key value as the row pointer.

A regular (non-unique) secondary index is the same structure without the uniqueness check. Consequences worth stating explicitly: every secondary index lookup that needs non-indexed columns does a second traversal into the clustered index (the 'bookmark lookup'), which is why covering indexes matter; a fat primary key such as a UUID string bloats every secondary index because the PK is appended to each of them; and if you define no primary key, InnoDB promotes the first NOT NULL unique index, or failing that generates a hidden 6-byte row ID, which also serializes inserts through a global counter and breaks tools that need a PK, so 'always define an explicit PK' is the correct stance. MySQL 8.0.30 added GIPK mode (sql_generate_invisible_primary_key) that auto-adds an invisible primary key precisely because PK-less tables cause so much replication pain with row-based binlogs, where the replica must full-scan to find each row to update. Differences on the constraint side: a table has one PK (implicitly NOT NULL) but many unique keys; unique keys accept NULLs; both are enforced at write time and both can be used by the optimizer identically for reads.

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- clustered
  email VARCHAR(254) NOT NULL,
  phone VARCHAR(15),
  city VARCHAR(80),
  UNIQUE KEY uk_email (email),   -- separate B+tree, leaves hold (email, id)
  KEY idx_city (city)            -- non-unique secondary index
) ENGINE=InnoDB;

-- Secondary lookup: idx_city B+tree -> id -> clustered index -> row
EXPLAIN SELECT phone FROM users WHERE city = 'Pune';

Answer

AUTO_INCREMENT hands out monotonically increasing values, but it guarantees uniqueness, not continuity. Gaps are normal and expected. They appear when a transaction that consumed a value rolls back (values are never returned to the pool), when INSERT ...

ON DUPLICATE KEY UPDATE takes the update branch after reserving an ID, when a bulk insert reserves a range it does not fully use, and when a duplicate key error aborts an insert. Interviewers often test whether you will fight gaps; the right answer is that any design requiring gapless sequences (like Indian GST invoice numbering) must not use AUTO_INCREMENT and instead needs an application-level counter table updated inside the same transaction, accepting the serialization cost. A genuinely version-specific fact: before MySQL 8.0 the counter lived only in memory, so a restart reset it to MAX(id)+1, which could resurrect IDs of recently deleted top rows and collide with references held elsewhere.

MySQL 8.0 persists the counter in the redo log, so it survives restarts. Related knobs: innodb_autoinc_lock_mode controls how the counter lock is held for bulk inserts (default 2, interleaved, in 8.0, which is safe because the default binlog format is ROW), and auto_increment_increment plus auto_increment_offset let old-style multi-primary setups avoid collisions by striding (primary A takes odd, primary B even). Also know the LAST_INSERT_ID() contract: it is per-connection, returns the first ID of a multi-row insert, and is unaffected by other sessions, which is why it is safe under connection pooling as long as you read it on the same connection that inserted.

CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL
) ENGINE=InnoDB;

INSERT INTO orders (user_id) VALUES (42);
SELECT LAST_INSERT_ID();  -- per-connection, pool-safe on same conn

-- Demonstrate a gap: this reserves an id even when it rolls back
START TRANSACTION;
INSERT INTO orders (user_id) VALUES (43);
ROLLBACK;
INSERT INTO orders (user_id) VALUES (44); -- id skips the rolled-back value

-- Inspect and reset the counter (only upward)
SELECT AUTO_INCREMENT FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name = 'orders';

Answer

INNER JOIN returns only row pairs where the join condition matches. LEFT JOIN returns every row from the left table, with NULLs filling the right table's columns when no match exists. RIGHT JOIN is a mirrored LEFT JOIN and is rarely written in practice; most style guides say rewrite it as a LEFT JOIN for readability.

MySQL does not implement FULL OUTER JOIN at all, a genuinely MySQL-specific fact interviewers use as a filter; you emulate it with a LEFT JOIN unioned with an anti-joined right side. The classic follow-up is the anti-join: find users who have never placed an order. The canonical form is LEFT JOIN ...

WHERE right.id IS NULL, but NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id) is equally correct and often clearer; since MySQL 8.0.17 the optimizer transforms NOT EXISTS and NOT IN patterns into proper anti-join plans, so the performance difference has mostly evaporated. One trap to name: NOT IN behaves pathologically when the subquery can return NULL (the whole predicate becomes UNKNOWN and returns nothing), so prefer NOT EXISTS. Another trap: putting a filter on the right table in the WHERE clause of a LEFT JOIN (WHERE o.status = 'paid') silently converts it into an INNER JOIN, because NULL rows fail the predicate; the filter belongs in the ON clause if you want to keep unmatched left rows. Being able to articulate that ON-vs-WHERE distinction crisply is one of the highest-signal moments in a screening round.

-- Users with zero orders: two equivalent anti-joins
SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

SELECT u.id, u.email
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Keep unmatched users while filtering orders: condition goes in ON
SELECT u.id, o.id AS paid_order_id
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id AND o.status = 'paid';

Answer

WHERE filters rows before grouping and aggregation; HAVING filters groups after aggregation. That ordering has a performance implication: a condition that could go in WHERE but is written in HAVING forces MySQL to aggregate rows it will immediately discard, so push every non-aggregate predicate down into WHERE. HAVING is only for conditions on aggregates, like HAVING SUM(amount) > 100000.

The second half of this question is where candidates trip. ONLY_FULL_GROUP_BY has been part of the default sql_mode since MySQL 5.7, and it rejects queries that select a column which is neither in the GROUP BY nor functionally dependent on it, raising error 1055. Older 5.6-era code selected arbitrary non-grouped columns and MySQL silently returned an indeterminate value from some row in the group, a real source of subtly wrong reports.

The fixes, in order of preference: actually group by the column, wrap it in an aggregate, or use ANY_VALUE(col) when you genuinely do not care which row's value appears. MySQL is also smart about functional dependency: if you GROUP BY the primary key, you may select any column of that table without error, because the PK determines them all. Interviewers at analytics-heavy teams often extend this into WITH ROLLUP, which appends super-aggregate rows (with NULLs in the grouping columns) for subtotals, and GROUPING() to distinguish a rollup NULL from a data NULL. Disabling ONLY_FULL_GROUP_BY globally to make legacy code run is the anti-pattern to call out: it hides real correctness bugs.

-- Cities with more than 1000 paid orders in July 2026
SELECT u.city, COUNT(*) AS paid_orders, SUM(o.amount_paise) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'                       -- row filter: WHERE
  AND o.created_at >= '2026-07-01'
  AND o.created_at <  '2026-08-01'
GROUP BY u.city
HAVING COUNT(*) > 1000                        -- group filter: HAVING
ORDER BY revenue DESC;

-- Subtotals per city plus a grand total row
SELECT u.city, SUM(o.amount_paise) AS revenue
FROM orders o JOIN users u ON u.id = o.user_id
GROUP BY u.city WITH ROLLUP;

Answer

NULL means 'unknown', and any ordinary comparison with it yields UNKNOWN, not true or false: col = NULL, col != NULL, even NULL = NULL all evaluate to UNKNOWN and filter the row out. The only correct tests are IS NULL and IS NOT NULL. MySQL additionally has the NULL-safe equality operator <=>, which returns 1 when both sides are NULL and behaves like = otherwise; it is handy in queries comparing two nullable columns and it is what row-based replication conceptually relies on when matching rows.

Aggregate behavior is a standard probe: COUNT(*) counts rows, COUNT(col) counts non-NULL values of col, and AVG(col) ignores NULLs entirely, so AVG over a column with NULLs is not SUM/COUNT(*). GROUP BY and DISTINCT treat NULLs as equal to each other (one group), while unique indexes do the opposite: InnoDB allows multiple rows with NULL in a unique column, because two NULLs are not considered duplicates. That asymmetry is a favorite interview trap, and it is why 'nullable unique' columns like optional phone numbers work at all.

On indexing: contrary to a persistent myth, InnoDB does index NULLs, and IS NULL can use an index; what hurts is that nullable columns cost an extra bit of storage per row and complicate the optimizer's range estimates. Functions worth naming: IFNULL(a, b) and the standard COALESCE(a, b, c) for defaults, and NULLIF(a, b) to convert sentinel values into NULL. The production guidance to close with: declare NOT NULL wherever the domain allows, and never use empty strings and NULL interchangeably to mean 'missing', because they compare differently and drive ORMs insane.

-- The trap: this returns zero rows even if phone has NULLs
SELECT COUNT(*) FROM users WHERE phone = NULL;   -- always 0
SELECT COUNT(*) FROM users WHERE phone IS NULL;  -- correct

-- NULL-safe comparison of two nullable columns
SELECT * FROM profile_sync
WHERE NOT (old_city <=> new_city);  -- changed, treating NULL=NULL as same

-- Aggregate semantics
SELECT COUNT(*) AS all_rows,
       COUNT(phone) AS with_phone,
       AVG(rating) AS avg_ignoring_nulls,
       AVG(COALESCE(rating, 0)) AS avg_counting_nulls_as_zero
FROM users;

Answer

DELETE is DML: it removes rows one at a time under the current transaction, writes undo for every row so it can ROLLBACK, fires DELETE triggers, honors foreign key ON DELETE actions, and can take a WHERE clause and LIMIT. On a large table an unfiltered DELETE is brutally slow and bloats the undo logs; the history list length climbs and purge lags behind. TRUNCATE TABLE is DDL: it drops and recreates the table, which is near-instant regardless of row count, resets AUTO_INCREMENT to 1, fires no triggers, cannot be rolled back (in 8.0 DDL is atomic but it still implicitly commits your open transaction), requires the DROP privilege, and fails outright with error 1701 if any other table references this one with a foreign key, even if the referencing table is empty.

DROP TABLE removes the table definition, data, indexes, and associated privileges entirely; also an implicit commit. Two production-oriented follow-ups you should volunteer. First, batched deletes: purging 50 million old rows should be a loop of DELETE ...

WHERE created_at < ? ORDER BY id LIMIT 10000 with a sleep between batches, so replicas keep up and locks stay short; tools like pt-archiver automate exactly this. Second, the partition alternative: if a table is RANGE-partitioned by month, ALTER TABLE ...

DROP PARTITION removes a month of data as fast as a truncate, which is the standard design for audit and event tables. Also worth a sentence: after massive deletes InnoDB does not shrink the tablespace file; you need OPTIMIZE TABLE (which rebuilds via online DDL) to reclaim disk.

-- Batched purge that replicas can keep up with
SET @batch := 10000;
REPEAT
  DELETE FROM events
  WHERE created_at < '2025-01-01'
  ORDER BY id
  LIMIT 10000;
UNTIL ROW_COUNT() = 0 END REPEAT;  -- inside a stored procedure

-- Instant alternatives
TRUNCATE TABLE staging_import;          -- resets AUTO_INCREMENT
ALTER TABLE events DROP PARTITION p2024_12;  -- partitioned purge

-- Reclaim space after a huge delete
OPTIMIZE TABLE events;  -- InnoDB: rebuilds table + analyzes

Answer

A foreign key makes InnoDB enforce that every value in a child column exists in the referenced parent column. The parent column must be indexed (the PK usually is), and InnoDB also requires an index on the child column, creating one automatically if you did not. Referential actions: ON DELETE RESTRICT (or the default NO ACTION, which behaves identically in MySQL: the delete fails with error 1451), ON DELETE CASCADE (children are deleted with the parent), ON DELETE SET NULL (child FK column set to NULL, so it must be nullable).

The write-side error is 1452, 'Cannot add or update a child row', when inserting a child pointing at a nonexistent parent. Important behavioral details interviewers probe: cascaded deletes do not fire triggers on the child table, and a CASCADE on a huge child table turns a one-row parent delete into a massive locking operation, which is why large-scale systems (Vitess-sharded fleets at companies like Flipkart-scale marketplaces, and most microservice teams) often skip FK constraints entirely and enforce integrity in application code plus reconciliation jobs; FKs also complicate online schema change tools like gh-ost, which is a real operational reason, not laziness. You can toggle enforcement per session with SET foreign_key_checks = 0, standard during bulk data loads and schema reshuffles, but leaving it off is how orphaned rows are born.

Also know that FK columns must match the parent's type exactly (including UNSIGNED and charset for strings), otherwise creation fails with error 3780 in 8.0, and that MySQL 8.4 tightened defaults around the old restrict behaviors. A crisp closing point: constraints are checked immediately, MySQL has no deferred constraint checking, which affects the order of inserts in circular schemas.

CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(20) NOT NULL,
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Bulk load pattern
SET foreign_key_checks = 0;
-- LOAD DATA / INSERT ... massive import
SET foreign_key_checks = 1;

-- Find orphans after loading with checks off
SELECT o.id FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL;

Answer

EXPLAIN shows the optimizer's plan without executing the query (for plain EXPLAIN). The columns that carry the signal: 'type' (access method), 'key' (the index chosen), 'rows' (estimated rows examined), 'filtered' (estimated percentage surviving the WHERE), and 'Extra'. The access types, best to worst: 'system'/'const' means at most one row, resolved via primary or unique key with constant values; 'eq_ref' means one row per row of the previous table, the ideal join type, PK or unique index lookup; 'ref' is a non-unique index lookup returning a handful of rows; 'range' is an index range scan from predicates like BETWEEN, >, IN; 'index' is a full scan of the index (better than ALL only because the index is smaller); and 'ALL' is a full table scan.

On a large table, type=ALL in a hot path is the finding: either no usable index exists, or the predicate defeats it (function wrapped around the column, leading-wildcard LIKE, type mismatch causing implicit casts, or collation mismatch). The 'Extra' values to memorize: 'Using index' means covering index, no clustered-index lookup needed, a good sign; 'Using index condition' is index condition pushdown; 'Using where' just means post-filtering; 'Using temporary' and 'Using filesort' mean an internal temp table and a sort pass respectively, the usual suspects behind slow GROUP BY / ORDER BY queries. In MySQL 8.0 also mention EXPLAIN FORMAT=TREE, which shows the actual iterator plan including hash joins, and EXPLAIN ANALYZE, which executes the query and prints real timings per iterator; and note that rows/filtered are estimates from index dives and statistics, so they can be badly wrong on skewed data, which is the segue to histograms.

EXPLAIN
SELECT o.id, o.amount_paise
FROM orders o
WHERE o.user_id = 9182 AND o.status = 'paid'
ORDER BY o.created_at DESC LIMIT 20;

-- Typical bad output: type=ALL, key=NULL, rows=4M, Extra=Using filesort
-- Fix with a composite index matching filter + sort:
ALTER TABLE orders
  ADD KEY idx_user_status_created (user_id, status, created_at DESC);

-- Re-check with real execution timings (8.0.18+)
EXPLAIN ANALYZE
SELECT o.id, o.amount_paise
FROM orders o
WHERE o.user_id = 9182 AND o.status = 'paid'
ORDER BY o.created_at DESC LIMIT 20;

Answer

LIMIT 20 OFFSET 1000000 does not skip to row one million; MySQL must generate and discard the first million rows of the ordered result, then return twenty. If the ORDER BY is satisfied by an index, that is a million index entries walked and possibly a million clustered-index lookups; if it is not, it is a filesort of the whole result first. This is why page 1 of an admin panel is instant and page 50,000 times out, a bug practically every Indian startup has shipped at least once in an internal dashboard or a partner-facing export API.

Keyset pagination (also called seek method or cursor pagination) fixes it by remembering where the last page ended and seeking directly there with a range predicate: WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20. With a matching composite index this is a pure index range scan, O(page size) regardless of depth.

The id tiebreaker matters: created_at alone is not unique, so rows sharing a timestamp would be skipped or duplicated across pages. Trade-offs to state honestly: keyset cannot jump to an arbitrary page number, only next/previous, so UIs must switch from numbered pages to infinite scroll or next/prev; and the sort key must be immutable for stable results. Related points that earn credit: the row-constructor comparison (a, b) < (x, y) is optimizer-friendly in MySQL 8.0; SQL_CALC_FOUND_ROWS is deprecated since 8.0.17 and was always a performance trap because it forces the full scan you were avoiding, so run a separate approximate COUNT(*) or cache the total instead.

-- Slow at depth: scans and discards 1,000,000 rows
SELECT id, title, created_at FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 1000000;

-- Keyset: client sends the last row's (created_at, id) as the cursor
SELECT id, title, created_at FROM posts
WHERE (created_at, id) < ('2026-08-01 10:15:00', 991182)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Supporting index (descending index, MySQL 8.0)
ALTER TABLE posts ADD KEY idx_created_id (created_at DESC, id DESC);

Answer

A B+tree index stores values in sorted order, so LIKE can use it only when the pattern has a fixed prefix: LIKE 'raz%' becomes a range scan between 'raz' and 'raz\xff'. The moment the pattern starts with a wildcard, LIKE '%pay' or LIKE '%zerodha%', the sorted order is useless and MySQL falls back to scanning, either the full table or, if the query is covered, a full index scan. Case sensitivity is a subtlety: with the default case-insensitive collations (utf8mb4_0900_ai_ci), LIKE is case-insensitive and the index still works for prefix patterns; with a _bin or case-sensitive collation the comparison changes accordingly.

Wrapping the column in a function, LOWER(name) LIKE 'a%', kills index use unless you have a functional index (supported since MySQL 8.0.13) or a generated column indexed on that expression. When you genuinely need contains-style or word search, the honest answers are: InnoDB FULLTEXT indexes with MATCH ... AGAINST for word-boundary search (with the ngram parser for scripts without spaces), a trigram-style approach via generated columns, or shipping the search workload out to OpenSearch or Elasticsearch, which is what most Indian product companies do once search becomes a feature rather than a filter.

GoodSpace-scale job search, Zomato restaurant search, and similar workloads all live outside MySQL. One more production note: leading-wildcard LIKE inside an OR chain poisons the whole predicate, and interviewers like seeing the rewrite into a UNION of two indexable queries when only one branch has the wildcard.

-- Uses idx_name as a range scan
SELECT id FROM companies WHERE name LIKE 'Raz%';

-- Cannot use the B+tree: full scan
SELECT id FROM companies WHERE name LIKE '%pay%';

-- Functional index for case-normalised prefix search (8.0.13+)
ALTER TABLE companies ADD INDEX idx_lower_name ((LOWER(name)));
SELECT id FROM companies WHERE LOWER(name) LIKE 'raz%';

-- Word search with InnoDB full-text
ALTER TABLE companies ADD FULLTEXT INDEX ft_name_about (name, about);
SELECT id, MATCH(name, about) AGAINST('payments fintech') AS score
FROM companies
WHERE MATCH(name, about) AGAINST('payments fintech')
ORDER BY score DESC LIMIT 20;

Answer

'Using filesort' in EXPLAIN means MySQL could not read rows in the requested order from an index, so it collects the result and sorts it explicitly. Despite the name, it is not necessarily on disk: the sort happens in a per-session buffer sized by sort_buffer_size and spills to temporary files only when the result exceeds it. It is still a full extra pass, and on large results it dominates query time.

ORDER BY avoids the sort when an index delivers rows already in the right order: the ORDER BY columns must be a contiguous suffix of an index whose prefix columns are pinned by equality predicates. WHERE user_id = ? ORDER BY created_at DESC works perfectly with KEY (user_id, created_at); WHERE status IN ('a','b') ORDER BY created_at generally does not, because multiple ranges cannot be merged into one ordered stream (each IN value produces its own sorted run).

Mixed directions, ORDER BY a ASC, b DESC, could never use a plain index before MySQL 8.0; 8.0's true descending indexes (KEY (a ASC, b DESC)) solve exactly this, and this is a version fact interviewers specifically fish for, since 5.7 parsed DESC in index definitions but silently ignored it. Other details worth naming: LIMIT n with filesort uses a priority queue optimization so the whole result need not be sorted to find the top n; ORDER BY on expressions needs a functional index; and sorting by a secondary index still costs clustered-index lookups for uncovered columns, so a covering index that includes the selected columns can beat an index-ordered plan that ping-pongs into the table.

-- Sort-free: equality on prefix, order by suffix
ALTER TABLE orders ADD KEY idx_user_created (user_id, created_at);
EXPLAIN SELECT id FROM orders
WHERE user_id = 42 ORDER BY created_at DESC LIMIT 50;
-- Extra: (no filesort; backward index scan)

-- Mixed directions need a descending index (8.0)
ALTER TABLE leaderboard
  ADD KEY idx_score_time (score DESC, achieved_at ASC);
EXPLAIN SELECT player_id FROM leaderboard
ORDER BY score DESC, achieved_at ASC LIMIT 100;

Answer

UNION ALL simply concatenates the results of the two queries. UNION (shorthand for UNION DISTINCT) additionally deduplicates the combined result, which requires materializing all rows into an internal temporary table with a uniqueness check, historically via a temp table with a unique key. That dedup pass costs memory, CPU, and sometimes a spill to disk for large results, so the professional default is UNION ALL, opting into UNION only when duplicates are actually possible and actually unwanted.

Many candidates have this backwards because UNION reads more 'correct'; stating the cost model plainly is an easy signal. Semantics to know: column counts must match and types are unified per position; column names come from the first SELECT; and ORDER BY / LIMIT written after the last query applies to the whole union, so per-branch ordering requires wrapping each branch in a parenthesized subquery with its own LIMIT (a plain ORDER BY inside a union branch without LIMIT is meaningless and MySQL may ignore it). MySQL 8.0.19+ also allows LIMIT in the branches directly with parentheses.

Two practical patterns: the poor-man's FULL OUTER JOIN is a LEFT JOIN UNION ALL an anti-joined right side; and splitting an OR across differently-indexed columns into a UNION ALL of two indexable queries often beats the OR form, though 8.0's index_merge handles many such cases automatically. MySQL 8.0.31 additionally added INTERSECT and EXCEPT, finally matching standard SQL, worth mentioning to show you track releases. If asked about performance debugging: a slow UNION with 'Using temporary' in EXPLAIN and millions of rows is usually a missing ALL.

-- Combined activity feed, no dedup needed: use ALL
(SELECT id, 'order' AS kind, created_at FROM orders WHERE user_id = 7)
UNION ALL
(SELECT id, 'refund' AS kind, created_at FROM refunds WHERE user_id = 7)
ORDER BY created_at DESC
LIMIT 20;

-- OR-split rewrite when columns have separate indexes
SELECT * FROM tickets WHERE assignee_id = 12
UNION ALL
SELECT * FROM tickets WHERE reporter_id = 12 AND assignee_id <> 12;

Answer

A non-correlated subquery runs independently of the outer query, conceptually once: WHERE user_id IN (SELECT user_id FROM blocked_users). A correlated subquery references outer columns, so it conceptually re-executes per outer row: WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id). The folklore that 'subqueries are slow in MySQL' dates to 5.5, which really did re-execute IN subqueries per row.

Modern MySQL transforms most of these: IN/EXISTS become semi-joins (with strategies you can see in EXPLAIN like FirstMatch, LooseScan, Duplicate Weedout, or materialization), and since 8.0.17 NOT IN/NOT EXISTS become anti-joins. So the blanket 'always rewrite to JOIN' advice is outdated; the optimizer often produces the same plan. Where rewrites still matter: scalar subqueries in the SELECT list that fire per row (the classic N+1 inside a single query), which are usually better as a LEFT JOIN onto a pre-aggregated derived table; and IN with a huge literal list, which is better loaded into a temp table and joined.

Semantic differences also decide the rewrite direction: a JOIN can duplicate outer rows when the inner side matches multiple times (needing DISTINCT to compensate), while EXISTS never does, so EXISTS is the correct tool for 'has at least one' questions. Since 8.0.14 MySQL also supports LATERAL derived tables, which make 'top 3 rows per outer row' queries expressible efficiently. When performance-debugging, compare plans with EXPLAIN FORMAT=TREE rather than assuming; state that habit in the interview and you separate yourself from candidates reciting 2012-era blog posts.

-- Per-row scalar subquery (fires per user): the slow shape
SELECT u.id,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u WHERE u.city = 'Bengaluru';

-- Rewrite: aggregate once, join once
SELECT u.id, COALESCE(oc.cnt, 0) AS order_count
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
) oc ON oc.user_id = u.id
WHERE u.city = 'Bengaluru';

-- Top 2 latest orders per user with LATERAL (8.0.14+)
SELECT u.id, o.id AS order_id, o.created_at
FROM users u,
LATERAL (SELECT id, created_at FROM orders
         WHERE user_id = u.id ORDER BY created_at DESC LIMIT 2) o;

Answer

MySQL runs with autocommit=1 by default: every statement is its own transaction, committed the moment it succeeds. START TRANSACTION (or BEGIN) suspends autocommit until you COMMIT or ROLLBACK. Inside a transaction, InnoDB gives you atomicity (all-or-nothing via undo logs), durability at commit (redo log flushed according to innodb_flush_log_at_trx_commit), and isolation per the session's transaction_isolation, REPEATABLE READ by default.

Things candidates commonly get wrong: DDL statements (ALTER, CREATE, TRUNCATE, and also things like LOCK TABLES) cause an implicit commit of whatever transaction is open, so you cannot mix schema changes into an application transaction; and ROLLBACK does not reset AUTO_INCREMENT values already consumed. SAVEPOINT name / ROLLBACK TO SAVEPOINT name give partial rollback within a transaction, which ORMs use to implement nested 'transactions'. Long-running transactions are a production hazard worth volunteering: an idle session that opened a transaction hours ago pins the undo history (history list length grows, purge stalls, disk fills) and blocks online DDL; find them via information_schema.innodb_trx ordered by trx_started, and kill with KILL <processlist_id>.

On the application side, the pattern is: keep transactions short, take locks late, never do network calls (payment gateway, Kafka publish) inside an open transaction, and pair external side effects with an outbox table written in the same transaction. Also mention SELECT ... FOR UPDATE briefly as the way to lock rows you intend to modify, and that with connection pools you must ensure connections are returned in autocommit mode with no dangling transaction, or the next borrower inherits stale snapshots.

START TRANSACTION;
  UPDATE wallets SET balance_paise = balance_paise - 50000
  WHERE user_id = 11 AND balance_paise >= 50000;
  -- ensure exactly one row changed before crediting
  UPDATE wallets SET balance_paise = balance_paise + 50000
  WHERE user_id = 22;
  INSERT INTO transfer_outbox (from_id, to_id, amount_paise)
  VALUES (11, 22, 50000);
COMMIT;

-- Find transactions open longer than 60 seconds
SELECT trx_mysql_thread_id, trx_started, trx_rows_locked
FROM information_schema.innodb_trx
WHERE trx_started < NOW() - INTERVAL 60 SECOND;

Answer

sql_mode is a session and global variable holding a comma-separated list of flags that change how permissive the SQL layer is. The 8.0 default includes ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION. STRICT_TRANS_TABLES is the big one: with it, inserting an out-of-range number, an over-length string, or an invalid date into a transactional table is a hard error; without it, MySQL 'helpfully' truncates the string, clamps the number, inserts 0000-00-00, and merely emits a warning that nobody reads.

Entire generations of PHP-era applications were built against that lenient behavior, so lifting a 5.5/5.6-era codebase onto 8.0 surfaces a wave of errors like 1265 (data truncated), 1292 (incorrect datetime value), and 1366 (incorrect string value). The wrong fix is turning strict mode off globally; the defensible temporary fix is relaxing sql_mode for the specific legacy connection while the code is corrected, since the variable is settable per session. Other flags worth knowing cold: ANSI_QUOTES makes double quotes mean identifiers instead of strings (breaking string literals in old code), PIPES_AS_CONCAT makes || concatenation, and NO_BACKSLASH_ESCAPES changes escaping rules; all three commonly appear when porting between MySQL and PostgreSQL.

Also know what disappeared: NO_AUTO_CREATE_USER was removed in 8.0 (GRANT no longer creates users at all, you must CREATE USER first), and a config file listing removed modes will stop the server from booting, a classic upgrade-day incident. Interviewers use this question to check whether you have actually operated MySQL across versions rather than only queried it.

SELECT @@sql_mode;

-- Strict mode: hard error
CREATE TABLE t (name VARCHAR(3), qty TINYINT);
INSERT INTO t VALUES ('Bengaluru', 999);
-- ERROR 1406 (22001): Data too long for column 'name'

-- Legacy-compat session (temporary, during migration only)
SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
INSERT INTO t VALUES ('Bengaluru', 999);  -- succeeds with warnings
SHOW WARNINGS;  -- 1265 Data truncated, 1264 Out of range
SELECT * FROM t;  -- ('Ben', 127)

Answer

A view is a stored SELECT that behaves like a virtual table. MySQL has no native materialized views (unlike PostgreSQL), so every reference re-evaluates the definition, with one of two algorithms: MERGE, where the view's SQL is folded into the outer query and indexes on base tables remain usable, and TEMPTABLE, where MySQL materializes the view into an internal temporary table first. MERGE is what you want; MySQL is forced into TEMPTABLE when the view contains GROUP BY, DISTINCT, aggregates, UNION, LIMIT, or window functions, and a materialized view result has no indexes, so an outer WHERE on it scans the temp table.

That is the performance catch: stacking views on aggregate views multiplies temp tables and is a classic cause of dashboard queries that take minutes. You can request an algorithm with CREATE ALGORITHM=MERGE VIEW, but MySQL silently falls back to TEMPTABLE when merging is impossible. A view is updatable (INSERT/UPDATE/DELETE pass through) only when it maps rows one-to-one to a single base table: no aggregates, no DISTINCT, no GROUP BY, no unions.

WITH CHECK OPTION makes writes through the view respect its WHERE clause, rejecting rows the view could not see, useful for tenant-scoped views. Practical uses that come up in interviews: a stable read model over an evolving schema, column-level security by granting SELECT on the view but not the base table, and hiding soft-delete filters (WHERE deleted_at IS NULL) uniformly. For genuine materialization, the standard MySQL answer is a summary table refreshed by triggers, cron, or stream processing, and being explicit that this is an application-level pattern scores points.

-- Tenant-scoped, updatable view with a write guard
CREATE ALGORITHM=MERGE VIEW active_jobs AS
SELECT id, tenant_id, title, status, created_at
FROM jobs
WHERE deleted_at IS NULL
WITH CHECK OPTION;

-- Forced TEMPTABLE (aggregate): outer WHERE cannot use base indexes
CREATE VIEW city_stats AS
SELECT city, COUNT(*) AS users FROM users GROUP BY city;

EXPLAIN SELECT * FROM city_stats WHERE city = 'Pune';
-- shows derived table materialization

Answer

mysqldump produces a SQL script that recreates schemas and data. The flags that separate a safe production backup from a table-locking outage: --single-transaction takes a consistent InnoDB snapshot inside one REPEATABLE READ transaction instead of locking tables; --routines and --triggers include stored programs (skipped by default); --set-gtid-purged controls whether GTID state is embedded (critical when the dump seeds a replica, usually OFF for a plain restore copy); --no-tablespaces avoids a privilege error on managed platforms like RDS; and --column-statistics=0 works around dumping from hosts where the client probes a missing statistics table. Never use --lock-all-tables against a live OLTP primary.

Know the limits honestly: mysqldump is single-threaded, and restore replays SQL row by row, so a few hundred GB can take many hours to restore; backup size is not the problem, restore time is, and interviewers probing RTO want you to say that. The modern replacements: MySQL Shell's utility functions util.dumpInstance() / util.dumpSchemas() and util.loadDump(), which are parallel, chunked, compressed (zstd), and can stream to S3-compatible or OCI object storage, with load parallelism per table; these effectively deprecate mysqlpump (officially deprecated in 8.0.34). For physical backups of multi-terabyte systems, Percona XtraBackup or the InnoDB clone plugin (8.0.17+) copy data files while tracking redo, giving restores at disk-copy speed. A complete answer ties it together: logical dumps for portability, schema archaeology, and small datasets; physical backups plus binlogs for point-in-time recovery at scale; and an untested backup is not a backup, so schedule restore drills.

# Consistent non-blocking dump of one schema, with programs
mysqldump --single-transaction --routines --triggers \
  --set-gtid-purged=OFF --no-tablespaces \
  -h prod-replica -u backup_user -p goodspace_db \
  | gzip > goodspace_db_$(date +%F).sql.gz

# Modern parallel dump/load with MySQL Shell
mysqlsh backup_user@prod-replica -- util dump-instance /backups/full \
  --threads=8 --compression=zstd
mysqlsh admin@new-host -- util load-dump /backups/full \
  --threads=8 --updateGtidSet=append

Answer

A MySQL account is 'user'@'host': app@'10.0.%' and app@'localhost' are different accounts, and the host part is the first thing to check when you hit error 1045 (access denied) or 1130 (host not allowed). MySQL 8.0 changed the default authentication plugin from mysql_native_password to caching_sha2_password, which broke a generation of old connectors and PHP clients at upgrade time; the fixes are upgrading the client library or, as a last resort, creating the user with the legacy plugin. Note the direction of travel: mysql_native_password is deprecated and disabled by default in MySQL 8.4, and removed in the 9.x series, so 'just switch back' is no longer a durable answer.

Privileges are layered: global (ON *.*), schema (ON db.*), table, column, and routine level, granted with GRANT and inspected with SHOW GRANTS FOR. In 8.0, GRANT no longer creates accounts implicitly; you must CREATE USER first. Version 8.0 also added roles: CREATE ROLE app_read; GRANT SELECT ON app.* TO app_read; GRANT app_read TO 'reporting'@'%'; then the role must be activated via SET DEFAULT ROLE or activate_all_roles_on_login=ON, an activation step that trips people up because granted-but-inactive roles look like missing permissions. Production hygiene interviewers like to hear: separate accounts for app writes, app reads (pointed at replicas), migrations (DDL rights), and humans (with REQUIRE SSL); no GRANT ALL ON *.* for applications; use dual passwords (8.0.14+) for zero-downtime credential rotation; and connection limits per account via MAX_USER_CONNECTIONS to stop one bad deploy from exhausting the server.

-- Least-privilege application accounts
CREATE USER 'app_rw'@'10.0.%' IDENTIFIED BY '...' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE, DELETE ON goodspace.* TO 'app_rw'@'10.0.%';

CREATE ROLE reporting_ro;
GRANT SELECT ON goodspace.* TO reporting_ro;
CREATE USER 'analyst'@'%' IDENTIFIED BY '...';
GRANT reporting_ro TO 'analyst'@'%';
SET DEFAULT ROLE reporting_ro TO 'analyst'@'%';

-- Zero-downtime password rotation (8.0.14+)
ALTER USER 'app_rw'@'10.0.%' IDENTIFIED BY 'new' RETAIN CURRENT PASSWORD;
-- deploy new secret everywhere, then:
ALTER USER 'app_rw'@'10.0.%' DISCARD OLD PASSWORD;

Answer

MySQL's integer family: TINYINT (1 byte), SMALLINT (2), MEDIUMINT (3), INT (4), BIGINT (8). Signed INT tops out at 2,147,483,647; UNSIGNED doubles the positive range by dropping negatives. The interview-relevant judgment call is primary keys on growth tables: an INT UNSIGNED PK caps at about 4.29 billion, and consumer-scale Indian apps genuinely exhaust it (an events or messages table writing 10 million rows a day crosses 4.29 billion in under a year and a half).

Running out shows up as error 1062 duplicate-entry storms or 167 out-of-range errors, and the emergency ALTER to BIGINT on a multi-hundred-GB table is a painful online-DDL or gh-ost project under incident pressure, so the standard 2026 guidance is BIGINT UNSIGNED for every surrogate key; the extra 4 bytes per row is cheap insurance. Now the version trivia that interviewers use to date your knowledge: the number in INT(11) was never a size or range, it was 'display width', a hint used only with ZEROFILL padding; both display width and ZEROFILL are deprecated since 8.0.17, and modern SHOW CREATE TABLE renders plain INT. TINYINT(1) survives as a convention because connectors treat it as boolean; BOOL/BOOLEAN are aliases for TINYINT(1). Related type guidance worth appending: money should be DECIMAL(p,s) or integer paise, never FLOAT/DOUBLE (binary floats cannot represent 0.1 exactly, and equality comparisons break); and BIGINT arithmetic in the application must account for JavaScript's Number losing precision past 2^53, which is why Node.js ORMs return BIGINT ids as strings.

CREATE TABLE events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  kind TINYINT UNSIGNED NOT NULL,          -- enum-like small domain
  is_test TINYINT(1) NOT NULL DEFAULT 0,   -- boolean convention
  amount_paise BIGINT UNSIGNED NULL,       -- money as integer paise
  price DECIMAL(12,2) NULL                 -- or exact decimal
) ENGINE=InnoDB;

-- How close is an INT key to exhaustion?
SELECT table_name, auto_increment,
       ROUND(auto_increment / 4294967295 * 100, 1) AS pct_of_uint_max
FROM information_schema.tables
WHERE table_schema = DATABASE() AND auto_increment IS NOT NULL
ORDER BY auto_increment DESC;

Answer

The query cache stored full result sets keyed by exact query text and returned them without re-execution. MySQL 8.0 removed it entirely (it was deprecated in 5.7.20), and knowing why demonstrates real performance understanding. First, invalidation was brutally coarse: any write to a table invalidated every cached result touching that table, so on OLTP workloads with steady writes the cache thrashed, all cost and no hits.

Second, it was protected by a single global mutex, which made it a scalability bottleneck on multi-core machines; heavily loaded servers often got faster by setting query_cache_type=0. Third, matching was byte-exact: a differing comment, whitespace, or literal missed the cache. The modern layered answer interviewers expect: (1) InnoDB buffer pool, which caches data and index pages (not results) and is the primary knob, size it to hold the hot working set; (2) application-side caching in Redis or Memcached with explicit keys and TTLs, where the application owns invalidation semantics, the pattern behind virtually every Indian consumer app's read path (cache-aside with a TTL plus event-driven invalidation); (3) ProxySQL query caching at the SQL layer with per-rule TTLs when you cannot change application code, accepting bounded staleness; (4) derived or summary tables for expensive aggregates. Also name the failure modes of app-level caching since interviewers push there: stampedes on hot key expiry (solved with jittered TTLs, locks, or early recomputation), stale-after-write (solved by deleting keys in the write path or via binlog-driven invalidation with Debezium-style CDC), and the fact that caching does not fix a bad query plan, it hides it until the cache is cold during an incident.

-- 8.0: these variables no longer exist
SHOW VARIABLES LIKE 'query_cache%';  -- empty result on 8.0+

-- What replaces it, layer by layer:
-- 1) Buffer pool sizing (my.cnf)
--    innodb_buffer_pool_size = 24G   # ~70% of a dedicated DB host

-- 2) App layer (pseudocode, cache-aside)
--    val = redis.get(key)
--    if val == null:
--        val = sql('SELECT ... WHERE id = ?', id)
--        redis.setex(key, 300 + jitter(), serialize(val))

-- 3) Summary table for a hot aggregate
CREATE TABLE daily_city_revenue (
  day DATE NOT NULL, city VARCHAR(80) NOT NULL,
  revenue_paise BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (day, city)
);

Answer

InnoDB is a page-oriented engine: all data and index content lives in 16 KB pages. The buffer pool is the in-memory cache of those pages, managed with a midpoint-insertion LRU (new pages enter 3/8 from the tail, promoted to the head only on re-access after a delay, which protects the hot set from one big table scan flushing everything). Writes modify pages in the buffer pool ('dirty' pages) and are made durable not by writing the page, but by appending compact change records to the redo log (write-ahead logging); at commit, only the redo log must be flushed, per innodb_flush_log_at_trx_commit.

Dirty pages are flushed to the tablespaces later, in the background, driven by checkpointing so the redo log can be reclaimed. Undo logs store the previous versions of modified rows; they serve both rollback and MVCC consistent reads, and are purged once no transaction can need them (a long-open transaction blocks purge, growing history list length). The change buffer batches modifications to non-unique secondary index pages that are not currently in memory, merging them when the page is eventually read, an insert-heavy workload optimization.

The doublewrite buffer defends against torn pages: because a 16 KB page write is not atomic on most storage, InnoDB first writes pages to a doublewrite area, then to their final location; on crash recovery a half-written page can be recovered from the doublewrite copy, after which redo is applied. In 8.0.20 the doublewrite files moved out of the system tablespace into dedicated files. Tie it together for the interviewer: commit latency depends on redo flushing, read latency on buffer pool hit rate, and crash recovery replays redo from the last checkpoint then rolls back uncommitted work via undo.

-- Health snapshot of the pieces
SHOW ENGINE INNODB STATUS\G   -- history list length, log sequence numbers

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
  'Innodb_buffer_pool_read_requests',  -- logical reads
  'Innodb_buffer_pool_reads',          -- misses that hit disk
  'Innodb_buffer_pool_pages_dirty',
  'Innodb_os_log_written'
);

-- Key sizing knobs (my.cnf)
-- innodb_buffer_pool_size = 24G
-- innodb_redo_log_capacity = 8G        # 8.0.30+
-- innodb_flush_log_at_trx_commit = 1   # full durability

Answer

Because the table is the clustered index, every insert physically lands at its primary key position. Monotonic keys (AUTO_INCREMENT, or time-ordered IDs) always append at the rightmost leaf page: pages fill sequentially, page splits are rare and cheap, and the working set of pages being written stays small and hot in the buffer pool. Random keys like UUIDv4 stored as strings scatter inserts uniformly across the whole B+tree: nearly every insert touches a random page, causing page splits that leave pages half full (index bloat commonly 30-50%), and once the index exceeds the buffer pool, each insert becomes a random disk read-modify-write.

Insert throughput can fall by an order of magnitude on large tables, and the fat 36-char key is duplicated into every secondary index, multiplying the damage. The mitigations, in the order worth presenting: prefer an internal BIGINT AUTO_INCREMENT PK and keep the UUID as a UNIQUE secondary key for external exposure (also avoids leaking row counts through sequential public IDs); if the UUID must be the PK, use a time-ordered variant, UUIDv7, or MySQL's UUID_TO_BIN(uuid, 1) which swaps the timestamp bytes of a v1 UUID to make it roughly sequential, storing it as BINARY(16) rather than CHAR(36) (16 bytes vs 36, and byte comparisons instead of collation-aware string comparisons). MySQL 8.0 provides UUID_TO_BIN/BIN_TO_UUID natively, and 9.x added IS_UUID improvements and UUIDv7 generation in recent releases (verify availability on your version rather than assuming). A senior-level footnote: monotonic PKs concentrate insert contention on the last page, which under extreme write rates shows as index-lock waits; that is the trade-off direction, though for almost all workloads append-only wins decisively.

-- Recommended shape: internal PK + external UUID
CREATE TABLE api_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  public_id BINARY(16) NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  UNIQUE KEY uk_public_id (public_id),
  KEY idx_user (user_id)
) ENGINE=InnoDB;

INSERT INTO api_tokens (public_id, user_id)
VALUES (UUID_TO_BIN(UUID(), 1), 42);   -- swap-flag reorders time bytes

SELECT BIN_TO_UUID(public_id, 1) AS public_id, user_id
FROM api_tokens
WHERE public_id = UUID_TO_BIN('f4b8b1c2-5e1d-11ee-8c99-0242ac120002', 1);

Answer

A composite index KEY (a, b, c) is a single B+tree sorted by a, then b within equal a, then c within equal (a, b). The optimizer can use it only for predicates that constrain a leftmost prefix: (a), (a, b), (a, b, c). A query filtering only on b or only on c cannot seek into the tree, because entries with the same b value are scattered across all values of a.

The second, subtler part of the rule: a range predicate stops index usage for columns to its right. WHERE a = 1 AND b > 5 AND c = 9 uses the index for a (equality) and b (range), but c is only checked as a filter (or via index condition pushdown), not used to narrow the seek. Hence the ordering heuristic: equality columns first, then the range or sort column last; among equality columns, order by what your queries share and, secondarily, by selectivity.

Concrete example: for WHERE tenant_id = ? AND status = ? AND created_at > ?

ORDER BY created_at, the right index is (tenant_id, status, created_at); indexing (created_at, tenant_id, status) would make the range column first and waste the rest. Consequences to volunteer: a separate index on (a) is redundant when (a, b) exists (drop it, saving write cost); ORDER BY can use the index only if its columns continue the prefix after equalities; and MySQL 8.0's skip scan optimization can sometimes use (a, b) for a query on b alone when a has very few distinct values, worth knowing but not relying on. Verify everything with EXPLAIN: key_len tells you exactly how many bytes, and therefore how many leading columns, are actually used for the seek.

ALTER TABLE orders
  ADD KEY idx_tenant_status_created (tenant_id, status, created_at);

-- Full prefix: seek on all three
EXPLAIN SELECT id FROM orders
WHERE tenant_id = 4 AND status = 'paid'
  AND created_at >= '2026-08-01';

-- Only 'status': cannot use this index (no leftmost column)
EXPLAIN SELECT id FROM orders WHERE status = 'paid';

-- Range in the middle: created_at filtered, not sought,
-- if the index were (tenant_id, created_at, status)
-- key_len shows bytes used; compare against column sizes

Answer

A covering index contains every column a query needs, filter, sort, and select list, so InnoDB answers the query entirely from the secondary index B+tree and never visits the clustered index. That second lookup (secondary index leaf gives the PK, then a second B+tree descent fetches the full row) is the hidden cost of secondary index reads; on a query returning thousands of rows it means thousands of extra random page accesses. Eliminating it routinely turns a 2-second query into a 20 ms one.

Remember that InnoDB secondary indexes implicitly include the primary key columns, so KEY (user_id, created_at) actually covers SELECT id FROM orders WHERE user_id = ? ORDER BY created_at without listing id. In EXPLAIN, covering shows as 'Using index' in the Extra column, which is entirely different from 'Using index condition': the latter is index condition pushdown (ICP), where predicates on non-sought index columns are evaluated inside the storage engine before fetching the row, reducing row fetches but not eliminating them.

'Using where' alone means the server filters after the engine returned rows. The design tension to articulate: wider indexes cover more queries but cost more on every write (each secondary index is a separate B+tree to maintain) and consume buffer pool; a common discipline is to design covering indexes only for the handful of hottest read paths, verified by slow-log analysis, rather than speculatively appending columns. Also mention that SELECT * defeats covering by construction, one more concrete reason beyond style to select explicit columns, and that in 8.0 you cannot add non-key 'included' columns like some databases; the columns must be real key parts, so they also inflate the sort order definition.

-- Query: last 50 order amounts for a user
SELECT id, amount_paise, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 50;

-- Non-covering index: seek + 50 row lookups
ALTER TABLE orders ADD KEY idx_user_created (user_id, created_at);

-- Covering index: everything from the index B+tree
ALTER TABLE orders
  DROP KEY idx_user_created,
  ADD KEY idx_user_created_amount (user_id, created_at, amount_paise);
-- EXPLAIN Extra: 'Using index' (id comes free: PK is embedded)

Answer

EXPLAIN prints the optimizer's plan with estimated costs and row counts; it does not run the query. EXPLAIN ANALYZE (MySQL 8.0.18+) executes the query fully, discards the result set, and prints the iterator tree in FORMAT=TREE annotated with measured numbers per node: actual time to first row and to last row (in milliseconds), actual rows produced, and loops (how many times the iterator ran, crucial under nested loop joins where the inner side executes once per outer row). Reading it is a diff exercise: find the node where actual rows massively diverges from estimated rows (statistics problem, consider ANALYZE TABLE or histograms), or where actual time concentrates.

A classic reading: a 'Table scan on t (actual time=0.1..4200 rows=8M loops=1)' feeding a filter that outputs 12 rows tells you an index on the filter predicate would eliminate almost all work. Loops matter as much as rows: an inner index lookup at 0.05 ms looks innocent until loops=2M shows it ran two million times, 100 seconds total. Caveats that show operational maturity: because it really executes, running EXPLAIN ANALYZE on a heavy UPDATE is dangerous (8.0 supports it for SELECT; for DML reproduce as a SELECT); it includes no network or client time; timings on a cold buffer pool differ wildly from warm, so run twice and compare; and mutating statements aside, it still takes the same locks a SELECT would take under the current isolation level. Also mention EXPLAIN FORMAT=JSON, which exposes cost numbers, and that since 8.0.16 EXPLAIN works on connections ('EXPLAIN FOR CONNECTION <id>') letting you inspect the plan of a query currently stuck in processlist, a genuinely useful incident tool.

EXPLAIN ANALYZE
SELECT u.city, COUNT(*)
FROM orders o JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2026-08-01'
GROUP BY u.city;

/* Sample output shape:
-> Table scan on <temporary>  (actual time=812..815 rows=42 loops=1)
  -> Aggregate using temporary table
    -> Nested loop inner join  (cost=9021 rows=8110)
         (actual time=0.3..790 rows=1.2e6 loops=1)
      -> Filter: (o.created_at >= ...)  <- big gap est vs actual? stats!
      -> Single-row index lookup on u using PRIMARY
           (actual time=0.004..0.004 rows=1 loops=1.2e6)  <- loops!
*/

-- Inspect a stuck query's plan live
SHOW PROCESSLIST;
EXPLAIN FOR CONNECTION 8123;

Answer

InnoDB supports READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (the default, unusual, since PostgreSQL, Oracle, and SQL Server default to READ COMMITTED), and SERIALIZABLE. MVCC is the machinery: every row carries the transaction ID that wrote it plus a roll pointer into undo logs, and a consistent read builds the row version visible to the reader's snapshot ('read view') by walking undo chains, so plain SELECTs never block on writers. Under READ COMMITTED a fresh snapshot is taken per statement, so two identical SELECTs in one transaction can see different data (non-repeatable reads).

Under REPEATABLE READ one snapshot is taken at the first read and reused for the whole transaction, so reads are stable; InnoDB additionally suppresses most phantoms for plain reads via that snapshot, and for locking reads (SELECT ... FOR UPDATE, UPDATE, DELETE) it uses next-key locks to physically prevent phantoms, which is stronger than the SQL-standard definition of RR but still not full serializability. The trap interviewers set: locking reads and writes always act on the latest committed row version, not your snapshot ('semi-consistent' current reads), so you can SELECT a row that shows the old value, then UPDATE it and affect the new value, the classic lost-update misunderstanding; the fix is reading with FOR UPDATE, or optimistic version-column checks.

Operational trade-offs: REPEATABLE READ's gap locking increases deadlocks on insert-heavy contended ranges, so many high-throughput shops deliberately run READ COMMITTED (also required historically with statement-format binlogs the other way around: READ COMMITTED requires row-based binlogs). Long RR transactions also pin their snapshot, forcing undo retention and ballooning history list length. SERIALIZABLE simply converts plain reads into shared locking reads, and is almost never used in MySQL practice.

-- Session-level switch
SET SESSION transaction_isolation = 'READ-COMMITTED';
SELECT @@transaction_isolation;

-- Lost-update demonstration under REPEATABLE READ
-- T1:
START TRANSACTION;
SELECT balance_paise FROM wallets WHERE user_id = 7;  -- sees 1000
-- T2 commits: UPDATE wallets SET balance_paise = 0 WHERE user_id = 7;
-- T1 (snapshot still shows 1000, but UPDATE acts on current row):
UPDATE wallets SET balance_paise = balance_paise - 100
WHERE user_id = 7;   -- balance becomes -100 relative to T2's 0
COMMIT;

-- Correct: lock the row when read-for-modify
START TRANSACTION;
SELECT balance_paise FROM wallets WHERE user_id = 7 FOR UPDATE;
-- decide, then update, then COMMIT

Answer

SELECT ... FOR UPDATE takes exclusive (X) locks on the rows it reads, blocking other locking reads and writes until commit; it is the pessimistic tool for read-then-modify flows like wallet debits and seat booking. SELECT ...

FOR SHARE (the 8.0 spelling of the old LOCK IN SHARE MODE) takes shared (S) locks: other transactions can also read-share but none can modify, useful for enforcing referential integrity manually (lock the parent while inserting a child). Both are 'current reads': they bypass the MVCC snapshot and see the latest committed data. By default a conflicting lock means waiting up to innodb_lock_wait_timeout (default 50 seconds) and then error 1205.

MySQL 8.0 added two modifiers that changed queue design in MySQL forever. NOWAIT returns error 3572 immediately instead of waiting, right for interactive flows where a busy row should surface as 'try again' rather than a hung request. SKIP LOCKED silently skips rows that are already locked and returns the rest, which is precisely the primitive a database-backed job queue needs: N workers each run SELECT ...

WHERE status='pending' ORDER BY id LIMIT 10 FOR UPDATE SKIP LOCKED and receive disjoint batches with no coordinator, no advisory locks, no double-processing. Before 8.0 people simulated this with UPDATE-claim patterns and suffered contention storms. Caveats worth stating: SKIP LOCKED returns a non-deterministic subset, so never use it in accounting-style reads; both modifiers only apply to row locks, not metadata locks; locking reads require an index to lock precisely, otherwise InnoDB locks every scanned row (a missing index turns FOR UPDATE into a table-wide lockfest); and under REPEATABLE READ locking reads also take gap locks, so a queue table is usually happier under READ COMMITTED.

-- Worker claiming jobs: disjoint batches per worker, no coordinator
START TRANSACTION;
SELECT id, payload FROM jobs
WHERE status = 'pending' AND run_after <= NOW()
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;

UPDATE jobs SET status = 'running', claimed_by = 'worker-7'
WHERE id IN (/* ids from above */);
COMMIT;

-- Interactive flow: fail fast instead of queueing 50s
SELECT * FROM seats
WHERE show_id = 991 AND seat_no = 'F12'
FOR UPDATE NOWAIT;  -- ERROR 3572 if someone holds it

Answer

InnoDB row locks come in three flavors, all taken on index entries, not on 'rows' in the abstract. A record lock locks a single index entry. A gap lock locks the open interval between two index entries (or before the first / after the last), preventing inserts into that range but locking no existing row.

A next-key lock is the combination: the record plus the gap before it, and it is what REPEATABLE READ uses for locking reads to prevent phantoms: if you run SELECT ... WHERE k BETWEEN 10 AND 20 FOR UPDATE, InnoDB next-key locks the scanned range so no new k=15 can appear before you commit. The developer-surprising part: gap locks are shared-compatible with each other.

Two transactions can both hold gap locks on the same gap, and each then blocks the other's INSERT into that gap (an insert needs an 'insert intention' lock that conflicts with others' gap locks), producing a deadlock in code that looks trivially safe: two sessions each SELECT ... FOR UPDATE a non-existent row ('check then insert') and then INSERT it. Error 1213, and neither transaction touched a row the other had.

This is the single most common 'mystery deadlock' in interview war stories, and the mitigations are the interesting part: use INSERT ... ON DUPLICATE KEY UPDATE or INSERT IGNORE instead of check-then-insert; rely on a unique constraint plus handling error 1062; or run the workload under READ COMMITTED, which disables gap locking for ordinary operations (except for foreign-key and duplicate-key checks). Diagnostics: the two most recent deadlock graphs appear in SHOW ENGINE INNODB STATUS; performance_schema.data_locks (8.0, replacing information_schema.innodb_locks) shows live lock holdings including lock_mode values like 'X,GAP' and 'X,INSERT_INTENTION'.

-- Deadlock-prone (both sessions, key 500 does not exist):
START TRANSACTION;
SELECT * FROM coupons WHERE code_hash = 500 FOR UPDATE; -- gap lock
-- both sessions reach here, then both:
INSERT INTO coupons (code_hash, used) VALUES (500, 1);   -- deadlock 1213

-- Safe rewrite: single atomic statement
INSERT INTO coupons (code_hash, used) VALUES (500, 1)
ON DUPLICATE KEY UPDATE used = used + 1;

-- Inspect live locks (8.0)
SELECT engine_transaction_id, object_name, index_name,
       lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks;

Answer

First, stop treating deadlocks as defects to eliminate entirely: InnoDB detects the cycle in milliseconds, rolls back the transaction with the fewest undo bytes, and returns error 1213; the application must retry the whole transaction (not just the failed statement, the transaction was rolled back). A missing retry wrapper is the most common real bug. Diagnosis: SHOW ENGINE INNODB STATUS prints the LATEST DETECTED DEADLOCK section with both transactions' last statements, the locks held and waited for, and which was rolled back.

Since only the latest graph is kept, enable innodb_print_all_deadlocks=ON to log every occurrence to the error log for pattern analysis. Read the graph for the two lock types involved: 'lock_mode X locks rec but not gap' means record contention (usually two flows updating the same rows in opposite orders), while 'locks gap before rec' or 'insert intention' points to the check-then-insert gap-lock pattern. Remediations by cause: opposite-order updates are fixed by imposing a global ordering (always lock accounts by ascending id: sort the ids in the application before the FOR UPDATE loop, or use one statement with IN + ORDER BY); long transactions holding many locks are fixed by shrinking transaction scope and moving reads outside; gap-lock deadlocks by upsert forms or READ COMMITTED; hot single-row counters by batching increments or moving the counter to Redis with periodic flush.

Also check indexes: an UPDATE whose WHERE clause is unindexed locks every scanned row, massively widening the conflict surface, so a deadlock spike after a query-plan change is often really an indexing regression. Finally, measure: performance_schema and SignOz/PMM-style dashboards should track deadlocks per minute so a code deploy that triples them gets caught in canary, not at 2 AM.

-- Capture every deadlock in the error log
SET GLOBAL innodb_print_all_deadlocks = ON;

-- Consistent lock ordering: sort ids before locking
SELECT * FROM wallets
WHERE user_id IN (22, 11)   -- app sorts to (11, 22) first
ORDER BY user_id
FOR UPDATE;

-- Application retry wrapper (pseudocode)
-- for attempt in 1..3:
--   try: run_transaction()
--   catch SQLError 1213, 1205:
--     sleep(random_jitter(attempt)); continue
--   break

Answer

Three layers, and strong candidates name all three. The slow query log: enable with slow_query_log=ON, set long_query_time (in seconds, supports fractions; 0.1 is a sane starting bar for OLTP, and it can be set dynamically), and consider log_queries_not_using_indexes=ON (noisy, pair with min_examined_row_limit to suppress trivial scans). Log to FILE and rotate.

The raw log is unreadable at volume, so aggregate with pt-query-digest from Percona Toolkit, which normalizes queries into fingerprints (literals stripped), ranks them by total time consumed, and prints per-fingerprint distributions of latency, rows examined, and rows sent. The killer metric is rows_examined versus rows_sent: a query examining 500,000 rows to return 20 is an indexing bug regardless of how fast it currently looks. Total-time ranking also corrects the intuition that the slowest single query matters most; a 30 ms query running 400 times per second usually dominates a 9-second nightly report.

The in-server alternative that needs no log files: performance_schema's events_statements_summary_by_digest table keeps normalized digests with counts, total/max latency, rows examined, tmp tables, and full-scan flags since last reset, and the sys schema wraps it readably as sys.statement_analysis (plus sys.statements_with_full_table_scans and friends). This is what RDS Performance Insights and PMM build on. Operationally: digest tables are lossy ring buffers (performance_schema_digests_size), so snapshot and diff them; TRUNCATE the summary table to start a measurement window around a deploy.

On managed AWS RDS, note that the slow log goes to CloudWatch or a table depending on log_output. Close with the workflow: find the top fingerprint, EXPLAIN ANALYZE a representative literal query, fix, and confirm the digest's mean drops in the next window.

-- Enable and tune the slow log at runtime
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.1;
SET GLOBAL log_output = 'FILE';

# Aggregate a day of slow log
pt-query-digest /var/lib/mysql/host-slow.log > digest.txt

-- No-log-file alternative: top statements by total latency
SELECT query, exec_count,
       sys.format_time(total_latency) AS total,
       rows_examined_avg, full_scan
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

Answer

MySQL's native JSON type (5.7+) validates documents on write and stores them in a binary format that permits direct path access without reparsing. You read paths with JSON_EXTRACT(doc, '$.a.b') or the -> operator, and ->> additionally unquotes (shorthand for JSON_UNQUOTE(JSON_EXTRACT(...))). Mutation functions JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE, and JSON_MERGE_PATCH edit in place, and 8.0 added a genuinely important write optimization: partial in-place updates of JSON columns can be logged compactly with binlog_row_value_options=PARTIAL_JSON, instead of writing the full document into the binlog on every touch.

The core limitation: you cannot index a JSON column directly. Two mechanisms fix that. First, generated columns: add a STORED or VIRTUAL column extracting the path, then index it; queries filtering on the same expression use the index (MySQL matches the expression automatically), and 8.0.13+ functional indexes let you skip the visible column entirely, though under the hood they are hidden virtual columns.

Use CAST in the expression to pin a type and remember collation subtleties when comparing extracted strings. Second, multi-valued indexes (8.0.17+): an index on CAST(doc->'$.tags' AS UNSIGNED ARRAY) indexes every array element, and queries with MEMBER OF, JSON_CONTAINS, or JSON_OVERLAPS can use it, MySQL's answer to 'find rows whose tags array contains X' without a full scan. State the design philosophy interviewers want: JSON is for genuinely variable attributes (marketplace product specs, webhook payloads, feature flags per tenant); anything you filter, join, or aggregate on regularly should be a real column, extracted at write time. All-JSON schemas throw away types, constraints, and statistics, and JSON documents inflate row size, pushing tables out of the buffer pool sooner.

CREATE TABLE products (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  specs JSON NOT NULL,
  -- extracted generated column, indexable
  brand VARCHAR(64)
    GENERATED ALWAYS AS (specs->>'$.brand') STORED,
  KEY idx_brand (brand),
  -- multi-valued index over a JSON array (8.0.17+)
  KEY idx_tags ((CAST(specs->'$.tags' AS CHAR(32) ARRAY)))
) ENGINE=InnoDB;

-- Uses idx_brand
SELECT id FROM products WHERE specs->>'$.brand' = 'boAt';

-- Uses idx_tags
SELECT id FROM products
WHERE 'wireless' MEMBER OF (specs->'$.tags');

Answer

Common Table Expressions arrived in MySQL 8.0 (their absence was a top reason teams chose PostgreSQL through the 5.x era). A non-recursive CTE, WITH name AS (SELECT ...), names a subquery for reuse and readability; referencing it multiple times in the main query avoids pasting the same derived table twice, and the optimizer decides between merging it into the outer query or materializing it once (you can force the choice with the /*+ MERGE() */ or /*+ NO_MERGE() */ hints). A recursive CTE, WITH RECURSIVE, is the real capability: it has an anchor member producing seed rows, UNION ALL, and a recursive member that references the CTE itself, iterating until no new rows appear.

That expresses org charts, category trees, threaded comments, BOM explosions, and sequence generation, all previously requiring application loops or nested-set schema contortions. The traversal pattern to be able to write on a whiteboard: anchor selects the root (manager_id IS NULL), recursive part joins employees to the CTE on manager_id, accumulating depth and a path string. Guard rails: cte_max_recursion_depth (default 1000) stops runaway recursion with error 3636, and cycles in the data (a manager loop) will hit it, so defensive versions carry the visited path and filter with FIND_IN_SET or LOCATE to break cycles; MySQL has no CYCLE clause, unlike newer PostgreSQL.

Also useful: recursive CTEs as inline number and date generators (a calendar of the last 90 days to LEFT JOIN against sparse data so missing days show as zeroes), which replaces permanent numbers tables. Mention the limitation that the recursive member cannot use aggregation, GROUP BY, ORDER BY, or LIMIT of the CTE itself in ways that would need re-reading prior iterations beyond the last one, and that deep hierarchies at OLTP query time are often better served by materializing an ancestor 'closure table' maintained on write.

-- Org chart: everyone under manager 3, with depth and path
WITH RECURSIVE team AS (
  SELECT id, name, manager_id,
         0 AS depth, CAST(id AS CHAR(200)) AS path
  FROM employees WHERE id = 3
  UNION ALL
  SELECT e.id, e.name, e.manager_id,
         t.depth + 1, CONCAT(t.path, '>', e.id)
  FROM employees e
  JOIN team t ON e.manager_id = t.id
  WHERE LOCATE(CONCAT('>', e.id, '>'), CONCAT(t.path, '>')) = 0  -- cycle guard
)
SELECT * FROM team ORDER BY path;

-- Date spine for a gapless daily report
WITH RECURSIVE days AS (
  SELECT CURDATE() - INTERVAL 89 DAY AS d
  UNION ALL SELECT d + INTERVAL 1 DAY FROM days WHERE d < CURDATE()
)
SELECT d, COALESCE(s.signups, 0) AS signups
FROM days LEFT JOIN daily_signups s ON s.day = d;

Answer

Window functions (8.0) compute a value over a set of rows related to the current row without collapsing them the way GROUP BY does. The syntax is fn() OVER (PARTITION BY ... ORDER BY ... frame), and named windows via the WINDOW clause avoid repetition.

The functions that come up in interviews: ROW_NUMBER() gives a unique sequence per partition; RANK() leaves gaps after ties (1,1,3) while DENSE_RANK() does not (1,1,2), and being able to state that difference instantly is table stakes; LAG()/LEAD() read neighboring rows, replacing painful self-joins for day-over-day deltas; SUM()/AVG() OVER with a frame like ROWS BETWEEN 6 PRECEDING AND CURRENT ROW produce running totals and moving averages; NTILE(n) buckets rows for percentile-style cohorting. The canonical interview task is top-N per group, latest 3 orders per customer, highest-paid employee per department, which pre-8.0 MySQL needed correlated subqueries or the groupwise-max hack for: now it is ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) wrapped in a subquery filtered to rn <= 3, because window functions cannot appear in WHERE directly (they evaluate after WHERE); 8.0's QUALIFY-free reality means the wrapper subquery or CTE is mandatory syntax to remember. Frames are the subtle part: the default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats peer rows (equal ORDER BY values) as one unit, occasionally producing 'wrong' running totals; specify ROWS explicitly when you mean row-positional accumulation. Performance: windows require sorting per partition unless an index supplies the order, and EXPLAIN shows 'Using filesort' with windowing iterators in FORMAT=TREE; on big partitions this is memory-and-temp-table territory, so pre-filter as early as possible.

-- Latest 3 orders per customer
WITH ranked AS (
  SELECT o.*, ROW_NUMBER() OVER (
    PARTITION BY customer_id ORDER BY created_at DESC
  ) AS rn
  FROM orders o
)
SELECT * FROM ranked WHERE rn <= 3;

-- Day-over-day GMV delta and 7-day moving average
SELECT day, gmv_paise,
  gmv_paise - LAG(gmv_paise) OVER w AS dod_delta,
  AVG(gmv_paise) OVER (
    w ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma7
FROM daily_gmv
WINDOW w AS (ORDER BY day);

Answer

Invisible indexes decouple 'index exists and is maintained' from 'optimizer may use it'. ALTER TABLE t ALTER INDEX idx INVISIBLE keeps the index fully updated on every write but hides it from the optimizer, and the flip back to VISIBLE is instantaneous metadata, no rebuild. This turns index removal from a leap of faith into a safe canary: before dropping a large index, make it invisible, watch the slow log and digest tables for a week (through at least one weekly report cycle, since monthly jobs are the classic landmine), and only then DROP.

If latency regresses, one ALTER restores it in milliseconds, versus hours of online index rebuild after a premature drop. The reverse direction works too: build a new index, keep it invisible, and test candidate queries with SET SESSION optimizer_switch='use_invisible_indexes=on' before exposing it fleet-wide, protecting other queries from surprise plan flips (a new index can make some other query slower by luring the optimizer to a worse plan). Two footnotes: the primary key (explicit or promoted) cannot be invisible, and invisible unique indexes still enforce uniqueness, they are hidden from planning, not from constraint checking.

Descending indexes are the other 8.0 addition: 5.7 parsed KEY (a DESC) but silently built it ascending; 8.0 actually stores descending order. They matter for mixed-direction sorts, ORDER BY score DESC, created_at ASC, which no single-direction index can serve without a filesort; a true (score DESC, created_at ASC) index can. For single-direction ORDER BY ... DESC, a normal ascending index already works via backward index scan, so descending indexes are specifically about mixed directions and about optimizing heavily-DESC scans where backward scanning is measurably slower on some workloads.

-- Canary an index removal
ALTER TABLE orders ALTER INDEX idx_legacy_status INVISIBLE;
-- ...observe slow log / sys.statement_analysis for days...
ALTER TABLE orders DROP INDEX idx_legacy_status;   -- or flip VISIBLE back

-- Test a hidden index in one session only
ALTER TABLE orders ADD INDEX idx_trial (user_id, status) INVISIBLE;
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN SELECT id FROM orders WHERE user_id = 9 AND status = 'paid';

-- Mixed-direction sort needs a true descending key part
ALTER TABLE leaderboard
  ADD KEY idx_score_time (score DESC, achieved_at ASC);

Answer

ALTER TABLE accepts ALGORITHM and LOCK clauses. COPY rebuilds the table into a copy while blocking writes: the legacy behavior, to be avoided on live tables. INPLACE rebuilds or modifies within InnoDB while permitting concurrent DML for most operations (adding a secondary index is the flagship case), though it may still rebuild the clustered index for things like changing a column type, and it briefly needs exclusive metadata locks at the start and end.

INSTANT (8.0.12+) changes only metadata: adding a column (append-only until 8.0.29, at arbitrary positions after), renaming a column, and setting defaults complete in milliseconds regardless of table size. 8.0.29 also made DROP COLUMN instant, implemented via 'row versions', with a hard cap of 64 instant changes before a real rebuild is required (error: 'Maximum row versions reached'), a sharp edge worth naming. Best practice: request the cheapest algorithm explicitly, ALGORITHM=INSTANT, and let MySQL fail with an error rather than silently degrading to a table copy, then decide. The killer everyone underestimates is metadata locking: even an INSTANT ALTER must acquire an exclusive MDL, and it queues behind any open transaction touching the table, while every new query queues behind the ALTER, so one forgotten long-running transaction turns a millisecond ALTER into a full outage ('Waiting for table metadata lock' pileup).

Mitigate with lock_wait_timeout set low for DDL sessions and by checking innodb_trx first. External tools exist for what native online DDL cannot do gracefully on huge, busy, replicated tables: pt-online-schema-change (triggers copy writes to a shadow table) and gh-ost (reads the binlog instead of using triggers, throttles on replica lag, allows pausing). Their operational cost: doubled disk during migration, FK complications, and cut-over locking, but they give resumability and lag-aware pacing that a monolithic ALTER never will.

-- Fail loudly if instant is impossible
ALTER TABLE orders
  ADD COLUMN utm_source VARCHAR(64) NULL,
  ALGORITHM=INSTANT;

-- Check for blockers BEFORE running DDL
SELECT trx_mysql_thread_id, trx_started, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

-- Keep a stuck DDL from strangling the app
SET SESSION lock_wait_timeout = 5;  -- give up fast, retry off-peak

# gh-ost on a big busy table (runs against a replica's binlog)
gh-ost --host=primary --database=app --table=orders \
  --alter='ADD COLUMN risk_score TINYINT UNSIGNED NOT NULL DEFAULT 0' \
  --max-lag-millis=1500 --chunk-size=1000 --execute

Answer

The primary records every committed change in the binary log. A replica runs an IO thread that connects as a client, streams binlog events, and appends them to its local relay log; SQL applier threads then execute relay-log events. Since MySQL 5.7/8.0 the applier is parallel (replica_parallel_workers) using logical clock or, in 8.0.27+, WRITESET-based dependency tracking, which dramatically improves apply throughput for uncorrelated transactions.

Binlog formats: STATEMENT logs SQL text, compact but unsafe for non-deterministic statements (NOW(), UUID(), LIMIT without ORDER BY, triggers), producing drift; ROW (the default since 5.7) logs before/after row images, deterministic and required by modern tooling (CDC via Debezium, gh-ost, Group Replication); MIXED switches per statement. With ROW, binlog_row_image=MINIMAL shrinks events, and PK-less tables are catastrophic because each row change forces the applier to scan for the matching row. GTIDs (gtid_mode=ON, enforce_gtid_consistency=ON) tag every transaction with a globally unique server_uuid:sequence identifier, replacing fragile file+position coordinates: replicas request 'everything I have not seen' via auto-positioning (CHANGE REPLICATION SOURCE TO ...

SOURCE_AUTO_POSITION=1), failover no longer requires computing binlog offsets, and gtid_executed/gtid_purged sets make provenance auditable. Semantics to volunteer: default replication is asynchronous, the primary commits without waiting, so a crashed primary can take committed-but-unreplicated transactions with it (that is the gap semi-sync closes). Monitoring moved too: SHOW REPLICA STATUS (the 8.0 terminology, with Seconds_Behind_Source) plus performance_schema.replication_applier_status_by_worker for per-worker lag and errors. Classic failure modes: duplicate key error 1062 on the applier after someone wrote directly to a replica (set replicas read_only/super_read_only), and replication stopping entirely on error, requiring skip/repair decisions, where with GTIDs you inject an empty transaction rather than the old sql_slave_skip_counter.

-- Primary essentials (my.cnf)
-- server_id=1
-- gtid_mode=ON
-- enforce_gtid_consistency=ON
-- binlog_format=ROW
-- binlog_expire_logs_seconds=259200

-- Point a replica with GTID auto-positioning (8.0 syntax)
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.1.5', SOURCE_USER='repl',
  SOURCE_PASSWORD='...', SOURCE_AUTO_POSITION=1,
  SOURCE_SSL=1;
START REPLICA;

SHOW REPLICA STATUS\G  -- Seconds_Behind_Source, Retrieved/Executed_Gtid_Set

-- Protect replicas from stray writes
SET GLOBAL super_read_only = ON;

Answer

Diagnose the bottleneck first: Seconds_Behind_Source is a crude derivative (it measures the timestamp gap of the event being applied, shows 0 while the IO thread itself is behind, and jumps erratically), so check Retrieved_Gtid_Set versus Executed_Gtid_Set to separate 'events not yet fetched' (network, primary binlog serialization) from 'events fetched but not applied' (applier-bound, the common case). Applier-side causes, in observed frequency order: large single transactions (a 2M-row batched DELETE is one transaction the replica must apply alone, single-threaded within the transaction, which is why purge jobs must commit in small batches); insufficient parallelism (raise replica_parallel_workers and use binlog_transaction_dependency_tracking=WRITESET on the primary so uncorrelated transactions apply concurrently, default behavior in the newest 8.x releases); PK-less tables under ROW format forcing per-row scans; DDL applying serially; and replicas on weaker hardware or with cold buffer pools serving heavy reads simultaneously. Replica-side settings like innodb_flush_log_at_trx_commit=2 and sync_binlog=0 on replicas are widely accepted risk trades to speed apply.

On the application side, design for lag rather than pretending it is zero. Segment reads: anything transactional or immediately-after-write goes to the primary; browse and analytics traffic goes to replicas. Implement read-your-writes: pin a user's reads to the primary for N seconds after their write (session stickiness via cookie timestamp), or use GTID-based consistency, capture the write's GTID and have the replica read wait with WAIT_FOR_EXECUTED_GTID_SET(gtid, timeout) before serving, which ProxySQL can automate.

Cap staleness: health-check replicas on their own measured lag (a heartbeat table updated on the primary every second beats Seconds_Behind_Source) and eject replicas beyond a threshold from the read pool. Finally, name the honest fix for chronic lag: reduce write amplification (batch commits, drop unused indexes) or shard, because parallel appliers cannot outrun a primary that commits faster than any single replica can apply indefinitely.

-- Primary: enable writeset parallelism metadata
SET GLOBAL binlog_transaction_dependency_tracking = 'WRITESET';

-- Replica: more parallel appliers, preserving commit order
STOP REPLICA SQL_THREAD;
SET GLOBAL replica_parallel_workers = 8;
SET GLOBAL replica_preserve_commit_order = ON;
START REPLICA SQL_THREAD;

-- Read-your-writes with GTID fencing (app pseudocode)
-- after write on primary:
--   gtid = SELECT @@global.gtid_executed;
-- before read on replica:
SELECT WAIT_FOR_EXECUTED_GTID_SET('3E11FA47-...:1-5678', 0.5);
-- returns 0 when caught up, 1 on timeout -> fall back to primary

Answer

Error 1040 'Too many connections' means max_connections (default 151) is exhausted; one extra slot is reserved for a CONNECTION_ADMIN user so an operator can still get in and triage. The naive fix, raising max_connections to 5000, misunderstands the economics: every connection is a server thread (MySQL community edition is thread-per-connection) with per-connection buffers, sort_buffer_size, join_buffer_size, read_rnd_buffer_size, tmp_table_size, allocated as needed per query. Several thousand active threads also thrash the scheduler and InnoDB's internal concurrency.

Throughput on OLTP hardware typically peaks at a concurrency far below people's connection counts; beyond it, more connections mean more context switching, not more work done. The correct architecture is layered pooling. In the application: a bounded pool per service instance (a Node.js service with mysql2 might cap at 10-20), sized so that instances x pool size stays comfortably under max_connections with headroom for humans, migrations, and replication.

When microservice sprawl makes that arithmetic impossible (200 pods x 20 connections), insert a proxy: ProxySQL multiplexes thousands of client connections onto a small number of backend connections, reusing a backend connection between transactions of different clients; it also gives query routing (read/write split by rules), per-user limits, and fast failover handling. RDS users reach the same idea with RDS Proxy. Diagnostics to name: SHOW STATUS LIKE 'Threads_connected' vs 'Threads_running' (hundreds connected, 8 running is normal and healthy; hundreds running is a stampede), Max_used_connections for the high-water mark, and the performance_schema.processlist to find who is hoarding. Also mention wait_timeout culling idle connections (defaults to 8 hours, often lowered), and that connection storms after a failover are why pools need jittered reconnect backoff, every incident review of a database stampede finds synchronized retries.

-- Where are we against the ceiling?
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS WHERE Variable_name IN
  ('Threads_connected', 'Threads_running', 'Max_used_connections');

-- Who is hoarding connections?
SELECT user, host, COUNT(*) AS conns,
       SUM(command = 'Sleep') AS idle
FROM performance_schema.processlist
GROUP BY user, host ORDER BY conns DESC;

-- Node.js (mysql2): bounded pool, fail fast when saturated
-- const pool = mysql.createPool({
--   host, user, password, database,
--   connectionLimit: 15,
--   maxIdle: 5,
--   idleTimeout: 60000,
--   queueLimit: 100      // reject instead of infinite queue
-- });

Answer

The naive ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci rewrites the whole table (ALGORITHM=COPY territory for the conversion path), so on a large hot table you run it through gh-ost or pt-online-schema-change instead, or do it during a maintenance window per table. But the charset flip is the easy part; the breakage list is what interviewers want. First, index length: utf8mb4 budget is 4 bytes per character, so a unique index on VARCHAR(255) needs 1020 bytes, fine under DYNAMIC row format with innodb_large_prefix behavior (3072 bytes, standard since 5.7), but tables still on the old COMPACT/REDUNDANT row format hit error 1071 'Specified key was too long; max key length is 767 bytes'; fix by converting ROW_FORMAT=DYNAMIC first, not by shrinking columns to 191 (that 191 convention is a fossil of this exact limit).

Second, data that is already wrong: apps that wrote 4-byte characters into utf8mb3 through a latin1 connection produced mojibake (stored double-encoded bytes); detect with CONVERT-based round-trip comparisons before migrating, because converting mojibake 'successfully' bakes the corruption in. Third, collation mixing: during a table-by-table rollout, joins between an already-converted column (utf8mb4_0900_ai_ci) and an unconverted one (utf8mb3_general_ci) raise 'Illegal mix of collations' (1267) or silently drop index usage due to coercion, so convert join-key tables in dependency groups and consider utf8mb4_general_ci as a transitional collation only if you must join across. Fourth, the client side: the connection charset must become utf8mb4 too (character_set_client/connection/results, driver flag charset=utf8mb4), or emoji still die at the wire.

Also update database and server defaults so new tables are born correct, and re-verify any triggers, procedures, and views, which capture charset context at creation time and may need re-creation. Validate with a checksum comparison (pt-table-checksum) between old and shadow tables before cutover.

-- Preflight: who is still on utf8mb3, and what row formats?
SELECT t.table_name, t.row_format, c.character_set_name
FROM information_schema.tables t
JOIN information_schema.columns c USING (table_schema, table_name)
WHERE t.table_schema = DATABASE()
  AND c.character_set_name = 'utf8mb3'
GROUP BY 1, 2, 3;

-- Row format first if needed
ALTER TABLE users ROW_FORMAT=DYNAMIC;

-- The conversion itself (small table / window case)
ALTER TABLE users
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

# Big hot table: run the same through gh-ost
gh-ost --alter='CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci' \
  --database=app --table=messages --max-lag-millis=1500 --execute

Answer

InnoDB partitioning splits one logical table into multiple physical tablespaces by RANGE, LIST, HASH, or KEY on an expression of the partitioning columns. The honest framing interviewers reward: partitioning is primarily a data lifecycle tool, secondarily a query tool, and not a general performance tool. The unambiguous win is time-series retention: RANGE partition an events table by month (commonly RANGE COLUMNS(created_at) or RANGE (TO_DAYS(created_at))), and purging a month becomes ALTER TABLE ...

DROP PARTITION, metadata-speed, no undo, no replication lag spike, versus a multi-hour batched DELETE. Query-side benefit requires partition pruning: the WHERE clause must constrain the partitioning column with predicates the pruner understands, verifiable via EXPLAIN's partitions column; a query without the partition key touches every partition, and can be slower than an unpartitioned table because each partition is a separate index tree to descend. Now the hard limitations, which are the meat of the question: every UNIQUE key, including the primary key, must include all partitioning columns, which forces PKs like (id, created_at) and means you cannot enforce global uniqueness on a column that is not in the partition key (a real modeling constraint: no unique email on a date-partitioned table); no foreign keys at all on partitioned tables, in either direction; no FULLTEXT indexes; and queries spanning many partitions pay per-partition overhead.

Also mention operational realities: maintaining a rolling window needs automation to pre-create future partitions (a missed month means inserts land in a catch-all or fail), REORGANIZE PARTITION rebuilds data, and the common alternative at scale, application-level sharding or simply archiving to object storage, often wins because partitioning does not reduce the working set the buffer pool must hold for hot queries. If asked 'will partitioning make my slow query fast', the correct instinct is: only if it prunes; otherwise fix the index.

CREATE TABLE events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  created_at DATETIME NOT NULL,
  kind VARCHAR(32) NOT NULL,
  payload JSON,
  PRIMARY KEY (id, created_at)          -- PK must include partition col
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p2026_06 VALUES LESS THAN ('2026-07-01'),
  PARTITION p2026_07 VALUES LESS THAN ('2026-08-01'),
  PARTITION p2026_08 VALUES LESS THAN ('2026-09-01'),
  PARTITION pmax     VALUES LESS THAN (MAXVALUE)
);

-- Instant retention purge
ALTER TABLE events DROP PARTITION p2026_06;

-- Verify pruning
EXPLAIN SELECT COUNT(*) FROM events
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-08';
-- partitions: p2026_08 only

Answer

The slow log records statements that crossed a latency threshold; performance_schema instruments the server itself, continuously and in memory, with configurable overhead (enabled by default since 5.6.6). The categories worth naming with examples. Statement digests: events_statements_summary_by_digest aggregates every normalized statement, including the fast ones the slow log never sees, so you catch the 5 ms query running 3,000 times per second that dominates load.

Wait analysis: events_waits_* break time into instrument classes (io/file, io/table, lock, synch/mutex), answering 'is this workload disk-bound, lock-bound, or CPU-bound' without guessing; sys.host_summary_by_file_io and sys.io_global_by_file_by_latency make it readable. Lock forensics: performance_schema.data_locks and data_lock_waits (8.0, replacing the old information_schema tables) show who holds and who waits right now, and sys.innodb_lock_waits renders a ready-made blocking chain with the killable blocking thread id, the single most useful view during a lock pileup. Metadata locks via performance_schema.metadata_locks explain 'Waiting for table metadata lock' pileups behind DDL.

Memory attribution: memory_summary_by_thread_by_event_name and sys.memory_by_thread_by_current_bytes attribute server memory to threads and instruments, the sane way to answer 'why is mysqld at 90% RAM'. Index audit: sys.schema_unused_indexes lists indexes never read since restart (drop candidates, after invisible-index canarying), and sys.schema_redundant_indexes finds prefixes shadowed by longer indexes. Table IO hotspots: sys.schema_table_statistics ranks tables by rows fetched/inserted/updated and latency. Two caveats that show real usage: counters reset at restart and digest tables are bounded (performance_schema_digests_size), so trend into an external system (PMM, SignOz, CloudWatch) rather than trusting since-boot aggregates; and heavy instrument classes (history_long, per-row waits) do cost measurable overhead, so enable selectively via setup_instruments/setup_consumers.

-- Live blocking chain during a lock pileup
SELECT waiting_pid, waiting_query,
       blocking_pid, blocking_query,
       wait_age, sql_kill_blocking_query
FROM sys.innodb_lock_waits;

-- Indexes never used since restart (verify uptime first!)
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'goodspace';

-- Where does server memory actually go?
SELECT thread_id, user, current_allocated
FROM sys.memory_by_thread_by_current_bytes
LIMIT 10;

-- Statement shape dominating load (even sub-slow-log queries)
SELECT digest_text, count_star,
       sys.format_time(sum_timer_wait) AS total_time
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 5;

Answer

The two errors demand different reflexes. 1213 means InnoDB chose your transaction as the deadlock victim and already rolled it back; the transaction is gone, so retrying is safe and correct, and because deadlocks resolve in milliseconds, immediate retry with small jitter (say 3 attempts, 10-50 ms jittered backoff) succeeds almost always. 1205 means you waited innodb_lock_wait_timeout (default 50 s) for a lock that never came; critically, by default only the statement is rolled back, not the transaction (unless innodb_rollback_on_timeout=ON), so blind statement-level retry can commit a half-applied transaction, one of the nastiest correctness bugs in this area: the correct handling is explicit ROLLBACK, then retry the whole unit. 1205 also signals a real contention or long-transaction problem upstream, so it should page differently than 1213. Retry safety requires idempotency, and this is where the answer becomes design: wrap the entire transaction in a function that can run twice without double effects. Techniques, concrete: (1) idempotency keys, a unique index on a client-supplied request_id, with INSERT of the key inside the same transaction as the work, so a retried duplicate fails fast with 1062 and returns the recorded outcome (the pattern behind UPI and payment-gateway APIs at Razorpay-class processors); (2) state-machine updates with guards, UPDATE orders SET status='paid' WHERE id=?

AND status='pending', checking affected-rows so replays are no-ops; (3) absolute writes over relative when possible (SET balance = ? beats balance = balance - ? for replay safety, though it needs the read under FOR UPDATE). Never nest retries (statement-level inside transaction-level) and never retry non-transactional side effects, emails, webhooks, cache writes, inside the loop; move them post-commit via an outbox table. Cap total retry time so lock storms fail fast instead of amplifying: retries multiply offered load exactly when the database is least able to absorb it, which is why jitter and budget caps are not optional decorations.

-- Idempotency table (unique key is the mechanism)
CREATE TABLE payment_requests (
  request_id CHAR(36) NOT NULL PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  outcome VARCHAR(16) NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB;

-- Inside the money transaction:
START TRANSACTION;
INSERT INTO payment_requests (request_id, order_id, outcome)
VALUES ('req-8f2c...', 991, 'captured');   -- 1062 here = duplicate call
UPDATE orders SET status = 'paid'
WHERE id = 991 AND status = 'pending';     -- guard: check ROW_COUNT() = 1
INSERT INTO outbox (topic, payload) VALUES ('order.paid', '{...}');
COMMIT;

-- Handler sketch: on 1213 retry whole txn; on 1205 ROLLBACK then retry;
-- on 1062 from payment_requests, SELECT the recorded outcome and return it.

Answer

InnoDB FULLTEXT indexes (5.6+) build an inverted index over word tokens of the indexed columns. Queries use MATCH(col1, col2) AGAINST('terms' IN NATURAL LANGUAGE MODE | IN BOOLEAN MODE | WITH QUERY EXPANSION); the MATCH column list must exactly match a FULLTEXT index definition. Natural language mode ranks by TF-IDF-style relevance (the MATCH expression in the SELECT list returns the score); boolean mode supports operators: +required, -excluded, "exact phrase", trailing* wildcard, and > < weight tweaks.

Tokenization knobs are where production surprises live: words shorter than innodb_ft_min_token_size (default 3) are not indexed, so 'AI' or 'Go' silently match nothing until you lower it and rebuild the index; stopwords are skipped via a default list you can replace with innodb_ft_server_stopword_table; and the 50% rule (rows matching in over half the table get zero relevance) applies only to MyISAM natural mode, not InnoDB, a myth worth debunking precisely. For languages without space-delimited words, and effectively for substring-ish matching, the built-in ngram parser (WITH PARSER ngram, token size via ngram_token_size, default 2) indexes overlapping character bigrams, the supported route for Chinese/Japanese/Korean and a workable hack for partial matching generally. Writes go through an in-memory FT cache merged in background (innodb_ft_cache_size), and deletes are tombstoned until OPTIMIZE TABLE with innodb_optimize_fulltext_only=ON compacts the index, so heavily-churned FT tables degrade without maintenance.

Where it stops being enough, and interviewers want this boundary drawn honestly: no typo tolerance or fuzzy matching, no stemming/lemmatization for Indian languages, no synonyms, no faceting, ranking far weaker than BM25 implementations, and relevance tuning is nearly nonexistent. That is why product search at any serious Indian marketplace or jobs platform lives in OpenSearch/Elasticsearch or a dedicated engine, fed from MySQL via CDC (Debezium reading the binlog), with MySQL remaining the system of record. InnoDB FTS is the right tool for admin panels, internal tools, and modest 'search my own notes' features, and the wrong tool for consumer search.

ALTER TABLE jobs ADD FULLTEXT INDEX ft_title_desc (title, description);

-- Ranked natural-language search
SELECT id, title,
       MATCH(title, description)
         AGAINST('backend engineer payments') AS score
FROM jobs
WHERE MATCH(title, description)
      AGAINST('backend engineer payments')
ORDER BY score DESC LIMIT 20;

-- Boolean mode: must have mysql, not intern, phrase match
SELECT id, title FROM jobs
WHERE MATCH(title, description) AGAINST(
  '+mysql -intern "database administrator"' IN BOOLEAN MODE);

-- Compact after heavy churn
SET GLOBAL innodb_optimize_fulltext_only = ON;
OPTIMIZE TABLE jobs;
SET GLOBAL innodb_optimize_fulltext_only = OFF;

Answer

Internal temporary tables are structures MySQL creates implicitly to hold intermediate results: for GROUP BY without a usable index, DISTINCT, some ORDER BY combinations (especially sorting a derived result), UNION (DISTINCT), materialized derived tables and CTEs, window function buffering, and semi-join materialization. EXPLAIN flags them as 'Using temporary'. They are distinct from user-created CREATE TEMPORARY TABLE tables, though those also exist per-session.

The memory story changed in 8.0: the in-memory engine for internal temp tables is now TempTable (replacing MEMORY), which supports variable-length types efficiently (VARCHAR no longer padded to full width, a huge win) and, unlike MEMORY, supports BLOB/TEXT in memory from 8.0.13. Per-table in-memory size is governed by tmp_table_size/max_heap_table_size semantics for legacy paths, but TempTable's global cap is temptable_max_ram (default 1 GiB across all sessions); overflow behavior goes to mmap'd files (controlled by temptable_use_mmap, deprecated in recent 8.0 releases) and then to InnoDB on-disk internal temp tables, which live in the session temporary tablespaces (ibt files in innodb_temp_tablespaces_dir). The classic incident this causes: a reporting query on a busy server explodes temp usage and fills the disk hosting the temp tablespaces, or memory spikes because dozens of sessions each materialize large results, so per-query 'small' settings still aggregate dangerously.

Diagnostics: SHOW STATUS LIKE 'Created_tmp%tables' gives created vs created-on-disk counts (a high disk ratio means intermediate results exceed memory budgets or contain types forcing disk), performance_schema memory instruments attribute TempTable RAM, and the slow log's extra fields (log_slow_extra) record per-query tmp table usage. Remedies in the right order: eliminate the temp table with a better index (GROUP BY on an index prefix avoids materialization), shrink the intermediate result (project fewer columns, filter earlier, avoid SELECT * into DISTINCT), and only then raise memory limits deliberately. Blindly raising tmp_table_size fleet-wide is the classic junior move that turns disk spills into OOM kills.

-- How often do temp tables spill to disk?
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- Created_tmp_tables vs Created_tmp_disk_tables ratio

-- Current TempTable memory ceiling
SELECT @@temptable_max_ram / 1024 / 1024 AS temptable_ram_mb;

-- See a query's temp usage plan
EXPLAIN FORMAT=TREE
SELECT city, COUNT(DISTINCT user_id)
FROM sessions GROUP BY city;
-- '-> Aggregate using temporary table' nodes

-- Avoid the temp table entirely with an index that feeds GROUP BY order
ALTER TABLE sessions ADD KEY idx_city_user (city, user_id);

Answer

Start from the working set, not folklore: the goal is that the pages your queries touch repeatedly fit in innodb_buffer_pool_size. The old '70-80% of RAM on a dedicated host' rule is a starting point, then measure. The raw signal is Innodb_buffer_pool_reads (misses that went to disk) versus Innodb_buffer_pool_read_requests (logical reads); a sustained miss rate above a fraction of a percent on an OLTP workload usually means the pool is undersized or a scan is polluting it.

Corroborate with page churn: Innodb_buffer_pool_pages_free, pages_data, and the young/not-young LRU promotion stats in SHOW ENGINE INNODB STATUS, which reveal whether the midpoint LRU is protecting the hot set. Since 5.7 the pool resizes online: SET GLOBAL innodb_buffer_pool_size = ... proceeds in chunks of innodb_buffer_pool_chunk_size (default 128M), and the pool size must be a multiple of chunk_size x instances; resizing rebalances page hash tables and can briefly stall, so do it off-peak. innodb_buffer_pool_instances splits the pool into independently-latched instances to reduce mutex contention on high-core machines (only meaningful when the pool exceeds 1 GB; 8 is a common setting, and 8.0 defaults sensibly). Restart behavior is an operational exam favorite: a cold buffer pool after failover or deploy means minutes-to-hours of elevated latency while pages fault in from disk.

InnoDB answers with buffer pool dump/restore: innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup (both ON by default in 8.0) persist the page list (just page IDs, tiny file ib_buffer_pool) and reload them on boot; innodb_buffer_pool_dump_pct (default 25) controls how much of the LRU is saved, and you can trigger dumps manually before planned failovers, plus warm a promoted replica by replaying production reads against it first. Finally, connect sizing to the rest of memory: buffer pool plus per-connection buffers plus TempTable RAM plus binlog caches must fit physical RAM, or the OOM killer ends the discussion.

-- Miss rate estimate
SELECT
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status
   WHERE VARIABLE_NAME='Innodb_buffer_pool_reads') /
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status
   WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests') AS miss_ratio;

-- Online grow to 48G (must align to chunk * instances)
SET GLOBAL innodb_buffer_pool_size = 48 * 1024 * 1024 * 1024;
SELECT @@innodb_buffer_pool_chunk_size, @@innodb_buffer_pool_instances;

-- Pre-failover warmup insurance
SET GLOBAL innodb_buffer_pool_dump_pct = 75;
SET GLOBAL innodb_buffer_pool_dump_now = ON;   -- writes ib_buffer_pool
-- on the new primary after start:
SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

Answer

Two logs must survive a crash for full durability: the InnoDB redo log and the binary log, each with its own flush knob, and 'durable' means both fsync on every commit. innodb_flush_log_at_trx_commit: 1 (default) writes and fsyncs the redo log at every commit, full ACID D, and the fsync dominates commit latency on non-NVMe storage; 2 writes to the OS page cache at commit and fsyncs roughly once per second, so a mysqld crash loses nothing but an OS/power crash loses up to a second of commits; 0 hands even the write to the background thread, losing up to a second on a mere mysqld crash. sync_binlog: 1 (default in 8.0) fsyncs the binlog per commit group; 0 or N>1 delegate to the OS or fsync every N groups, risking binlog/engine divergence on crash, which corrupts replication downstream (a replica may have applied a transaction the recovered primary no longer has). The paired setting 1/1 is called 'true durability'; MySQL mitigates its cost with group commit, batching many transactions' fsyncs into one, plus the binlog and engine kept consistent by the internal two-phase commit between InnoDB and the binlog (XA-style, using the redo log as the coordinator record in 8.0). Where relaxing is legitimate: replicas (rebuildable from the primary), bulk load windows, and analytics staging commonly run 2/0; a primary holding money movement must not, and in interviews you should say that Razorpay-style payment ledgers run 1/1 and recover latency through NVMe and group commit rather than relaxed durability.

Redo capacity: since 8.0.30 a single dynamic variable innodb_redo_log_capacity (replacing innodb_log_file_size/innodb_log_files_in_group) sizes the redo space; too small forces aggressive checkpoint flushing (write stalls, 'checkpoint age' pressure visible in SHOW ENGINE INNODB STATUS), while larger capacity smooths write bursts at the cost of longer crash recovery. Size it so that roughly an hour of peak write volume fits, then verify stall metrics. Also name innodb_flush_method=O_DIRECT to skip double-buffering through the OS cache on Linux, standard on dedicated hosts.

-- Primary with money on the line
-- my.cnf:
-- innodb_flush_log_at_trx_commit = 1
-- sync_binlog = 1
-- innodb_redo_log_capacity = 8G
-- innodb_flush_method = O_DIRECT

-- Replica / staging: latency over durability (rebuildable)
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL sync_binlog = 0;

-- Watch checkpoint pressure (log pad / async flush points)
SHOW ENGINE INNODB STATUS\G   -- LOG section: checkpoint age vs capacity
SELECT @@innodb_redo_log_capacity / 1024 / 1024 AS redo_mb;

Answer

Asynchronous replication: the primary commits and acknowledges the client without waiting for any replica; simplest, lowest latency, and a primary crash can lose committed transactions that never left the host, so failover risks both data loss and split-brain if the old primary revives. Semi-synchronous (the rpl_semi_sync_source/replica plugins in 8.0 naming): the primary waits, after committing in the engine, for at least rpl_semi_sync_source_wait_for_replica_count replicas to acknowledge receipt (not application) of the binlog events before answering the client; with the default AFTER_SYNC wait point (lossless semi-sync), the ack is awaited before the engine commit becomes visible, so a crashed primary cannot have acknowledged a transaction that no replica holds. It bounds data loss to zero acknowledged transactions at the cost of a network round trip per commit group, and it degrades to async after rpl_semi_sync_source_timeout milliseconds without acks, a behavior you must alarm on because it silently drops your durability guarantee.

Group Replication is a different animal: a Paxos-derived group communication layer where a transaction must be certified by a majority of the group before commit; conflicts between concurrently-writing members are detected at certification (first committer wins, others roll back with certification errors). It gives automatic membership, majority-based fencing (a partitioned minority refuses writes, structurally preventing split-brain), and single-primary mode (default, with automatic primary election) or multi-primary mode (write-anywhere, with real conflict-rollback consequences for hotspot rows). InnoDB Cluster is the productized bundle: Group Replication + MySQL Router (transparent connection routing and failover for applications) + MySQL Shell's AdminAPI (dba.createCluster(), cluster.addInstance()) for orchestration.

Deploy it when you need automated failover with quorum semantics and can pay the constraints: every table needs a PK, large/long transactions hurt certification, network latency bounds write throughput, and a healthy cluster wants three nodes in low-latency reach, which maps well to three AZs in one AWS region (ap-south-1) but poorly to cross-region writes. Many teams instead run semi-sync plus Orchestrator for managed failover, or delegate the whole problem to RDS/Aurora; being able to argue that choice is what makes this an advanced answer.

-- Semi-sync on the primary (8.0 component/plugin)
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = ON;
SET GLOBAL rpl_semi_sync_source_wait_for_replica_count = 1;
SET GLOBAL rpl_semi_sync_source_timeout = 2000;  -- ms; alarm on fallback!
SHOW STATUS LIKE 'Rpl_semi_sync_source_status';

-- InnoDB Cluster bootstrap via MySQL Shell AdminAPI
-- mysqlsh admin@db1
dba.configureInstance('admin@db1:3306')
var cluster = dba.createCluster('prod')
cluster.addInstance('admin@db2:3306', {recoveryMethod: 'clone'})
cluster.addInstance('admin@db3:3306', {recoveryMethod: 'clone'})
cluster.status()

Answer

Detection: monitor the primary through multiple vantage points (agent on the host, checks from at least two networks) to distinguish 'primary is dead' from 'monitoring is partitioned', with a decision timeout balancing false failovers against downtime; Orchestrator (the standard OSS tool for this) models the whole topology and requires corroboration from replicas themselves (they report broken IO threads) before declaring failure, which is the right idea to cite. Candidate selection: promote the most caught-up replica, with GTIDs this is comparable directly via gtid_executed superset checks; if another replica has transactions the candidate lacks, either wait for relay log drain or use the errant-transaction checks. Fencing, the step everyone forgets and the one interviewers listen for: before promoting, ensure the old primary cannot accept writes if it comes back, kill the instance via cloud API or STONITH, remove its VIP, set super_read_only=ON in its config so a restart comes up read-only, and revoke it at the proxy layer; skipping fencing is how split-brain corrupts both datasets and forces a painful reconcile-or-discard decision.

Promotion: on the chosen replica, wait for relay application, STOP REPLICA; RESET REPLICA ALL; SET GLOBAL super_read_only=OFF, and repoint remaining replicas with SOURCE_AUTO_POSITION=1 (GTID makes this one statement, no offset math). Client redirection: applications should never hold hardcoded IPs; use ProxySQL/HAProxy health-checked backends, a DNS name with low TTL, or service discovery (Consul), and expect a reconnect storm, which is why pool jittered backoff belongs in the runbook. Old-primary reintegration: with GTID, check for errant transactions (writes it took after the partition began); if none, it rejoins as a replica, if some, rebuild it from a backup or clone plugin rather than inventing history surgery.

Close the runbook with drills: an untested failover procedure fails during the real incident, and the 19-20 July class of outage where nobody notices the primary died for hours means detection alerting is part of HA, not an afterthought. State the RDS/Aurora comparison: managed multi-AZ failover automates exactly these steps, which is what you are paying for.

-- Promotion on the chosen replica (after relay drain)
STOP REPLICA;
SELECT @@gtid_executed;          -- compare across candidates first
RESET REPLICA ALL;
SET GLOBAL read_only = OFF, GLOBAL super_read_only = OFF;

-- Repoint a surviving replica at the new primary (GTID)
STOP REPLICA;
CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.0.1.9',
  SOURCE_USER='repl', SOURCE_PASSWORD='...',
  SOURCE_AUTO_POSITION=1;
START REPLICA;

-- Errant transaction check for the returning old primary
-- (its gtid_executed minus new primary's = must be empty)
SELECT GTID_SUBTRACT('<old_primary_set>', '<new_primary_set>');

Answer

InnoDB's default statistics are thin: per-index cardinality estimates derived from sampling innodb_stats_persistent_sample_pages (default 20) leaf pages, persisted in mysql.innodb_table_stats/innodb_index_stats, refreshed automatically after ~10% of rows change (innodb_stats_auto_recalc). Cardinality answers 'how many distinct values', which implies a uniformity assumption: for a column with 10 distinct values over 10M rows, any equality predicate is estimated at 1M rows. Real data is skewed, status='failed' might be 0.1% while status='completed' is 90%, so plans flip wrongly: the optimizer picks a full scan for the rare value or an index dive for the dominant one, join orders invert, and you get the classic 'same query, fast for customer A, catastrophic for customer B' ticket.

For predicates with literal values, MySQL does index dives, actual range estimation in the index, which is accurate; the pain concentrates on non-indexed columns and on plans where dives are skipped (eq_range_index_dive_limit, prepared-statement parameter situations). Histograms (8.0) attack exactly this: ANALYZE TABLE t UPDATE HISTOGRAM ON col WITH 100 BUCKETS samples the column (memory governed by histogram_generation_max_mem_size) and stores an equi-height or singleton histogram in the data dictionary (visible in information_schema.column_statistics), giving the optimizer real selectivity for skewed predicates without the write-path cost of an index; since 8.0.31 AUTO UPDATE can refresh them with automatic recalculation. Use histograms on: low-to-medium cardinality skewed columns used in WHERE but not worth indexing (status, country, plan_type), especially when they drive join order.

Do not bother on: columns already leading an index used with literals (dives beat histograms), or rapidly shifting distributions without auto update, since a stale histogram misleads worse than none. Complete the toolkit: ANALYZE TABLE refreshes base stats after bulk loads (mysqldump restores famously leave terrible stats), innodb_stats_persistent_sample_pages can be raised per-table via STATS_SAMPLE_PAGES, and when statistics cannot save a plan, optimizer hints or forced indexes are the escape hatch, ideally temporary.

-- Build a 100-bucket equi-height histogram on a skewed column
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;

-- 8.0.31+: keep it fresh automatically
ANALYZE TABLE orders UPDATE HISTOGRAM ON status
  WITH 100 BUCKETS AUTO UPDATE;

-- Inspect what the optimizer now believes
SELECT column_name,
       JSON_EXTRACT(histogram, '$."histogram-type"') AS type,
       JSON_LENGTH(histogram, '$.buckets') AS buckets
FROM information_schema.column_statistics
WHERE table_name = 'orders';

-- Base stats refresh after a bulk load
ANALYZE TABLE orders;

Answer

Until 8.0.18, MySQL executed every join as a nested loop (with the block nested loop buffering variant for index-less inner sides). 8.0.18 introduced hash joins: build a hash table from the smaller input on the equi-join key, probe with the larger, spilling to disk partitions when the build side exceeds join_buffer_size. From 8.0.20 the hash join fully replaced BNL, so any equi-join without a usable index on the inner table now hash-joins, which converted a whole category of accidentally-quadratic report queries into linear ones. Expectations to set correctly: for OLTP point lookups, an indexed nested loop remains superior (probing one row via an index beats hashing millions); hash join shines when both sides are large and the join key is unindexed or when a scan is inevitable, i.e., analytics-shaped queries.

You see it in EXPLAIN FORMAT=TREE as 'Inner hash join'; classic EXPLAIN shows 'Using join buffer (hash join)'. It requires at least one equi-join condition (pure inequality joins fall back to Cartesian-with-filter). Hints: MySQL 8.0's /*+ ... */ comment syntax after SELECT is the sanctioned mechanism, JOIN_ORDER(t1, t2), JOIN_PREFIX, HASH_JOIN/NO_HASH_JOIN (8.0.18-8.0.19 era; later versions rely on BNL/NO_BNL semantics), INDEX/NO_INDEX and the older FORCE INDEX table-level form, MERGE/NO_MERGE for derived tables, SEMIJOIN/NO_SEMIJOIN with strategies, and SET_VAR(sort_buffer_size=...) to scope a variable to one statement, which is enormously useful for one heavy nightly query needing a bigger sort buffer without a global change.

MAX_EXECUTION_TIME(ms) caps a SELECT's runtime server-side, a good belt-and-braces for user-facing search endpoints. Responsible usage doctrine, which is what the question is really probing: hints are pinned exceptions, not tuning strategy; every hint is a bet that your knowledge stays truer than the optimizer's statistics as data grows, so first fix statistics (ANALYZE, histograms) and indexes, use hints to stop the bleeding in an incident, document them, and schedule their removal; a codebase crusted with FORCE INDEX from 2019 is a museum of unfixed root causes. Where plans must be stable above all (payments cutoffs), some teams pin with hints deliberately and re-validate at every upgrade, an honest trade-off worth articulating.

-- Analytics join with no useful index: hash join, bigger buffer for this
-- statement only, and a server-side timeout
SELECT /*+ SET_VAR(join_buffer_size = 256M) MAX_EXECUTION_TIME(30000) */
  u.city, SUM(o.amount_paise)
FROM orders o
JOIN users u ON u.id = o.user_id           -- equi-join: hash-joinable
WHERE o.created_at >= '2026-01-01'
GROUP BY u.city;

EXPLAIN FORMAT=TREE
SELECT u.city, COUNT(*)
FROM big_a a JOIN big_b b ON a.k = b.k, users u;
-- '-> Inner hash join (a.k = b.k)' nodes reveal the strategy

-- Pin a join order during an incident (document + ticket its removal)
SELECT /*+ JOIN_ORDER(o, u) */ ...

Answer

Point-in-time recovery answers the question a nightly backup cannot: not 'give me last night', but 'give me 14:37:11, one second before the migration ran UPDATE users SET phone = NULL with no WHERE clause'. It needs two ingredients, a consistent base backup and an unbroken chain of binary logs from that backup's coordinate forward, which is why log_bin and binlog_expire_logs_seconds (default 2592000, thirty days, the 8.0 replacement for expire_logs_days) are recovery settings, not just replication settings. Mechanics: take the base with Percona XtraBackup or CLONE INSTANCE FROM (8.0.17+) on large datasets, or mysqldump --single-transaction --source-data=2 on small ones, which writes the starting binlog file and position into the dump header.

Locate the damage by decoding row events with mysqlbinlog --base64-output=DECODE-ROWS -vv, then replay with --start-position and --stop-datetime, or with --exclude-gtids to skip specific transactions in a GTID topology, into a scratch instance rather than onto the live primary. Two numbers drive the design. RPO is bounded by where the binlogs physically live: if they exist only on the primary's local disk, losing that host loses everything since the base backup, so stream them continuously with mysqlbinlog --read-from-remote-server --raw --stop-never to another host or to object storage.

RTO is dominated by replay, which runs as one serial stream through the mysql client while the primary produced those events with parallel writers, so eight hours of binlog can take longer than eight hours to apply. The fixes are more frequent base backups and a delayed replica (CHANGE REPLICATION SOURCE TO SOURCE_DELAY=3600), which converts human-error recovery from a restore into a stop-and-promote. Details that earn credit: set gtid_purged correctly after restoring a physical backup or replicas refuse to attach; SET sql_log_bin = 0 during surgical repairs so they do not ship downstream; MySQL 8.4 renamed RESET MASTER to RESET BINARY LOGS AND GTIDS, quietly breaking old runbooks; and on RDS or Aurora, PITR is a console operation with roughly five-minute granularity that restores into a brand new instance, so endpoint cutover is part of the procedure.

# 1) Base restore on a scratch host
xtrabackup --prepare --target-dir=/backups/2026-08-11_full
xtrabackup --copy-back --target-dir=/backups/2026-08-11_full

# 2) Find the damaging statement in ROW-format binlogs
mysqlbinlog --base64-output=DECODE-ROWS -vv --start-datetime='2026-08-11 14:30:00' binlog.000842 | less

# 3) Replay up to one second before it
mysqlbinlog --start-position=194 --stop-datetime='2026-08-11 14:37:11' binlog.000841 binlog.000842 | mysql -h scratch-host

# 4) Keep the binlog tail off-host: this is your real RPO
mysqlbinlog --read-from-remote-server --host=prod-primary --raw --stop-never binlog.000842

-- Human-error insurance: an hour-delayed replica
CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 3600;
START REPLICA;

Answer

Under REPEATABLE READ that transaction's read view must keep seeing the database exactly as it looked when it started, so InnoDB cannot purge any row version created since. The damage stacks up in a predictable order. Undo grows first: history list length climbs (trx_rseg_history_len in information_schema.innodb_metrics, and the TRANSACTIONS section of SHOW ENGINE INNODB STATUS), undo tablespaces expand, and because innodb_undo_log_truncate can only reclaim segments no longer needed by any read view, disk usage keeps rising for as long as the transaction lives.

Then reads get slower for everyone: a SELECT touching a hot, frequently-updated row must walk a version chain that is now thousands of entries deep to find the version visible to its own snapshot, so p99 latency drifts upward on queries that never went near the reporting tables. Secondary indexes bloat in parallel, because delete-marked entries cannot be purged, so range scans read more pages and the buffer pool fills with rows nobody wants. Worst case, the open transaction holds a shared metadata lock on a table a deploy wants to ALTER: the DDL waits, and every query arriving after it queues behind the DDL, so an idle transaction becomes a full outage in under a minute.

Detection is cheap and belongs in monitoring: alert on the oldest transaction age from information_schema.innodb_trx ordered by trx_started, and on history list length crossing a threshold. sys.session exposes the specific pathology of a connection idle inside a transaction, which usually means an ORM opened one on connection checkout and the application then made a slow external call. Prevention that holds up: run analytics against a replica, put reporting sessions on READ COMMITTED so read views are per-statement, set MAX_EXECUTION_TIME on those connections, keep network calls outside transaction boundaries, and run pt-kill or a scheduled killer for transactions older than N minutes. Note that wait_timeout is not a guard: its default of 28800 seconds is longer than most incidents and it only fires between statements, so it never interrupts a running query. Also flag the self-inflicted version, mysqldump --single-transaction against a busy primary, which pins a snapshot for the entire dump.

-- Oldest open transactions, with the thread id you would kill
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds,
       trx_mysql_thread_id, LEFT(COALESCE(trx_query, '<idle>'), 60) AS q
FROM information_schema.innodb_trx
ORDER BY trx_started;

-- Purge backlog: this climbing steadily is the alarm
SELECT name, count FROM information_schema.innodb_metrics
WHERE name = 'trx_rseg_history_len';
SHOW ENGINE INNODB STATUS\G   -- TRANSACTIONS: History list length

-- Connections idling INSIDE a transaction
SELECT thd_id, conn_id, user, trx_state, trx_started
FROM sys.session WHERE trx_state IS NOT NULL AND command = 'Sleep';

KILL 481920;   -- trx_mysql_thread_id from above

-- Guard rails for reporting connections
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT /*+ MAX_EXECUTION_TIME(30000) */ ... ;

Answer

Start with the pre-flight check rather than the maintenance window. MySQL Shell's util.checkForServerUpgrade() connects to the running server and reports removed features, reserved words used as identifiers, orphaned or utf8mb3 objects, zero dates, and configuration the target rejects; it accepts a targetVersion so you can aim it directly at 8.4. Rule one: patch to a recent 8.0 release first, because the supported jump to 8.4 is from a current 8.0 patch level, not from whatever shipped three years ago.

Rule two: replication compatibility has a direction, a higher-version replica can replicate from a lower-version source but not the reverse, so the rolling order is upgrade replicas, verify under real read traffic, fail over, then upgrade the old primary. That order also gives you the rollback, because there is no supported in-place downgrade across series and 'we would restore from backup' is a plan people discover the flaws of at 2 a.m. Now the breakage.

Authentication: 8.4 ships with mysql_native_password disabled by default, so any account still on that plugin, and any ancient connector that cannot speak caching_sha2_password, fails to connect; audit mysql.user for plugin values and inventory driver versions across app pods, cron boxes, and BI tools nobody owns. Replication syntax: 8.4 completed the terminology migration, so SHOW SLAVE STATUS, START SLAVE, CHANGE MASTER TO and RESET MASTER are gone, and every monitoring script, Ansible role, and runbook calling them errors out; the replacements are SHOW REPLICA STATUS, START REPLICA, CHANGE REPLICATION SOURCE TO and RESET BINARY LOGS AND GTIDS. Defaults also shift, notably around parallel replica application and writeset-based dependency tracking, which changes replica behavior under load with no change on your side.

Then the query layer: replay captured slow-log traffic against a shadow instance and diff plans with pt-query-digest, because optimizer changes between series can flip a plan on your data even when the SQL is byte-identical. Time the project on support grounds: 8.0's premier support window closes during 2026, which is exactly why these upgrades sit on 2026 roadmaps at most Indian product companies.

-- MySQL Shell pre-flight (JS mode)
util.checkForServerUpgrade('admin@prod-db:3306', {targetVersion: '8.4.0'})

-- Accounts that will fail to connect on 8.4
SELECT user, host, plugin FROM mysql.user
WHERE plugin <> 'caching_sha2_password';
ALTER USER 'app'@'%' IDENTIFIED WITH caching_sha2_password BY '<secret>';

-- Runbook search-and-replace before the upgrade
-- SHOW SLAVE STATUS      -> SHOW REPLICA STATUS
-- START SLAVE            -> START REPLICA
-- CHANGE MASTER TO       -> CHANGE REPLICATION SOURCE TO
-- RESET MASTER           -> RESET BINARY LOGS AND GTIDS

-- Verify each node after the rolling upgrade
SELECT @@version, @@version_comment;
SHOW REPLICA STATUS\G   -- Replica_IO_Running / Replica_SQL_Running / Seconds_Behind_Source

Answer

Exhaust the cheaper options honestly first, because this question tests judgment rather than enthusiasm for distributed systems. Reads scale with replicas and caching. Writes scale vertically for a surprisingly long time: more cores, NVMe, a buffer pool that actually holds the working set, batched inserts, and deleting redundant indexes that multiply write amplification.

After that comes the functional split, moving distinct domains such as events, notifications, or audit trails onto their own instances, which is a two-week project instead of a two-quarter one. Shard only when a single writer is genuinely saturated: CPU pinned by write concurrency, redo and fsync at device limits, working set several times RAM, or a table so large that ALTER and restore times have stopped being operationally acceptable. Shard key choice is the whole game.

It must appear in nearly every hot query, or each read becomes a scatter-gather across all shards; tenant_id suits B2B, user_id suits consumer products, and a monotonically increasing key is the classic mistake because every new write lands on one shard. The tax you accept is cross-shard joins, global uniqueness constraints such as email, and fleet-wide reporting, handled with lookup tables, a search index or warehouse, and application-side merges rather than distributed joins. Vitess changes ergonomics, not physics: VTGate speaks the MySQL wire protocol so applications keep their existing driver, the VSchema declares vindexes (a hash vindex on the shard key, lookup vindexes for secondary access paths), and resharding runs online through VReplication with MoveTables, Reshard, and a controlled traffic switch, which is precisely the part hand-rolled sharding gets wrong.

It also enforces discipline you want anyway, such as requiring a primary key on every table and discouraging cross-shard transactions. Shard in the application instead and you own the routing layer, the shard directory, backfill and cutover tooling, and per-shard migrations, forever. Avoid two-phase commit across shards: use per-shard transactions with an outbox table, idempotent consumers, and reconciliation jobs.

Generate identifiers that need no central counter, since per-shard AUTO_INCREMENT collides the moment data moves. Then name the exit ramp: a managed distributed SQL engine, or the realisation that better indexes would have kept the workload on one primary.

-- Evidence that the writer, not the reader, is the ceiling
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_time_avg';
SELECT ROUND(SUM(data_length + index_length) / POW(1024,3)) AS data_gb
FROM information_schema.tables WHERE table_schema = DATABASE();

-- After sharding, every hot query carries the shard key
SELECT * FROM orders WHERE tenant_id = 4412 AND id = 99120;   -- single shard
SELECT COUNT(*) FROM orders WHERE status = 'paid';            -- scatter: avoid

-- Vitess VSchema fragment: hash vindex on tenant_id
-- {
--   "sharded": true,
--   "vindexes": { "hash": { "type": "hash" } },
--   "tables": {
--     "orders": { "column_vindexes": [ { "column": "tenant_id", "name": "hash" } ] }
--   }
-- }

-- Online move + reshard, then switch reads before writes
-- vtctldclient MoveTables --workflow orders_move Create ...
-- vtctldclient Reshard --workflow o_split Create --source-shards '0' --target-shards '-80,80-'
-- vtctldclient Reshard --workflow o_split SwitchTraffic --tablet-types replica,rdonly

Answer

Start with the symptom picture, because it is distinctive: throughput on one SKU flatlines while CPU sits idle, Innodb_row_lock_waits and Innodb_row_lock_time_avg climb, sys.innodb_lock_waits shows hundreds of sessions blocked on the same lock, and eventually connections pile up until max_connections is exhausted and error 1040 takes down endpoints that have nothing to do with the sale. The mechanism is arithmetic, not mystery: every request needs an exclusive lock on the same row, so maximum throughput on that row is one divided by the lock hold time. Code that does SELECT ...

FOR UPDATE, then application logic, then UPDATE, then COMMIT holds the lock for an entire application round trip, and if there is a payment gateway call inside the transaction, hold time is hundreds of milliseconds and the ceiling is a few sales per second. Fixes in order of leverage. First, collapse read-modify-write into one guarded statement: UPDATE inventory SET qty = qty - 1 WHERE sku_id = ?

AND qty >= 1, then branch on ROW_COUNT(). The lock lives for microseconds, overselling is impossible because the check and the decrement are the same atomic operation, and no isolation-level gymnastics are required. Second, evict everything slow from the transaction: no gateway calls, no queue publishes, no rendering between BEGIN and COMMIT.

Third, if one row still cannot absorb the rate, shard the counter into N bucket rows per SKU, decrement a randomly chosen bucket with the same guard, sum for display, and reconcile periodically; contention falls by roughly N in exchange for a slightly fuzzy read. Fourth, for genuinely scarce allocations, change the model: pre-create reservation token rows and hand them out with SELECT ... FOR UPDATE SKIP LOCKED, so workers never convoy on the same row.

Fifth, for money, stop updating a balance row at all: append to a ledger and maintain the balance as a snapshot, which is exactly why payment platforms model wallets as append-only. Finally, set innodb_lock_wait_timeout low on these paths (a few seconds, not the 50 second default) so failures are fast and retryable, and make the retry idempotent with a client-supplied request key.

-- Anti-pattern: lock held across application logic
START TRANSACTION;
SELECT qty FROM inventory WHERE sku_id = 77 FOR UPDATE;  -- everyone queues here
-- ... business logic, sometimes an HTTP call ...
UPDATE inventory SET qty = qty - 1 WHERE sku_id = 77;
COMMIT;

-- One atomic guarded write: no oversell, microsecond lock
UPDATE inventory SET qty = qty - 1
WHERE sku_id = 77 AND qty >= 1;
-- ROW_COUNT() = 0 means sold out

-- Sharded counter: spread one hot row across 16 buckets
UPDATE inventory_bucket SET qty = qty - 1
WHERE sku_id = 77 AND bucket = FLOOR(RAND() * 16) AND qty >= 1;
SELECT SUM(qty) FROM inventory_bucket WHERE sku_id = 77;

-- Token handout without convoying
SELECT id FROM reservation_tokens
WHERE sku_id = 77 AND claimed_at IS NULL
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;

-- Who is blocked on whom, right now
SELECT * FROM sys.innodb_lock_waits\G
SET SESSION innodb_lock_wait_timeout = 3;

Answer

RDS MySQL is upstream MySQL with the operational toil managed: automated backups with point-in-time restore inside the retention window, Multi-AZ with a synchronously replicated standby and DNS-based failover, scheduled minor version patching, and configuration through parameter groups instead of my.cnf, with a subset of variables read-only. The real loss is access. There is no SUPER privilege and no shell, so no perf, no pt-stalk, no XtraBackup, no arbitrary plugins; diagnostics run through Performance Insights, Enhanced Monitoring, and the same performance_schema and sys views you already know how to read.

Aurora MySQL is a different animal: a MySQL-compatible engine (Aurora MySQL 3 tracks the 8.0 series) on a rewritten storage layer where the redo stream goes to a distributed fleet with a six-way quorum across three availability zones. Readers attach to that shared storage instead of replaying binlogs, so reader lag is typically milliseconds rather than seconds, adding a reader takes minutes with no data copy, and failover promotes an existing reader in tens of seconds. The trade-offs deserve equal airtime: Aurora carries a price premium over EC2 with gp3 volumes, Aurora Standard bills per I/O so write-heavy workloads with many small commits can produce a shocking bill (I/O-Optimized is the flat-rate answer), binary logging is off by default and turning it on for downstream CDC costs write throughput, and managed engines trail upstream releases, so a feature in a current MySQL release may not be available yet.

Both give encryption at rest through KMS and TLS in transit, which is the baseline for anyone handling Indian user data under DPDP obligations, enforced with require_secure_transport and by keeping the fleet in ap-south-1 for residency and latency. What stays yours, which is what the question is really probing: schema and index design, query plans, transaction scope, connection pooling (RDS Proxy or ProxySQL, because managed hosting does not fix an app that opens two thousand connections), a strategy for large ALTERs, and alerting on replica lag, lock waits, and oldest transaction age. Managed services remove backup, patching, and failover mechanics. They do not remove a missing composite index, a hot row, or a four-hour transaction, which are the things that actually page people at 3 a.m.

-- What you no longer control (parameter group, not my.cnf)
SHOW GRANTS FOR CURRENT_USER();
SELECT @@innodb_flush_log_at_trx_commit, @@sync_binlog, @@read_only;

-- Transport security baseline
SELECT @@require_secure_transport;
ALTER USER 'app'@'%' REQUIRE SSL;

-- Aurora-only introspection
SELECT @@aurora_version;
SELECT server_id, session_id, replica_lag_in_milliseconds
FROM information_schema.replica_host_status;

-- RDS read replica still uses classic replication
SHOW REPLICA STATUS\G   -- Seconds_Behind_Source

-- Still your job on any platform
EXPLAIN ANALYZE SELECT ... ;
SELECT * FROM sys.schema_unused_indexes;