DEV Community

Cover image for Top 30 MySQL Interview Questions and Answers (2025)
Laravel Daily tips
Laravel Daily tips

Posted on

Top 30 MySQL Interview Questions and Answers (2025)

🔥 Top 30 MySQL Interview Questions and Answers for 2025

Whether you're a backend developer, data engineer, or full-stack pro, MySQL is a must-know skill. Here are 30 top MySQL questions and answers that will sharpen your knowledge and help you ace technical interviews in 2025.

1. What is the difference between INNER JOIN, LEFT JOIN, and RIGHT JOIN?

Answer:

  • INNER JOIN: Returns rows with matching values in both tables.
  • LEFT JOIN: Returns all rows from the left table and matched rows from the right.
  • RIGHT JOIN: Returns all rows from the right table and matched rows from the left.

2. What are the differences between MyISAM and InnoDB storage engines?

Feature MyISAM InnoDB
Transactions ❌ No ✅ Yes
Foreign Keys ❌ No ✅ Yes
Locking Table-level Row-level
Performance Faster read Better for writes
Crash Recovery Poor Good

3. What is the difference between UNION and UNION ALL?

Answer:

  • UNION: Combines and removes duplicates.
  • UNION ALL: Combines all rows including duplicates.

4. Explain indexing and types of indexes in MySQL.

Answer:

Indexes speed up query execution. Types include:

  • Primary Index
  • Unique Index
  • Full-text Index
  • Spatial Index
  • Composite Index

5. What is the purpose of EXPLAIN in MySQL?

Answer:

EXPLAIN shows the query execution plan. It helps in optimizing slow queries by analyzing how tables are scanned and joined.

6. How does AUTO_INCREMENT work in MySQL?

Answer:

It auto-generates a unique value for a column, usually used for primary keys. It increments by 1 (or configured step) for each new row.

7. What are derived tables and common table expressions (CTEs)?

Answer:

  • Derived Table: A subquery used as a table in FROM clause.
  • CTE: A temporary result set using WITH keyword for better readability and recursion.

8. Difference between NOW(), CURRENT_TIMESTAMP(), SYSDATE()?

Answer:

  • NOW() & CURRENT_TIMESTAMP(): Return the current timestamp at query start.
  • SYSDATE(): Returns the current timestamp at function execution time.

9. What is the difference between CHAR and VARCHAR?

Answer:

  • CHAR: Fixed-length, faster, padded with spaces.
  • VARCHAR: Variable-length, saves space but slightly slower.

10. How is ACID compliance achieved in MySQL?

Answer:

  • Atomicity: All-or-nothing transactions.
  • Consistency: Data remains valid after operations.
  • Isolation: Concurrent transactions don’t interfere.
  • Durability: Changes persist after commit.

✅ InnoDB engine supports full ACID compliance.

11. What is the difference between TRUNCATE, DELETE, and DROP?

Answer:

  • DELETE: Removes rows with WHERE, logged.
  • TRUNCATE: Removes all rows, faster, resets AUTO_INCREMENT.
  • DROP: Deletes the table structure completely.

12. Explain replication in MySQL.

Answer:

Replication allows one (master) database to copy data to one or more (slave) databases for redundancy and load distribution.

Types:

  • Master-Slave
  • Master-Master
  • Group Replication

13. What is query optimization and how to achieve it?

Answer:

  • Use indexes
  • Avoid SELECT *
  • Use LIMIT
  • Reduce joins/subqueries
  • Use EXPLAIN

14. What is the difference between WHERE and HAVING clause?

Answer:

  • WHERE: Filters rows before aggregation.
  • HAVING: Filters after GROUP BY aggregation.

15. How does foreign key constraint work in MySQL?

Answer:

A foreign key ensures referential integrity by linking a column in one table to a primary key in another. Enforced in InnoDB.

16. What are stored procedures and why are they used?

Answer:

Stored procedures are precompiled SQL statements stored in the DB.

✅ Used for reusability, security, and performance.

17. Explain difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)?

Answer:

  • COUNT(*): Counts all rows.
  • COUNT(column): Ignores NULLs.
  • COUNT(DISTINCT column): Counts unique non-null values.

