Advanced MySQL Interview Questions – Part 2: Transactions, Joins, and Storage Engines
Advanced MySQL Interview Questions – Part 2:
Transactions, Joins, and Storage Engines
Welcome back to
our three-part series on Advanced MySQL Interview Questions.
In Part 1 we explored foundational concepts like UNION,
subqueries, stored procedures, and indexing. In this Part 2, we’re
diving deeper into:
- How MySQL handles transactions
- The differences between storage engines
- Key JOIN strategies
- Views, temporary tables, and full-text search
Let’s unpack these
powerful tools and concepts that drive performance and data integrity in
production-level databases.
🔹 7. What is a transaction in MySQL, and how
does it work?
✅ Answer:
A transaction is
a sequence of one or more SQL statements that execute as a single unit
of work. It ensures data integrity even in the event of
system failures or user errors.
🔑 Properties of Transactions (ACID):
- Atomicity:
All operations succeed or none do.
- Consistency:
The database remains valid before and after the transaction.
- Isolation:
Transactions don’t interfere with each other.
- Durability:
Once committed, changes are permanent.
🔍 Example:
START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;
If any update fails, you
can use:
ROLLBACK;
🚀 MySQL Commands:
- START TRANSACTION or BEGIN
- COMMIT
- ROLLBACK
- SAVEPOINT and ROLLBACK TO SAVEPOINT for
finer control
⚠️ Important:
Only certain MySQL
storage engines (like InnoDB) support transactions.
🔹 8. What is the difference between MyISAM
and InnoDB storage engines in MySQL?
✅ Answer:
MySQL supports multiple
storage engines, but the two most common are MyISAM and InnoDB.
|
Feature |
MyISAM |
InnoDB |
|
Transactions |
❌ Not Supported |
✅ Supported (ACID-compliant) |
|
Foreign Keys |
❌ Not Supported |
✅ Supported |
|
Locking |
Table-level |
Row-level |
|
Crash Recovery |
Limited |
Robust |
|
Speed |
Fast reads |
Balanced read/write |
|
Storage |
Smaller footprint |
Slightly larger due to overhead |
💡 Use MyISAM for:
- Read-heavy workloads
- Simpler, non-critical tables
💡 Use InnoDB for:
- Apps requiring data integrity
- Concurrent access
- Transactional operations
🔹 9. What is a database index, and how is it
used in MySQL?
✅ Answer:
A database index is
a structure that improves the speed of data retrieval at the cost of additional
storage and slower write performance.
MySQL Index Types:
- Primary Index:
Automatically created on primary key.
- Secondary Index:
Created on other columns using CREATE
INDEX.
- Composite Index:
Multi-column indexes.
- Full-Text Index:
For textual search (MATCH ... AGAINST).
🔍 Use Case:
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = 'user@example.com';
Indexing improves
performance significantly when used on columns involved in:
- WHERE clauses
- JOIN conditions
- ORDER BY clauses
⚠️ Indexing too many
columns can hurt write performance. Use selectively.
🔹 10. What is normalization, and why is it
important in database design?
✅ Answer (Expanded Recap):
Normalization is the
process of structuring a relational database to reduce redundancy and
improve data integrity.
✅ Goals of Normalization:
- Eliminate duplication
- Ensure data consistency
- Simplify updates and deletions
🔢 Normal Forms:
1.
1NF – Atomic values only, no repeating groups
2.
2NF – No partial dependency on primary key
3.
3NF – No transitive dependency
4.
BCNF – Each determinant is a candidate key
5.
4NF/5NF – Advanced decomposition for complex
relationships
🔹 11. What is the difference between a LEFT
JOIN and an INNER JOIN in MySQL?
✅ Answer:
Both JOINs are used to
combine data from multiple tables, but they behave differently when
there's no match in one of the tables.
🔍 INNER JOIN:
Returns only rows with
matching values in both tables.
SELECT a.name, b.department
FROM employees a
INNER JOIN departments b
ON a.dept_id = b.id;
🔍 LEFT JOIN (LEFT OUTER JOIN):
Returns all rows from
the left table, and matched rows from the right. If no match, NULLs are
returned for right-side columns.
SELECT a.name, b.department
FROM employees a
LEFT JOIN departments b
ON a.dept_id = b.id;
|
Join
Type |
Records
Returned |
|
INNER JOIN |
Only matching records in both
tables |
|
LEFT JOIN |
All records from the left +
matched (or NULL) right |
🧠 Use LEFT JOIN
when you want to retain unmatched rows from the first table.
🔹 12. What is a trigger in MySQL, and how is
it used?
✅ Answer:
A trigger in
MySQL is a set of SQL statements that execute automatically in
response to a data modification event (INSERT, UPDATE, or DELETE).
🛠 Syntax:
CREATE TRIGGER trg_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO audit_log
(order_id, action, timestamp)
VALUES (NEW.id, 'INSERT',
NOW());
END;
⚙️ Use Cases:
- Enforcing business logic
- Maintaining audit trails
- Updating derived tables
- Validating or rejecting changes
🧠 Avoid using
triggers for core business logic—they can be hard to debug and test.
🔹 13. What is a subquery in MySQL, and how
is it used?
✅ Answer:
A subquery is
a query nested within another SQL query. It can return:
- A single value (scalar subquery)
- A row or column
- A complete result set
🔍 Example:
SELECT name
FROM employees
WHERE salary > (
SELECT AVG(salary) FROM
employees
);
Types of subqueries:
- In SELECT, WHERE, or FROM clauses
- Correlated subqueries (depend on outer query)
- Scalar subqueries (return one value)
- Multi-row subqueries (IN, ANY, ALL)
🧠 Tip:
Use JOINs when
possible for better performance, especially with large datasets.
🔹 14. What is the difference between a clustered
index and a non-clustered index in MySQL?
✅ Answer:
|
Index
Type |
Description |
|
Clustered Index |
Sorts and stores table rows based
on key order (InnoDB uses this by default) |
|
Non-Clustered Index |
Separate structure storing only
index values and pointers to table rows |
- Clustered Index:
Only one per table, typically the primary key.
- Non-Clustered Index:
Can be many; optimized for fast lookups on non-key columns.
🧠 In MySQL
InnoDB, the primary key is the clustered index by default.
🔹 15. What is a stored function in MySQL,
and how is it used?
✅ Answer:
A stored
function is similar to a stored procedure but is designed to return
a single value and is often used in SQL expressions.
🔍 Example:
CREATE FUNCTION get_total_salary(dept_id INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE total
DECIMAL(10,2);
SELECT SUM(salary) INTO
total FROM employees WHERE department_id = dept_id;
RETURN total;
END;
Call it like:
SELECT get_total_salary(3);
📌 Use Cases:
- Reusable calculations
- Business rules
- Data transformations within SELECTs
🔮 Coming Up in Part 3:
In the final
part, we’ll cover:
- Primary vs. Unique Keys
- Views and Temporary Tables
- Full-text Indexing
- Interview tricks for handling large datasets
- Bonus: Common pitfalls and optimization tips
✅ Related Post
Post a Comment