MySQL Database Interview Questions for Web Development
25 MySQL Database questions with answers for web development roles — 8 easy, 9 medium, 8 hard.
About MySQL Database
MySQL stores the data behind most PHP applications. You will revise keys, normalization, joins, grouping, indexes, InnoDB, transactions and isolation levels, and write queries such as second highest salary, top N per group and duplicate detection. Scenario questions cover slow queries, deadlocks, pagination and recovering from mistakes.
Sheet progressStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
✓ 3 questions free • Unlock the rest in seconds
01
What is the difference between a primary key and a foreign key?
A primary key uniquely identifies each row in a table and cannot be NULL, for example students.id. A foreign key is a column in another table that refers to that primary key, such as enrollments.student_id, and enforces referential integrity so you cannot add an enrollment for a student who does not exist. Foreign keys can define ON DELETE CASCADE or RESTRICT behaviour. InnoDB supports foreign keys.
INNER JOIN returns only rows with matching values in both tables, such as students who have enrolled. LEFT JOIN returns all rows from the left table and matching rows from the right, filling NULL where there is no match, which is useful to find students with no enrollment. RIGHT JOIN is the mirror of LEFT JOIN. MySQL has no FULL OUTER JOIN, but you can simulate it with a UNION of LEFT and RIGHT joins.
WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates. For example, SELECT city, COUNT(*) FROM students WHERE status = 'active' GROUP BY city HAVING COUNT(*) > 50 first keeps only active students, then shows cities with more than 50 of them. Filtering in WHERE when possible is faster because fewer rows are grouped.
04
What is the difference between DELETE, TRUNCATE and DROP?
DELETE removes selected rows using a WHERE clause, can be rolled back inside a transaction and fires triggers, but is slow on large tables. TRUNCATE removes all rows quickly by recreating the table, resets AUTO_INCREMENT and cannot be rolled back in MySQL. DROP removes the table structure and data completely. Always take a backup and double-check the WHERE clause before running DELETE on production.
CHAR(n) stores a fixed length and pads shorter values, which suits fixed codes like a 6-digit PIN code or two-letter state code. VARCHAR(n) stores variable length up to n characters with a small length prefix, ideal for names and emails. TEXT is for long content like blog bodies but cannot have a default value and is indexed only by prefix. Using right-sized types saves memory and improves index efficiency.
06
What is the difference between UNION and UNION ALL?
Both combine the results of two SELECT queries with the same number and type of columns. UNION removes duplicate rows, which requires extra sorting or hashing work. UNION ALL keeps all rows, including duplicates, and is therefore faster. Use UNION ALL when you know results cannot overlap or duplicates are acceptable, for example combining this year's and last year's order tables.
Two common approaches:
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
The first returns NULL when there is no second value. With MySQL 8 you can also use DENSE_RANK() OVER (ORDER BY salary DESC) and filter rank = 2, which generalises to the Nth highest.
08
How do you make sure you do not break production data while working?
I always test queries on a local or staging copy first, run a SELECT with the same WHERE clause before any UPDATE or DELETE, and wrap risky changes in a transaction so I can roll back. I confirm a fresh backup exists before migrations. In one release, this habit caught a WHERE condition that would have updated every row, and we fixed it before it ever reached production.
09
Explain database normalization up to third normal form.
Normalization reduces duplicate data and update problems. 1NF requires atomic values, no repeating groups like phone1, phone2. 2NF requires every non-key column to depend on the whole primary key, not part of a composite key. 3NF removes transitive dependencies, so a column depends only on the key, for example moving city details out of an orders table. Read-heavy reports sometimes denormalise deliberately for speed.
InnoDB is the default MySQL storage engine. It supports transactions, row-level locking, foreign keys and crash recovery, making it suitable for almost all applications. MyISAM uses table-level locking, has no transactions or foreign keys, and can corrupt on crashes. MyISAM was once faster for read-only workloads, but modern InnoDB matches or beats it. Old tables still on MyISAM should usually be converted.
A transaction groups several SQL statements so they succeed or fail together. ACID means Atomicity, all or nothing; Consistency, rules and constraints stay valid; Isolation, concurrent transactions do not see each other's partial changes; and Durability, committed data survives crashes. For example, transferring wallet balance debits one row and credits another inside START TRANSACTION and COMMIT, with ROLLBACK if anything fails.
12
Write a query to find duplicate email addresses in a users table.
Group by the column and keep groups with more than one row:
SELECT email, COUNT(*) AS total
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
To delete duplicates but keep the lowest id:
DELETE u1 FROM users u1
JOIN users u2
ON u1.email = u2.email AND u1.id > u2.id;
Afterwards add a UNIQUE index on email so duplicates cannot come back.
13
Create students, courses and enrollments tables with proper keys.
A many-to-many relationship needs a junction table:
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL UNIQUE
);
CREATE TABLE courses (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(150) NOT NULL
);
CREATE TABLE enrollments (
student_id INT,
course_id INT,
enrolled_on DATE NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id)
);
14
Show monthly revenue for the current year from an orders table.
Group by month and sum the amounts:
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
COUNT(*) AS orders,
SUM(amount) AS revenue
FROM orders
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01'
AND status = 'paid'
GROUP BY month
ORDER BY month;
Using a date range instead of YEAR(created_at) = 2026 lets MySQL use an index on created_at.
15
How do you back up and restore a MySQL database from the command line?
mysqldump creates a logical SQL backup:
mysqldump -u root -p --single-transaction --routines school > school_2026_11_12.sql
mysql -u root -p school < school_2026_11_12.sql
--single-transaction takes a consistent InnoDB snapshot without locking tables. For large databases, physical backup tools or managed cloud snapshots are faster. Backups should be automated, stored off-server, and restored on a test server regularly to prove they work.
16
The database server is overloaded by read traffic. What architecture changes would you suggest?
I would first add caching for frequent reads with Redis or application-level caching. Then I would set up one or more read replicas and route SELECT queries to them while writes go to the primary, keeping replication lag in mind for read-after-write cases. Optimising slow queries and indexes often gives the cheapest wins. Managed services like Amazon RDS make adding replicas straightforward.
Situation: in my internship, a training institute tracked batches, students and fees in spreadsheets. Task: I had to design a MySQL schema for a web portal. Action: I normalised data into students, batches, enrollments and payments tables, added foreign keys and indexes on common filters, and wrote views for monthly reports. Result: reports that took a day manually now loaded in seconds, and data errors dropped sharply.
18
How do indexes work and when can they hurt performance?
InnoDB indexes are B+ trees that let MySQL find rows without scanning the whole table, which speeds up WHERE, JOIN and ORDER BY on indexed columns. A composite index follows the leftmost prefix rule. Indexes cost extra disk space and slow down INSERT, UPDATE and DELETE because each index must be updated. Low-selectivity columns like gender rarely benefit, and functions on columns in WHERE can stop index use.
EXPLAIN shows how MySQL plans to run a query. Key columns are type, where ALL means a full table scan and ref or eq_ref mean index lookups; key, the index actually chosen; rows, the estimated rows examined; and Extra, where 'Using filesort' or 'Using temporary' signal costly operations. EXPLAIN ANALYZE in MySQL 8 runs the query and shows real timings, which helps confirm an optimisation.
READ UNCOMMITTED allows dirty reads of uncommitted data. READ COMMITTED sees only committed data but the same query can return different results within a transaction. REPEATABLE READ, the InnoDB default, gives a consistent snapshot for the whole transaction and uses gap locks to prevent most phantom rows. SERIALIZABLE is the strictest and slowest. Higher isolation means fewer anomalies but more locking and contention.
21
Find the top 3 highest-paid employees in each department.
Use a window function in MySQL 8:
WITH ranked AS (
SELECT name, department_id, salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT name, department_id, salary
FROM ranked
WHERE rnk <= 3;
PARTITION BY restarts ranking per department. DENSE_RANK keeps ties together, while ROW_NUMBER would return exactly three rows even if salaries tie.
22
A search query on a 2 million row products table takes 6 seconds. How do you speed it up?
I would run EXPLAIN to see if it does a full table scan. If the WHERE uses LIKE '%phone%', a normal index cannot help, so I would add a FULLTEXT index with MATCH AGAINST or use a search engine like Elasticsearch or Meilisearch. For filters such as category and price, a composite index in the right order helps. I would also select only needed columns and cache popular searches.
23
Your application logs show frequent deadlock errors. What do you do?
A deadlock happens when two transactions each hold a lock the other needs; InnoDB kills one. I would read SHOW ENGINE INNODB STATUS to see the conflicting queries. Fixes include accessing tables and rows in a consistent order, keeping transactions short, adding indexes so updates lock fewer rows, and avoiding user interaction inside a transaction. The application should also retry the failed transaction once or twice.
24
Pagination becomes very slow on later pages. Why, and how do you fix it?
LIMIT 20 OFFSET 100000 forces MySQL to read and discard 100,000 rows before returning 20, so deep pages get slower. Keyset or cursor pagination fixes this: remember the last seen id and query WHERE id < :last_id ORDER BY id DESC LIMIT 20, which uses the index directly. It suits infinite scroll and APIs. For numbered pages, limiting maximum depth or caching counts also helps.
25
Someone accidentally ran DELETE without a WHERE clause on production. How do you recover?
First I would stop writes to that table to avoid further damage and inform the team. Then I would restore the latest backup to a separate server and, if binary logging is enabled, replay binlog events up to just before the bad statement using mysqlbinlog for point-in-time recovery. Finally I would copy the recovered rows back. Afterwards I would enable safe-updates mode and restrict production access.
Frequently asked questions
How important is MySQL Database in Web Development interviews?
MySQL stores the data behind most PHP applications. You will revise keys, normalization, joins, grouping, indexes, InnoDB, transactions and isolation levels, and write queries such as second highest salary, top N per group and duplicate detection. Scenario questions cover slow queries, deadlocks, pagination and recovering from mistakes.
How should I use the MySQL Database sheet?
Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.