Top 60 MySQL Interview Questions (2026)

MySQL is an open-source relational database management system. It stores data in tables of rows and columns and lets you read and change that data with SQL. A server process holds the data, and applications to it over the network connects to run queries.

It's used for web and application backends: WordPress, e-commerce sites, SaaS apps, and the classic LAMP stack all run on MySQL. It's popular because it's fast for read-heavy work, free to start with, and hosted almost everywhere.

SQL is the language you use to query and manage relational data: SELECT, INSERT, UPDATE, DELETE, and so on. MySQL is a database product that understands SQL and actually stores and manages the data.

So SQL is the standard, and MySQL is one implementation of it, alongside PostgreSQL, SQL Server, and Oracle. Each product adds its own extensions on top of standard SQL.

A primary key uniquely identifies each row in a table. Its values are unique and can't be NULL, and a table has exactly one. A foreign key is a column that references the primary key of another table, linking the two.

Together they model relationships: an orders table's customer_id foreign key points at the customers table's primary key, so every order belongs to a real customer. The database enforces that link.

SQL commands group into DDL, DML, DCL, and TCL. DDL (CREATE, ALTER, DROP) defines structure. DML (SELECT, INSERT, UPDATE, DELETE) works with the data itself. DCL (GRANT, REVOKE) controls permissions. TCL (COMMIT, ROLLBACK) manages transactions.

Some people put SELECT in its own DQL bucket. Knowing the categories helps you reason about what can be rolled back: DML runs inside transactions, most DDL commits implicitly in MySQL.

SELECT names the columns you want, FROM names the table, WHERE filters which rows come back, ORDER BY sorts them, and LIMIT caps how many you get. Chain those clauses in that written order and you've got the shape of almost every read query you'll write.

The clauses run in a fixed logical order regardless of how you type them: FROM, then WHERE, then SELECT, then ORDER BY, then LIMIT. That order explains why a column alias from SELECT can't be used in WHERE.

INNER JOIN returns only rows that match in both tables. LEFT JOIN returns all rows from the left table plus matches from the right, with NULLs where there's no match. RIGHT JOIN is the mirror. CROSS JOIN returns every combination of rows.

MySQL has no native FULL OUTER JOIN, but you can emulate it by UNIONing a LEFT and a RIGHT JOIN. The one you'll write most is LEFT JOIN, often to find rows with no related record.

WHERE filters individual rows before any grouping happens, and it can't use aggregate functions because the groups don't exist yet. HAVING filters the groups after GROUP BY runs, so it can reference aggregates like COUNT() or SUM(). In short, WHERE screens rows, HAVING screens groups.

So you filter raw rows with WHERE, then aggregate, then filter the aggregated groups with HAVING. Using both in one query is common and correct.

GROUP BY collapses rows that share a value into one row per group, so you can compute a summary per group. Aggregate functions (COUNT, SUM, AVG, MIN, MAX) do that computation across each group.

Every column in SELECT that isn't inside an aggregate must appear in GROUP BY, otherwise the value is ambiguous. MySQL 8 enforces this by default with ONLY_FULL_GROUP_BY.

DISTINCT removes duplicate rows from a result set, keeping only unique combinations of the selected columns. SELECT DISTINCT city returns each city once, no matter how many customers share it. It compares the whole set of selected columns together, not just the first one.

It applies to the whole row of selected columns, not just the first column. It also costs work, since MySQL has to sort or hash to detect duplicates, so don't add it out of habit.

NULL means 'unknown', not zero or an empty string. Any comparison with NULL using =, <, or > returns NULL (treated as not true), which is why you must use IS NULL and IS NOT NULL to test for it.

NULL also affects aggregates: COUNT(column) skips NULLs while COUNT(*) counts every row. Use COALESCE or IFNULL to substitute a default when NULL would break a calculation.

CHAR is fixed-length: CHAR(10) always reserves 10 characters and pads shorter values with trailing spaces. VARCHAR is variable-length: VARCHAR(10) stores only what you actually put in, up to the limit, plus a length prefix of one or two bytes to record how long the value is.

Use CHAR for values that are truly fixed size, like a country code or a hash. Use VARCHAR for anything variable, like names or emails, which covers most columns.

AUTO_INCREMENT tells MySQL to generate the next number automatically for a column whenever you insert a row without a value for it, so you get unique, always-growing IDs without setting them yourself. It's almost always applied to the integer primary key, which is exactly what it's designed for.

