MySQL Cheat Sheet
Essential MySQL commands for connecting, writing SQL, managing indexes, and choosing storage engines and data types for reliable relational apps.
2 PagesIntermediateMar 5, 2026
Connecting & Basics
CLI connection and schema browsing.
sql
mysql -u root -pSHOW DATABASES;USE mydb;SHOW TABLES;DESCRIBE users;SOURCE dump.sql; -- run a .sql file
SQL Essentials
Core DDL and DML statements.
sql
CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE, created_at DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;INSERT INTO users (email) VALUES ('[email protected]');SELECT * FROM users WHERE email LIKE '%example%';UPDATE users SET email = '[email protected]' WHERE id = 1;DELETE FROM users WHERE id = 1;
Indexes & EXPLAIN
Adding indexes and inspecting execution plans.
sql
CREATE INDEX idx_email ON users(email);ALTER TABLE users ADD INDEX idx_created (created_at);SHOW INDEX FROM users;EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
Storage Engines & Types
Engine choices and common column types.
- InnoDB- default engine; supports transactions, foreign keys, row-level locking
- MyISAM- legacy engine; no transactions, table-level locks only
- VARCHAR(n)- variable-length string up to n characters
- TEXT- large variable-length string, stored off-row
- DECIMAL(p,s)- exact fixed-precision numeric type
- JSON- native JSON column type with validation (5.7+)
- ENUM- string restricted to a fixed, predefined list of values
Transactions & Isolation Levels
Controlling transaction boundaries and concurrency isolation.
sql
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- InnoDB defaultSTART TRANSACTION;UPDATE accounts SET balance = balance - 100 WHERE id = 1;SAVEPOINT before_credit;UPDATE accounts SET balance = balance + 100 WHERE id = 2;-- ROLLBACK TO SAVEPOINT before_credit; -- undo just the second updateCOMMIT;-- Inspect current locks and blocking transactionsSELECT * FROM performance_schema.data_locks;SELECT * FROM sys.innodb_lock_waits;
Window Functions
Ranking and running aggregates without collapsing rows (MySQL 8+).
sql
SELECT id, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk, SUM(salary) OVER (PARTITION BY department) AS dept_total, LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salaryFROM employees;-- Top earner per departmentSELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees e) t WHERE rn = 1;
Recursive CTEs
Walking hierarchical data such as org charts or category trees.
sql
WITH RECURSIVE org_chart AS ( SELECT id, name, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, oc.depth + 1 FROM employees e JOIN org_chart oc ON e.manager_id = oc.id)SELECT * FROM org_chart ORDER BY depth, name;
JSON Columns & Functions
Querying and updating native JSON columns in place.
sql
SELECT payload->>'$.user.email' AS email -- unquoted extractFROM eventsWHERE JSON_EXTRACT(payload, '$.type') = 'signup';UPDATE eventsSET payload = JSON_SET(payload, '$.processed', TRUE)WHERE id = 42;SELECT * FROM eventsWHERE JSON_CONTAINS(payload, '"admin"', '$.roles');ALTER TABLE events ADD COLUMN event_type VARCHAR(50) GENERATED ALWAYS AS (payload->>'$.type') STORED, ADD INDEX idx_event_type (event_type);
Performance & Optimizer Internals
Concepts that matter once queries and tables grow large.
- Covering index- an index containing every column a query needs, so InnoDB never touches the base table (visible as 'Using index' in EXPLAIN)
- innodb_buffer_pool_size- the main cache for table/index data; typically 60-75% of available RAM on a dedicated DB host
- Query optimizer hints- e.g. /*+ INDEX(t idx_name) */ to force an index when the optimizer picks poorly
- ANALYZE TABLE- refreshes index cardinality statistics used by the optimizer's cost model
- Slow query log- logs queries exceeding long_query_time; pair with pt-query-digest or EXPLAIN ANALYZE to fix them
- RANGE / LIST partitioning- splits a large table into physical sub-tables by column value, enabling partition pruning
- Left-most prefix rule- a composite index on (a,b,c) only serves lookups/sorts on a, (a,b), or (a,b,c), not b alone
- Optimistic vs pessimistic locking- version-column checks (UPDATE ... WHERE version = ?) vs SELECT ... FOR UPDATE row locks
Pro Tip
Stick with InnoDB (the default since 5.5) for new tables — it gives you row-level locking, foreign keys, and crash recovery, while MyISAM offers none of those and is effectively legacy.
Was this cheat sheet helpful?
Explore Topics
#MySQL#MySQLCheatSheet#Database#Intermediate#ConnectingBasics#SQLEssentials#IndexesEXPLAIN#StorageEnginesTypes#Databases#CheatSheet#SkillVeris
