MySQL — Cheat Sheet

Tags: mysql, sql, database, rdbms, backend, indexing

Level: intermediate

TL;DR: Quick reference for MySQL architecture, core SQL operations, optimization techniques, and common technical interview questions.

Key concepts

Relational Database Management System (RDBMS) based on structured query language.

ACID properties guarantee transaction validity: Atomicity, Consistency, Isolation, Durability.

Normalization minimizes data redundancy across tables (1NF, 2NF, 3NF, BCNF).

Indexes (B-Tree, Hash) speed up data retrieval operations at the cost of slower writes.

InnoDB is the default transactional storage engine with row-level locking and foreign key support.

MyISAM is a non-transactional engine with table-level locking, suitable for read-heavy workloads.

Joins combine rows from two or more tables based on a related column (INNER, LEFT, RIGHT, CROSS).

Views are virtual tables based on the result-set of an SQL statement.

Stored Procedures are prepared SQL codes that you can save and reuse.

Typical questions and ready answers

Q: What is the difference between InnoDB and MyISAM?A: InnoDB supports transactions, row-level locking, and foreign keys. MyISAM lacks transaction support and uses table-level locking, but is faster for read-heavy operations.

Q: How do you optimize a slow query?A: I prepend EXPLAIN to the query to analyze the execution plan. I look for full table scans, ensure appropriate indexes are used, and avoid SELECT *.

Q: What is the N+1 query problem and how do you fix it?A: It happens when code executes one query for a list, then N queries for related data. I resolve it using JOINs or an IN clause.

Pitfalls and common mistakes

Creating too many indexes, which drastically degrades the performance of INSERT, UPDATE, and DELETE operations.

Using SELECT * instead of explicitly naming required columns, leading to excessive memory usage and network load.

Failing to use prepared statements, leaving the application vulnerable to SQL injection attacks.

Not properly defining Foreign Key constraints, which can lead to orphaned records and compromised data integrity.

Snippets and commandsSQL

```-- Analyze query execution plan

EXPLAIN SELECT name, email FROM users WHERE status = 'active';

-- Create an index on a frequently queried column

CREATE INDEX idx_user_status ON users(status);

-- Basic INNER JOIN example

SELECT u.name, o.order_date

FROM users u

INNER JOIN orders o ON u.id = o.user_id

WHERE u.status = 'active';

-- Start and commit a transaction

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

```