The counter doesn't reuse gaps: deleting rows or rolling back an insert leaves holes in the sequence, which is expected and fine. TRUNCATE resets the counter; DELETE does not.

INSERT adds new rows to a table. UPDATE changes column values in existing rows that match a WHERE clause. DELETE removes the rows that match a WHERE clause. These three are the write side of DML, and each runs inside a transaction so you can roll it back.

The safety rule that matters in interviews: UPDATE and DELETE without a WHERE clause affect every row in the table. Always double-check the WHERE before running either against real data.

DELETE removes selected rows and can use WHERE; it's transactional and can be rolled back. TRUNCATE empties the whole table fast, resets AUTO_INCREMENT, and can't target specific rows. DROP removes the table itself, structure and all.

The mental model: DELETE edits rows, TRUNCATE empties the table, DROP deletes the table. DELETE is DML; TRUNCATE and DROP are DDL and commit implicitly in MySQL.

Constraints are rules that limit what values a column or table accepts, and the database enforces them on every write. The common ones are NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, DEFAULT, and CHECK. They push data-integrity rules down into the database so bad data can't slip in.

They protect data integrity at the source, so bad data can't get in even if the application has a bug. A UNIQUE constraint on email, for instance, makes duplicate signups impossible at the database level.

LIKE matches text against a pattern. The percent sign matches any number of characters, and the underscore matches exactly one. 'a%' matches anything starting with a; '_a%' matches anything with a as the second character.

A leading wildcard like '%son' can't use a normal index, so it forces a full scan. That performance note is often the real point of the question.

The logical order is FROM and JOIN, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT. You write SELECT first, but it runs near the end.

This order explains two common surprises: WHERE can't reference a SELECT alias because SELECT hasn't run yet, and HAVING can filter on aggregates because it runs after GROUP BY. ORDER BY can use aliases because it runs after SELECT.

Aggregate functions work across many rows and collapse them into one value: COUNT, SUM, AVG, MIN, and MAX. Scalar functions work on a single value per row and return one result per row: UPPER, LENGTH, ROUND, NOW, and CONCAT. Aggregates summarize a group; scalars transform each row.

The distinction matters with GROUP BY: aggregates collapse groups, scalars don't. Mixing a non-aggregated scalar column with an aggregate without GROUP BY is the classic error.

MySQL supports three comment styles: a double dash followed by a space for a single line, a hash for a single line, and slash-star ... star-slash for a block that can span lines.

Comments matter in interviews when you annotate a tricky query to show your reasoning, which many reviewers score positively.

BETWEEN tests whether a value falls in a range, and it's inclusive on both ends: age BETWEEN 18 AND 25 matches 18 and 25 too. IN tests whether a value is one of a listed set, which reads cleaner than a chain of OR conditions.

Both are shorthand for conditions you could write longhand. IN with a subquery is common, but watch the NULL trap: NOT IN with a NULL in the set returns no rows.

An index is a separate data structure, usually a B-tree in InnoDB, that lets MySQL find rows without scanning the whole table. It's like a book's index: instead of reading every page, you jump straight to the right one.

Indexes speed up lookups, range scans, and sorts on their columns. The trade-off is that every INSERT, UPDATE, and DELETE has to maintain the index too, so over-indexing slows writes and wastes space.

In InnoDB, the clustered index is the table itself: the rows are physically stored on disk in primary-key order, so the primary key and the clustered index are one and the same thing. There's exactly one clustered index per table, which is why every InnoDB table wants a primary key.

A secondary index is any other index. Its leaf nodes store the primary-key value, not the full row, so a lookup by secondary index does a second step to fetch the row from the clustered index unless the index covers the query.

A composite index on (a, b, c) works left to right. It helps queries that filter on a, on a and b, or on a, b, and c, but not queries that filter only on b or only on c. This is the leftmost-prefix rule.

So put the column you filter on most, or the one used in equality checks, first. A range condition stops the index from being used for columns after it.

A subquery is a query nested inside another query. A scalar subquery returns one value, a row subquery returns one row, and a table subquery returns many rows, used with IN, EXISTS, or in the FROM clause as a derived table.

A correlated subquery references the outer query and runs once per outer row, which can be slow. Many correlated subqueries rewrite as joins, and interviewers like to see that instinct.

IN checks whether a value is in a list or subquery result. EXISTS checks whether a subquery returns any row at all, stopping at the first match. Both filter, but they behave differently around NULLs and performance.

The NULL trap: NOT IN with a list that contains a NULL returns no rows, because the comparison becomes unknown. NOT EXISTS avoids that. When correctness with NULLs matters, prefer EXISTS.

