MySQL Interview Questions

Core Concepts

What is the difference between MyISAM and InnoDB?

Feature	MyISAM	InnoDB
Transactions	No	Yes (ACID)
Foreign keys	No	Yes
Row-level locking	No (table-level)	Yes
Full-text search	Yes	Yes (5.6+)
Default since	MySQL 5.5	MySQL 5.5+

InnoDB is the default and recommended engine for most applications.

---

What is the difference between CHAR and VARCHAR?

• CHAR(n) — fixed-length, always uses n bytes. Faster for fixed-size data (e.g., country codes).
• VARCHAR(n) — variable-length, uses only as much space as needed plus 1-2 bytes for length. Better for strings of variable size.

---

What are the different types of JOINs?

-- INNER JOIN: only matching rows in both tables
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

-- LEFT JOIN: all rows from left, matching from right (NULL if no match)
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

-- RIGHT JOIN: all rows from right, matching from left
SELECT c.name, o.id
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

-- CROSS JOIN: cartesian product
SELECT a.name, b.name
FROM employees a
CROSS JOIN departments b;

---

Indexes

What is an index and when should you use one?

An index is a data structure that speeds up SELECT queries at the cost of slower INSERT/UPDATE/DELETE and extra storage.

Use indexes on:

• Columns in WHERE clauses
• Columns used in JOIN conditions
• Columns in ORDER BY or GROUP BY
• Foreign key columns

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_composite ON orders(customer_id, created_at);

---

What is the difference between a clustered and non-clustered index?

• Clustered index — the table data is physically stored in the order of this index. InnoDB uses the primary key as the clustered index automatically.
• Non-clustered index — a separate structure with pointers to the actual rows. All non-primary indexes in InnoDB are non-clustered.

---

What is a covering index?

A covering index contains all the columns a query needs, so MySQL can satisfy the query from the index alone without touching the table data:

-- Query
SELECT customer_id, created_at FROM orders WHERE customer_id = 5;

-- Covering index — both columns are in the index
CREATE INDEX idx_covering ON orders(customer_id, created_at);

---

Transactions and Locking

What are the ACID properties?

• Atomicity — all operations in a transaction succeed or all are rolled back
• Consistency — the database moves from one valid state to another
• Isolation — concurrent transactions don’t interfere with each other
• Durability — committed transactions survive crashes

---

What are transaction isolation levels?

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	Yes	Yes	Yes
READ COMMITTED	No	Yes	Yes
REPEATABLE READ (default)	No	No	Yes
SERIALIZABLE	No	No	No

---

What is a deadlock?

Two transactions each hold a lock the other needs, causing both to wait forever. MySQL detects deadlocks and rolls back one transaction automatically.

-- Transaction 1               -- Transaction 2
BEGIN;                         BEGIN;
UPDATE accounts SET bal=...    UPDATE accounts SET bal=...
  WHERE id = 1;                  WHERE id = 2;
-- waits for lock on id=2      -- waits for lock on id=1
-- DEADLOCK detected

Prevention: always acquire locks in the same order.

---

Performance

How do you analyze a slow query?

EXPLAIN SELECT * FROM orders WHERE customer_id = 5;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;

Key columns to check:

• type: ALL (full scan, bad) → ref or const (index use, good)
• rows: estimated rows examined — lower is better
• Extra: Using filesort or Using temporary are warning signs

---

What is query caching and is it recommended?

MySQL’s query cache was deprecated in MySQL 5.7 and removed in MySQL 8.0. It caused significant contention. Use application-level caching (Redis, Memcached) instead.

---

Stored Procedures and Functions

-- Stored Procedure
DELIMITER //
CREATE PROCEDURE GetOrdersByCustomer(IN cust_id INT)
BEGIN
    SELECT * FROM orders WHERE customer_id = cust_id;
END //
DELIMITER ;

CALL GetOrdersByCustomer(42);

-- Function
DELIMITER //
CREATE FUNCTION CalculateDiscount(price DECIMAL(10,2), pct INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN price * (1 - pct / 100);
END //
DELIMITER ;

SELECT CalculateDiscount(100.00, 20);  -- returns 80.00

---

Quick Reference: Common Commands

-- Show databases and tables
SHOW DATABASES;
SHOW TABLES;
DESCRIBE orders;

-- Check indexes
SHOW INDEX FROM orders;

-- Check slow queries
SHOW VARIABLES LIKE 'slow_query_log%';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- Table size
SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY size_mb DESC;