MySQL
The world's most widely deployed open-source relational database (and its drop-in fork MariaDB). Common for web backends and analytics warehouses of record — SQL dialect notes here complement the standard SQL sheet.
Table of Contents
Installation / Setup
# Docker
docker run -d -e MYSQL_ROOT_PASSWORD=pass -p 3306:3306 mysql:8
# Ubuntu: sudo apt install mysql-server
mysql -u root -p -h localhost mydb
import pymysql # or mysql-connector-python / SQLAlchemy
import pandas as pd
con = pymysql.connect(host="localhost", user="root", password="pass", database="mydb")
pd.read_sql("SELECT * FROM orders", con)
| mysql client command |
Meaning |
SHOW DATABASES; |
list databases |
USE mydb; |
select database |
SHOW TABLES; |
list tables |
DESCRIBE users; / SHOW COLUMNS FROM users; |
table structure |
SHOW CREATE TABLE users; |
DDL used to build it |
EXPLAIN SELECT ...; |
query plan |
Core Syntax
Common types
| Type |
Use |
INT, BIGINT, SMALLINT |
integers (signed/unsigned) |
DECIMAL(p, s) |
exact decimal |
FLOAT, DOUBLE |
approximate floats |
CHAR(n) / VARCHAR(n) |
fixed / variable strings |
TEXT, MEDIUMTEXT, LONGTEXT |
long text |
DATE, DATETIME, TIMESTAMP, TIME |
date/time |
JSON |
JSON documents (MySQL 5.7+) |
ENUM('a','b','c') |
restricted value set |
AUTO_INCREMENT |
auto ids (like SERIAL) |
Dialect quick wins
-- Case-insensitive by default for *_ci collations
SELECT * FROM users WHERE name = 'ada'; -- matches 'Ada'
-- String & date functions
SELECT CONCAT(first_name, ' ', last_name) FROM users;
SELECT DATE_FORMAT(created_at, '%Y-%m') AS ym FROM orders;
SELECT DATEDIFF('2024-06-01', '2024-01-01'); -- 152 days
SELECT DATE_ADD(created_at, INTERVAL 30 DAY) FROM orders;
SELECT YEAR(created_at), MONTH(created_at) FROM orders;
-- If / case expressions
SELECT IF(age >= 18, 'adult', 'minor') FROM users;
-- Concatenation with NULL-safe GROUP_CONCAT
SELECT department, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ')
FROM employees GROUP BY department;
-- LIMIT with offset, and row-level locking hints aside, basics:
SELECT * FROM logs ORDER BY ts DESC LIMIT 10 OFFSET 20;
Common Operations
Indexing & EXPLAIN
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE UNIQUE INDEX idx_users_email ON users(email);
DROP INDEX idx_orders_customer ON orders;
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- key: which index used; type: ALL = full table scan (bad);
-- rows: estimated scanned; Extra: 'Using index' = covering
Full-text search (MyISAM/InnoDB with FULLTEXT)
CREATE FULLTEXT INDEX ft_articles ON articles(title, body);
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST ('machine learning' IN NATURAL LANGUAGE MODE);
Users & permissions
CREATE USER 'analyst'@'%' IDENTIFIED BY 'secret';
GRANT SELECT ON analytics.* TO 'analyst'@'%';
GRANT SELECT, INSERT, UPDATE ON mydb.orders TO 'analyst'@'%';
REVOKE DELETE ON mydb.orders FROM 'analyst'@'%';
SHOW GRANTS FOR 'analyst'@'%';
Practical Examples
1. Daily revenue with window functions (MySQL 8+)
WITH daily AS (
SELECT DATE(created_at) AS day, SUM(total) AS revenue
FROM orders WHERE status = 'paid'
GROUP BY 1
)
SELECT day, revenue,
SUM(revenue) OVER (ORDER BY day) AS cumulative,
revenue - LAG(revenue) OVER (ORDER BY day) AS day_change
FROM daily ORDER BY day;
2. Category statistics with CASE aggregation
SELECT
DATE_FORMAT(created_at, '%Y-%m') AS ym,
COUNT(*) AS orders,
COUNT(DISTINCT customer_id) AS customers,
SUM(CASE WHEN total >= 100 THEN 1 ELSE 0 END) AS big_orders,
AVG(total) AS avg_order
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY 1 ORDER BY 1;
-- Top 5 customers by lifetime value
SELECT customer_id, SUM(total) AS ltv
FROM orders GROUP BY customer_id
ORDER BY ltv DESC LIMIT 5;
Common Patterns
-- Upsert (MySQL 8: ON DUPLICATE KEY UPDATE)
INSERT INTO users (id, email, name) VALUES (1, 'a@x.com', 'Ada')
ON DUPLICATE KEY UPDATE name = VALUES(name);
-- High-speed bulk load
LOAD DATA INFILE '/tmp/orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 LINES;
-- Keep tables lean: only_full_group_by compliance
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';
-- Avoid slow OFFSET on big tables: record the last id
SELECT * FROM orders WHERE id > 500000 ORDER BY id LIMIT 100;
Best Practices
- Test queries with
EXPLAIN — look for type=ALL full scans on large tables.
- Index join keys and
WHERE columns; composite indexes for multi-column filters ((status, created_at)).
- Order matters in composite indexes — leftmost prefix rule applies.
- Use
DATETIME for business time, TIMESTAMP for automatic UTC-ish logging; store UTC.
- Prefer
DECIMAL for currency; floats round unexpectedly.
- Beware
COUNT(*) on huge tables — approximate with information_schema when you need speed.
- Choose
InnoDB (default): transactions + row-level locking; MyISAM is legacy.
- Alias-dependent: use
HAVING/ORDER BY with aliases, but never WHERE aliases.
Common Mistakes
| Mistake |
Consequence |
Fix |
WHERE alias = ... |
Unknown column error |
Repeat the expression or use a subquery/HAVING |
GROUP BY with non-aggregated column (strict mode) |
Error in MySQL 5.7+/8 |
Aggregate or add to GROUP BY |
| String comparison depending on collation |
Surprising case sensitivity |
Pick a _ci or _bin collation deliberately |
LIMIT 10 OFFSET 100000 |
Very slow deep pagination |
Keyset pagination by id |
VALUES(name) in upsert (deprecated 8.0.20) |
Warning/removal in future |
Use alias: INSERT ... VALUES (...) AS new ON DUPLICATE KEY UPDATE name = new.name |
| Index on low-cardinality column alone |
Rarely used |
Composite index with a selective column |
| Loading big CSV row-by-row in a loop |
Hours instead of seconds |
LOAD DATA INFILE |
Troubleshooting
| Symptom |
Cause |
Fix |
Access denied for user |
Wrong host/privileges |
GRANT from correct host; flush privileges |
ERROR 1175 updating without key in WHERE |
Safe-update mode |
Add key-based WHERE or SET SQL_SAFE_UPDATES=0 |
Table doesn't exist |
Wrong database selected |
USE dbname or qualify dbname.table |
| Slow query after adding index |
Optimizer chose another path |
ANALYZE TABLE; FORCE INDEX as last resort |
Lock wait timeout exceeded |
Long transactions |
Inspect SHOW PROCESSLIST; commit sooner |
| Character garbled |
Charset mismatch |
utf8mb4 end-to-end; SET NAMES utf8mb4 |
Official Documentation
Last Updated
2026-09-06 — Initial version.