Top 50 MySQL Interview Questions and Answers for 2027
-
By Devraj
-
19th November 2026
The most common MySQL interview topics are keys and constraints, joins, indexes, transactions (ACID), query optimization with EXPLAIN, window functions, and replication. Freshers are usually asked basics and simple queries; experienced candidates and DBAs are asked about performance tuning, backups, point-in-time recovery, and replication. This guide covers 50 questions across all levels.
Master MySQL Interviews
Ace your next technical round with our comprehensive guide featuring beginner-to-DBA level questions, live query challenges, and performance optimization tips.
What interviewers ask at each level
| Level | Typical Topics | Questions in This Guide |
|---|---|---|
| Beginner | Tables, keys, data types, constraints, views | 1–10 |
| Intermediate | Joins, indexes, stored procedures, triggers, normalization | 11–22 |
| Advanced | ACID, isolation levels, deadlocks, EXPLAIN, window functions, CTEs |
23–32 |
| Query Challenges | Nth highest salary, duplicates, joins, aggregation | 33–42 |
| DBA | Backups, privileges, replication, point-in-time recovery, monitoring | 43–50 |
1. Beginner Questions
1. What is MySQL?
An open-source relational database management system (RDBMS) that stores data in tables and is queried with SQL. It is owned by Oracle and runs on Linux, Windows, and macOS.
2. What is the difference between SQL and MySQL?
SQL is the query language. MySQL is a database server that implements it. PostgreSQL, SQL Server, and Oracle also speak SQL, each with its own dialect.
3. Name the main strengths of MySQL.
Open-source community edition, wide hosting support, solid performance, mature replication, good tooling, and drivers for almost every language (Python, Java, PHP, Node.js, Go).
4. What are a table, a row, and a column?
A table is a structured collection of data. A column defines one attribute (such as email), and a row is one complete record (one customer).
5. What is a primary key?
A column or combination of columns that uniquely identifies every row. It cannot be NULL and a table has at most one.
6. What is a foreign key?
A column that references the primary key of another table to enforce a relationship. Example: orders.customer_id referencing customers.id. Foreign keys are enforced by InnoDB.
7. Which data types does MySQL offer?
- Numeric: TINYINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE
- Text: CHAR, VARCHAR, TEXT
- Date and time: DATE, TIME, DATETIME, TIMESTAMP, YEAR
- Other: JSON, ENUM, BLOB, and spatial types
Booleans are stored as TINYINT(1); TRUE and FALSE are aliases for 1 and 0.
8. What is the difference between CHAR and VARCHAR?
CHAR(n) is fixed length and padded with spaces. VARCHAR(n) stores only what is needed plus a length prefix. Use CHAR for fixed-size codes (country codes) and VARCHAR for variable text.
9. What are constraints?
Rules that protect data quality: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, DEFAULT, and CHECK (enforced since MySQL 8.0.16).
10. What is a view?
A saved SELECT that behaves like a virtual table. It stores no data itself. Views simplify repeated queries and can limit which columns a user sees.
sql
CREATE VIEW active_customers AS
SELECT id, name, email FROM customers WHERE status = ‘active’;
2. Intermediate Questions
11. What is a storage engine? Compare InnoDB and MyISAM.
The engine decides how data is stored and locked. InnoDB (the default) offers transactions, row-level locking, crash recovery, and foreign keys. MyISAM has none of those, uses table-level locks, and is rarely chosen for new work. In 2027, the safe answer is “InnoDB unless there is a specific reason.”
12. Explain the types of JOIN.
- INNER JOIN: only matching rows from both sides
- LEFT JOIN: all left rows, matches from the right (or NULL)
- RIGHT JOIN: the mirror image
- CROSS JOIN: every combination of rows
- Self join: a table joined to itself, such as employees and their managers
MySQL has no FULL OUTER JOIN; emulate it with LEFT JOIN … UNION … RIGHT JOIN.
13. What is an index and why does it matter?
An index is a separate data structure (usually a B-tree) that lets MySQL find rows without scanning the whole table. It speeds up reads and slows down writes slightly, so index the columns used in WHERE, JOIN, and ORDER BY, not everything.
14. What is a composite index and the leftmost-prefix rule?
An index over several columns, e.g. (country, city, created_at). MySQL can use it for queries that filter on country, or country + city, but generally not for city alone.
15. What is a covering index?
An index containing every column a query needs, so MySQL answers from the index without touching the table. EXPLAIN shows this as Using index.
16. Primary key vs. unique key?
Both enforce uniqueness. A table has one primary key, which cannot be NULL, and InnoDB uses it as the clustered index. A table can have many unique keys, and in MySQL a unique column can hold multiple NULL values, since NULL is not equal to NULL.
17. What is a stored procedure?
Named SQL logic stored on the server and run with CALL. It can take parameters, return result sets, and contain control flow.
sql
DELIMITER //
CREATE PROCEDURE get_students_by_age(IN min_age INT)
BEGIN
SELECT id, name FROM students WHERE age >= min_age;
END //
DELIMITER ;
CALL get_students_by_age(20);
18. What is a trigger?
Code that fires automatically before or after INSERT, UPDATE, or DELETE on a table. Common uses: audit logs and auto-filled timestamps. Overuse makes behavior hard to trace, so mention that trade-off.
19. How do you handle NULL?
Test with IS NULL / IS NOT NULL (never = NULL). Replace values with IFNULL(col, ‘n/a’) or COALESCE(a, b, c), which returns the first non-null argument.
20. What is the difference between WHERE and HAVING?
WHERE filters rows before grouping. HAVING filters groups after aggregation.
21. DELETE vs. TRUNCATE vs. DROP?
DELETE removes selected rows, is logged row by row, and can be rolled back. TRUNCATE empties the table quickly, resets AUTO_INCREMENT, and cannot be rolled back (it is DDL). DROP removes the table itself.
22. What is normalization?
Organizing data to reduce redundancy. 1NF: atomic values. 2NF: no partial dependency on part of a composite key. 3NF: no transitive dependencies. Be ready to mention that read-heavy systems sometimes denormalize on purpose.
3. Advanced Questions
23. What are ACID properties?
- Atomicity: all statements in a transaction succeed or none do
- Consistency: data moves from one valid state to another
- Isolation: concurrent transactions do not corrupt each other
- Durability: committed data survives a crash
sql
START TRANSACTION;
UPDATE accounts SET balance = balance – 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; — or ROLLBACK on error
24. What are the transaction isolation levels?
READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (InnoDB default), and SERIALIZABLE. They trade consistency against concurrency by controlling dirty reads, non-repeatable reads, and phantom reads.
25. What is a deadlock and how do you deal with it?
Two transactions each hold a lock the other needs. InnoDB detects this and rolls one back. Reduce deadlocks by accessing tables in a consistent order, keeping transactions short, and indexing properly. Applications should retry the failed transaction. Inspect with SHOW ENGINE INNODB STATUS.
26. How do you read an EXPLAIN plan?
Look at type (aim for const, ref, range; ALL means a full scan), key (index used), rows (estimated rows examined), and Extra (Using filesort or Using temporary are warning signs). EXPLAIN ANALYZE (8.0.18+) also runs the query and shows real timings.
27. How would you optimize a slow query?
- Capture it from the slow query log
- Run EXPLAIN / EXPLAIN ANALYZE
- Add or fix indexes
- Select only needed columns
- Avoid functions on indexed columns in WHERE (WHERE YEAR(d) = 2027 blocks the index; use a date range)
- Paginate efficiently (keyset pagination instead of huge OFFSET)
- Re-measure
28. What are window functions?
Functions that calculate across related rows without collapsing them: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER (…). Available since MySQL 8.0 and very common in interviews.
sql
SELECT name, dept_id, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank
FROM employees;
29. What is a CTE?
A Common Table Expression (WITH … AS) names a subquery for readability. Recursive CTEs handle hierarchies such as org charts and category trees.
sql
WITH RECURSIVE chain AS (
SELECT id, manager_id, name FROM employees WHERE id = 1
UNION ALL
SELECT e.id, e.manager_id, e.name
FROM employees e JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain;
30. Stored function vs. stored procedure?
A function returns exactly one value and can be used inside a SELECT. A procedure is invoked with CALL, can return multiple result sets or OUT parameters, and can perform operations like DML.
31. Temporary table vs. derived table?
A temporary table lives for the session and can be reused across statements. A derived table is a subquery in FROM and exists only for that single statement.
32. How does MySQL support JSON?
A native JSON type with validation, functions like JSON_EXTRACT (or the -> / ->> operators), and the ability to index JSON values through generated columns or functional indexes. Good for semi-structured attributes, not a replacement for proper relational design.
Turn Knowledge Into Job-Ready Skills
Ready to go further in your career? Join Skill Hives to bridge the gap between theory and real-world database engineering.
4. Hands-On Query Challenges
Assume tables employees(id, name, salary, dept_id, email, hire_date) and departments(dept_id, dept_name).
33. Find duplicate names.
sql
SELECT name, COUNT(*) AS cnt
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;
34. Find the Nth highest salary (here N = 3).
Use DENSE_RANK so ties are handled correctly:
sql
SELECT DISTINCT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r
FROM employees
) t
WHERE r = 3;
Alternative: SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;
35. Second-highest salary without LIMIT.
sql
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
36. Headcount per department.
sql SELECT d.dept_name, COUNT(e.id) AS total FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id GROUP BY d.dept_id, d.dept_name;
37. Employees earning above the company average.
sql SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
38. Employees with no valid department.
sql
SELECT e.name
FROM employees e
LEFT JOIN departments d ON d.dept_id = e.dept_id
WHERE d.dept_id IS NULL;
39. Delete duplicates, keep the lowest id.
sql
DELETE e1
FROM employees e1
JOIN employees e2
ON e1.name = e2.name AND e1.id > e2.id;
40. Top earner in each department.
sql
SELECT * FROM (
SELECT e.*, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employees e
) x
WHERE rn = 1;
41. Hired in the last 30 days.
sql
SELECT * FROM employees
WHERE hire_date >= CURDATE() – INTERVAL 30 DAY;
42. Update one table from another.
sql
UPDATE employees e
JOIN departments d ON d.dept_id = e.dept_id
SET e.dept_name = d.dept_name;
5. DBA and Operations Questions
43. What is the slow query log?
A log of statements slower than long_query_time. Enable it dynamically:
sql
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
Summarize it with mysqldumpslow or Percona’s pt-query-digest.
44. Logical vs. physical backups?
Logical (mysqldump, mysqlpump, MySQL Shell dump utilities) export SQL or data files: portable but slower on large data. Physical backups (MySQL Enterprise Backup, Percona XtraBackup, filesystem or cloud snapshots) copy data files: faster to take and restore on big systems. Always test restores, not just backups.
45. How do you manage users and privileges?
Follow least privilege and use roles.
sql
CREATE USER ‘app’@’10.0.%’ IDENTIFIED BY ‘a-strong-secret’;
CREATE ROLE ‘read_only’;
GRANT SELECT ON shop.* TO ‘read_only’;
GRANT ‘read_only’ TO ‘app’@’10.0.%’;
SET DEFAULT ROLE ‘read_only’ TO ‘app’@’10.0.%’;
FLUSH PRIVILEGES is only needed if you edit the grant tables directly, not after normal GRANT or REVOKE.
46. How does replication work, and what changed in recent versions?
The source writes changes to its binary log; replicas copy them into a relay log and apply them. Current MySQL uses inclusive terminology and syntax:
sql
— On the source
SHOW BINARY LOG STATUS; — replaces SHOW MASTER STATUS in 8.4+
— On the replica
CHANGE REPLICATION SOURCE TO
SOURCE_HOST=’10.0.0.10′,
SOURCE_USER=’repl’,
SOURCE_PASSWORD=’secret’,
SOURCE_AUTO_POSITION=1; — GTID-based
START REPLICA;
SHOW REPLICA STATUS\G
The older CHANGE MASTER TO, START SLAVE, and SHOW SLAVE STATUS forms are deprecated or removed in recent releases, so use the new names in interviews. Modes include asynchronous, semi-synchronous, and Group Replication / InnoDB Cluster for high availability.
47. What is replication lag and how do you reduce it?
Lag is the delay between a change on the source and its application on a replica (Seconds_Behind_Source). Causes: large transactions, weak replica hardware, network issues, single-threaded apply. Fixes: break big writes into batches, enable multi-threaded replicas (replica_parallel_workers), improve I/O, keep heavy reporting off lagging replicas.
48. What are binary logs used for?
Replication, point-in-time recovery, and auditing changes. Choose ROW format for safety in most cases.
49. How do you do point-in-time recovery?
Restore the last full backup, then replay binary logs up to just before the mistake:
bash
mysqlbinlog –start-position= –stop-datetime=”2027-01-15 14:00:00″ binlog.000042 | mysql -u root -p
50. How do you monitor a MySQL server and check table health?
Use SHOW PROCESSLIST, SHOW GLOBAL STATUS, and the performance_schema and sys schemas for waits, top queries, and lock contention; pair them with a metrics stack such as Prometheus with mysqld_exporter, Percona Monitoring and Management, or your cloud provider’s tools. For table checks use CHECK TABLE; InnoDB generally recovers itself after a crash, so repair tools are mostly relevant to legacy MyISAM tables.
6. How to Prepare (Skill Hives Study Plan)
- Lock down the fundamentals. Keys, joins, indexes, normalization, and transactions come up in nearly every interview.
- Write queries every day. Install MySQL locally or in Docker and load the Sakila or Employees sample database.
- Learn modern SQL. Window functions and CTEs now appear constantly; many candidates still skip them.
- Read execution plans. Being able to explain why a query is slow separates strong candidates from average ones.
- Practice failure scenarios. Break replication, simulate a deadlock, restore from a backup, run a point-in-time recovery.
- Think out loud. Interviewers reward clear reasoning, so state your assumptions before you write the query.
- Know your tooling. MySQL Workbench, MySQL Shell, DBeaver, and your own preferred client are all fair game.
- Revisit regularly. Check the release notes for the LTS version your target company uses.
Ready to go further? Skill Hives helps learners turn knowledge into job-ready skills. Bookmark this guide, practice the challenges above, and come back before every interview.
FAQ
What should I expect in a MySQL interview?
A mix of concept questions (keys, indexes, transactions), live query writing, and for senior roles, performance tuning and operations scenarios.
Is MySQL enough for a full-stack role?
It is important but not sufficient. You will also be tested on a backend language, APIs, a frontend framework, and deployment basics.
Do data analysts get MySQL questions?
Yes. Expect joins, aggregation, window functions, and date handling.
How can I practice for free?
Use MySQL Community Edition with sample databases, plus free SQL practice sites and the official MySQL documentation.
Which version should I study?
Focus on MySQL 8.4 LTS features, and skim what is new in the 9.x line.
Practice SQL Like a Pro
Build hands-on expertise with sample databases, modern window functions, and execution plans to stand out in any tech interview.
Recent Articles
Top 50 MySQL Interview Questions and Answers for 2027
How to Implement Form Validation in React Native: Step-by-Step Guide
Data Analyst Course in Chandigarh: Fee, Syllabus & Career Scope
Top 40+ Angular Interview Questions and Answers for 2027