mySQL Interview Questions

Feature	InnoDB	MyISAM
Transactions	Supported	Not supported
Row Locking	Yes	No
Foreign Keys	Supported	Not supported

InnoDB is the default storage engine because it supports transactions and better concurrency.

A clustered index determines the physical order of rows in a table. In MySQL InnoDB, the primary key acts as the clustered index.

Command	Description
DELETE	Removes specific rows
TRUNCATE	Deletes all rows quickly
DROP	Removes table structure and data

A composite index is an index on multiple columns. Example:

CREATE INDEX idx_user_name_email
ON users(name,email);

Normalization organizes tables to reduce redundancy.

• 1NF – Atomic columns
• 2NF – Remove partial dependency
• 3NF – Remove transitive dependency

WHERE filters rows before grouping. HAVING filters aggregated results. Example:

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

Indexes improve query performance by allowing the database engine to locate rows faster instead of scanning the entire table.

Join Type	Description
INNER JOIN	Returns matching rows only
LEFT JOIN	Returns all rows from left table

ACID properties guarantee transaction reliability:

• Atomicity
• Consistency
• Isolation
• Durability

EXPLAIN shows how MySQL executes a query. Example:

EXPLAIN SELECT * FROM users
WHERE email='user@email.com';

It helps identify slow queries.

A deadlock occurs when two transactions wait for each other to release locks. MySQL detects and resolves deadlocks automatically.

A foreign key creates a relationship between two tables.

FOREIGN KEY (customer_id)
REFERENCES customers(id);

Query optimization improves query performance using indexes, optimized joins, and efficient query plans.

A view is a virtual table created from a query.

CREATE VIEW active_users AS
SELECT id, name
FROM users
WHERE status='ACTIVE';

A stored procedure is a reusable SQL program stored in the database.

CREATE PROCEDURE getUsers()
BEGIN
SELECT * FROM users;
END;

Replication copies data from a primary database to replica databases to improve availability and read scalability.

Sharding distributes data across multiple databases to handle large datasets and high traffic.

Common causes:

• Missing indexes
• Large table scans
• Unoptimized joins

A covering index contains all the columns needed by a query, eliminating the need to read the table.

Steps:

• Use EXPLAIN
• Add proper indexes
• Avoid SELECT *
• Optimize joins
• Limit returned rows