MySQL Interview Questions

Note: Interviewers use this to find out whether you have actually carried an on-call pager or only worked on a laptop copy of a database. Give concrete routines, not adjectives.

Structure your answer around the three things every DBA is judged on:

• Backups you have actually restored. Say how often you took them (nightly logical dump plus binary logs for point-in-time recovery, for example) and — more importantly — the last time you rehearsed a restore. A backup nobody has restored is a guess.
• What you monitored. Replication lag, slow query log, connection count, buffer pool hit rate, disk headroom. Name the threshold that pages you.
• How you handled schema changes. Whether you used online DDL, pt-online-schema-change, or a maintenance window, and how you rolled back.

Close with an incident: what broke, how you noticed, how long recovery took, and the change you made afterwards so it could not happen twice.

This is the single most common MySQL behavioural question, because it maps directly onto the job. Walk through it as an investigation, in order:

• How you noticed. An alert, a user complaint, or a spike in the slow query log — say which.
• How you isolated it. SHOW FULL PROCESSLIST to see what was running, the slow query log or Performance Schema to find the worst offenders by total time rather than by single-run time.
• What EXPLAIN told you. The usual culprits: a full table scan, a missing composite index, an index that could not be used because of a function wrapped around the column, or a bad join order.
• What you changed and what it bought you. Give the before and after number.

Note: Mention that you fixed the worst query by total execution time, not the slowest individual one. A query that takes 200ms and runs 50,000 times an hour costs far more than a 4-second report nobody runs.

Note: Do not answer "never". Everyone who has worked at scale has a story, and claiming otherwise reads as inexperience or dishonesty. Pick a real incident with a clean recovery.

A good answer has four beats:

• The mistake, stated plainly. An UPDATE without a WHERE clause, a migration that locked a large table during peak traffic, a DROP on the wrong environment.
• How fast you owned it. Interviewers care much more about whether you told someone immediately than about the mistake itself.
• The recovery. Point-in-time recovery from binary logs, a restore into a scratch schema and a targeted re-insert, or a failover to a replica.
• The guardrail you added. Running in a transaction so you can ROLLBACK, enabling sql_safe_updates, requiring a peer to review every migration, or removing write access to production entirely.

The last beat is what actually gets you hired.

Translate the decision into the currency your listener cares about — time, money, or risk — and keep the internals out of it unless you are asked.

• For developers, frame it as behaviour they will see: "this index means the search endpoint stops scanning 4 million rows, so the page loads in 40ms instead of 3 seconds — but every INSERT into this table now costs a little more."
• For managers, frame it as cost and risk: "this change lets us stay on the current instance size for another year" or "without it, checkout starts timing out during a sale."

Always state the trade-off out loud. Indexes speed up reads and slow down writes; denormalising speeds up reads and creates a consistency problem you now have to own. Presenting only the upside is what makes non-specialists distrust database advice.

Note: If you have ever written a short design document or run a lunch-and-learn on this, mention it. It signals that you scale your knowledge rather than hoarding it.

Answer in two halves — how you learn, and how you filter.

How you learn: the official MySQL release notes and worklogs, the Percona and PlanetScale engineering blogs, and — most persuasively — a local instance you deliberately break. Reading about EXPLAIN ANALYZE is not the same as running it against a badly written query and watching the row estimates drift.

How you filter: a new version or feature has to clear three bars before you would put it in production:

• Does it solve a problem we actually have, measured, rather than one we might have?
• Is it out of the earliest releases and running somewhere at comparable scale?
• Can we roll back if it disappoints, and have we tested that rollback?

Note: Naming one feature you chose NOT to adopt, and why, is more convincing than listing ten you like.

The interviewer wants proof that you treat a schema change on a big table as a small project with a plan and a way back, not just one ALTER statement run late at night. Use the STAR structure and put numbers on it.

