mysql

name	mysql
description	MySQL/MariaDB — InnoDB, indexing, replication, JSON, query tuning.

MySQL Reference

When to Load

Load when the project uses MySQL or MariaDB (signal: mysql, mysql2, mysql: in docker-compose, DATABASE_URL with mysql://).

CLI Connection

mysql -h "$DB_HOST" -P "${DB_PORT:-3306}" -u "$DB_USER" -p"$DB_PASSWORD" "$DB_NAME"
# non-interactive:
mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" -e "SELECT VERSION();"

Key inspection commands:

SHOW ENGINE INNODB STATUS;                        -- lock waits, undo log
SHOW FULL PROCESSLIST;                            -- running queries
EXPLAIN FORMAT=JSON SELECT ...;                   -- detailed query plan
SELECT * FROM performance_schema.events_statements_summary_by_digest
  ORDER BY sum_timer_wait DESC LIMIT 10;          -- slow query digest

InnoDB Storage Engine

• Default engine since MySQL 5.5; always use InnoDB (not MyISAM) for new tables
• Row-level locking; supports foreign keys and transactions
• Clustered primary key index: all secondary indexes contain the PK value
• Large PK → large secondary indexes; prefer INT AUTO_INCREMENT or compact UUID

Index Behavior

Scenario	Recommendation
Composite index	Most selective column first; match query column order
Covering index	Include all queried columns: CREATE INDEX idx ON t(a, b) INCLUDE (c)
Prefix index	For long VARCHAR: CREATE INDEX ON t(col(20))
EXPLAIN	Always run before merging slow queries; check type column (prefer ref or range, avoid ALL)

Transactions & Isolation

Default isolation: REPEATABLE READ (unlike PostgreSQL's READ COMMITTED).

• MVCC via undo logs (not tuple versioning like Postgres)
• Long transactions grow the undo log → monitor with SHOW ENGINE INNODB STATUS
• Use READ COMMITTED for OLAP or reporting queries to reduce lock contention

Replication

Type	Consistency	Use when
Async (default)	Eventual	Most setups; risk of data loss on primary failure
Semi-sync	Near-sync	One replica ACKs before commit returns
GTID-based	Position-independent	Failover without manual binlog coordinates

• Never write to a replica; use read_only = ON on replicas
• Replica lag: monitor Seconds_Behind_Master or performance_schema.replication_connection_status

JSON Type (MySQL 5.7.8+)

• Store as JSON column for validation; queried with -> (returns JSON) and ->> (returns string)
• Index generated columns: ALTER TABLE t ADD COLUMN col VARCHAR(255) GENERATED ALWAYS AS (json_col->>'$.key') VIRTUAL, ADD INDEX idx(col)
• Avoid deep nesting; normalize frequently queried fields

Common Gotchas

Gotcha	Fix
Silent truncation in strict mode OFF	Enable sql_mode = STRICT_ALL_TABLES
GROUP BY without ONLY_FULL_GROUP_BY silently picks random non-aggregated values	Enable ONLY_FULL_GROUP_BY
utf8 charset is actually 3-byte (no emoji)	Use utf8mb4 + utf8mb4_unicode_ci
TINYINT(1) returned as boolean in some ORMs	Use TINYINT(1) deliberately for booleans
Deadlocks on concurrent INSERT ... ON DUPLICATE KEY UPDATE	Use sparingly; prefer application-level locking

Useful Diagnostics

SHOW ENGINE INNODB STATUS;                          -- lock waits, undo log
SHOW FULL PROCESSLIST;                              -- running queries
SELECT * FROM performance_schema.events_statements_summary_by_digest
  ORDER BY sum_timer_wait DESC LIMIT 10;            -- slow queries
EXPLAIN FORMAT=JSON SELECT ...;                     -- detailed plan