MySQL Interview Questions: Scenario-Based Questions

Optimize a Sample Query

Given a slow query joining two tables, analyze it using EXPLAIN and suggest improvements.

Expected output: A list of optimization steps including index creation and query rewriting.

Hint: Look for full table scans and missing indexes in the EXPLAIN output.

Design a Transaction Scenario

Create a transaction that transfers funds between two accounts ensuring atomicity.

Expected output: A SQL script demonstrating a safe funds transfer transaction.

Hint: Use START TRANSACTION, COMMIT, and ROLLBACK commands appropriately.

I would start by using EXPLAIN to analyze the query plan, check for missing indexes on join columns, add indexes if necessary, and rewrite the query to select only needed columns or simplify joins.

I would use transaction control commands like ROLLBACK to undo changes made during the transaction, ensuring data consistency, and then investigate the cause before retrying or aborting.

Stored procedures are useful for encapsulating complex business logic on the database side, such as batch updates or calculations that need to be executed repeatedly and atomically.