• Situation. Give the scale: for example, a 400 GB orders table taking 2,000 writes a second, where a column type had to change from INT to BIGINT before the ID ran out.
• Options you weighed. Show you know the tools. Native online DDL (ALGORITHM=INSTANT or INPLACE) where it applies, and gh-ost or pt-online-schema-change when the change forces a table copy.
• Rehearsal. Restore a recent backup to staging, run the migration there, and time it. This is how you find out about replication lag, disk space (a shadow copy needs roughly the table's size again) and long-running transactions that block the final rename.
• Safety net. A fresh backup, a written rollback plan, throttling tied to replica lag, and a cut-over window agreed with the product and support teams.
• Execution and monitoring. Say what you watched, such as replica lag, p99 query latency and error rates, and the thresholds that would have made you pause.
• Result. For example: ‘It finished in 9 hours with zero write downtime and a cut-over lock of under a second.’

If something went wrong, say so. A migration paused at 60% because lag went past 30 seconds, then resumed off-peak, is a stronger story than a perfect one because it shows the safety net worked.

Note: Close with what you changed afterwards, such as a migration checklist or a rule that every ALTER on a table above a set size goes through an online schema change tool.

This question tests whether you can protect the database without being the person who blocks everything. A strong answer moves the argument from opinion to evidence and ends in a decision both of you can live with.

1. Understand the goal first. Ask what the change is for. Often the developer needs a feature by a deadline and the query is just their first attempt. Knowing the real need opens up alternatives.
2. Bring evidence, not authority. Run EXPLAIN or EXPLAIN ANALYZE against production-sized data and show the numbers, for example ‘this scans 12 million rows and takes 4 seconds under load’. Reproducing the problem on a staging copy is far more persuasive than ‘I have seen this before’.
3. Offer a fix, not just a no. Suggest the composite index, the rewrite from a correlated subquery to a join, the batching, or the background job that makes the idea safe.
4. Agree on guardrails if you still differ. A feature flag, a rollout to a small share of traffic, a query timeout via MAX_EXECUTION_TIME, and a named person watching the dashboards.
5. Escalate cleanly if you must. If the risk is serious and you still disagree, take it to the tech lead together with both views written down, rather than going around the developer.

A good example sounds like this: ‘A report query joined five tables with no usable index. I showed the plan, we added one covering index and moved the report to a replica, and it went from 40 seconds to 300 milliseconds. The developer later asked me to review their other queries.’

Note: Avoid stories where you simply overruled someone. Interviewers listen for respect, data and a shared outcome.

The interviewer is checking whether you stay calm, stabilise first and investigate second, and keep people informed. Walk through a clear sequence.

1. Acknowledge and communicate. Acknowledge the page and post in the incident channel that you are investigating, so no one else starts making changes at the same time.
2. See what is running. Run SHOW FULL PROCESSLIST or query sys.processlist. Look for many copies of the same query, long-running statements, or sessions waiting on locks.
3. Check what changed. A deploy in the last hour, a new cron job or report, a traffic spike, or a dropped index are the usual causes. Recent deploys are the first suspect.
4. Stabilise. Depending on the cause: kill the runaway query with KILL, disable the offending feature or cron job, roll back the deploy, or send read traffic to replicas. Get the site working before you look for the root cause.
5. Diagnose. Use the slow query log and performance_schema statement digests to confirm which query pattern used the CPU, then run EXPLAIN on it. A typical finding is a new query doing a full table scan, run thousands of times a minute.
6. Fix properly the next day. Add the index, rewrite the query or add caching, and test it under load.
7. Write the post-mortem. Keep it blameless: timeline, root cause, what detected it, and follow-ups such as query review in CI or alerting on rows examined per second.

Give a real example if you have one, with timings: ‘Detected at 2:04, mitigated at 2:19 by disabling the export job, root cause fixed the next morning with a composite index.’

Note: Mention what you would not do, such as restarting MySQL blindly. A restart empties the buffer pool and can make a slow site slower for the next hour.

Interviewers ask this to see how you balance getting the model right with not over-engineering it for requirements that may never arrive. Structure your answer around the decisions you made and why.

• Start from the questions the data must answer. Before drawing tables, list the key reads and writes. For example, for a coupon feature: ‘Which coupons can this user apply to this cart?’ and ‘How many times has this code been redeemed today?’ The access patterns decide the keys and indexes.
• Model the stable core properly. Entities that are clearly going to last, such as coupons, redemptions and users, get normalised tables, real foreign keys and NOT NULL constraints. Integrity is cheap to add at the start and painful to retrofit.
• Leave room where things are uncertain. Rules that product was still debating went into a JSON column with a version field, so we could try variations without a migration each week. Once a rule settled, it was promoted to a proper column.
• Make change cheap. Versioned migrations in the repository, backwards-compatible steps (add the column, backfill, switch reads, then drop the old one), and feature flags so the schema and the code could move separately.
• Review with others. A short design review with the backend lead and an analyst caught a reporting need we had missed.

End with the outcome and a lesson: ‘Requirements changed three times in six weeks, and we needed only additive migrations. The one thing I would change is adding the unique constraint on (coupon_id, order_id) from day one, because we had to clean up duplicate redemptions later.’

Note: Admitting a design choice you would change shows maturity. Just make sure the lesson is specific.

This tests judgement on security and privacy as much as MySQL skill. The strong answer is ‘yes, but safely’, not a flat refusal and not handing over the root password.

1. Clarify the need. Which data, how fresh, for how long, and for what purpose? Analysts usually need aggregates or a few tables, not every column in every table.
2. Keep them off the primary. Point read access at a replica or a reporting copy so a heavy ad hoc query cannot slow down checkout.
3. Minimise the data. Expose views or a separate schema that leave out or mask personal fields: hash email addresses, show only the last four digits of phone numbers, and drop PAN or Aadhaar-type identifiers entirely. Under India’s Digital Personal Data Protection Act, purpose limitation and data minimisation are expected, not optional.
4. Grant least privilege. Create a named account per person or vendor, grant SELECT on only the views they need, use MySQL 8 roles to keep grants tidy, require TLS, and restrict the host. Never share accounts.
5. Put limits in place. Set MAX_USER_CONNECTIONS, a statement timeout, and an expiry date for vendor access in the ticket.
6. Audit and review. Log access, get a data processing agreement signed for vendors, and review or revoke access at the end of the engagement.

A concrete story works well: ‘Marketing wanted the full customers table. We agreed on a view with city, signup month and order counts, served from a replica through a read-only role. They got their dashboard in two days, and no personal identifiers left the primary.’

Note: Mention who you involved, such as security or the data protection officer. Showing that you know when a decision is not only yours to make is a plus.

They are two different implementations sitting under the same SQL layer, and they differ in the guarantees they give you.

• Transactions. InnoDB is fully ACID and supports COMMIT and ROLLBACK. MyISAM has no transactions at all — a half-finished multi-statement change stays half-finished.
• Locking. InnoDB locks individual rows, so concurrent writers to different rows do not block each other. MyISAM locks the whole table on write, which collapses under concurrent traffic.
• Crash recovery. InnoDB replays its redo log and comes back consistent. MyISAM tables must be repaired after an unclean shutdown and can lose data.
• Foreign keys. Supported by InnoDB, silently ignored by MyISAM.

InnoDB became the default in MySQL 5.5 because durability and row-level concurrency matter for essentially every real application. MyISAM survives only in legacy schemas and in a few read-only cases where its smaller footprint and full-text history were useful — and even that argument disappeared once InnoDB gained full-text indexes.

An InnoDB index is a B+ tree: keys are held in sorted order, all values live in the leaf nodes, and the leaves are linked so a range scan can walk sideways without returning to the root. Because it is sorted, the engine can find a value in roughly log(n) page reads instead of scanning every row.

Two kinds matter:

• The clustered index is the primary key, and the full row is stored in its leaves. There is exactly one per table.
• A secondary index stores the indexed columns plus the primary key value, so using one usually costs a second lookup back into the clustered index unless the index covers every column the query needs.

An index will be ignored when:

• You wrap the column in a function — WHERE YEAR(created_at) = 2024 cannot use an index on created_at, but WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01' can.
• You skip a leading column of a composite index. An index on (a, b, c) serves WHERE a = ? and WHERE a = ? AND b = ?, but not WHERE b = ? alone.
• The pattern is leading-wildcard, like LIKE '%term'.
• There is an implicit type conversion between the column and the value.
• The optimiser estimates the query will match a large fraction of the table, in which case a sequential scan is genuinely cheaper.

Isolation levels decide how much of other people's uncommitted or in-flight work your transaction is allowed to see. From weakest to strongest:

• READ UNCOMMITTED — you can read another transaction's uncommitted changes. Those are dirty reads, and they may be rolled back out from under you. Almost never appropriate.
• READ COMMITTED — you only see committed data, but two identical reads inside one transaction can return different values because someone committed in between. This is the default in PostgreSQL and Oracle.
• REPEATABLE READ — MySQL's default with InnoDB. Your transaction takes a consistent snapshot at its first read, so the same query returns the same rows all the way through. InnoDB additionally uses gap locks so phantom rows are largely prevented too, which is stronger than the SQL standard requires.
• SERIALIZABLE — transactions behave as if they ran one after another. Correct, but the extra locking costs concurrency.

Note: A very common follow-up is why READ COMMITTED is often preferred for high-write workloads: it takes fewer gap locks and therefore deadlocks less.

A join decides which rows survive when two tables are matched on a condition.

• INNER JOIN returns only rows that match on both sides. Non-matching rows from either table disappear.
• LEFT JOIN returns every row from the left table, with NULLs filled in where the right table has no match.
• RIGHT JOIN is the mirror image — every row from the right table. It is rarely used in practice because you can always rewrite it as a LEFT JOIN with the tables swapped, which reads more naturally.
• FULL OUTER JOIN returns every row from both sides. MySQL does not support it, so you emulate it with a LEFT JOIN UNIONed to a RIGHT JOIN.

Note: The classic trap is putting a filter on the right-hand table in the WHERE clause of a LEFT JOIN. WHERE b.status = 'active' throws away the NULL rows and silently turns your LEFT JOIN back into an INNER JOIN. Put that condition in the ON clause instead.

Replication copies changes from a source server to one or more replicas. The source writes every change into its binary log; each replica has an I/O thread that pulls those events into a local relay log, and an applier thread that replays them.

The three modes differ in how long the source waits before telling the client the commit succeeded:

• Asynchronous (the default) — the source commits and replies immediately, without waiting for any replica. Fastest, but if the source dies before a replica catches up, those transactions are lost.
• Semi-synchronous — the source waits until at least one replica confirms it has written the event to its relay log. It does not wait for the replica to apply it. This trades a little latency for a much smaller window of data loss.
• Group Replication — a group of servers agree on transactions using a consensus protocol, giving you automatic failover and either single-primary or multi-primary writes. This is the foundation of InnoDB Cluster.

Binary log formats also matter: ROW is the safe default, STATEMENT is compact but unsafe for non-deterministic functions, and MIXED switches between them.

EXPLAIN prints the plan the optimiser intends to use. The columns worth reading in order:

• type — the access method, and the first thing to check. Roughly best to worst: const, eq_ref, ref, range, index, ALL. Seeing ALL on a large table means a full scan.
• key — which index was actually chosen. NULL here alongside a large rows value is the classic missing-index signature.
• rows — the optimiser's estimate of rows examined at this step. Multiply across joined tables to see the real cost.
• filtered — the percentage of those rows expected to survive the WHERE clause.
• Extra — where the useful warnings live. Using filesort and Using temporary mean work is spilling out of the index; Using index is the good case, a covering index that never touches the table.

Note: EXPLAIN shows the plan; EXPLAIN ANALYZE actually runs the query and shows real timings and real row counts next to the estimates. When the estimate and the actual differ wildly, your table statistics are stale — run ANALYZE TABLE.

Normalisation organises a schema so that every fact is stored exactly once, which removes update anomalies.

• First normal form (1NF) — every column holds a single atomic value. No comma-separated lists, no repeating groups like phone1, phone2, phone3.
• Second normal form (2NF) — 1NF, and every non-key column depends on the whole primary key. This only bites with composite keys: if a table keyed on (order_id, product_id) also stores product_name, that column depends on half the key and belongs in the products table.
• Third normal form (3NF) — 2NF, and no non-key column depends on another non-key column. Storing city and state alongside pincode breaks it, because state is a fact about the pincode, not about the row.

When to denormalise: when a read path is measurably too slow and the join is the proven cause. Typical cases are a cached aggregate such as comment_count on a post, or a reporting table built for one dashboard. The price is that you now own the consistency problem — you must update the copy whenever the source changes, usually in the same transaction or through a scheduled rebuild.

All three remove data, but at different levels and with very different costs.

• DELETE is DML. It removes rows one at a time, accepts a WHERE clause, fires triggers, is fully logged, and can be rolled back inside a transaction. Because each row is logged, deleting millions of rows is slow and produces a large binary log.
• TRUNCATE is DDL. It drops and recreates the table, so it is far faster, takes no WHERE clause, fires no row triggers, resets AUTO_INCREMENT to 1, and causes an implicit commit — you cannot roll it back.
• DROP is DDL that removes the table itself: rows, structure, indexes, and permissions all go.

Note: A frequent follow-up is why DELETE does not release disk space back to the operating system. InnoDB marks the pages reusable inside the tablespace but does not shrink the file. To actually reclaim it you need OPTIMIZE TABLE, which rebuilds the table.

A deadlock is two transactions each holding a lock the other needs. InnoDB detects the cycle automatically, picks the transaction that has done less work, and rolls it back with error 1213. Nothing hangs forever.

How to diagnose: run SHOW ENGINE INNODB STATUS and read the LATEST DETECTED DEADLOCK section. It shows both transactions, the exact statements, and which locks each was holding and waiting for.

How to prevent:

• Take locks in a consistent order. Most deadlocks come from one code path updating table A then B while another does B then A. Agreeing an order across the codebase removes the cycle entirely.
• Keep transactions short. Never hold a transaction open across a network call or user input.
• Index the columns you filter on. Without an index, InnoDB locks far more rows than you intended, which widens the window enormously.
• Consider READ COMMITTED for write-heavy workloads, since it takes fewer gap locks.

Note: The most important point to make is that your application must retry a deadlocked transaction. Deadlocks are normal at concurrency and are not by themselves a bug.

CHAR(n) is fixed width — every value occupies n characters and shorter values are padded with spaces that are stripped on retrieval. VARCHAR(n) is variable width and stores a one or two byte length prefix plus the actual characters.

Use CHAR only when values really are a constant length: a country code, a hash of known width, an MD5 digest. Use VARCHAR for anything with a spread of lengths — names, emails, titles.

Choosing types generally, in priority order:

• Pick the smallest type that comfortably fits. Narrower rows mean more rows per page, which means fewer disk reads and a more effective buffer pool. Do not use BIGINT for a column that will never exceed a few million.
• Never store money in FLOAT or DOUBLE. Use DECIMAL, which is exact.
• Prefer NOT NULL where the value is genuinely mandatory. Nullable columns cost an extra bit and make every query author reason about three-valued logic.
• Store dates in DATE, DATETIME or TIMESTAMP, never in a VARCHAR. TIMESTAMP converts to UTC and is range-limited to 2038; DATETIME does not convert and has a much wider range.

Choose by meaning first. A JOIN combines rows from two tables and can multiply them. A subquery in IN or EXISTS only filters, so each outer row appears at most once.

• Use a JOIN when you need columns from the other table in the result, for example the customer name next to each order.
• Use EXISTS or IN when you only need to know whether a related row exists. If you find yourself writing a JOIN followed by DISTINCT to remove duplicates, you probably wanted a semi-join.
• Use a derived table or CTE to aggregate first and join second, which avoids summing values that the join has already duplicated.

-- Customers with at least one order in 2024
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
    AND o.created_at >= '2024-01-01'
);

