Interview Questions — MySQL

MySQL Interview Questions

This page covers 25+ of the most commonly asked MySQL interview questions with thorough, production-accurate answers. Questions are ordered roughly from fundamental to advanced.

Q1: What is the difference between CHAR and VARCHAR?

Feature	CHAR	VARCHAR
Storage	Fixed length — always uses declared length	Variable length — uses actual length + 1-2 bytes overhead
Trailing spaces	Padded with spaces; stripped on retrieval	Not padded; preserved exactly
Performance	Slightly faster for fixed-width data	Better for variable-width data
Best use case	Fixed-width codes: country codes, status flags	Names, emails, addresses — anything variable

SQL

CHAR(10)    -- always stores 10 bytes
VARCHAR(10) -- stores 1-10 bytes + 1 byte for length

Q2: What is the difference between DELETE, TRUNCATE, and DROP?

Feature	DELETE	TRUNCATE	DROP
Type	DML	DDL	DDL
Can filter rows	Yes — WHERE clause	No — removes all rows	Removes entire table
Rollbackable	Yes (in a transaction)	No (implicit commit)	No (implicit commit)
AUTO_INCREMENT reset	No	Yes — resets to 1	Table is gone
Triggers fired	Yes	No	No
Speed	Slow for large tables	Very fast (deallocates pages)	Instantaneous

Q3: Explain ACID Properties

ACID defines the guarantees a transaction must provide:

Atomicity — a transaction is all-or-nothing. If any statement fails, all changes are rolled back. Either the entire transfer completes, or nothing changes.
Consistency — a transaction brings the database from one valid state to another. Constraints (FK, UNIQUE, NOT NULL) are always enforced.
Isolation — concurrent transactions do not see each other's intermediate state. The degree is controlled by the isolation level (READ COMMITTED, REPEATABLE READ, etc.).
Durability — once a transaction commits, the changes survive crashes. InnoDB achieves this via the redo log flushed to disk before commit.

Q4: What are the different types of JOINs?

Join	Returns
INNER JOIN	Only rows with matches in both tables.
LEFT JOIN (LEFT OUTER JOIN)	All rows from left table; NULL columns for right where no match.
RIGHT JOIN (RIGHT OUTER JOIN)	All rows from right table; NULL columns for left where no match.
CROSS JOIN	Cartesian product — every combination of left and right rows.
SELF JOIN	A table joined to itself, using two aliases (e.g., employees and managers).
FULL OUTER JOIN	All rows from both tables; NULL where no match. MySQL simulates this with LEFT JOIN UNION RIGHT JOIN.

Q5: What is normalization? Explain 1NF, 2NF, 3NF.

Normalization is the process of organizing a relational database to reduce data redundancy and improve data integrity.

1NF (First Normal Form) — each column holds atomic (indivisible) values; no repeating groups. No storing a comma-separated list in a single column.
2NF (Second Normal Form) — satisfies 1NF, and every non-key column is fully dependent on the entire primary key (eliminates partial dependencies in composite-key tables).
3NF (Third Normal Form) — satisfies 2NF, and no non-key column depends on another non-key column (eliminates transitive dependencies). E.g., storing both zip_code and city in a users table violates 3NF because city depends on zip_code, not the user ID.

Q6: What is an index? How does a B-tree index work?

An index is a separate data structure that allows MySQL to find rows quickly without scanning every row in the table.

A B-tree index (the default for InnoDB) is a balanced tree where:

Leaf nodes contain the indexed key values and pointers to the actual rows.
Internal nodes contain key ranges that guide the search down the tree.
The tree is always balanced — lookups, inserts, and deletes take O(log n) time.

An equality lookup like WHERE id = 42 descends from the root to a leaf in a few operations, regardless of table size.

Q7: What is the difference between clustered and non-clustered indexes?

Feature	Clustered (InnoDB Primary Key)	Non-Clustered (Secondary Index)
Data storage	Table data IS the index — stored in PK order	Separate structure; leaf nodes contain PK value
Lookup cost	Direct — data is at the leaf	Two-step: find PK in secondary index, then look up in clustered index
Count per table	Exactly one	Unlimited
Row order	Rows physically ordered by PK	No effect on physical row order

Note

In InnoDB, a secondary index lookup always involves two B-tree traversals: one for the secondary index and one for the clustered (primary key) index. A covering index avoids the second lookup by including all needed columns in the secondary index itself.

