MySQL

Quick Reference

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