Tags: mysql, sql, joins, indexing, stored-procedures, triggers, replication, explain, database Last updated: 2026-06-26

MySQL Cheatsheet

Quick Reference

Concept	Example
JOIN	`FROM a JOIN b ON a.id = b.a_id`
CREATE INDEX	`CREATE INDEX idx ON t (col)`
Stored procedure	`CREATE PROCEDURE n() BEGIN ... END`
Trigger	`CREATE TRIGGER n BEFORE INSERT ...`
EXPLAIN	`EXPLAIN SELECT ...`
EXPLAIN ANALYZE	`EXPLAIN ANALYZE SELECT ...` (8.0.18+)
Replication	`SHOW REPLICA STATUS\G`
Full-text	`MATCH(col) AGAINST('term')`

Joins

SELECT u.name, o.total FROM users u
JOIN orders o ON u.id = o.user_id;

SELECT u.name, o.total FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

Indexing

CREATE INDEX idx_email ON users (email);
CREATE UNIQUE INDEX idx_uq ON users (email);
CREATE INDEX idx_user_date ON orders (user_id, created_at DESC);
CREATE FULLTEXT INDEX idx_body ON posts (title, body);

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
EXPLAIN FORMAT=JSON SELECT ...;
EXPLAIN ANALYZE SELECT ...;    -- 8.0.18+

Stored Procedures

DELIMITER //
CREATE PROCEDURE GetOrders(IN uid INT)
BEGIN
  SELECT * FROM orders WHERE user_id = uid;
END //
DELIMITER ;
CALL GetOrders(1);

Triggers

DELIMITER //
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END //
DELIMITER ;

Replication Basics

-- my.cnf
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='master', SOURCE_USER='r',
  SOURCE_PASSWORD='p', SOURCE_LOG_FILE='...',
  SOURCE_LOG_POS=157;
START REPLICA;
SHOW REPLICA STATUS\G

EXPLAIN Analysis

Column	Meaning
`type`	ALL=bad, ref=good, const=best
`key`	Index used
`rows`	Estimated rows scanned
`Extra`	Using index=good, filesort/temporary=bad

Tips

InnoDB is the default engine — transactions, row-level locking.
Set innodb_buffer_pool_size to ~70% of available RAM.
Use EXPLAIN ANALYZE (8.0.18+) for actual row counts and timings.