Q8: What is a view? Is it always updatable?

A view is a stored SELECT query that acts like a virtual table. It does not store data itself — every query against the view executes the underlying SELECT.

A view is updatable (you can INSERT/UPDATE/DELETE through it) only if:

It references exactly one base table.
It does not use DISTINCT, GROUP BY, HAVING, UNION, or aggregate functions.
It does not use subqueries in the SELECT list.
The view includes all NOT NULL columns with no default.

Views that aggregate data (e.g., COUNT, SUM) or join multiple tables are read-only.

Q9: What is a stored procedure vs a function?

Feature	Stored Procedure	Function
Called with	CALL proc_name()	Used in expressions: SELECT func_name()
Return value	Zero or more via OUT parameters or result sets	Exactly one scalar value via RETURN
Can have side effects	Yes — can INSERT/UPDATE/DELETE	Traditionally not (deterministic expected); MySQL allows it but is discouraged
Use in SQL	Cannot be used in SELECT, WHERE, etc.	Can be used anywhere an expression is valid
Transaction control	Can issue COMMIT/ROLLBACK	Cannot

Q10: What is a trigger?

A trigger is a stored program that automatically executes in response to a DML event (INSERT, UPDATE, or DELETE) on a table. Triggers fire BEFORE or AFTER the event.

Common uses: audit logging, enforcing complex business rules, maintaining denormalized columns.

SQL

-- Automatically set updated_at on every UPDATE
CREATE TRIGGER trg_users_updated
BEFORE UPDATE ON users
FOR EACH ROW
  SET NEW.updated_at = NOW();

Tip

Use triggers sparingly — they add hidden complexity and can slow DML significantly if they do expensive work. Prefer application-level logic where possible.

Q11: Explain transaction isolation levels.

Level	Dirty Read	Non-Repeatable Read	Phantom Read	InnoDB Default?
READ UNCOMMITTED	Possible	Possible	Possible	No
READ COMMITTED	Not possible	Possible	Possible	No
REPEATABLE READ	Not possible	Not possible	Possible (mostly prevented by InnoDB)	Yes
SERIALIZABLE	Not possible	Not possible	Not possible	No

Dirty read — reading uncommitted changes from another transaction.
Non-repeatable read — reading the same row twice and getting different values because another transaction committed in between.
Phantom read — a query returns different rows on two executions because another transaction inserted/deleted rows matching the WHERE clause.

Q12: What is a deadlock? How do you prevent it?

A deadlock occurs when two transactions each hold a lock the other needs, causing both to wait indefinitely. MySQL detects deadlocks automatically and rolls back the cheaper transaction.

Prevention strategies:

Always acquire locks in the same order across all transactions.
Keep transactions short to minimize lock-hold time.
Use SELECT ... FOR UPDATE only when you intend to update the row.
Index columns used in WHERE and JOIN — unindexed joins can lock far more rows than expected.
Reduce transaction scope — avoid doing non-DB work (HTTP calls) inside a transaction.

Q13: What is the difference between InnoDB and MyISAM?

Feature	InnoDB	MyISAM
Transactions	Yes	No
Foreign keys	Yes	No
Locking	Row-level	Table-level
Crash recovery	Automatic via redo log	Manual REPAIR TABLE needed
Full-text search	Yes (MySQL 5.6+)	Yes (legacy)
Recommendation	Always use for new tables	Avoid — legacy only

Q14: What is EXPLAIN used for?

EXPLAIN shows the query execution plan — how MySQL intends to execute a query. Key things to look for:

type: ALL means a full table scan — needs an index.
key: NULL means no index is used.
rows: shows the estimated number of rows examined — lower is better.
Extra: Using filesort or Using temporary indicate expensive sorting/grouping.

Q15: How would you optimize a slow query?

Run EXPLAIN and identify the access type, key used, and rows examined.
Add an index on the column(s) in the WHERE clause if type is ALL.
Use a composite index if filtering on multiple columns together.
Avoid functions on indexed columns in WHERE: write WHERE created_at > x instead of WHERE YEAR(created_at) = x.
Select only needed columns — avoid SELECT *.
Break a large query into smaller steps using CTEs for readability and optimizer hints.
Use EXPLAIN ANALYZE (MySQL 8.0+) to compare estimates against actual row counts.
Check if a covering index can serve the query without touching the table.

Q16: What is SQL injection? How do you prevent it?