-- Same result with a join needs DISTINCT
SELECT DISTINCT c.id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2024-01-01';

Performance. Subqueries got their bad name in MySQL 5.5 and earlier, where IN (SELECT ...) was often run once per outer row as a dependent subquery. Since 5.6 the optimiser rewrites IN subqueries as semi-joins, using strategies such as table pull-out, FirstMatch, LooseScan, materialisation and duplicate weed-out. MySQL 8.0.16 and later apply the same to EXISTS, and NOT EXISTS becomes an anti-join. Derived tables are merged into the outer query where possible.

So in MySQL 8, write the query that states your intent most clearly, then check with EXPLAIN. If the plan shows DEPENDENT SUBQUERY against a large table, rewrite it.

Note: Correlated scalar subqueries in the SELECT list, such as fetching the latest order date per customer, are still run per row. For large results, a join to a grouped derived table or a window function is usually faster.

A correlated subquery refers to a column of the outer query, so logically it is evaluated once for each outer row. A non-correlated subquery stands alone and can be evaluated once and reused.

-- Correlated: depends on p.category_id
SELECT p.name, p.price
FROM products p
WHERE p.price > (SELECT AVG(price)
                 FROM products x
                 WHERE x.category_id = p.category_id);

EXISTS versus IN. For positive tests they usually produce the same plan in MySQL 8, because both become semi-joins. EXISTS reads naturally when the check involves several columns or extra conditions, and it can stop at the first matching row.

NOT EXISTS versus NOT IN is where it really matters, because of NULLs:

-- Returns NO rows if any orders.customer_id is NULL
SELECT id FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- NULL-safe, and optimised as an anti-join
SELECT c.id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.id);

x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. The last comparison is UNKNOWN, so the whole condition can never be TRUE and the query silently returns nothing. This is a classic production bug that appears only when a nullable column gets its first NULL.

Guidelines:

• Prefer NOT EXISTS for ‘rows with no match’. The LEFT JOIN ... WHERE o.id IS NULL pattern is an equivalent alternative.
• Use IN freely with a short literal list, such as status IN ('paid', 'shipped').
• Make sure the correlated column, here orders.customer_id, is indexed. Otherwise every probe is a scan.

Note: If an interviewer asks why a NOT IN query ‘suddenly’ returned zero rows, NULL in the subquery is almost always the answer.

A window function calculates a value across a set of rows related to the current row, but unlike GROUP BY it does not collapse them. Every input row stays in the result with the extra computed column. The window is defined with OVER (PARTITION BY ... ORDER BY ...). MySQL has supported them since 8.0.

The three ranking functions differ only in how they treat ties. For scores 100, 90, 90 and 80:

• ROW_NUMBER always gives unique, consecutive numbers. Ties are broken arbitrarily unless you add a tie-breaker column to ORDER BY.
• RANK gives ties the same rank and then skips, like a sports league table.
• DENSE_RANK gives ties the same rank without gaps.

The most common interview task is top N per group. Window functions cannot appear in WHERE, because they are computed after filtering, so you wrap the query:

WITH ranked AS (
  SELECT e.*,
         DENSE_RANK() OVER (PARTITION BY dept_id
                            ORDER BY salary DESC) AS rnk
  FROM employees e
)
SELECT * FROM ranked WHERE rnk <= 3;

Pick the function that matches the business rule. ‘Exactly three rows per department’ needs ROW_NUMBER with a tie-breaker. ‘Everyone earning one of the top three salaries’ needs DENSE_RANK.

Note: Before MySQL 8, people emulated ranking with user variables such as @rn := @rn + 1. That behaviour was never guaranteed and is deprecated, so mention window functions as the modern answer.

Both are classic window-function tasks. Aggregate by month first, then apply the window over the monthly rows.

