Advanced MySQL Interview Questions

Analyze a Slow Query

Use EXPLAIN on a provided slow query and identify possible optimization strategies.

Expected output: A report detailing query plan analysis and recommended indexes.

Hint: Look for full table scans and missing indexes.

Implement a Transaction

Write SQL statements to perform a bank transfer using transactions ensuring atomicity.

Expected output: SQL code that updates balances atomically.

Hint: Use START TRANSACTION and COMMIT statements.

Describe Replication Setup

Outline the steps to configure master-slave replication in MySQL.

Expected output: A step-by-step replication setup guide.

Hint: Include configuration changes on master and slave servers.

MySQL uses indexes to quickly locate rows matching query conditions, reducing the need for full table scans. Indexes like B-tree support range queries, while hash indexes optimize equality searches. Proper indexing improves query performance significantly.

Isolation levels control how transaction changes are visible to others. READ UNCOMMITTED allows dirty reads; READ COMMITTED prevents dirty reads but allows non-repeatable reads; REPEATABLE READ prevents non-repeatable reads but allows phantom reads; SERIALIZABLE prevents all these anomalies by fully isolating transactions.

MySQL supports statement-based and row-based replication. Statement-based replicates SQL statements, while row-based replicates actual data changes. Semi-synchronous replication ensures the master waits for at least one slave to acknowledge changes, improving durability. Replication is used for scaling reads and high availability.