SQL injection is an attack where user input is embedded directly into a SQL query, allowing the attacker to alter the query's logic. A classic example is a login bypass using admin' -- as a username.

Prevention:

Always use parameterized queries (prepared statements) — the only reliable fix.
Apply least-privilege database users — limit the damage if injection does occur.
Validate and sanitize input as a defense-in-depth layer.
Never expose raw database error messages to users.

Q17: What is the difference between UNION and UNION ALL?

Feature	UNION	UNION ALL
Duplicates	Removes duplicate rows (runs a DISTINCT)	Keeps all rows including duplicates
Performance	Slower — must sort and deduplicate	Faster — no deduplication step
When to use	When you need unique results from two queries	When duplicates are acceptable or impossible (different tables)

Q18: What is a CTE?

A Common Table Expression (CTE) is a named temporary result set defined at the beginning of a query using the WITH keyword. It improves readability and can be referenced multiple times in the same query.

SQL

WITH top_customers AS (
  SELECT customer_id, SUM(total) AS lifetime_value
  FROM orders GROUP BY customer_id
  HAVING SUM(total) > 1000
)
SELECT c.name, tc.lifetime_value
FROM customers c JOIN top_customers tc ON tc.customer_id = c.id;

Recursive CTEs can traverse hierarchical data (org charts, category trees) using a self-referencing UNION ALL.

Q19: What are window functions?

Window functions perform calculations across a set of rows related to the current row without collapsing them into groups (unlike GROUP BY). They use the OVER() clause.

SQL

SELECT
  name,
  salary,
  AVG(salary) OVER (PARTITION BY department)     AS dept_avg,
  RANK()      OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
  SUM(salary) OVER (ORDER BY hire_date ROWS UNBOUNDED PRECEDING)  AS running_total
FROM employees;

Common window functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), SUM(), AVG(), COUNT().

Q20: What is the difference between WHERE and HAVING?

Feature	WHERE	HAVING
Filters	Individual rows before grouping	Groups after GROUP BY
Can use aggregates	No	Yes — HAVING COUNT(*) > 5
Performance	Faster — reduces rows before aggregation	Slower — aggregation happens first

Q21: What is a covering index?

A covering index is an index that contains all the columns a query needs, so MySQL can answer the query entirely from the index without reading the actual table rows. You will see Using index in the EXPLAIN Extra column.

SQL

-- This query is covered by an index on (status, created_at, total)
-- MySQL never touches the orders table rows
SELECT status, created_at, total FROM orders
WHERE status = 'completed'
ORDER BY created_at DESC;

Q22: What are the differences between NOW(), SYSDATE(), and CURRENT_TIMESTAMP?

Function	Returns	Evaluated
NOW()	Current datetime at statement start	Once per statement — consistent within a statement
SYSDATE()	Current datetime at function call time	Each time it is called — may differ within a statement
CURRENT_TIMESTAMP	Alias for NOW()	Same as NOW()

Q23: What is the purpose of EXPLAIN FORMAT=JSON?

EXPLAIN FORMAT=JSON produces the query execution plan as a JSON document, providing additional detail not shown in the tabular format including estimated cost values, buffer usage, and a clearer representation of subquery nesting. This is particularly useful for complex queries with multiple subqueries or derived tables.

Q24: How does InnoDB handle concurrency?

InnoDB uses Multi-Version Concurrency Control (MVCC) — readers see a snapshot of the data as it existed at the start of their transaction, without blocking writers.
Writers use row-level locks — only the specific rows being modified are locked, not the entire table.
Gap locks and next-key locks prevent phantom reads at REPEATABLE READ isolation level.
Deadlocks are detected automatically and resolved by rolling back the transaction with the smallest undo log.

Q25: What happens when you run ALTER TABLE on a large table?

In older MySQL versions, most ALTER TABLE operations required rebuilding the entire table while holding a metadata lock — blocking all reads and writes for the duration.

MySQL 5.6+ Online DDL allows many operations to proceed while the table remains readable and writable:

Adding/dropping secondary indexes — online (no table copy).
Adding nullable columns (MySQL 8.0) — instant (no table copy needed).
Changing column data type — still requires a table rebuild.
Adding/dropping primary keys — requires a full table rebuild.

SQL

-- Check if the operation will be online or will lock the table
ALTER TABLE orders
  ADD COLUMN notes TEXT,
  ALGORITHM=INSTANT,  -- fastest: only metadata change
  LOCK=NONE;          -- fail if locking would be required