18. What are views in MySQL?

Answer:

A view is a virtual table created from a SELECT query. It simplifies complex queries and provides security by exposing only needed data.

19. What’s the difference between DELETE with WHERE and TRUNCATE?

Answer:

  • DELETE: Removes selected rows (logged).
  • TRUNCATE: Removes all rows (faster, not logged row-by-row).

20. How to handle concurrency and deadlocks in MySQL?

Answer:

  • Use proper transaction isolation levels
  • Row-level locking (InnoDB)
  • Keep transactions short
  • Retry failed deadlocks

21. What is the difference between Sharding and Partitioning in MySQL?

Sharding

  • Splits data across multiple databases or servers
  • Requires app-level routing logic
  • Useful for horizontal scaling

Example:

Users with user_id % 4 = 0 go to DB1, others to DB2, etc.

Partitioning

  • Splits a single table logically within one DB server
  • Handled natively by MySQL (RANGE, HASH, LIST)
  • Improves large-table performance

Example:

Orders partitioned by year (order_date) into 2022, 2023, 2024.

22. What is the difference between a Transaction and a Deadlock in MySQL?

Transaction:

  • Set of SQL operations treated as a single unit
  • Supports ACID properties
  • Commit or rollback whole block

Deadlock:

  • Occurs when two transactions block each other
  • MySQL resolves it by rolling back one
  • Avoided with consistent locking and short transactions

23. How does indexing work in MySQL and what are the best practices?

Answer:

  • Speeds up SELECT, JOIN, WHERE
  • MySQL uses B-Trees for most indexes
  • Primary Key = clustered index
  • Avoid over-indexing
  • Use composite indexes with leftmost prefix rule
  • Index high-cardinality columns

24. How can you optimize a slow-running query in MySQL?

Answer:

  • Use EXPLAIN
  • Add indexes to WHERE, JOIN, ORDER BY
  • Avoid SELECT *
  • Use LIMIT
  • Rewrite subqueries as JOINs
  • Partition large tables
  • Avoid functions on indexed columns (WHERE YEAR(date))

25. How do you find the second highest salary from a table? 

SELECT MAX(salary) 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

-- OR --

SELECT * 
FROM salary 
ORDER BY salary DESC 
LIMIT 1 OFFSET 1;
Enter fullscreen mode Exit fullscreen mode

25. How do you find the second highest salary from a table? 

SELECT MAX(salary) 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

-- OR --

SELECT * 
FROM salary 
ORDER BY salary DESC 
LIMIT 1 OFFSET 1;
Enter fullscreen mode Exit fullscreen mode

26. How to get duplicate records from a table? 

SELECT name, COUNT(*) 
FROM users 
GROUP BY name 
HAVING COUNT(*) > 1;
Enter fullscreen mode Exit fullscreen mode

27. How to fetch the latest order per customer? 

SELECT * 
FROM orders o1
WHERE order_date = (
  SELECT MAX(order_date) 
  FROM orders o2 
  WHERE o1.customer_id = o2.customer_id
);
Enter fullscreen mode Exit fullscreen mode

28. How to delete duplicate rows but keep one? 

DELETE FROM users 
WHERE id NOT IN (
  SELECT MIN(id) 
  FROM users 
  GROUP BY email
);
Enter fullscreen mode Exit fullscreen mode

29. How to retrieve Nth highest salary (e.g., 3rd highest)? 

SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) AS temp
WHERE rnk = 3;
Enter fullscreen mode Exit fullscreen mode

30. How to get departments with more than 5 employees? 

SELECT department_id, COUNT(*) 
FROM employees 
GROUP BY department_id 
HAVING COUNT(*) > 5;
Enter fullscreen mode Exit fullscreen mode

Conclusion

These 30 MySQL Interview Questions and Answers for 2025 are ideal for intermediate and advanced developers preparing for backend, full-stack, or data-related roles. Practice the theory and master the SQL query patterns to stand out in interviews.

📚 Want more Laravel tips?

Visit LaravelDailyTips.com for practical guides, interview questions, and tricks.

👉 Read the original article here

Top comments (1)

Some comments may only be visible to logged-in visitors. Sign in to view all comments.