Back to All Cheatsheet Libraries
cheatsheets
SQL and CLI command reference, user/privilege administration, and a slow-query diagnosis workflow.
Total Commands: 0
| Category | Command | Description |
|---|---|---|
| Connect | mysql -u user -p -h host dbname | Opens an interactive session against a database on a given host. |
| Query | SELECT * FROM users WHERE active = 1; | Basic filtered read — the most common query shape. |
| Query | SELECT * FROM orders o JOIN users u ON o.user_id = u.id; | Joins two tables on a matching key column. |
| Write | INSERT INTO users (name, email) VALUES ('A', 'a@x.com'); | Adds a new row to a table. |
| Write | UPDATE users SET active = 0 WHERE id = 5; | Modifies existing rows matching the WHERE clause — always include one, or every row updates. |
| Delete | DELETE FROM sessions WHERE expires_at < NOW(); | Removes rows matching the condition — irreversible without a backup. |
| Schema | SHOW TABLES; | Lists tables in the currently selected database. |
| Schema | DESCRIBE users; | Shows a table's columns, types, and keys. |
| Index | CREATE INDEX idx_email ON users(email); | Adds an index to speed up lookups/filters on that column. |
| Diagnose | EXPLAIN SELECT ...; | Shows the query planner's execution plan — the first step in diagnosing a slow query. |
| Backup | mysqldump -u user -p dbname > backup.sql | Exports a database to a plain-SQL dump file. |
| Restore | mysql -u user -p dbname < backup.sql | Restores a database from a dump file created by mysqldump. |
Users & Privileges
| Statement | Purpose |
|---|---|
CREATE USER 'app'@'%' IDENTIFIED BY 'pw'; | Creates a new user, allowed to connect from any host ('%'). |
GRANT SELECT, INSERT ON dbname.* TO 'app'@'%'; | Grants specific privileges scoped to one database — narrower than ALL PRIVILEGES. |
FLUSH PRIVILEGES; | Reloads the grant tables — needed after directly editing privilege tables (not after GRANT itself). |
SHOW GRANTS FOR 'app'@'%'; | Lists exactly what a user is allowed to do. |
Storage Engines & Concepts
InnoDB
Transactions
Foreign Keys
Replication
1
Run EXPLAIN first
Check for a full table scan (type: ALL) where an index lookup was expected — the single most common cause of an unexpectedly slow query.
2
Add an index on the filtered/joined columns
Columns in WHERE, JOIN ON, and ORDER BY clauses are the usual index candidates — but every index also slows down writes, so don't index everything reflexively.
3
Enable the slow query log
long_query_time in the config flags any query over a threshold — turns "the app feels slow" into a specific, fixable query.
4
Consider the query shape, not just indexes
SELECT * pulling unused columns, N+1 query patterns from an ORM, and unbounded result sets often matter more than any single missing index.
Quick Tips
UPDATE/DELETE without WHERE hits every row
Run a SELECT with the same WHERE clause first to confirm exactly which rows will be affected before switching to UPDATE/DELETE.
Wrap multi-statement changes in a transaction
BEGIN ... COMMIT ensures a multi-step change either fully applies or fully rolls back — never leaves data half-updated if one statement fails.
Test restores, not just backups
A mysqldump nobody has ever restored is an assumption, not a backup — periodically restore into a scratch database to confirm it actually works.