MySQL

## Quick Reference | Topic | File | |-------|------| | Index design deep dive | `indexes.md` | | Transactions and locking | `transactions.md` | | Query optimization | `queries.md` | | Production config | `production.md` | ## Character Set Traps - `utf8` is broken—only 3 bytes, can't store emoji; always use `utf8mb4` - `utf8mb4_unicode_ci` for case-insensitive sorting; `utf8mb4_bin` for exact byte comparison - Collation mismatch in JOINs kills performance—ensure consistent collation across tables - Connection charset must match: `SET NAMES utf8mb4` or connection string parameter - Index on utf8mb4 column larger—may hit index size limits; consider prefix index ## Index Differences from PostgreSQL - No partial indexes—can't `WHERE active = true` in index definition - No expression indexes until MySQL 8.0.13—must use generated columns before that - TEXT/BLOB needs prefix length: `INDEX (description(100))`—without length, error - No INCLUDE for covering—add columns to index itself: `INDEX (a, b, c)` to cover c - Foreign keys auto-indexed only in InnoDB—verify engine before assuming ## UPSERT Patterns - `INSERT ... ON DUPLICATE KEY UPDATE`—not standard SQL; needs unique key conflict - `LAST_INSERT_ID()` for auto-increment—no RETURNING clause like PostgreSQL - `REPLACE INTO` deletes then inserts—changes auto-increment ID, triggers DELETE cascade - Check affected rows: 1 = inserted, 2 = updated (counter-intuitive) ## Locking Traps - `SELECT ... FOR UPDATE` locks rows—but gap locks may lock more than expected - InnoDB uses next-key locking—prevents phantom reads but can cause deadlocks - Lock wait timeout default 50s—`innodb_lock_wait_timeout` for adjustment - `FOR UPDATE SKIP LOCKED` exists in MySQL 8+—queue pattern - InnoDB default isolation is REPEATABLE READ, not READ COMMITTED like PostgreSQL - Deadlocks are expected—code must catch and retry, not just fail ## GROUP BY Strictness - `sql_mode` includes `ONLY_FULL_GROUP_BY` by default in MySQL 5.7+ - Non-aggregated columns must be in GROUP BY—unlike old MySQL permissive mode - `ANY_VALUE(column)` to silence error when you know values are same - Check sql_mode on legacy databases—may behave differently ## InnoDB vs MyISAM - Always use InnoDB—transactions, row locking, foreign keys, crash recovery - MyISAM still default for some system tables—don't use for application data - Check engine: `SHOW TABLE STATUS`—convert with `ALTER TABLE ... ENGINE=InnoDB` - Mixed engines in JOINs work but lose transaction guarantees ## Query Quirks - `LIMIT offset, count` different order than PostgreSQL's `LIMIT count OFFSET offset` - `!=` and `<>` both work; prefer `<>` for SQL standard - No transactional DDL—`ALTER TABLE` commits immediately, can't rollback - Boolean is `TINYINT(1)`—`TRUE`/`FALSE` are just 1/0 - `IFNULL(a, b)` instead of `COALESCE` for two args—though COALESCE works ## Connection Management - `wait_timeout` kills idle connections—default 8 hours; pooler may not notice - `max_connections` default 151—often too low; each uses memory - Connection pools: don't exceed max_connections across all app instances - `SHOW PROCESSLIST` to see active connections—kill long-running with `KILL <id>` ## Replication Awareness - Statement-based replication can break with non-deterministic functions—UUID(), NOW() - Row-based replication safer but more bandwidth—default in MySQL 8 - Read replicas have lag—check `Seconds_Behind_Master` before relying on replica reads - Don't write to replica—usually read-only but verify ## Performance - `EXPLAIN ANALYZE` only in MySQL 8.0.18+—older versions just EXPLAIN without actual times - Query cache removed in MySQL 8—don't rely on it; cache at application level - `OPTIMIZE TABLE` for fragmented tables—locks table; use pt-online-schema-change for big tables - `innodb_buffer_pool_size`—set to 70-80% of RAM for dedicated DB server