Both stack the results of two SELECTs that have matching columns and types, one result set on top of the other. UNION removes duplicate rows from the combined result; UNION ALL keeps every row, including duplicates, which is why it's the faster of the two.

UNION ALL is faster because it skips the de-duplication step. Use UNION only when you actually need distinct rows, and UNION ALL when you know there are no duplicates or you want them.

A transaction is a group of statements that either all succeed or all fail together, wrapped in BEGIN and COMMIT, with ROLLBACK to undo. It keeps related changes consistent, like moving money between two accounts.

ACID stands for Atomicity (all or nothing), Consistency (rules stay satisfied), Isolation (concurrent transactions don't corrupt each other), and Durability (committed changes survive a crash). InnoDB provides all four; MyISAM does not.

MySQL supports four, from loosest to strictest: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (InnoDB's default), and SERIALIZABLE. Each level trades some concurrency for stronger consistency, deciding which other transactions' changes your transaction is allowed to see while it runs.

Each level prevents more anomalies: dirty reads, non-repeatable reads, and phantom reads. REPEATABLE READ, the default, gives a consistent snapshot for the whole transaction, which surprises people expecting to see others' committed changes mid-transaction.

Normalization organizes tables to reduce redundancy and avoid update anomalies by splitting data into related tables. 1NF requires atomic columns and no repeating groups. 2NF requires 1NF plus no partial dependency on part of a composite key. 3NF requires 2NF plus no transitive dependency of one non-key column on another.

The plain version: each fact lives in one place. A customer's address sits in the customers table, not copied into every order, so you update it once.

Denormalization deliberately adds redundant data, like a duplicated column or a precomputed total, to avoid expensive joins and speed up reads. It's the reverse trade of normalization: you accept some redundancy in exchange for fewer joins at query time, which can matter a lot on read-heavy pages.

It's justified for read-heavy reporting or dashboards where join cost hurts, but it puts the burden on you to keep the duplicated data in sync on every write. The rule: normalize first, denormalize only when a measured read problem demands it.

InnoDB is MySQL's default engine: it supports transactions, foreign keys, and row-level locking, and it recovers cleanly after a crash. MyISAM is older, has no transactions or foreign keys, and locks the whole table on writes.

Use InnoDB for almost everything today. MyISAM's only real edge was fast full-table counts and simple full-text search, both of which InnoDB now handles well enough.

EXPLAIN shows how MySQL plans to run a query: which indexes it'll use, the join order, and roughly how many rows it expects to examine. It's the first tool you reach for on a slow query.

Key columns: type (ALL means a full table scan, which is usually bad; ref or eq_ref means an index is used), key (the index chosen), and rows (estimated rows scanned). A high rows count with type ALL is your signal to add an index.

A view is a saved SELECT query that acts like a virtual table. You query it by name, and MySQL runs the underlying SELECT each time. It doesn't store data itself (that's a materialized view, which MySQL doesn't natively support).

Use views to hide complex joins behind a simple name, to expose a limited set of columns for security, or to give a stable interface while the underlying tables change.

A stored procedure is a named block of SQL stored in the database and run with CALL. It can take input and output parameters, run multiple statements, and include control flow. A stored function returns a single value and can be used inside a query.

Procedures suit multi-step operations run as a unit; functions suit computed values you reuse in SELECT. The trade-off is putting logic in the database instead of the application, which some teams avoid for testability.

A trigger is SQL that runs automatically before or after an INSERT, UPDATE, or DELETE on a table. Common uses are keeping an audit log, maintaining a denormalized total, or validating data on write.

The risk is hidden logic: a trigger fires invisibly, so a slow or buggy trigger can make writes mysteriously slow or fail with no obvious cause in the application code. Use them sparingly and document them.

A self join joins a table to itself using table aliases, so you can relate rows within the same table. The classic case is an employees table where each row has a manager_id pointing at another row in the same table.

You need it whenever a table models a hierarchy or a relationship between its own rows, like employees and managers, or categories and parent categories.

CASE returns different values based on conditions, working like an if/else inside a query. You give it a series of WHEN conditions and the values to return, plus an optional ELSE. It's useful for bucketing values into labels, pivoting rows into columns, or conditional aggregation.

A common trick is conditional counting: SUM with a CASE inside counts rows that meet a condition per group, which does pivots without extra queries.

LIMIT with OFFSET is the simple way: LIMIT 20 OFFSET 40 returns rows 41 to 60. It's fine for early pages, but deep offsets get slow because MySQL still reads and discards every row before the offset.

For deep pagination, use keyset (seek) pagination instead: remember the last seen sort value and use WHERE id > last_id ORDER BY id LIMIT 20. That uses the index and stays fast at any depth.

A connection pool keeps a set of open database connections ready to reuse, instead of opening a fresh one per request. Opening a connection is expensive, so reusing them cuts latency and load.

Without pooling, a busy app can exhaust MySQL's max_connections limit and start rejecting requests. The pool caps concurrent connections and hands them back when a request finishes.

A temporary table is a real table that exists only for your session and holds its own copy of data. A CTE (WITH clause) is a named subquery that lives only for the single statement it's in. A view is a saved query definition that runs fresh every time you select from it.

So temporary tables materialize data you'll reuse across statements, CTEs organize one complex statement, and views give a permanent named interface over a query.

InnoDB stores rows in a clustered B-tree keyed by the primary key, so the table is the primary-key index. Data lives in 16KB pages inside tablespace files, and each secondary index is its own B-tree whose leaves hold primary-key values.

This layout drives real behavior: sequential primary keys keep inserts fast and pages full, while random keys (like UUIDs) cause page splits and fragmentation. It's the reason auto-increment integer keys are the default recommendation.

The redo log records changes before they're written to the data files, so after a crash InnoDB can replay committed changes it hadn't flushed yet. That's what makes durability cheap: commit writes a small sequential log entry, not the full page.

The undo log stores the previous version of changed rows. It powers rollback and multi-version concurrency control, letting a reader see a consistent snapshot while a writer changes the same rows. Together they're how InnoDB gets ACID without locking readers.

Multi-version concurrency control lets readers see a consistent snapshot without blocking writers. Each row keeps hidden columns pointing at older versions in the undo log, and each transaction reads the version that was committed as of its snapshot.

The payoff: a long-running SELECT doesn't lock out UPDATEs, and vice versa, so read and write concurrency both stay high. The cost is undo-log growth if a very long transaction holds an old snapshot open, which can bloat storage.

A deadlock happens when two transactions each hold a lock the other needs, so neither can proceed. InnoDB detects this, picks a victim, and rolls it back with an error, so the deadlock resolves itself rather than hanging forever.

The prevention playbook: acquire locks in a consistent order across transactions, keep transactions short, and add indexes so locks target specific rows instead of ranges. In the app, catch the deadlock error and retry the transaction.

Measure first: run EXPLAIN (or EXPLAIN ANALYZE in MySQL 8) to see the plan, the chosen indexes, and estimated rows. A type of ALL with a high row count means a full scan that an index can fix.

Then work in order: add or fix the index the WHERE and JOIN columns need, make the index cover the query if you can, rewrite correlated subqueries as joins, avoid SELECT * and leading-wildcard LIKE, and only then consider caching or denormalization. Re-run EXPLAIN to confirm the plan actually changed.

A covering index contains every column a query needs, in the index itself, so MySQL answers the query from the index alone without touching the table. EXPLAIN shows 'Using index' when this happens.

It's fast because it skips the second lookup into the clustered index that a normal secondary-index read requires. The cost is a wider index, so you cover queries that run often and matter, not every query.

Partitioning splits one logical table into physical pieces by a rule, usually a range on a date column, so MySQL can prune to just the relevant partitions for a query and drop old data by dropping a partition instead of a slow DELETE.

It helps very large tables with queries that filter on the partition key and with cheap bulk deletion of old rows. It doesn't help if queries don't filter on the partition key, and it adds operational complexity, so it's a targeted tool, not a default.

One primary server records every change to a binary log, and one or more replicas read that log and replay the changes, so they hold copies of the data. It's used for read scaling (send reads to replicas), backups, and failover.

Replication can be asynchronous (fast, but replicas lag and can lose recent writes on primary failure) or semi-synchronous (the primary waits for at least one replica to acknowledge, trading a little latency for safety). Replica lag is the thing that bites in production.

Reads scale by adding read replicas and routing SELECT traffic to them, plus caching hot results outside the database in something like Redis. That handles most read-heavy apps well, since reads usually outnumber writes by a wide margin and replicas are cheap to add.

Writes are harder. First tune (indexes, batching, bigger hardware), then archive cold data, and only when a single primary genuinely can't keep up, shard: split data across multiple primaries by a shard key. Sharding adds cross-shard query and rebalancing pain, so it's the last resort.

InnoDB uses row-level locks: shared locks for reads that block writers, exclusive locks for writes. It also uses gap locks and next-key locks under REPEATABLE READ to lock ranges and prevent phantom rows during a transaction.

SELECT ... FOR UPDATE takes an exclusive lock so you can read-then-write safely, and FOR SHARE takes a shared lock. Understanding that missing indexes force InnoDB to lock more rows (even whole ranges) is the practical takeaway.

SQL injection is when user input is concatenated into a query, so an attacker can inject SQL that runs with your database's privileges, reading or destroying data. A classic input like ' OR '1'='1 turns a login check into a always-true condition.

The fix is parameterized queries (prepared statements): the SQL structure and the data travel separately, so input is always treated as a value, never as code. Never build SQL by string concatenation with user input, and grant the app account only the privileges it needs.

Logical backups (mysqldump) export SQL statements: portable and simple, but slow to restore at scale. Physical backups (Percona XtraBackup) copy the data files: fast restore for large databases. Binary logs enable point-in-time recovery to a specific moment before an accident.

The real answer is a strategy, not a tool: regular full backups, incremental or binlog capture between them, off-site copies, and, the part people skip, periodically test a restore. An untested backup isn't a backup.

A character set defines which characters can be stored (use utf8mb4, which is full UTF-8 including emoji; the old utf8 alias is only 3 bytes and can't store them). A collation defines how strings compare and sort, including case and accent sensitivity.

Mismatched charsets or collations between columns, tables, or the connection cause silent bugs: failed joins on text keys, 'illegal mix of collations' errors, and mojibake. Set utf8mb4 consistently across the database, tables, and connection.

The N+1 problem is running one query to fetch a list, then one extra query per row to fetch related data, so a page of 100 items fires 101 queries. It's a common performance killer, usually created invisibly by an ORM's lazy loading.

The fix is to fetch the related data in one query: a JOIN, or an IN query with the collected IDs (eager loading). Most ORMs have an eager-load option; the skill is spotting the pattern in a slow query log.

Window functions (MySQL 8) compute across a set of rows related to the current row, but without collapsing them the way GROUP BY does. So you keep every row and add a computed column: a running total, a rank, or a per-group row number.

ROW_NUMBER, RANK, and LAG over a PARTITION BY are the common ones. They solve 'top N per group' and running-total problems that used to need ugly self-joins or subqueries.

A common table expression (CTE) is a named temporary result defined with WITH, used to break a complex query into readable steps or to reference the same subquery twice. A recursive CTE references itself, which lets you walk hierarchies like an org chart or a category tree.

Recursive CTEs (MySQL 8) replace the old, painful workarounds for tree traversal. They have an anchor part and a recursive part joined by UNION ALL, with a termination condition to avoid infinite loops.

Turn on the slow query log with a sensible long_query_time threshold, then aggregate it with a tool like pt-query-digest to rank queries by total time, not just single-run time. A fast query that runs a million times often costs more than one slow report.

The Performance Schema and sys schema (sys.statement_analysis) give live views of the heaviest statements without parsing log files. The principle is the same: find the queries that consume the most total time and fix those first.

A naive ALTER TABLE on a huge table can lock it for a long time and block the application. MySQL 8's online DDL handles many changes (adding a nullable column, adding an index) without a full lock, so check whether the change qualifies first.

For changes that would still block, use an online schema-change tool (pt-online-schema-change or gh-ost) that builds a shadow table, copies rows in batches, keeps it in sync with triggers or the binlog, then swaps it in atomically. Always test on a copy and have a rollback plan.

The InnoDB buffer pool is an in-memory cache of table and index pages. Reads and writes hit memory first, and MySQL flushes changed pages to disk in the background, so a well-sized pool keeps the hot working set in RAM and turns most reads into memory hits.

If the pool is too small, MySQL constantly evicts and re-reads pages from disk, and query latency climbs. On a dedicated server, innodb_buffer_pool_size is commonly set to around 70 to 80 percent of RAM, leaving room for the OS and connections.

Pessimistic locking takes a database lock up front (SELECT ... FOR UPDATE) so no one else can touch the row until you commit. It's safe under heavy contention but holds locks and can hurt concurrency. Optimistic locking takes no lock; it adds a version column and, on update, checks the version hasn't changed since you read it, retrying if it has.

Choose pessimistic when conflicts are frequent and retries are costly, optimistic when conflicts are rare and you want maximum concurrency, which fits most web workloads.