WITH monthly AS (
  SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
         SUM(total) AS revenue
  FROM orders
  GROUP BY month
)
SELECT month,
       revenue,
       SUM(revenue) OVER w AS running_total,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       ROUND(100 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS mom_pct,
       AVG(revenue) OVER (ORDER BY month
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3m
FROM monthly
WINDOW w AS (ORDER BY month
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ORDER BY month;

What each piece does:

• Running total. SUM() OVER (ORDER BY ...) with a frame from the first row to the current row.
• LAG and LEAD. LAG(col) reads the previous row in the window order and LEAD(col) the next. Both take an optional offset and default value, as in LAG(revenue, 12, 0) for the same month last year.
• NULLIF. This avoids division by zero. The first month has no previous value, so the percentage is NULL, which is the honest answer.
• Moving average. A sliding frame of the current row and the two before it.
• WINDOW clause. A named window keeps long queries readable when several columns share the same definition.

The frame trap. If you write ORDER BY without a frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with equal sort values as peers, so two orders at the same timestamp both show the combined total. Use an explicit ROWS frame, or a unique sort key, when you need a true row-by-row running total.

Note: Add PARTITION BY to restart the calculation per group, for example a running total per customer or per city.

A common table expression (CTE), available since MySQL 8.0, is a named temporary result set defined with WITH that exists only for one statement. It does the same job as a derived table in the FROM clause but is easier to read, and it can be referenced more than once in the same query.

WITH paid AS (
  SELECT customer_id, SUM(total) AS spend
  FROM orders WHERE status = 'paid'
  GROUP BY customer_id
)
SELECT c.name, p.spend
FROM paid p JOIN customers c ON c.id = p.customer_id
WHERE p.spend > (SELECT AVG(spend) FROM paid);

Here paid is used twice. The optimiser decides whether to merge the CTE into the outer query or materialise it once as an internal temporary table.

Recursive CTEs handle data with no fixed depth, such as category trees, org charts, bill-of-materials and threaded comments, or generating a series of rows. They have two parts joined by UNION ALL:

• The anchor member runs once and produces the starting rows.
• The recursive member refers to the CTE itself and runs repeatedly on the rows produced by the previous round, until it returns nothing.

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS depth,
         CAST(name AS CHAR(500)) AS path
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1,
         CONCAT(t.path, ' / ', c.name)
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY path;

Gotchas:

• The anchor decides column types, hence the CAST. Without it, the path would be truncated to the width of the first name.
• cte_max_recursion_depth (default 1000) stops runaway recursion if the data contains a cycle.
• Index parent_id, because every round joins on it.

Note: Generating a date series with a recursive CTE, then LEFT JOINing sales to it, is a neat way to show days with zero orders in a report.

A composite index is sorted by its first column, then by the second within equal values of the first, and so on. MySQL can only use a leftmost prefix of it, and it can seek precisely only until it reaches the first range condition. That gives a reliable rule of thumb:

1. Equality columns first (= or short IN lists).
2. Then the column you sort by or filter by range. Only one range column benefits fully from the index. Columns after it cannot narrow the seek, although index condition pushdown can still filter on them inside the index.
3. Optionally, extra columns to make the index covering.

SELECT id, total
FROM orders
WHERE customer_id = ?
  AND status = 'paid'
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

-- Good: two equalities, then the range and sort column
ALTER TABLE orders
  ADD INDEX idx_cust_status_created (customer_id, status, created_at);

With this index, MySQL jumps straight to the entries for one customer and status, walks them in created_at order backwards, and stops after 20 rows. There is no filesort and very little reading. If the index were (created_at, customer_id, status), it would have to scan every order since January and filter them.

Other points interviewers like to hear:

• Among equality columns, the order does not change the efficiency of this query, so put first the column that other queries also filter on, so one index serves several queries.
• An index on (a, b) makes an index on (a) alone redundant. sys.schema_redundant_indexes will find these for you.
• MySQL 8 supports descending index columns, which helps mixed sorts such as ORDER BY priority DESC, created_at ASC.
• Every index slows down writes and uses buffer pool memory, so design indexes around your most important queries, not every query.

Note: Confirm the result with EXPLAIN. key_len tells you how many index columns were actually used, and ‘Using filesort’ disappearing tells you the sort was satisfied.

A covering index contains every column a query needs, in its WHERE, SELECT, GROUP BY and ORDER BY clauses. MySQL can answer the query from the index alone, without the second lookup into the clustered index to fetch the full row. On a large table that removes a random read per row, which can make a query many times faster.

The InnoDB detail to mention: every secondary index already stores the primary key. So an index on (email) covers SELECT id, email FROM users WHERE email = ? without any extra columns.

-- Query
SELECT customer_id, SUM(total)
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

-- Covering index: filter, group, then the summed column
ALTER TABLE orders
  ADD INDEX idx_status_cust_total (status, customer_id, total);

Reading EXPLAIN:

• Extra: Using index means the query is covered and no table rows are read.
• Extra: Using index condition is different. It is index condition pushdown: the index filters rows early, but the full rows are still fetched.
• Extra: Using where; Using index means covered, with some filtering applied to the index entries.
• type: index together with Using index means a full scan of the index. That is cheaper than scanning the table, but it is still a scan.

Trade-offs. Wider indexes take more disk and buffer pool memory, and every insert and update has to maintain them. Adding columns to cover one query is worth it for hot, frequent queries such as a dashboard counter or an API list endpoint, not for a report that runs once a day. Also avoid covering with large TEXT or wide VARCHAR columns.

Note: SELECT * almost never benefits from a covering index. Naming only the columns you need is what makes covering possible.

Plain EXPLAIN shows the plan the optimiser intends to use, with estimated row counts, and does not run the query. EXPLAIN ANALYZE, added in MySQL 8.0.18, actually executes the query, times every step of the plan and reports what really happened.

The output is a tree of iterators, read from the innermost line outwards:

EXPLAIN ANALYZE
SELECT c.name, COUNT(*)
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
GROUP BY c.name;

-> Table scan on o
   (cost=10240 rows=98500)
   (actual time=0.06..84.2 rows=1250000 loops=1)

How to read each line:

• cost and rows in the first bracket are the optimiser’s estimates.
• actual time=A..B is the time in milliseconds to the first row and to the last row.
• rows in the second bracket is the average number of rows produced per loop, and loops is how many times the step ran. Total rows are rows multiplied by loops, which matters for the inner side of a nested-loop join.

Why the comparison matters. In the example the optimiser expected about 98,000 rows and got 1.25 million. When estimates are off by an order of magnitude, the optimiser has probably chosen the wrong join order or index. The usual causes and fixes are:

• Stale index statistics: run ANALYZE TABLE orders.
• Skewed data in a non-indexed column: build a histogram with ANALYZE TABLE ... UPDATE HISTOGRAM ON status.
• Conditions the optimiser cannot estimate, such as functions on columns: rewrite them as plain ranges.

Cautions. Because it runs the statement, a 10-minute query takes 10 minutes. Try heavy queries on a replica or staging copy. For a quick look at the plan without running anything, use EXPLAIN FORMAT=TREE.

Note: Look for the step where the time jumps. The slowest iterator, not the first line, is where your index or rewrite should go.

In InnoDB the table is the primary key index. Rows are stored in the leaf pages of a B+ tree ordered by the primary key, and every secondary index stores the primary key value as its pointer back to the row. That has two consequences for your choice of key.

• Size multiplies. The primary key is copied into every secondary index. A 36-byte CHAR(36) UUID key on a table with five secondary indexes adds that 36 bytes six times per row, compared with 8 bytes for a BIGINT. Bigger indexes mean fewer entries per page, more I/O and less of the working set in the buffer pool.
• Insert order matters. An AUTO_INCREMENT key always appends to the right-hand edge of the tree, so pages fill up neatly and the hot page stays in memory. A random UUID (version 4) lands on a random page each time. That causes page splits, pages left about half full, fragmentation, and random reads once the table is larger than memory. Insert throughput can drop sharply as the table grows.

Practical options:

1. Use BIGINT UNSIGNED AUTO_INCREMENT as the internal primary key and add a separate UUID column with a unique index for public identifiers in URLs and APIs.
2. If the UUID must be the key, store it as BINARY(16) rather than text, and use a time-ordered UUID such as version 7 or a ULID so inserts are roughly sequential.
3. For MySQL-generated version 1 UUIDs, UUID_TO_BIN(UUID(), 1) swaps the time fields so values sort in time order.

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id BINARY(16) NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_public_id (public_id)
);

Always define a primary key. Without one, InnoDB uses the first UNIQUE NOT NULL index, and failing that a hidden 6-byte row ID drawn from a global counter. Hidden keys cannot be used by your queries, and tables without a primary key make row-based replication very slow, because the replica may have to scan the table for each row change.

Note: Keep primary keys short, stable and never updated. Changing a primary key value physically moves the row and rewrites every secondary index entry.

Multi-version concurrency control means InnoDB keeps older versions of rows so that a plain SELECT sees a consistent snapshot without taking locks. Readers do not block writers and writers do not block readers.

How it works:

• Every row carries hidden fields, including DB_TRX_ID (the last transaction that changed it) and DB_ROLL_PTR (a pointer into the undo log).
• When a transaction updates a row, InnoDB changes the row in place and writes the previous version to the undo log. The versions form a chain.
• A consistent read uses a read view: a record of which transactions were committed when the snapshot was taken. If the current row version is too new for that view, InnoDB follows the roll pointer back through the undo chain until it finds a version the view is allowed to see.

When the snapshot is taken depends on the isolation level:

• REPEATABLE READ (the default): at the first consistent read in the transaction, and reused for the rest of it, so repeated reads return the same data.
• READ COMMITTED: a fresh snapshot for every statement.

Locking reads do not use the snapshot. SELECT ... FOR UPDATE, UPDATE and DELETE always work on the latest committed version and take locks. This is why a transaction can read an old balance with a plain SELECT and then update the current one. It is also why read-modify-write logic needs a locking read or an atomic UPDATE ... SET balance = balance - 100.

The operational catch: long transactions. The purge thread can only delete old versions once no open read view needs them. A transaction left open for hours, even an idle one from a forgotten console session, pins every version created since it began. The history list length grows, undo tablespaces swell, and reads slow down as they walk longer version chains.

-- Oldest open transactions
SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 5;

Note: Watch ‘History list length’ in SHOW ENGINE INNODB STATUS. A number that keeps rising into the millions almost always points to one long-running transaction.

InnoDB uses write-ahead logging. Changes are made to 16 KB pages in the buffer pool in memory, and a compact description of each change is written to the redo log. A commit only has to make sure the redo log reaches disk. The changed (dirty) pages are written to the data files later by background flushing, at checkpoints.

Crash recovery then works in two passes:

1. Replay the redo log from the last checkpoint, reapplying every change that was committed but not yet written to the data files.
2. Use the undo logs to roll back transactions that had not committed when the server stopped.

The doublewrite buffer protects against torn pages. If power fails halfway through writing a 16 KB page, the page on disk is part old and part new, and redo cannot repair a corrupt page. So InnoDB first writes pages to the doublewrite area, then to their real location. After a crash it can restore a clean copy from the doublewrite area and then apply redo.

innodb_flush_log_at_trx_commit controls how strict each commit is:

For a primary that also replicates, the safe pairing is innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1, so the redo log and binary log agree after a crash. Values of 0 or 2 are sometimes acceptable on a replica you can rebuild, or for bulk loads.

Redo log size matters for performance. Too small and InnoDB is forced into aggressive flushing at checkpoints, which shows up as write stalls. In MySQL 8.0.30 and later it is set with innodb_redo_log_capacity.

Note: This is the D in ACID. Relaxing the flush setting trades durability for commit speed, and that trade should be a conscious business decision, not a copied config line.

InnoDB locks index records, not rows in the abstract, and it has three kinds that interviewers expect you to name:

• Record lock: a lock on a single index entry.
• Gap lock: a lock on the space between two index entries. It holds nothing already there; it only stops other transactions inserting into that gap.
• Next-key lock: a record lock plus a lock on the gap before it. This is InnoDB’s default for locking reads and for UPDATE and DELETE under REPEATABLE READ.

Why they exist. Under REPEATABLE READ, a locking range query must return the same rows if it is repeated in the same transaction. Locking only the existing rows would still let someone insert a new row into the range, which is a phantom. Locking the gaps prevents that.

-- Session A
START TRANSACTION;
SELECT * FROM orders
WHERE amount BETWEEN 1000 AND 2000 FOR UPDATE;

-- Session B blocks until A commits,
-- because 1500 falls inside a locked gap
INSERT INTO orders (amount) VALUES (1500);

Consequences to mention:

• Equality search on a unique index for a row that exists takes only a record lock. Gap locks come from ranges, non-unique indexes and searches for rows that do not exist.
• The classic deadlock. Two sessions each run SELECT ... FOR UPDATE for an ID that does not exist yet (both get the same gap lock, since gap locks do not conflict with each other), then both INSERT it. Each insert waits for the other’s gap lock, and InnoDB rolls one back.
• Missing indexes widen locks. Locks are placed on every index record that is scanned, so an UPDATE with no usable index can lock essentially the whole table.
• READ COMMITTED turns off gap locking for ordinary searches and scans (it is still used for foreign-key and duplicate-key checks). That is why some high-concurrency systems choose it.

To see what is locked right now, query performance_schema.data_locks and data_lock_waits. After a deadlock, SHOW ENGINE INNODB STATUS shows the last one.

Note: If asked how to reduce lock waits, say: index the columns in your WHERE clause, keep transactions short, and touch rows in a consistent order.

Both are locking reads. They read the latest committed version of each row, not the transaction’s snapshot, and hold their locks until the transaction commits or rolls back.

• FOR SHARE (formerly LOCK IN SHARE MODE) takes shared locks. Other transactions can also read with FOR SHARE, but none can update or delete the rows until you finish. Use it to make sure a row you depend on does not change or vanish, for example checking that a parent account is active before inserting a child row.
• FOR UPDATE takes exclusive locks. Other locking reads and writes on those rows wait. Plain non-locking SELECTs still run, because they read from the MVCC snapshot. Use it for read-then-modify logic such as reserving a seat or deducting stock.

MySQL 8.0 added two modifiers that change what happens when a row is already locked:

• NOWAIT fails immediately with an error instead of waiting. It suits interactive actions where it is better to tell the user ‘someone else is editing this’ than to hang.
• SKIP LOCKED quietly leaves out locked rows. It is ideal for work queues, where many workers each need a different batch of jobs.

START TRANSACTION;
SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;

UPDATE jobs SET status = 'running', worker = 'w3'
WHERE id IN (101, 102, 103);  -- ids returned above
COMMIT;

Each worker gets its own ten jobs without waiting for the others. An index on (status, id) is essential, otherwise the scan locks far more than ten rows.

Cautions:

• SKIP LOCKED deliberately returns an incomplete view, so never use it for reports or totals.
• Locks only work inside a transaction. With autocommit on, a lone SELECT ... FOR UPDATE releases its locks at the end of the statement.
• Keep the time between locking and committing short, and never wait for user input or an HTTP call while holding row locks.

Note: Mentioning SKIP LOCKED as a way to build a simple, reliable job queue in MySQL without extra infrastructure is a strong practical point.

Both prevent lost updates, where two users read the same row and the second save silently overwrites the first.

• Pessimistic locking assumes a conflict is likely and locks the row up front with SELECT ... FOR UPDATE, so others wait. It works well for short, high-contention operations inside a single request, such as decrementing stock during a flash sale. It is unsuitable when the ‘transaction’ spans user think time, because you cannot hold a database lock while someone edits a form for five minutes.
• Optimistic locking assumes conflicts are rare. You take no lock while reading. When you write, you check the row has not changed since you read it, and if it has, you reject or retry.

Implementation with a version column:

ALTER TABLE products ADD COLUMN version INT UNSIGNED NOT NULL DEFAULT 0;

-- 1. Read, and remember the version (say 7)
SELECT id, name, price, version FROM products WHERE id = 42;

-- 2. Save only if nobody else saved in between
UPDATE products
SET price = 499, version = version + 1
WHERE id = 42 AND version = 7;

Then check the affected row count. If it is 1, the save succeeded. If it is 0, someone else changed the row first: reload it, show the user the conflict, or retry automatically when the change can be safely merged. Because the version always increases, MySQL’s ‘rows changed’ count is reliable here. An updated_at timestamp can be used the same way, but it needs enough precision, such as DATETIME(6), to avoid two saves in the same second.

Often you need neither. For counters and balances, an atomic conditional update does the read and the write in one statement:

UPDATE inventory
SET qty = qty - 1
WHERE product_id = 42 AND qty >= 1;

Choosing: optimistic for edit screens, admin panels and APIs where conflicts are rare and requests are stateless. Pessimistic for short, contended critical sections. Atomic updates wherever the logic fits into one statement.

Note: ORMs support this directly. Doctrine has a Version mapping, and in Laravel you add the version condition to the update query and check the returned count.

Autocommit. By default autocommit = 1, so every statement is its own transaction and commits as soon as it succeeds. START TRANSACTION (or BEGIN) suspends autocommit until you COMMIT or ROLLBACK. Setting autocommit = 0 for a session means a transaction is always open, and nothing is saved until you commit. That is a common source of ‘my changes disappeared’ and of long-held locks from tools that use this setting.

Savepoints let you undo part of a transaction:

START TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (7, 1500);
SAVEPOINT before_coupon;
UPDATE coupons SET used = used + 1 WHERE code = 'DIWALI10';
-- coupon rule failed in application code
ROLLBACK TO SAVEPOINT before_coupon;
COMMIT;  -- the order is saved, the coupon change is not

RELEASE SAVEPOINT removes a savepoint without rolling back. Frameworks use savepoints to support ‘nested’ transactions: Laravel’s DB::transaction() inside another transaction creates a savepoint rather than a new transaction.

Implicit commits are the part that catches people. Some statements silently commit the current transaction before they run, and cannot be rolled back:

• DDL: CREATE, ALTER, DROP, RENAME, TRUNCATE.
• Account statements such as CREATE USER and GRANT.
• LOCK TABLES, and a new START TRANSACTION while one is already open.

So, unlike PostgreSQL, MySQL cannot wrap a schema migration in a transaction and roll it back if a later step fails. Keep each migration small and make it safe to re-run.

Errors do not roll back the whole transaction. A failed statement, such as a duplicate key, rolls back only that statement, and the transaction stays open. The application must catch the error and issue ROLLBACK itself. The exception is a deadlock, where InnoDB rolls back the entire transaction, so the correct response is to retry all of it.

Note: In PHP 8, calling PDO commit() after a DDL statement has already committed implicitly throws ‘There is no active transaction’. That surprises many developers writing migrations.

The goal is to find the queries that cost the most in total, not simply the single slowest one. A 50 ms query run 200,000 times an hour usually matters far more than a 20-second report run once a night.

1. Capture. Turn on the slow query log with a threshold that suits your traffic:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;   -- seconds; 0 captures everything briefly
SET GLOBAL log_slow_extra = ON;     -- 8.0.14+: adds more fields per entry

2. Aggregate. Run pt-query-digest over the log. It normalises queries into fingerprints (literals replaced with placeholders) and ranks them by total response time, with call count, average and 95th percentile latency, and rows examined against rows sent.

3. Or ask Performance Schema directly. It aggregates statements continuously with no log file, and the sys schema makes it readable:

SELECT query, exec_count, total_latency, avg_latency,
       rows_examined_avg, rows_sent_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

4. Diagnose the top items. For each one:

• Compare rows examined with rows sent. Examining 500,000 rows to return 20 means a missing or badly ordered index.
• Run EXPLAIN or EXPLAIN ANALYZE and look for full scans, filesorts and temporary tables.
• Check the call count. A cheap query called thousands of times per page is an N+1 problem in the application, and the fix is in code, not in the database.

5. Fix, measure and repeat. Add or adjust an index, rewrite the query, cache the result, or batch the calls. Then compare the same digest before and after, so you can report a result such as ‘p95 fell from 800 ms to 40 ms’.

Note: Use sys.schema_unused_indexes as well. Dropping indexes nobody uses speeds up writes, and interviewers like candidates who tune in both directions.

LIMIT 20 OFFSET 100000 does not jump to row 100,001. MySQL has to produce and throw away the first 100,000 rows in sort order and then return the next 20. Page 1 is fast, page 5,000 is slow, and search-engine crawlers or scripts that walk every page can cause serious load.

Keyset pagination (also called the seek method) remembers where the previous page ended and asks for rows after that point, which an index can find directly:

-- First page
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: pass the last row's created_at and id
SELECT id, title, created_at
FROM articles
WHERE created_at < '2024-06-01 10:15:00'
   OR (created_at = '2024-06-01 10:15:00' AND id < 98231)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Supporting index
ALTER TABLE articles ADD INDEX idx_created_id (created_at, id);

Every page now costs the same: an index seek plus 20 rows, whether it is page 2 or page 20,000.

Details that matter:

• Always add a unique tie-breaker such as id. Without it, rows sharing a timestamp can be skipped or repeated across pages.
• The expanded OR form above is the safest to write. A row comparison such as (created_at, id) < (?, ?) is shorter, but check with EXPLAIN that your MySQL version uses a range scan for it.
• The API returns an opaque cursor, often the last values base64-encoded, instead of a page number.

Trade-offs. You cannot jump straight to page 347, and showing the total number of pages still needs a separate count. In return, pages stay stable when new rows arrive, with no duplicates or gaps while a user scrolls. That makes it the right choice for infinite scroll, feeds and API exports.

Fallback when you must keep OFFSET: a deferred join skips rows using a narrow covering index and fetches full rows only for the final 20:

SELECT a.* FROM articles a
JOIN (SELECT id FROM articles
      ORDER BY created_at DESC LIMIT 100000, 20) x USING (id);

Note: Laravel supports this out of the box with cursorPaginate(), which is a handy real-world reference in an interview.

Partitioning splits one logical table into several physical pieces inside the same server, based on the value of a partitioning expression. Queries still address a single table. It is different from sharding, which spreads data across separate servers.

Types:

• RANGE / RANGE COLUMNS: by value ranges, most often dates.
• LIST / LIST COLUMNS: by explicit value lists, such as region codes.
• HASH / KEY: spread evenly by a hash of a column.

CREATE TABLE events (
  id BIGINT NOT NULL AUTO_INCREMENT,
  created_at DATETIME NOT NULL,
  payload JSON,
  PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p2024_05 VALUES LESS THAN ('2024-06-01'),
  PARTITION p2024_06 VALUES LESS THAN ('2024-07-01'),
  PARTITION pmax     VALUES LESS THAN (MAXVALUE)
);

-- Remove a whole month instantly
ALTER TABLE events DROP PARTITION p2024_05;

Where it genuinely helps:

• Data lifecycle. This is the biggest win. Dropping or truncating an old partition is a quick metadata operation, where a DELETE of 50 million rows would take hours, bloat the undo log and lag every replica.
• Partition pruning. When the WHERE clause includes the partitioning column, MySQL reads only the matching partitions. EXPLAIN lists them in the partitions column.
• Maintenance. You can rebuild or analyse one partition at a time.

Where it does not help, or hurts:

• It is not a replacement for indexes. A query that does not filter on the partition key has to search every partition, which can be slower than one table.
• Every unique key, including the primary key, must include all partitioning columns. That is why the example key is (id, created_at), and it means you cannot enforce uniqueness on id alone.
• Partitioned InnoDB tables cannot have foreign keys, in either direction.
• Many partitions add overhead for opening files and planning queries.

A good rule: partition time-series, log and event tables that you purge by age and query by date range. For ordinary OLTP tables, good indexing wins.

Note: Keep a MAXVALUE catch-all partition and a scheduled job that adds next month’s partition ahead of time, or inserts for new dates will pile into the catch-all.

The JSON data type, available since MySQL 5.7, validates documents on insert and stores them in an optimised binary format, so reading one key does not mean re-parsing the whole text. Invalid JSON is rejected.

Querying:

• col->'$.path' is shorthand for JSON_EXTRACT() and returns a JSON value (strings keep their quotes).
• col->>'$.path' also unquotes, returning plain text you can compare.
• JSON_CONTAINS(), JSON_OVERLAPS() and MEMBER OF() test arrays, and JSON_TABLE() turns a JSON array into rows you can join.
• JSON_SET(), JSON_REPLACE() and JSON_REMOVE() can update in place without rewriting the whole document.

Indexing. A JSON column cannot be indexed directly, so you index an expression derived from it.

-- 1. Generated column plus a normal index
ALTER TABLE products
  ADD COLUMN brand VARCHAR(100)
      AS (attrs->>'$.brand') VIRTUAL,
  ADD INDEX idx_brand (brand);

SELECT id, name FROM products WHERE brand = 'Tata';

-- 2. Multi-valued index on an array (8.0.17+)
ALTER TABLE products
  ADD INDEX idx_tags ((CAST(attrs->'$.tags' AS CHAR(40) ARRAY)));

SELECT id FROM products
WHERE 'organic' MEMBER OF (attrs->'$.tags');

MySQL 8.0.13 and later also allow functional indexes directly on an expression. For JSON strings you need a CAST(... AS CHAR(n)), with a matching collation, and the query must use exactly the same expression for the index to be chosen. A generated column is often easier to maintain, and it gives the value a readable name.

When to use JSON. It suits attributes that vary by product type, API payloads you store for audit, and settings blobs. It is a poor home for data you join on, enforce constraints on or report on heavily. If you keep filtering by a key, promote it to a real column. Large documents also make every row read heavier.

Note: A good interview line is: model the stable, queried fields relationally and use JSON for the flexible remainder, then index the few JSON paths that turn out to be hot.

Character set. For historical reasons, MySQL’s utf8 means utf8mb3: at most 3 bytes per character. It cannot store 4-byte characters such as emoji and some rarer CJK characters. Depending on sql_mode, those are rejected with an error or silently truncated. utf8mb4 is real, complete UTF-8, and it has been the default since MySQL 8.0, with the collation utf8mb4_0900_ai_ci. Use it for all new tables.

Collation decides how strings are compared and sorted. The suffix tells you the rules:

• _ci: case-insensitive, so 'Rahul' = 'rahul' is true.
• _ai: accent-insensitive, so 'resume' equals the accented spelling.
• _cs, _as or _bin: case-sensitive, accent-sensitive or byte-by-byte.

Why it matters in practice:

• Unique indexes follow the collation. Under a _ci collation, 'Rahul@x.com' and 'rahul@x.com' count as duplicates. That is usually what you want for emails and usernames, but not for case-sensitive tokens, which need _bin.
• Mixed collations break joins. Joining columns with different collations gives ‘Illegal mix of collations’, or forces a conversion that stops the index being used.
• Trailing spaces. The newer 0900 collations are NO PAD, so 'a ' and 'a' are different. Older PAD SPACE collations treat them as equal.
• Index size. utf8mb4 reserves 4 bytes per character, so an index on VARCHAR(255) can take 1,020 bytes. The InnoDB key limit is 3,072 bytes with the DYNAMIC row format, which matters for long composite indexes.

The connection matters too. Tables in utf8mb4 still show garbled text if the client connects as latin1. In PHP, put charset=utf8mb4 in the PDO DSN.

ALTER TABLE comments
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;   -- rebuilds the table

Note: If an interviewer asks why a user’s emoji turned into question marks, the answer is almost always a utf8mb3 column or a connection that is not using utf8mb4.

There are two families of backup, and serious setups usually use both.

Logical backups write SQL statements or data files:

• mysqldump --single-transaction --routines --triggers --events takes a consistent InnoDB snapshot without locking tables, because it reads inside one REPEATABLE READ transaction.
• MySQL Shell’s util.dumpInstance() and util.loadDump() do the same in parallel and are much faster on large data.
• Pros: portable across versions, and you can restore a single table. Cons: restoring hundreds of gigabytes is slow, because every index has to be rebuilt.

Physical backups copy the data files:

• Percona XtraBackup or MySQL Enterprise Backup copy InnoDB files while the server runs and use the redo log to make the copy consistent.
• Storage snapshots, such as LVM or cloud volume snapshots, are another option.
• Pros: restores are close to disk-copy speed. Cons: tied to the same major version and platform.

Point-in-time recovery (PITR) handles the ‘someone ran DELETE without a WHERE at 3:42 p.m.’ case. You need the binary log enabled and kept long enough (binlog_expire_logs_seconds).

1. Restore the most recent full backup. It records the binlog file and position, or the GTID set, it corresponds to.
2. Replay the binary logs from that point up to just before the bad statement.

mysqlbinlog --start-position=154 \
  --stop-datetime='2024-06-10 15:41:59' \
  binlog.000412 binlog.000413 | mysql -u admin -p

Practices interviewers listen for:

• Take backups from a replica so the primary is not loaded.
• A replica is not a backup. A DROP TABLE replicates within milliseconds.
• Keep copies off-site and encrypted, and follow a retention policy.
• Test restores regularly. A backup you have never restored is only a hope. Automate a restore into a scratch server and run checks against it.
• Know your recovery time and recovery point objectives, and make sure the backup design meets them.

Note: Delayed replicas, configured with SOURCE_DELAY of an hour or so, give a fast way to recover from human error without a full restore.

Start by knowing which of MySQL’s online DDL algorithms your change will use, and ask for it explicitly, so MySQL raises an error instead of silently falling back to a blocking copy.

• ALGORITHM=INSTANT changes only metadata and completes in milliseconds whatever the table size. In MySQL 8.0 this covers adding a column (at any position from 8.0.29), dropping a column (8.0.29), renaming a column, and changing a default.
• ALGORITHM=INPLACE, LOCK=NONE rebuilds or builds in the background while reads and writes continue, for example adding a secondary index.
• ALGORITHM=COPY copies the whole table and blocks writes for the duration. Changing a column’s data type is the usual example, and this is what you must avoid on a hot table.

SET SESSION lock_wait_timeout = 5;

ALTER TABLE orders
  ADD COLUMN source VARCHAR(20) NULL,
  ALGORITHM=INSTANT;

ALTER TABLE orders
  ADD INDEX idx_status_created (status, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

The metadata lock trap. Even an instant ALTER needs an exclusive metadata lock for a moment. If one long transaction is holding a shared metadata lock on the table, the ALTER waits, and every new query on that table then queues behind the ALTER. A two-millisecond change turns into a full outage. So check information_schema.innodb_trx for long transactions first, and set a short lock_wait_timeout so the ALTER gives up and can be retried rather than blocking everyone.

Replicas. An in-place ALTER that runs for an hour on the primary replays for an hour on each replica, where it can block the applier and build up replication lag.

When native DDL is not enough, use an online schema change tool:

• gh-ost creates a shadow table, copies rows in chunks, follows ongoing changes from the binary log, throttles on replica lag, can be paused, and finishes with an atomic rename.
• pt-online-schema-change does the same using triggers, which is simpler but adds write overhead.

Pair any of these with the expand and contract pattern: add the new column, deploy code that writes both, backfill in batches, switch reads, and drop the old column in a later release.

Note: Always rehearse on a production-sized copy. Timing, disk space and lag surprises are far cheaper to discover there.

ONLY_FULL_GROUP_BY has been part of the default sql_mode since MySQL 5.7. It rejects a grouped query whose SELECT, HAVING or ORDER BY refers to a column that is neither in the GROUP BY, nor aggregated, nor functionally dependent on the grouping columns.

SELECT customer_id, city, SUM(total)
FROM orders
GROUP BY customer_id;
-- ERROR 1055: Expression #2 of SELECT list is not in
-- GROUP BY clause and contains nonaggregated column 'city'

Why it exists. Older MySQL accepted this and returned the city from an arbitrary row in each group. If a customer had orders delivered to two cities, the output was silently unpredictable. The mode turns a hidden bug into a visible error.

Fixes, depending on what you actually mean:

• The column really defines the group: add it to GROUP BY customer_id, city.
• Any value will do, because they are all the same: say so explicitly with ANY_VALUE(city).
• You want one specific value: use an aggregate such as MAX(city), or use MIN(created_at) for the first order date.
• You want the whole latest row per group, which is the most common real case, use a window function:

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

Functional dependency helps. If you group by a table’s primary key, MySQL knows every other column of that table has exactly one value per group, so this is allowed:

SELECT c.id, c.name, c.email, COUNT(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

What not to do: remove ONLY_FULL_GROUP_BY from sql_mode to make old code run. That brings back the nondeterministic results, and they tend to change after an upgrade or a new index.

Note: This error often appears when moving legacy PHP apps from MySQL 5.6 to 5.7 or 8.0. Explaining why it is a correctness fix, not an annoyance, shows real understanding.

NULL means ‘unknown’, and SQL uses three-valued logic: TRUE, FALSE and UNKNOWN. Most surprises follow from that.

Comparisons

• NULL = NULL, NULL <> 5 and 5 > NULL all return NULL, not TRUE or FALSE. WHERE keeps only rows where the condition is TRUE, so WHERE phone = NULL never matches anything.
• Use IS NULL and IS NOT NULL, or MySQL’s NULL-safe equality <=>, where NULL <=> NULL is 1.
• WHERE status <> 'cancelled' also leaves out rows where status is NULL, which often surprises people writing reports.

Expressions

• Arithmetic with NULL gives NULL, so price * qty is NULL if either is NULL.
• CONCAT('a', NULL) is NULL, while CONCAT_WS(' ', first, middle, last) skips NULLs, which is handy for names.
• COALESCE(a, b, 0) returns the first non-NULL value, and IFNULL(a, 0) is the two-argument form.

Aggregates

• Aggregates ignore NULLs. COUNT(*) counts rows, but COUNT(phone) counts only non-NULL phones.
• AVG(rating) divides by the number of non-NULL ratings, not all rows. That is usually right, but be deliberate about it.
• SUM() over no rows, or over only NULLs, returns NULL rather than 0. Wrap it: COALESCE(SUM(total), 0).

Sorting and grouping

• NULLs sort first in ASC and last in DESC. To put them last in ascending order: ORDER BY col IS NULL, col.
• GROUP BY and DISTINCT treat all NULLs as one group.

Indexes and constraints

• A UNIQUE index allows many NULLs, because NULL is not equal to NULL. A unique (user_id, deleted_at) key therefore does not stop duplicate active rows where deleted_at is NULL.
• IS NULL can use an index in InnoDB.

Design advice: declare columns NOT NULL with sensible defaults unless ‘unknown’ is genuinely meaningful. It makes queries simpler and removes a whole class of bugs.

Note: The NOT IN trap, where one NULL in a subquery makes the query return no rows, comes from the same three-valued logic. It makes a good example to finish on.

Common causes of lag

• Big transactions. A single DELETE of 10 million rows commits once on the primary, and the replica can only start applying it after that, so it replays as one long block.
• Limited parallel apply. Older setups applied changes on a single thread while the primary accepted writes from hundreds of connections. MySQL 8.0 uses multi-threaded replicas (replica_parallel_workers, 4 by default from 8.0.27) with dependency tracking based on write sets.
• Tables without a primary key. With row-based replication, the replica may have to scan the table to find each changed row.
• Long DDL that blocks the applier, heavy reporting queries on the replica, or weaker replica hardware.

Measuring it

• SHOW REPLICA STATUS shows Seconds_Behind_Source. It is useful but coarse: it reads 0 when the replica is idle or disconnected, and it jumps around during big transactions.
• A heartbeat table, where the primary writes a timestamp every second (for example with pt-heartbeat), gives a true end-to-end number.
• Comparing GTID sets, and performance_schema.replication_applier_status_by_worker, show exactly what is applied and what is waiting.

Read-after-write consistency. A user saves their profile, the next page reads from a lagging replica, and the old data appears. Options:

1. Sticky reads. After a user writes, send that user’s reads to the primary for a short time, or for the rest of the request. Laravel’s 'sticky' => true database option does this within a request.
2. GTID waits. Record the GTID of the write, then on the replica call WAIT_FOR_EXECUTED_GTID_SET(gtid, 1) before reading. Proxies such as ProxySQL can do this automatically.
3. Route by importance. Balances, order status and anything the user just changed come from the primary. Catalogue pages and analytics can tolerate a second or two of lag.

Reducing lag at the source: delete and update in batches of a few thousand rows, give every table a primary key, enable parallel apply, and keep heavy analytics on a separate replica.

Note: Alert on lag trends rather than single spikes, and remove a replica from the read pool automatically when its lag goes past a threshold.

A many-to-many relationship, such as students and courses, needs a third junction table (also called a link, bridge or associative table) holding one row per pairing.

CREATE TABLE enrolments (
  student_id  BIGINT UNSIGNED NOT NULL,
  course_id   BIGINT UNSIGNED NOT NULL,
  enrolled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  grade       CHAR(2) NULL,
  PRIMARY KEY (student_id, course_id),
  KEY idx_course (course_id),
  CONSTRAINT fk_enrol_student FOREIGN KEY (student_id)
    REFERENCES students (id) ON DELETE CASCADE,
  CONSTRAINT fk_enrol_course FOREIGN KEY (course_id)
    REFERENCES courses (id) ON DELETE RESTRICT
);

Why these keys:

• Composite primary key (student_id, course_id). It stops the same student enrolling twice, and because InnoDB stores rows in primary key order, ‘all courses for a student’ is a single range read.
• Secondary index on course_id. This serves the reverse question, ‘all students on a course’. InnoDB secondary indexes include the primary key columns, so this index is effectively (course_id, student_id) and covers the lookup.
• Foreign keys keep the pairs valid. Choose the ON DELETE rule on purpose: cascading enrolments when a student is deleted may be fine, while a course with enrolments probably should not be deletable.
• Relationship attributes such as enrolled_at and grade belong here, because they describe the pairing and not either entity.

Surrogate key or composite key? Some teams add an id column because their ORM prefers it. That works, but you must still add UNIQUE (student_id, course_id), or duplicates will creep in. If the relationship becomes an entity in its own right, with its own children such as attendance records, a surrogate key is often cleaner.

-- Students on course 12, with their grades
SELECT s.name, e.grade
FROM enrolments e
JOIN students s ON s.id = e.student_id
WHERE e.course_id = 12;

Note: Laravel calls this a pivot table and handles it with belongsToMany(). Extra columns are exposed with withPivot(), which is a useful link to make if the role is PHP-heavy.

Work in four steps: find, protect, delete, prevent.

1. Find the duplicates. First decide what ‘duplicate’ means. Here it is the same email address.

SELECT email, COUNT(*) AS copies, MIN(id) AS keep_id
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;

2. Protect yourself. Copy the rows you are about to delete into an archive table, and check whether child tables such as orders reference the extra IDs. If they do, repoint them to the surviving row first, or the foreign keys will block the delete, or worse, cascade it.

3. Delete all but one. Two common ways to keep the lowest id:

-- Self-join delete
DELETE u1
FROM users u1
JOIN users u2
  ON u1.email = u2.email
 AND u1.id > u2.id;

-- Window function, MySQL 8
DELETE FROM users
WHERE id IN (
  SELECT id FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
  ) AS d
  WHERE rn > 1
);

The extra derived table d is needed because MySQL does not let you delete from a table while selecting from that same table directly in a subquery (error 1093). Wrapping it in a derived table forces MySQL to materialise the list first. The window-function version is also easier to adapt when you want to keep the newest row, or the one with the most complete data.

On a large table, delete in batches, for example by adding AND u1.id BETWEEN ? AND ? and looping, so you do not hold locks for minutes or cause replication lag.

4. Prevent it happening again.

ALTER TABLE users ADD UNIQUE KEY uq_users_email (email);

Then fix the application code that created the duplicates. Usually it checks ‘does this exist?’ and then inserts, which races under concurrent requests. With the unique key in place, catch the duplicate-key error, or use INSERT ... ON DUPLICATE KEY UPDATE.

Note: Normalise before comparing. With a case-sensitive collation, or stray spaces, you may need LOWER(TRIM(email)) to find the duplicates that really matter.

MySQL uses a cost-based optimiser. For each candidate plan (which index, which join order, whether to sort) it estimates how many rows will be read and what that will cost, then picks the cheapest. The plan is only as good as its estimates, and these come from two places:

• Index dives. For range and equality conditions, MySQL can probe the index to estimate how many rows fall in each range. This is accurate, but for long IN lists beyond eq_range_index_dive_limit (200 by default) it falls back to statistics.
• Index statistics. InnoDB samples a number of pages (innodb_stats_persistent_sample_pages, 20 by default) to estimate the number of distinct values. After big data changes, or with skewed data, these can be badly wrong. ANALYZE TABLE refreshes them.

Histograms (MySQL 8.0) describe how values are distributed in columns that are not indexed. That helps the optimiser estimate filters and choose join order, for example knowing that 95% of orders are ‘delivered’ and only 0.1% are ‘disputed’:

ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS;

SELECT column_name, JSON_EXTRACT(histogram, '$."histogram-type"')
FROM information_schema.column_statistics
WHERE table_name = 'orders';

Histograms cost nothing on writes because they are not maintained automatically, so rebuild them on a schedule.

Hints, for when you know better:

• Index hints: USE INDEX, FORCE INDEX, IGNORE INDEX.
• Optimiser hints in comments, which are more precise: SELECT /*+ INDEX(o idx_status_created) JOIN_ORDER(o, c) */ ...
• Invisible indexes (ALTER TABLE ... ALTER INDEX idx INVISIBLE) hide an index from the optimiser without dropping it. They are ideal for testing whether an index can safely be removed.

Use hints as a last resort. A hint freezes a plan that was right for today’s data. As the data changes, it can become the wrong plan, and nobody remembers it is there. First try refreshing statistics, adding a histogram, rewriting the condition so it can use an index, or creating a better composite index.

Note: When a query ‘suddenly’ gets slow with no code change, suspect a plan flip caused by changed statistics. Comparing EXPLAIN output from before and after is the quickest way to prove it.

All three deal with an insert that would violate a PRIMARY KEY or UNIQUE index, but they behave very differently.

INSERT ... ON DUPLICATE KEY UPDATE inserts the row, or if the key already exists, updates the existing row in place. This is the true upsert and the usual right choice.

INSERT INTO daily_stats (page_id, day, views)
VALUES (42, '2024-06-10', 1) AS new
ON DUPLICATE KEY UPDATE views = daily_stats.views + new.views;

The row alias (AS new) is the MySQL 8.0.19+ syntax. The older VALUES(views) function is deprecated. The affected-row count tells you what happened: 1 for an insert, 2 for an update, and 0 if the update changed nothing.

REPLACE INTO deletes the conflicting row and inserts a new one. That has side effects people do not expect:

• Columns you did not supply are reset to their defaults, not kept.
• An AUTO_INCREMENT key gets a new value, which breaks references to the old ID.
• Delete triggers fire, and ON DELETE CASCADE foreign keys can wipe out child rows.

INSERT IGNORE skips rows that conflict, but it also turns many other errors into warnings: values truncated to fit, invalid dates, NULL in a NOT NULL column. It can quietly store bad data or drop rows. Use it only when ‘insert if absent, otherwise do nothing’ is exactly what you want, and check SHOW WARNINGS.

Other caveats: if a table has more than one unique key, a row can match different existing rows on different keys, so keep upsert targets to one unique key. All three can use up auto-increment values even when no row is inserted. Under heavy concurrency, upserts take next-key locks and can deadlock, so be ready to retry.

Note: In Laravel, upsert() compiles to ON DUPLICATE KEY UPDATE on MySQL, which makes a practical example if the interviewer works with PHP.

Both types hold a date and a time, but they differ in what is stored and whether time zones are applied.

Both support fractional seconds, such as DATETIME(6), and both support DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP for automatic created and updated columns.

The key behaviour to explain. With TIMESTAMP, the same stored value displays differently depending on the connection’s time_zone:

SET time_zone = '+00:00';
INSERT INTO t (ts, dt) VALUES ('2024-06-10 09:00:00', '2024-06-10 09:00:00');

SET time_zone = '+05:30';
SELECT ts, dt FROM t;
-- ts: 2024-06-10 14:30:00  (converted to IST)
-- dt: 2024-06-10 09:00:00  (unchanged)

Recommended approach:

• Store UTC everywhere. Set the connection time zone to UTC in the application (SET time_zone = '+00:00', or the framework’s setting) and keep PHP’s default time zone consistent with it.
• Convert at the edges. Display in the user’s zone, such as Asia/Kolkata, in the application or template layer. Convert user input back to UTC before saving.
• Prefer DATETIME for new designs, both because of the 2038 limit and because its value does not depend on session settings. TIMESTAMP remains fine for audit columns if every connection uses the same time zone.
• Use DATE for values with no time part, such as a date of birth, so a time zone shift can never move it to a different day.
• For future local events, such as a 10 a.m. class in Mumbai, store the local time and the zone name, because offsets can change.

Gotchas: the server default time_zone = SYSTEM depends on the host’s configuration. CONVERT_TZ() with named zones returns NULL until the time zone tables are loaded with mysql_tzinfo_to_sql.

Note: The 2038 limit is a good detail to mention. Long-running systems storing TIMESTAMP columns for expiry dates or schedules can hit it well before 2038 arrives.