Top 30 MySQL Interview Questions and Answers (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?

Answer:

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:

• 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 include:

• Master-Slave

• Master-Master

• Group Replication

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

Answer:
Improving performance of SQL queries by:

• Using indexes

• Avoiding SELECT *

• Using LIMIT

• Reducing joins/subqueries

• Using 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 with WHERE deletes 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, and retry failed deadlocks.

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

Sharding:

• Splits data across multiple databases or servers.

• Each shard contains a portion of the total data.

• Requires application-level logic to route queries to the correct shard.

• Useful for horizontal scaling in large distributed systems.

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

Partitioning:

• Splits a single table into logical segments within the same database server.

• Managed natively by MySQL (e.g., RANGE, HASH, LIST partitions).

• Helps with performance on very large tables by reducing scanned rows.

• Example: Orders partitioned by year (order_date) into 2022, 2023, 2024 partitions.

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

Transaction:

• A set of SQL operations executed as a single unit.

• Ensures ACID properties: Atomicity, Consistency, Isolation, Durability.

• You can either commit (save) or rollback (undo) the entire transaction.

• Used in critical operations like payments, inventory updates, etc.

Deadlock:

• Occurs when two or more transactions block each other, each waiting for a resource held by the other.

• MySQL automatically detects and resolves it by rolling back one transaction.

• Usually happens due to conflicting row locks in concurrent transactions.

• Can be avoided by consistent locking order, short transactions, and avoiding long waits inside transactions.

23. How does indexing work in MySQL and what are the best practices for using indexes effectively?

Answer:

• Indexes speed up SELECT, JOIN, and WHERE clause operations by reducing the number of rows scanned.

• MySQL uses B-Trees for most indexes (except for FULLTEXT or spatial indexes).

• Indexes are automatically used only when the query planner decides it’s efficient.

• Primary Key creates a clustered index automatically.

• Avoid over-indexing, too many indexes slow down INSERT, UPDATE, DELETE.

• Use composite indexes (multi-column) wisely: order matters (leftmost prefix rule).

• Index high-cardinality columns (i.e., more unique values) for better performance.

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

Answer:

• Use EXPLAIN to analyze query execution.

• Add appropriate indexes on WHERE, JOIN, and ORDER BY columns.

• Avoid using SELECT *; select only needed columns.

• Use LIMIT for large result sets.

• Rewrite subqueries as JOINs when possible.

• Partition large tables if needed.

• Avoid functions on indexed columns (e.g., 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;

26. How to get duplicate records from a table?

SELECT name, COUNT(*) 
FROM users 
GROUP BY name 
HAVING COUNT(*) > 1;

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
);

28. How to delete duplicate rows but keep one?

DELETE FROM users 
WHERE id NOT IN (
  SELECT MIN(id) 
  FROM users 
  GROUP BY email
);

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;

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

SELECT department_id, COUNT(*) 
FROM employees 
GROUP BY department_id 
HAVING COUNT(*) > 5;

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 for practical guides, interview questions, and tricks.

Subscribe now and get battle-tested Laravel insights delivered to your inbox before anyone else!