MySQL & SQL — Cheat Sheet

• INNER JOIN: Returns only rows with a match in both tables. • LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table, matched rows from the right (NULL for no match). • RIGHT JOIN: All rows from right, matched from left. • FULL OUTER JOIN: All rows from both tables (MySQL doesn't support it natively — use UNION of LEFT and RIGHT JOIN). • CROSS JOIN: Cartesian product of both tables (every combination). • SELF JOIN: A table joined with itself (useful for hierarchical data, org charts). Most common performance tip: Ensure join columns are indexed. The optimizer uses the smaller result set as the driving table.

InnoDB uses B-Tree indexes by default. A B-Tree keeps data sorted and allows O(log n) search, insert, and delete. Primary key (Clustered Index): The actual table rows are stored in the B-Tree leaf nodes, sorted by the primary key. Each table has exactly one clustered index. Secondary indexes: Store the indexed column(s) + the primary key value. A secondary index lookup first finds the PK in the B-Tree leaf, then does a second lookup (row lookup / key lookup) in the clustered index. This is why: • Choosing a small primary key matters (it's stored in every secondary index) • Covering indexes (index contains all queried columns) eliminate the row lookup • Random PK insertion (UUID) causes page splits and fragmentation — prefer sequential IDs

A covering index includes all columns required to satisfy a query — MySQL can fulfill the query entirely from the index without accessing the actual table rows (no row lookup). EXPLAIN will show "Using index" in the Extra column when a covering index is used. Example: For SELECT email, name FROM users WHERE status = 'active', create INDEX idx_status_covering (status, email, name). All three columns are in the index. Benefit: Avoids the extra I/O of a row lookup, significantly faster for read-heavy queries. Trade-off: Larger index = more disk space and slower writes. Design covering indexes for your critical read queries, not preemptively.

Isolation levels control what dirty/inconsistent data a transaction can see: • READ UNCOMMITTED: See uncommitted changes of other transactions (dirty reads possible). Fastest, rarely used. • READ COMMITTED: See only committed data. Prevents dirty reads. Oracle/PostgreSQL default. Risk: non-repeatable reads. • REPEATABLE READ (InnoDB default): Same SELECT returns same result within a transaction. Prevents dirty + non-repeatable reads. InnoDB uses MVCC — no read locks. Risk: phantom reads (new rows in a range can appear) — InnoDB uses gap locks to prevent most cases. • SERIALIZABLE: Strictest — fully isolated. Reads acquire shared locks. Eliminates all anomalies. High contention, low throughput. Most apps work fine with InnoDB's REPEATABLE READ default.

MVCC allows readers and writers to work concurrently without blocking each other. Instead of locking rows for reads, InnoDB maintains multiple versions of each row. Each row has two hidden columns: DB_TRX_ID (transaction ID that last modified it) and DB_ROLL_PTR (pointer to the undo log for previous versions). When a transaction reads a row, it uses its read view to determine which version of the row it should see based on transaction start time. Older versions are stored in the undo log. Result: SELECT never blocks because of concurrent INSERT/UPDATE/DELETE. Long-running transactions hold onto undo log segments, causing bloat. PURGE thread cleans old versions when no transaction needs them. This is why InnoDB's REPEATABLE READ doesn't need shared locks for reads.

EXPLAIN shows how MySQL's query optimizer plans to execute a query. Key columns: • type: Access method — const (PK lookup), ref (index), range (index range), ALL (full table scan — bad!) • possible_keys: Indexes that could be used • key: Index actually chosen • key_len: Bytes of index used (longer = more columns used) • rows: Estimated rows examined • Extra: "Using index" (covering index), "Using filesort" (no index for ORDER BY — potentially slow), "Using temporary" (temp table needed) Use EXPLAIN ANALYZE (MySQL 8+) for actual execution stats vs estimates. Red flags: type=ALL on large tables, "Using filesort" on large result sets, high rows estimate.

Window functions perform calculations across a set of rows related to the current row without collapsing rows like GROUP BY does. Syntax: function() OVER (PARTITION BY col ORDER BY col ROWS/RANGE BETWEEN ...) Ranking: ROW_NUMBER() — unique sequential, RANK() — with gaps on ties, DENSE_RANK() — no gaps Offset: LAG(col, n) — value from n rows before, LEAD(col, n) — n rows ahead Aggregate: SUM(), AVG(), COUNT() as window functions — running totals, moving averages NTILE(n) — divide into n equal buckets FIRST_VALUE(), LAST_VALUE(), NTH_VALUE() Example: Rank employees by salary within each department: SELECT name, dept, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC)

A CTE (WITH clause) defines a named temporary result set within a query. It improves readability by breaking complex queries into named steps. Syntax: WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name Multiple CTEs: WITH cte1 AS (...), cte2 AS (...) SELECT ... Recursive CTEs: Process hierarchical/tree data (org charts, category trees): WITH RECURSIVE tree AS ( SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, c.parent_id FROM categories c JOIN tree t ON c.parent_id = t.id ) SELECT * FROM tree; CTEs vs Subqueries: CTEs are not materialized by default in MySQL 8 (same execution plan). Use CTEs for readability; use subqueries when the optimizer hint matters.

The N+1 problem occurs when code executes 1 query to fetch N records, then N additional queries to fetch related data for each record. Example: Fetch 100 orders, then for each order fetch the customer: 1 + 100 = 101 queries. Fixes: • JOIN: Fetch orders and customers in one query with a JOIN • Eager loading (Hibernate): Use FETCH JOIN or @EntityGraph to load associations in one query • Batch loading: Hibernate @BatchSize(size=25) fetches 25 associations per query instead of 1 • Subquery IN clause: SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders) Detection: Enable query logging (spring.jpa.show-sql=true) or use p6spy. Look for repeated queries with different ID parameters.

A deadlock occurs when two transactions each hold a lock and wait for the other to release theirs, creating a circular dependency. Example: • Transaction A: Locks row 1, then tries to lock row 2 • Transaction B: Locks row 2, then tries to lock row 1 • Both wait forever → InnoDB detects and rolls back one automatically Common causes: Different transaction lock order, index absence (row lock degrades to table lock), long transactions holding many locks. Prevention: • Consistent lock ordering across all transactions • Keep transactions short — commit as soon as possible • Add indexes so row-level locking works correctly (no table locks) • Use SELECT ... FOR UPDATE only when necessary Monitor: SHOW ENGINE INNODB STATUS; shows last detected deadlock.

DELETE: DML statement. Removes specific rows based on WHERE condition (or all rows without). Generates undo log (MVCC-safe, transaction-rollback possible). Fires triggers. Slow on large tables — rows removed one-by-one. TRUNCATE: DDL statement. Removes all rows without scanning them — resets the table to empty by dropping and recreating the data page. Much faster than DELETE for full table clear. Cannot be rolled back (in MySQL, it implicitly commits). Does not fire row-level triggers. Resets AUTO_INCREMENT counter. DROP: DDL. Removes the entire table structure, data, indexes, and all associated objects. Irreversible. Tip: Use TRUNCATE for clearing large tables (e.g., in test setup). Use DELETE when WHERE filtering is needed or trigger behavior matters.

Step-by-step optimization process: 1. EXPLAIN / EXPLAIN ANALYZE: Identify full table scans (type=ALL), missing indexes, using filesort 2. Add indexes: Index columns in WHERE, JOIN ON, ORDER BY, GROUP BY. Consider composite indexes — column order matters (leftmost prefix rule) 3. Covering index: Include SELECT columns in the index to avoid row lookups 4. Rewrite query: Avoid SELECT *, avoid functions on indexed columns in WHERE (WHERE YEAR(created_at) = 2024 defeats the index), push filters down 5. Partition large tables: Range or hash partitioning for time-series data 6. Avoid N+1: Use JOINs or batch loading 7. Query cache (replaced by ProxySQL / application-level cache in MySQL 8) 8. Connection pooling: HikariCP reduces connection overhead 9. Read replicas: Route heavy reads to replicas

Normalization organizes data to reduce redundancy and improve integrity by following normal forms: 1NF: Atomic values (no arrays or comma-separated lists in a cell), unique rows, each column has a single data type. 2NF: 1NF + no partial dependencies (every non-key attribute depends on the whole primary key, not part of it). Applies to composite keys. 3NF: 2NF + no transitive dependencies (non-key attributes depend only on the primary key, not on other non-key attributes). BCNF: 3NF strengthened — every determinant is a candidate key. Denormalization: Intentionally violating NF for performance (precomputed aggregates, cached joins). Common in data warehouses and CQRS read models. Profile before denormalizing.

MySQL replication asynchronously copies data from a primary (source) to one or more replicas. How it works: 1. Primary writes changes to the binary log (binlog) in either statement-based (SQL), row-based (actual row changes), or mixed format 2. Replica's I/O thread connects to primary and copies binlog events to its relay log 3. Replica's SQL thread reads relay log and applies events Use cases: Read scaling (route reads to replicas), backups without locking primary, failover. Delays: Async replication has lag — reads from replica may be stale. Semi-sync replication waits for at least one replica to acknowledge before committing. GTID (Global Transaction Identifier): Tracks every committed transaction globally, enabling reliable failover and replica promotion (auto-position).

Partitioning divides a table's data into separate sub-tables (partitions) based on a partition key. Query pruning allows MySQL to skip irrelevant partitions. Types: • RANGE: Partitioned by a range of values (partition by year — each partition holds one year's data). Most common for time-series. • LIST: Explicit list of values per partition. • HASH: Distributes rows evenly using a hash function. • KEY: Similar to HASH but MySQL chooses the function. Benefits: Faster range queries (pruning), easier archiving (DROP PARTITION is fast), improved write performance on the active partition. Limitations: All partition key columns must be part of every unique/PK index. Foreign keys not supported across partitions. Not a substitute for indexing.

GROUP BY collapses rows with the same value into a single group, allowing aggregate functions (COUNT, SUM, AVG, MAX, MIN) to summarize each group. HAVING filters groups after aggregation. WHERE filters rows before aggregation. Example: SELECT department, COUNT(*) as emp_count FROM employees WHERE status = 'active' -- filters rows first GROUP BY department HAVING COUNT(*) > 10 -- filters groups after aggregation ORDER BY emp_count DESC; Performance tip: Use WHERE to reduce rows before grouping when possible — HAVING can't use indexes for filtering aggregated columns.

A stored procedure is a precompiled collection of SQL statements stored in the database and executed with CALL procedure_name(). Advantages: Reduced network traffic (batch operations in one call), reusable logic, can be granted specific EXECUTE permissions. Disadvantages: Business logic mixed into the DB layer (hard to test, version control, and refactor), limited debugging tools, performance varies across DB versions, tightly couples application to DB. Modern best practice: Keep business logic in the application layer. Use stored procedures only for: bulk data operations (ETL), database-triggered auditing, complex reporting queries that need to run close to the data, or legacy systems where moving logic out is not feasible.

InnoDB supports multiple lock granularities: • Row-level locks: Lock individual rows (not entire table). Dramatically reduces contention. • Shared lock (S): Multiple transactions can read the same row concurrently. Acquired with SELECT ... LOCK IN SHARE MODE. • Exclusive lock (X): Only one transaction can write. Acquired automatically with UPDATE/DELETE or SELECT ... FOR UPDATE. • Intention locks (IS, IX): Table-level locks that signal intent to acquire row locks (allows table lock compatibility checks). • Gap locks: Lock a range between index records to prevent phantom reads in REPEATABLE READ. • Next-key lock: Row lock + gap lock on the preceding gap (InnoDB's default lock for range queries). Lock escalation does not happen in InnoDB (unlike SQL Server). But if the WHERE clause columns are not indexed, InnoDB may lock more rows than necessary.

UNION: Combines results from two SELECT statements and removes duplicate rows. Internally uses a sort/hash operation to find and eliminate duplicates — more expensive. UNION ALL: Combines results and keeps all rows including duplicates. Faster because no deduplication step. Use UNION ALL whenever you know results won't overlap (different date ranges, different categories) or when duplicates are acceptable — it's always faster. Both require the same number of columns and compatible data types in each SELECT. Common use: Implement FULL OUTER JOIN in MySQL: SELECT * FROM a LEFT JOIN b ON a.id=b.id UNION ALL SELECT * FROM a RIGHT JOIN b ON a.id=b.id WHERE a.id IS NULL;

Three multi-tenancy approaches: 1. Separate database per tenant: Strongest isolation, easy tenant-specific backup/migration. High operational overhead for many tenants. Best for enterprise customers. 2. Separate schema (same DB, different schemas): Medium isolation. PostgreSQL supports this well; MySQL less so. Good for dozens of tenants. 3. Shared schema with tenant_id column: All tenants share tables with a tenant_id discriminator column. Lowest cost but weakest isolation — must be included in every query WHERE clause and every index. For shared schema: • Index all tables on (tenant_id, primary_key) for efficient scoping • Use Row-Level Security (PostgreSQL) or application-level filtering • Ensure tenant_id is always in the execution context to prevent data leakage • Consider virtual columns or computed columns for tenant-specific logic

Clustered index: The table data rows are physically stored in the order of the index key. In InnoDB, the PRIMARY KEY is always the clustered index. There can be only one clustered index per table (data can only be sorted one way). Benefits: Range scans on primary key are extremely fast — pages are contiguous on disk. No separate lookup needed after finding the index entry. Non-clustered index (secondary index): A separate structure from the table data. Leaf nodes contain the index key + primary key value (not the actual row data). To fetch the full row, InnoDB does a secondary lookup using the primary key (bookmark lookup). Implication: Keep the primary key narrow (INT or BIGINT). Every secondary index stores the PK value, so a fat PK (e.g., UUID) bloats all secondary indexes. Covering indexes avoid the bookmark lookup by including all needed columns in the index itself.

AUTO_INCREMENT generates a unique integer for each new row automatically. InnoDB maintains an in-memory auto-increment counter per table, persisted to the data dictionary. Behavior: Values are monotonically increasing but not necessarily consecutive — gaps occur on rollbacks, DELETE operations, or failed inserts. Never rely on consecutive values. MySQL 8.0 change: InnoDB persists the AUTO_INCREMENT counter to redo log. In MySQL 5.7 and earlier, the counter was re-initialized on restart from MAX(id) — if the max row was deleted, a restarted server could reuse that ID. Distributed systems: AUTO_INCREMENT doesn't work across shards — each shard generates duplicates. Use UUID, Snowflake IDs (Twitter), or a centralized sequence service instead. Performance: Inserting in primary key order is optimal for InnoDB (B-Tree leaf pages fill sequentially). UUID as primary key causes random page writes — causes page splits and B-Tree fragmentation.

A covering index contains all columns needed for a query — the query can be satisfied entirely from the index without reading the actual table row (no bookmark lookup). Example: Query SELECT name, email FROM users WHERE status = 'active' with index (status, name, email) — all columns (status for filter, name and email for output) are in the index. No table access needed. EXPLAIN shows "Using index" in Extra column when a covering index is used. Performance impact: Can be 5-10× faster for read-heavy queries. Secondary index pages are much smaller than data pages (fewer columns) — more entries fit per page, less I/O. Design: Add frequently-read columns to an index using the INCLUDE clause (SQL Server, PostgreSQL) or simply include them in the composite key (MySQL). Don't over-index — extra columns increase index maintenance cost on writes. Rule: Index the WHERE clause columns first (for filtering), then the SELECT columns (to cover). Example: INDEX(status, created_at, name, email) for WHERE status=? ORDER BY created_at SELECT name, email.

EXPLAIN shows MySQL's query execution plan — which indexes are used, join types, estimated rows. Key columns: • type (join type — most important): const > eq_ref > ref > range > index > ALL (ALL = full table scan — bad) • possible_keys: Indexes MySQL considered • key: Index MySQL actually chose (NULL = no index used) • rows: Estimated rows examined (multiply across joins for total cost estimate) • Extra: Additional info — "Using index" (covering), "Using filesort" (sort in memory/disk), "Using temporary" (temp table — expensive), "Using where" (filter applied after index) Red flags: • type = ALL on large table • Using filesort on large result set • Using temporary on large result set • rows × rows × rows (huge joins) EXPLAIN FORMAT=JSON or EXPLAIN ANALYZE (MySQL 8.0) gives more detail including actual vs estimated rows. Workflow: EXPLAIN → identify bottleneck (full scan, filesort) → add index or rewrite query → EXPLAIN again to verify.

Trigger: A stored procedure that automatically executes in response to INSERT, UPDATE, or DELETE events on a table. Can fire BEFORE or AFTER the event. Use cases: Audit logging (INSERT into audit_log automatically), enforcing complex business rules that can't be expressed as constraints, maintaining denormalized data. Why to avoid them in most modern applications: • Hidden logic: Triggers execute invisibly — developers don't see them when reading application code. Makes debugging very hard. • Performance surprise: A simple INSERT triggers complex logic. Can't see the cost from the application side. • Testing difficulty: Hard to unit test, need actual DB to test trigger behavior. • Cascade complexity: Trigger A fires trigger B fires trigger C — debugging becomes a nightmare. • Replication issues: Row-based replication replicates effects, statement-based doesn't execute triggers on replica. • Transaction extension: Trigger runs in same transaction — a failure in trigger rolls back the triggering statement. Better alternatives: Application-level events, change data capture (Debezium on the WAL), explicit service calls.

Foreign key: A constraint ensuring referential integrity — values in child column must exist in the parent column. Example: orders.user_id REFERENCES users(id). Behavior: On INSERT/UPDATE to child — MySQL checks parent table. On DELETE/UPDATE to parent — enforces ON DELETE/ON UPDATE action: RESTRICT (block), CASCADE (propagate), SET NULL, NO ACTION. Performance implications: • Additional lookup on every INSERT/UPDATE to child table — verifies parent row exists • Additional lock on parent row during child INSERT — prevents concurrent parent deletion • CASCADE deletes can trigger recursive deletes across large sets of rows — long-running transactions • The foreign key column in child table must be indexed (InnoDB does this automatically for FK columns) When to skip foreign keys: • Microservices: Each service owns its data — FK across service DBs is impossible • Sharded databases: Parent and child may be on different shards • Performance-critical bulk load: Disable FK checks (SET foreign_key_checks = 0) during bulk import, re-enable after • High-write-throughput tables where FK overhead is measurable Alternative: Enforce referential integrity in application code, with periodic reconciliation jobs to detect orphans.

CHAR(n): Fixed-length string. Always stores exactly n bytes (padded with spaces). Fast for fixed-length data — no length prefix needed, fixed-size rows. Good for: country codes (CHAR(2)), status fields (CHAR(1)), UUIDs stored as string (CHAR(36)). VARCHAR(n): Variable-length string. Stores 1-2 bytes for length prefix + actual content. n is maximum length, not actual. Uses only as much space as needed. Good for: names, emails, descriptions — anything variable length. Performance: CHAR is marginally faster for exact lookups on fixed-length fields (no length decoding). VARCHAR is more space-efficient for variable data. On disk, space savings translate to more rows per page = less I/O. MySQL behavior: Trailing spaces stripped from CHAR on retrieval. VARCHAR preserves trailing spaces. MySQL row size limit: 65,535 bytes per row (all VARCHAR columns combined). For very long text, use TEXT/BLOB types (stored off-page). Practical guidance: Use VARCHAR for almost everything. Use CHAR for truly fixed-length values where you want the clarity of intent.

MySQL FULLTEXT index supports full-text search on CHAR, VARCHAR, and TEXT columns. Only for InnoDB and MyISAM. Creating: ALTER TABLE articles ADD FULLTEXT INDEX ft_idx (title, body); Querying with MATCH ... AGAINST: • Natural language mode (default): MATCH(title,body) AGAINST ('java spring') — ranks results by relevance (TF-IDF scoring). Stops words (common words like "the", "and") are ignored. • Boolean mode: MATCH(title,body) AGAINST ('+java -python' IN BOOLEAN MODE) — + means must include, - means must exclude, * is wildcard. • Query expansion mode: Performs search twice, uses words from first result set to expand second search. Limitations: • Minimum word length (ft_min_word_len = 4 by default) — shorter words not indexed • Only works on complete words — no substring search • No fuzzy matching, no synonym handling • Not suitable for complex queries or large-scale full-text search For production full-text search: Use Elasticsearch or OpenSearch. More powerful analyzers (stemming, synonyms, multilingual), better relevance scoring, horizontal scalability, autocomplete, and faceted search.

InnoDB (default since MySQL 5.5): ACID-compliant, supports transactions, foreign keys, row-level locking, MVCC for concurrent reads. Crash recovery via redo log. Clustered index — data stored with primary key. Supports all integrity features. MyISAM (legacy): No transactions, no foreign keys, table-level locking (only one writer at a time, blocks all readers). No crash recovery (can corrupt on unclean shutdown). Non-clustered index. Faster for certain read-only workloads due to simpler design. MyISAM advantages (mostly historical): Faster full-table scans (no MVCC overhead), better for read-only tables, stores table row count (COUNT(*) is instant — InnoDB must scan). Smaller storage for some data patterns. When to use MyISAM: Almost never in 2024. InnoDB has matched or exceeded MyISAM performance in nearly all scenarios and is far more reliable. MySQL 8.0: Internal system tables migrated from MyISAM to InnoDB. MyISAM still exists but is effectively deprecated for user tables. Conclusion: Always use InnoDB unless you have a very specific, benchmarked reason not to.

MySQL replication: Primary records all changes to the binary log (binlog). Replica connects, reads binlog, and replays events. Binlog formats: • STATEMENT: Logs the SQL statement. Compact but non-deterministic statements (NOW(), RAND()) can produce different results on replica. • ROW (recommended): Logs the actual row changes (before/after values). Larger log but always correct. • MIXED: Uses STATEMENT by default, switches to ROW for non-deterministic statements. Asynchronous replication (default): Primary commits → replica catches up eventually. Replica lag can range from ms to seconds. Primary doesn't wait for replica — zero write latency impact. Semi-synchronous: Primary waits for at least one replica to acknowledge receipt before committing. Reduces data loss on primary failure. Adds ~1 network round-trip latency per commit. GTID (Global Transaction ID): Each transaction gets a global unique ID. Simplifies failover — replica can resume from GTID without tracking binlog position. Easier orchestration with tools like Orchestrator. Group replication / InnoDB Cluster: Multi-primary or single-primary with Paxos-based consensus. All nodes in sync. Foundation for MySQL NDB Cluster.

Deadlock: Transaction A holds lock on row X and waits for row Y. Transaction B holds lock on row Y and waits for row X. Neither can proceed. InnoDB detection: InnoDB has a deadlock detector that runs continuously. When it finds a cycle in the wait-for graph, it selects one transaction as the "victim" (typically the one that has done less work, configurable via innodb_deadlock_detect). The victim transaction is rolled back and gets ER_LOCK_DEADLOCK error. Handling in application: Catch deadlock exception and retry the transaction. Use exponential backoff between retries. Prevention strategies: • Access tables and rows in the same order in all transactions — eliminates cycles • Keep transactions short — hold locks for minimal time • Use SELECT ... FOR UPDATE only when necessary • Avoid user interaction within a transaction • Add appropriate indexes — missing indexes cause InnoDB to lock more rows than needed (full scan locks entire table) • Consider NOWAIT or SKIP LOCKED (MySQL 8.0) for non-blocking reads Deadlock log: SHOW ENGINE INNODB STATUS — shows last deadlock details including which transactions and which rows were involved.

Stored procedure: A named, compiled SQL routine stored in the database. Called by name with CALL procedure_name(params). Advantages: • Reduce network round trips: Execute multiple SQL statements server-side in one call • Encapsulate complex SQL logic • Code reuse across different applications • Permissions: Grant EXECUTE without exposing underlying tables Disadvantages: • Hard to version control alongside application code • Difficult to unit test • Poor debugging tools compared to application-layer code • Logic split between application and DB — harder to understand the full system • DB becomes a bottleneck (compute-heavy procedures prevent horizontal DB scaling) • Language is limited — MySQL stored procedure language is far less expressive than Java/Python When to use: Legacy systems where stored procedures are already in use. Batch operations with many SQL steps that would require many round trips. Data migrations. Reporting queries used by multiple teams. When NOT to use: New projects. Any logic that involves business rules — keep those in the service layer. Anything you want to test easily.

Slow query log: MySQL's built-in mechanism to log queries exceeding a time threshold. Enable: SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; (log queries > 1 second). SET GLOBAL log_queries_not_using_indexes = ON (log non-indexed queries regardless of time). Analysis tools: • pt-query-digest (Percona Toolkit): Aggregates slow query log by query fingerprint, shows total time, average time, call count, worst offenders. Best tool for production analysis. • mysqldumpslow: Basic aggregation, built into MySQL distribution. • MySQL Workbench Performance Dashboard. Workflow: 1. Enable slow query log in production (minimal overhead) 2. Run pt-query-digest on the log file → get top queries by total time 3. EXPLAIN the worst queries 4. Add missing indexes or rewrite queries 5. Use performance_schema for deeper profiling (table I/O, mutex waits) Performance Schema: performance_schema.events_statements_summary_by_digest gives aggregated query stats including avg/max latency, rows examined — no log file needed.

InnoDB buffer pool: The main memory cache for InnoDB. Caches data pages and index pages. The most important MySQL performance tuning parameter. How it works: Frequently accessed pages stay in memory. LRU algorithm manages eviction (with a "young" and "old" sublist to prevent sequential scans evicting hot data). Dirty pages (modified but not yet written to disk) are flushed by background threads. Sizing rule: Set innodb_buffer_pool_size to 70-80% of available RAM for a dedicated MySQL server. Example: 32GB RAM → 24GB buffer pool. Why it matters: Buffer pool hit rate should be > 99%. If a query needs a page not in buffer pool, it does a disk read — orders of magnitude slower than memory access. Monitoring: SHOW ENGINE INNODB STATUS shows buffer pool hit rate. performance_schema for detailed stats. Buffer pool pages read vs read ahead tells you if data fits in memory. Multiple buffer pool instances: innodb_buffer_pool_instances (default 8 for pools > 1GB) — reduces mutex contention for high-concurrency workloads. Each instance has its own LRU list and mutex. Warm-up: After restart, buffer pool is empty. Use innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup to persist and restore hot pages.

WHERE: Filters rows before grouping. Operates on individual rows from the table. Cannot reference aggregate functions (SUM, COUNT, AVG) because aggregation hasn't happened yet. HAVING: Filters groups after GROUP BY aggregation. Can reference aggregate functions. Can also reference non-aggregated columns (though WHERE is more efficient for those). Execution order: FROM → JOIN → WHERE → GROUP BY → aggregate functions → HAVING → SELECT → ORDER BY → LIMIT Example: SELECT department, COUNT(*) as emp_count FROM employees WHERE status = 'active' -- filter rows before grouping GROUP BY department HAVING COUNT(*) > 10; -- filter groups after aggregation Performance: Always filter with WHERE when possible — reduces rows before grouping (less data to aggregate). HAVING filtering on non-aggregate columns is less efficient than equivalent WHERE clause. Common mistake: Using HAVING without GROUP BY — it acts like WHERE but less efficiently. Also: using HAVING to filter on a column that could be in WHERE.

Query cache (removed in MySQL 8.0): Cached the result of SELECT statements. On identical queries, returned the cached result without executing the query. How it worked: Query text (exact string match, including whitespace) → MD5 hash → lookup result in query cache. Any write to a cached table invalidated all cached queries for that table. Why it failed and was removed: • Coarse invalidation: Any INSERT/UPDATE/DELETE on a table invalidated ALL cached queries for that table — cache thrashing on write-heavy systems. • Mutex contention: A single global mutex protected the query cache — every SELECT acquired it, serializing all queries at high concurrency. • Memory overhead: Managing cache entries, fragmentation over time. • Exact match only: SELECT * FROM t WHERE id=1 and SELECT * FROM t where id=1 (different case) are different cache entries. • For most workloads, the mutex contention and invalidation overhead made it net-negative — it slowed things down. MySQL 5.6+: Query cache disabled by default. MySQL 8.0: Completely removed. Better alternatives: Application-level caching (Redis/Memcached), ProxySQL query caching, database-level result sets for specific queries.

mysqldump with --single-transaction (InnoDB): Uses a repeatable read transaction snapshot. Consistent backup without locking. Does not work for MyISAM tables. mysqldump --single-transaction --quick --master-data=2 -u root -p mydb > backup.sql --quick: Reads rows one by one instead of buffering entire table — essential for large tables. --master-data=2: Records binlog position as a comment — enables point-in-time recovery. Xtrabackup (Percona): Hot backup tool for InnoDB. Copies physical data files while database is running. Much faster than mysqldump for large databases (100GB+). Incremental backups supported. Requires xtrabackup --prepare step before restoration. MySQL Enterprise Backup: Oracle's commercial equivalent to XtraBackup. Binlog-based point-in-time recovery: Take full backup weekly/nightly, apply binlog events up to desired timestamp. Enables recovery to any point between backups. Testing backups: Regularly restore backup to a test environment and verify. An untested backup is not a backup. Cloud: AWS RDS automated snapshots and point-in-time recovery via binlog. Zero operational overhead.

Pessimistic locking: Assume conflicts will happen — lock the row immediately on read. SELECT ... FOR UPDATE acquires an exclusive row lock. Other transactions block until the lock is released by COMMIT or ROLLBACK. Use case: High-contention resources (inventory decrement during checkout, bank account balance update). Prevents conflicts at cost of concurrency. Optimistic locking: Assume conflicts are rare — read without locking. Before writing, verify nothing has changed using a version column. Implementation: 1. Add version INT column (or updated_at TIMESTAMP) 2. Read row: SELECT id, balance, version FROM accounts WHERE id = ? 3. Compute new balance 4. Update: UPDATE accounts SET balance = ?, version = version + 1 WHERE id = ? AND version = ? 5. If affected rows = 0 → conflict — someone else modified it → retry JPA support: @Version annotation on entity field. JPA automatically adds AND version = ? to UPDATE and throws OptimisticLockException on conflict. Choice: Use pessimistic when conflicts are frequent or retrying is expensive. Use optimistic when conflicts are rare and retry cost is low. For flash sales (extreme contention), consider Redis atomic operations instead.

MySQL partitioning splits a table into multiple physical sub-tables while appearing as one logical table. MySQL supports four types: RANGE partitioning: Rows assigned to partition based on column value falling in a range. Most common. Example: partition by YEAR(created_at) — one partition per year. Enables partition pruning for date-range queries and easy data lifecycle (DROP PARTITION old_year — instant, no row-by-row delete). LIST partitioning: Rows assigned based on column value matching a list. Example: partition by region (PARTITION p_us VALUES IN ('US','CA'), PARTITION p_eu VALUES IN ('DE','FR')). HASH partitioning: MySQL hashes a column value and assigns to partition. Distributes rows evenly. No partition pruning advantage for range queries — used for load balancing across partitions. KEY partitioning: Similar to HASH but uses MySQL's internal hashing function. Can use multiple columns. Limitations: Foreign keys not supported with partitioned tables. Only one partitioning column per table. All partitions must use same storage engine. Partition pruning only works when the partition key is in the WHERE clause. Best use: Time-series tables with data lifecycle management (delete old partitions). Tables where queries always filter on the partition key.

Problem: ALTER TABLE on a large table (100M rows) causes a full table copy — locks the table for hours in older MySQL. Even with Online DDL, it consumes resources. Online DDL (MySQL 5.6+): Many ALTER TABLE operations are in-place and don't lock the table. ALGORITHM=INPLACE, LOCK=NONE for supported operations. Adding an index, adding a nullable column are generally online. gh-ost (GitHub's Online Schema Change): Creates a new table with the new schema, uses triggers and binlog streaming to copy data and keep the ghost table in sync with ongoing writes. Cut over with minimal downtime (< 1 second table lock at the end). Pauses if replication lag increases. Industry standard for large MySQL table migrations. pt-online-schema-change (Percona): Similar approach using triggers. Slightly older but widely used. Data migrations (moving/transforming data): • Never do it in a single large transaction — holds locks too long • Process in batches: UPDATE ... WHERE id BETWEEN ? AND ? LIMIT 1000, commit each batch, sleep between batches • Monitor replication lag — slow down or pause if lag grows • Run during off-peak hours • Have a rollback plan before starting Deploy in phases: Add column first (nullable) → backfill data in batches → add NOT NULL constraint after backfill.

IN with subquery: SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status='vip'). MySQL materializes the subquery result as a temp table then does a lookup. For large subquery results, this can be slow. EXISTS: SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.status='vip'). Correlated subquery — for each outer row, runs the inner query. Stops as soon as it finds one match. Often faster than IN when the subquery result is large but existence check is selective. JOIN: SELECT o.* FROM orders o JOIN users u ON u.id = o.user_id WHERE u.status='vip'. Usually the most efficient — optimizer has the most freedom to choose join order and use indexes. MySQL optimizer behavior: Modern MySQL (8.0) often rewrites IN to a semi-join internally for similar performance to EXISTS. But explicit JOIN gives the optimizer the most flexibility. General rule: Prefer JOIN over subqueries for performance. Use EXISTS for "does any related row exist" checks, especially with NOT EXISTS (anti-join). Use IN when the list is short and static (IN (1, 2, 3)). NOT IN danger: NOT IN with a subquery that could return NULL produces no results (NULL comparisons). Use NOT EXISTS instead.

Redo log: Records all changes made by committed transactions (write-ahead log). On crash, MySQL replays the redo log to recover committed transactions that weren't yet written to data files. Sequential writes — much faster than random writes to data pages. Fixed-size circular log files (ib_logfile0, ib_logfile1 in MySQL 5.7; redo.log in MySQL 8.0). Redo log sizing: innodb_log_file_size × innodb_log_files_in_group. Too small → frequent checkpoints (flushing dirty pages) → I/O spikes. Too large → slow crash recovery (more log to replay). Typical: 1-4GB per log file. Undo log: Records the old version of rows before they were modified. Used for two purposes: 1. Transaction rollback: Restore rows to previous state if transaction rolls back. 2. MVCC (Multi-Version Concurrency Control): Long-running transactions read old versions of rows from undo log. This is why long transactions are harmful — they keep undo log from being purged, causing it to grow. Undo tablespace: Stored in ibdata1 (MySQL 5.7) or separate undo tablespaces (MySQL 8.0, truncatable). The "InnoDB history list length" in SHOW ENGINE INNODB STATUS shows unpurged undo entries — high values indicate long-running transactions or slow purge thread.

LIMIT OFFSET (naive): SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 1000. Problem: MySQL reads and discards 1000 rows before returning 10. Gets slower as offset grows — O(offset) cost. Keyset pagination (seek method, recommended): Use the last row's value as a cursor. Page 1: SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 10; Page 2: SELECT * FROM posts WHERE (created_at, id) < (last_created_at, last_id) ORDER BY created_at DESC, id DESC LIMIT 10; Benefits: O(1) — index seeks directly to the position regardless of how deep you are. Consistent under concurrent inserts (no rows skipped or duplicated from insertions). Requirements: Need a composite index (created_at, id). Pagination key must be unique (hence adding id as tiebreaker). Cannot jump to arbitrary page — only next/previous. Deferred join (for offset-based UI): If you must use LIMIT OFFSET, use a deferred join to reduce I/O: SELECT p.* FROM posts p JOIN (SELECT id FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 1000) AS t ON p.id = t.id; The inner query only reads the covering index (id + created_at), not full rows. Then joins to fetch full rows only for the 10 results.

INNER JOIN: Returns rows where the join condition matches in both tables. Most common. Rows without a match in either table are excluded. LEFT (OUTER) JOIN: Returns all rows from the left table plus matching rows from the right. If no match in right table, right columns are NULL. Use to find "orders with optional user data" or to detect orphan rows (WHERE right.id IS NULL). RIGHT (OUTER) JOIN: Returns all rows from the right table plus matching rows from left. Rarely needed — rewrite as LEFT JOIN with tables swapped for clarity. FULL OUTER JOIN: Returns all rows from both tables. Not directly supported in MySQL — simulate with UNION: SELECT * FROM a LEFT JOIN b ON a.id=b.id UNION ALL SELECT * FROM a RIGHT JOIN b ON a.id=b.id WHERE a.id IS NULL; CROSS JOIN: Cartesian product — every row from left combined with every row from right. N×M rows. Use for generating combinations or populating test data. Accidental cross join (missing join condition) is a common bug. SELF JOIN: Join a table to itself. Example: find employees and their managers where both are in the same employees table. Performance: Ensure join columns are indexed on the inner table. Use EXPLAIN to verify index usage. Avoid joining on functions (YEAR(date) = 2023 — prevents index use; use range instead).

Group Replication: MySQL's built-in multi-primary or single-primary replication plugin using Paxos-based distributed consensus. All nodes have the same data. Transactions committed only when a majority of nodes agree (quorum). Modes: • Single-primary: One node accepts writes, others are read-only replicas. Automatic primary election on failure. • Multi-primary: All nodes accept writes. Concurrent write conflict detection — conflicting transactions are rolled back on all but one node. Guarantees: No data loss on node failure (committed = majority agreed). Automatic failover — new primary elected within seconds. Self-healing — failed node rejoins and catches up. InnoDB Cluster: MySQL's complete HA solution combining Group Replication + MySQL Router (query routing) + MySQL Shell (administration). Three-node minimum for quorum. MySQL Router: Lightweight proxy that routes write traffic to primary, read traffic to replicas. Applications connect to router, not directly to nodes — transparent failover. Comparison to async replication: Standard async replication has no automatic failover and risks data loss on primary failure. Group Replication provides HA with automatic failover and no data loss. Requirements: All nodes must be low-latency (< 5ms between nodes recommended). All tables must use InnoDB with a primary key.

View: A stored SELECT query that appears as a table. No data stored — each query against the view re-executes the underlying query. Always returns current data. Use cases: Security (hide sensitive columns — grant access to view, not table), simplify complex queries, provide stable interface while underlying schema evolves. Limitation: Performance — if the view query is expensive, every access to the view pays the full cost. MySQL views are not automatically optimized (no view merging in many cases). Materialized view: The view query result is precomputed and stored physically. Reads hit the stored result, not the underlying tables — much faster. Must be refreshed periodically (on schedule or on-demand) to stay current. MySQL: Does NOT have native materialized views. Workarounds: Manual — create a summary table and refresh it with a scheduled event or application job. pt-summary-refresh or triggers. PostgreSQL: Has native MATERIALIZED VIEW with REFRESH MATERIALIZED VIEW CONCURRENTLY. When to use views: Simplification and access control. When to use materialized views (or equivalent summary tables): Pre-aggregated dashboards, reports, expensive joins that are queried frequently but don't need to be perfectly current.

MySQL query optimizer: Cost-based optimizer. For each possible execution plan, estimates the cost (I/O operations, CPU) and selects the cheapest. Statistics: Optimizer uses index statistics (cardinality — number of distinct values, stored in information_schema.STATISTICS) to estimate how many rows an index will return. Stale statistics → bad plans. Refresh with ANALYZE TABLE. Join order optimization: For N-table joins, there are N! possible orders. MySQL evaluates combinations up to optimizer_search_depth (default 62, but limits for large N). For very large joins it may not find the optimal order. Index selection: For each table access, optimizer estimates cost of: full table scan, each applicable index, covering index. Chooses lowest cost. Sometimes gets it wrong — override with USE INDEX, FORCE INDEX, or IGNORE INDEX hints (last resort). Subquery handling: MySQL 8.0 rewrites many subqueries as semi-joins, lateral joins, or derived tables for better optimization. Optimizer hints (MySQL 8.0): /*+ NO_INDEX_MERGE(t idx1) */ or /*+ JOIN_ORDER(t1, t2) */ — cleaner than index hints. When optimizer chooses wrong: EXPLAIN shows the chosen plan. If cardinality estimates are wildly off from reality, ANALYZE TABLE to refresh stats. If still wrong, use hints or rewrite the query.

SELECT *: Fetches all columns including large ones you don't need. Prevents covering index usage. Always specify needed columns. Functions on indexed columns in WHERE: WHERE YEAR(created_at) = 2023 — defeats the index. Use WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31' instead. LEADING WILDCARD: WHERE name LIKE '%smith%' — cannot use B-Tree index (can't start from the middle of a string). Use full-text search or reverse the pattern if possible. Implicit type conversion: WHERE varchar_column = 123 (integer) — MySQL converts all varchar values to int before comparing, preventing index use. Match data types. N+1 queries: 1 query returns N rows, then N queries fetch related data. Use JOINs or batch SELECT ... WHERE id IN (...). Missing indexes on JOIN columns: Join on an unindexed column → full scan of the inner table for each outer row. Large OFFSET: SELECT ... LIMIT 10 OFFSET 100000 — scans and discards 100K rows. Use keyset pagination. Unbounded queries: No LIMIT on queries that could return millions of rows — OOM or extremely slow. OVERUSE of NULL: Null-handling adds complexity. Use meaningful defaults where appropriate. Storing serialized data (JSON/XML) in a column and querying on it: Defeats indexing. Normalize the data or use JSON column with generated columns + indexes.

Generated column (MySQL 5.7+): A column whose value is computed from an expression involving other columns. The expression is defined in the schema — not inserted by the application. Two types: • VIRTUAL: Computed on the fly when the column is read. No storage. Default. • STORED: Computed and stored on INSERT/UPDATE. Consumes disk space but allows indexing. Syntax: ALTER TABLE orders ADD COLUMN total_with_tax DECIMAL(10,2) GENERATED ALWAYS AS (total * 1.18) STORED; Indexing: You can index a STORED generated column. This enables indexing on computed values — like indexing YEAR(created_at) without changing query syntax: ADD COLUMN order_year INT GENERATED ALWAYS AS (YEAR(created_at)) VIRTUAL; ADD INDEX idx_order_year (order_year); JSON use case: Extract JSON field to a generated column and index it: ADD COLUMN user_type VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(metadata->'$.type')) STORED; ADD INDEX idx_user_type (user_type); Constraints: Expression cannot reference other generated columns or non-deterministic functions (NOW(), RAND()). Cannot be used in foreign keys.

Use appropriate data types: INT (4 bytes) over BIGINT (8 bytes) when values fit. TINYINT for booleans (0/1). DATE not DATETIME if time is unnecessary. Smaller types = more rows per page = better cache utilization. Choose primary keys wisely: Use INT or BIGINT AUTO_INCREMENT for most tables. Avoid UUID as PK (random inserts cause B-Tree page splits, fragmentation, larger secondary indexes). If UUID needed, store as BINARY(16), use UUID_TO_BIN() — more compact, sortable with time-ordered UUIDs. Normalize first, denormalize intentionally: Start with 3NF. Denormalize only when query performance requires it and you understand the maintenance overhead. Index for your queries: Don't index everything — indexes slow writes. Index columns used in WHERE, JOIN ON, ORDER BY, GROUP BY. Composite index column order matters (most selective, equality columns first). Avoid NULL where possible: NULL complicates queries, indexes, and logic. Use meaningful defaults (0, '', 'unknown') where semantically equivalent. Timestamps: Always store created_at and updated_at. Use DATETIME (not TIMESTAMP — TIMESTAMP max is 2038). Store in UTC. Soft deletes: Add deleted_at DATETIME NULL. Partial index or filter WHERE deleted_at IS NULL. Consider archiving to separate table for very large datasets. Foreign keys: Use them for data integrity in non-sharded, non-microservice contexts. Ensure the FK column is indexed.

JSON column type (MySQL 5.7.8+): Stores JSON documents natively. Validates JSON on insert. Supports JSON path expressions for querying and updating. Querying: Use -> (JSON_EXTRACT shorthand) or ->> (JSON_UNQUOTE(JSON_EXTRACT)): SELECT data->'$.name' FROM users; -- returns "John" (with quotes) SELECT data->>'$.name' FROM users; -- returns John (no quotes) SELECT * FROM users WHERE data->>'$.age' > 25; Updating: JSON_SET, JSON_REPLACE, JSON_REMOVE for partial updates without replacing the entire document: UPDATE users SET data = JSON_SET(data, '$.status', 'active') WHERE id = 1; Indexing: Cannot index a JSON column directly. Use generated columns: ALTER TABLE users ADD email_gen VARCHAR(100) GENERATED ALWAYS AS (data->>'$.email') STORED; ALTER TABLE users ADD INDEX idx_email (email_gen); When to use: Flexible attributes that vary per row, configuration blobs, event payloads stored for auditing, prototype stages. When NOT to use: Fields you query/filter frequently — normalize those. Fields that need foreign key integrity. Any field that could be a proper column in a normalized schema.

MySQL supports four standard SQL isolation levels (InnoDB default: REPEATABLE READ): READ UNCOMMITTED: Can read uncommitted changes from other transactions (dirty read). Never use — can read rolled-back data. READ COMMITTED: Only reads committed data. Dirty reads prevented. Phantom reads and non-repeatable reads can occur. Each statement sees a fresh snapshot. Standard for most OLTP applications (PostgreSQL default). REPEATABLE READ (InnoDB default): Same snapshot used for the entire transaction. Non-repeatable reads prevented. Phantom reads theoretically possible but InnoDB prevents them with next-key locks in range scans. SERIALIZABLE: Transactions execute as if serial. All SELECT statements implicitly use LOCK IN SHARE MODE. Highest isolation, lowest concurrency. Use for financial reporting that must be completely accurate. InnoDB MVCC: All levels except SERIALIZABLE use snapshot reads (MVCC) for regular SELECTs — no locks needed for reads. Locking reads (SELECT FOR UPDATE) always read current data regardless of isolation level. Set per session: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Adjacency list: Each row has a parent_id column. Simple inserts. Retrieving subtrees requires recursive CTEs (MySQL 8.0) or multiple queries. Good for simple parent-child. Recursive CTE (MySQL 8.0): WITH RECURSIVE tree AS ( SELECT * FROM categories WHERE id = 1 UNION ALL SELECT c.* FROM categories c JOIN tree t ON c.parent_id = t.id ) SELECT * FROM tree; Path enumeration (materialized path): Store full path in a string column: /1/3/7/. Retrieving subtree: WHERE path LIKE '/1/3/%'. Easy for reads, path must be updated when moving nodes. Nested sets (modified preorder tree traversal): Each node has lft and rgt values representing DFS traversal order. Subtree = WHERE lft BETWEEN parent.lft AND parent.rgt. Fast reads for subtrees, expensive for inserts/moves (must update many nodes). Closure table: Separate table storing ALL ancestor-descendant relationships (not just parent-child). Rows: (ancestor_id, descendant_id, depth). Easy to query at any depth. Best for complex tree queries with many reads. More storage. Choice: Simple trees with MySQL 8.0 → recursive CTE on adjacency list. Read-heavy, stable hierarchy → nested sets or closure table.

ProxySQL: A high-performance MySQL proxy that sits between applications and MySQL servers. Acts as a smart middleware layer. Key features: • Read/write splitting: Automatically routes SELECT queries to replicas and writes to primary. No application code changes needed. • Connection pooling: Maintains fewer actual MySQL connections than client connections (multiplex thousands of app connections to dozens of DB connections). • Query routing rules: Route specific query patterns to specific servers (e.g., heavy analytics queries to dedicated replica). • Query rewriting: Modify queries in flight (add query hints, replace problematic queries). • Query caching: Cache results of specified queries (unlike MySQL's removed query cache, this is more configurable). • Rate limiting: Limit queries per second per user/query pattern. • Failover: Monitor server health, automatically remove failed nodes from the pool. Deployment: Run as a sidecar or standalone proxy layer. Applications connect to ProxySQL instead of MySQL directly. Admin interface: MySQL-compatible admin interface for runtime reconfiguration — no restart needed. Alternatives: MySQL Router (simpler, part of InnoDB Cluster), MaxScale (Mariadb), HAProxy (layer 4, less MySQL-aware). Use case: ProxySQL is industry standard for read/write split and connection pooling at MySQL scale.

MVCC (Multi-Version Concurrency Control): Enables concurrent reads and writes without readers blocking writers or writers blocking readers. Each transaction sees a consistent snapshot of the database as of its start time. InnoDB implementation: Each row has two hidden columns: • DB_TRX_ID: Transaction ID of the last transaction that modified the row • DB_ROLL_PTR: Pointer to the undo log record with the previous version Consistent read snapshot: At transaction start, InnoDB records the highest committed transaction ID (read view). A row version is visible if: its TRX_ID < read view low mark (committed before snapshot), or it's the current transaction's own changes. If the current row version is not visible to the snapshot (modified by a newer transaction), InnoDB follows ROLL_PTR to the undo log to find an older version that IS visible. Purge thread: Background thread cleans up undo log entries that are no longer needed by any active transaction. Long-running transactions prevent purge → undo log grows → InnoDB history list length increases → performance degrades. Benefit: SELECT queries never block on INSERT/UPDATE/DELETE. This is why InnoDB can handle high concurrency — reads and writes don't compete for the same locks.

Index fragmentation: Over time, INSERT/UPDATE/DELETE operations fragment B-Tree index pages. Pages are not fully utilized, data is not sequential — more I/O for the same data. Detecting: SELECT table_name, data_free, data_length FROM information_schema.TABLES WHERE table_schema='mydb'. High data_free relative to data_length indicates fragmentation. Causes: Random primary key inserts (UUID) cause page splits — inserts in the middle of the B-Tree. DELETE operations leave pages partially empty. Rows that grew in size after UPDATE force row overflow. Fixes: OPTIMIZE TABLE: Rebuilds the table and all indexes. Temporary full table copy. Reclaims space, defragments. For large tables, causes significant I/O during rebuild — use during maintenance window or via gh-ost/pt-osc. ALTER TABLE ... ENGINE=InnoDB: Forces table rebuild. Same effect as OPTIMIZE TABLE for InnoDB. pt-online-schema-change: Rebuild table without blocking reads/writes. Prevention: Use INT AUTO_INCREMENT as primary key (sequential inserts = no page splits). Avoid UUID as PK. Use time-ordered UUIDs (UUIDv7, ULID) if UUID-like PK needed.

Major MySQL 8.0 improvements: CTE (Common Table Expressions): WITH and WITH RECURSIVE — cleaner hierarchical and multi-step queries. Window functions: ROW_NUMBER(), RANK(), LAG(), LEAD(), NTILE() — analytical queries without complex subqueries. Descending indexes: INDEX(col DESC) — ORDER BY col DESC no longer requires filesort. JSON improvements: JSON_TABLE() function (JSON to relational), JSON aggregation functions. Invisible indexes: Mark index as INVISIBLE — optimizer ignores it but it stays maintained. Test impact before dropping. Atomic DDL: CREATE/DROP TABLE are now atomic — crash during DDL leaves DB consistent (no orphan .frm files). Role-based access control: Database roles for user permission management. Caching_sha2_password: Default auth plugin — more secure than mysql_native_password. Improved data dictionary: All metadata in InnoDB (previously some in .frm files). INNODB_DEDICATED_SERVER: Auto-configures buffer pool, log file size based on available RAM. Query hints: OPTIMIZER_HINTS for fine-grained plan control. Removed: Query cache (removed), MyISAM system tables, PROCEDURE ANALYSE().

Audit log requirements: Record who did what to which record and when. Immutable. Queryable for compliance. Doesn't slow down the main application. Approach 1 — Separate audit table per entity: orders_audit (id, order_id, action, changed_fields JSONB, old_values JSONB, new_values JSONB, user_id, ip_address, created_at). Triggered by application code (service layer). Fine-grained control. Approach 2 — Generic audit log table: audit_log (id, table_name, record_id, action, diff JSONB, user_id, created_at). One table for all entities. Simpler schema, harder to query for entity-specific history. Approach 3 — CDC-based: Debezium captures row changes from binlog → Kafka → audit storage (Elasticsearch, S3). No application code changes. Complete — captures all writes including direct DB access. Scalable — audit storage is separate from main DB. Design principles: • INSERT ONLY: Never UPDATE or DELETE audit records. Use partitioned table and DROP PARTITION for retention instead of DELETE. • Async: Write audit log asynchronously (via Kafka) to not add latency to main request. • Store diff: JSON diff of old vs new values — more useful than full snapshots. • Index on: record_id + table_name (for entity history), user_id (for user activity), created_at (for time range queries).

Binary log (binlog): MySQL's append-only log of all data modification statements (INSERT, UPDATE, DELETE) and DDL. Used for replication and point-in-time recovery. Formats: • STATEMENT: Logs SQL statements. Smaller but non-deterministic. • ROW: Logs actual row data (before/after images). Larger but deterministic — required for CDC. • MIXED: Default — uses STATEMENT normally, ROW for non-deterministic. CDC with Debezium: Debezium is an open-source CDC platform. MySQL connector connects to MySQL as a replica — reads binlog stream. Publishes row-level change events (INSERT/UPDATE/DELETE) to Kafka topics with full before/after row data. Config: MySQL must have binlog enabled (log_bin=ON), ROW format, GTID enabled (gtid_mode=ON, enforce_gtid_consistency=ON). Debezium user needs REPLICATION SLAVE and REPLICATION CLIENT privileges. Use cases: • Search indexing: Keep Elasticsearch in sync with MySQL • Cache invalidation: Invalidate Redis cache when DB rows change • Event streaming: Publish domain events without application code changes • Analytics: Stream changes to data warehouse • Audit log: Capture all changes for compliance Benefits over application-level events: Catches all writes (including direct DB access), no application code changes, no performance overhead on the write path.

performance_schema: A built-in database (schema) that exposes runtime metrics about MySQL server execution. Zero-copy instrumentation with minimal overhead (< 5% in most cases). Enabled by default in MySQL 5.7+. Key tables: • events_statements_summary_by_digest: Aggregated query stats (count, avg/max latency, rows examined) grouped by query fingerprint. Best starting point for identifying slow queries. • events_waits_summary_by_event_name: Where time is spent waiting (locks, I/O, network). • table_io_waits_summary_by_table: I/O stats per table — find which tables are read/written most. • file_summary_by_event_name: File I/O — disk read/write counts and bytes. • threads: All active threads with their current state. Finding top queries: SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1e12 avg_sec FROM performance_schema.events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC LIMIT 10; Lock analysis: SELECT * FROM performance_schema.events_waits_summary_global_by_event_name WHERE EVENT_NAME LIKE 'wait/lock%'; sys schema: A convenience layer on top of performance_schema with human-readable views. sys.statements_with_full_table_scans, sys.innodb_lock_waits — much easier to query.

COUNT(*): Counts all rows including those with NULL values. In InnoDB, NOT an instant operation (unlike MyISAM which stores row count). MySQL optimizes it to use the smallest available index rather than the full table scan. COUNT(1): Counts all rows — treats 1 as a constant expression (never NULL). Functionally identical to COUNT(*). Optimizer treats them the same — no performance difference. COUNT(col): Counts rows where col is NOT NULL. If col has NULL values, returns a smaller number than COUNT(*). Uses a different query plan — may or may not be index-optimized depending on column and index. MySQL InnoDB behavior: For COUNT(*) or COUNT(1) without WHERE clause, MySQL uses the smallest available secondary index (index scans are faster than table scans — fewer pages). This is why a covering index on a small column helps COUNT performance. Common mistake: COUNT(DISTINCT col) is much more expensive — requires accumulating distinct values. For approximate counts use HyperLogLog in Redis or batch-computed counters. Summary: Use COUNT(*) for row counting — it's the most clear and equally optimized. COUNT(col) when you specifically want to exclude NULLs.

Composite index: An index on multiple columns (col_a, col_b, col_c). B-Tree sorts by col_a first, then col_b within same col_a, then col_c within same col_b. Left-prefix rule: A composite index (a, b, c) can support queries on: (a), (a, b), (a, b, c). It CANNOT support queries on just (b), just (c), or (b, c) — because without a, the sort order of b is not predictable. Column ordering strategy: 1. Equality columns first: Columns used in WHERE col = value (high cardinality) → reduces result set quickly. 2. Range column last: Columns used in WHERE col > value or BETWEEN — once you hit a range, further columns can't be used for index scanning (still can be used for filtering after scan). 3. High cardinality first (within equality columns): More selective columns narrow the result faster. Example: Query WHERE status='active' AND created_at > '2024-01-01' ORDER BY user_id Index: (status, created_at, user_id) — status (equality) first, created_at (range) second, user_id (order by) included. Covering index: Add SELECT columns at the end of the composite index to avoid table lookup. Don't over-engineer — index maintenance cost on writes.

Soft delete: Mark records as deleted without physically removing them. Enables recovery, audit trail, and referential integrity. Implementation: Add deleted_at DATETIME NULL column. DELETE → UPDATE tbl SET deleted_at = NOW() WHERE id = ?. Active records: WHERE deleted_at IS NULL. Index considerations: Most queries filter WHERE deleted_at IS NULL. A regular index on deleted_at is low cardinality (almost all rows are NULL). Partial index (PostgreSQL: CREATE INDEX WHERE deleted_at IS NULL) excludes deleted rows — MySQL doesn't support partial indexes directly. MySQL workaround for efficient soft delete index: Add an is_deleted TINYINT(1) DEFAULT 0 column. Create composite index (is_deleted, other_filter_columns). WHERE is_deleted = 0 AND user_id = ? — index prunes deleted rows. More binary and index-friendly than datetime NULL check. Problems with soft delete: • Every query must include the soft delete filter — easy to forget → data leakage • Unique constraints: user email must be unique, but a deleted user's email should be reusable. Add (email, deleted_at) unique constraint or nullify email on delete. • Joins: Related tables must also soft-delete consistently • DB bloat: Deleted rows accumulate — archive periodically to a cold storage table Alternative: Move deleted rows to an archive table — cleaner, but more complex.

CHAR(n): Fixed-length, 0-255 characters. Padded with spaces. Stored in row. Fast for fixed-size fields. VARCHAR(n): Variable-length, 0-65535 characters (row limit applies). 1-2 byte length prefix + data. Stored in row (for short values). Most common string type. TEXT types: TINYTEXT (255B), TEXT (64KB), MEDIUMTEXT (16MB), LONGTEXT (4GB). Stored off-page (in a separate page) for large values — the row itself stores a 20-byte pointer. Cannot have a DEFAULT value (MySQL 5.7). Cannot use full length in index (must specify index prefix length: INDEX(bio(100))). BLOB types: Same sizes as TEXT types. Binary — no character set or collation. For binary data: images, files, encrypted data. Choice guide: • < 255 chars, fixed length → CHAR • < 16KB variable → VARCHAR (stored in-row when small, off-row when large) • Large text content (articles, descriptions) → TEXT • Binary data → BLOB Performance note: Off-page storage (TEXT/BLOB) for large values means two I/O operations to read the row (row page + overflow page). For frequently read large fields, consider storing in object storage (S3) and keeping only the URL in the DB.

InnoDB row-level locking: When a transaction modifies a row (UPDATE/DELETE), it acquires an exclusive (X) row lock. Other transactions trying to modify the same row must wait until the first transaction commits or rolls back. Read-write concurrency: Regular SELECT (snapshot read via MVCC) does not block on row locks. Writers don't block readers. Readers don't block writers. This is why InnoDB can handle high concurrency. Locking reads: SELECT ... FOR UPDATE acquires X lock (no other updates, no other FOR UPDATE reads). SELECT ... FOR SHARE (LOCK IN SHARE MODE) acquires S lock (multiple readers allowed, no updates allowed). Lock wait timeout: innodb_lock_wait_timeout (default 50 seconds). Transaction waits this long for a lock before receiving ER_LOCK_WAIT_TIMEOUT error. Deadlock: Two transactions waiting on each other's locks. InnoDB detects and rolls back the victim. Application must retry. Row lock granularity: InnoDB locks the index record, not the disk row. If the WHERE clause uses a non-indexed column, InnoDB may escalate to locking more rows (or the entire table for non-indexed scans). Always index your WHERE columns for targeted locking. Gap locks: Prevent phantom reads in REPEATABLE READ by locking the gap between index values in addition to the row itself.

Schema-on-write (relational/SQL databases): Schema defined and enforced at write time. Every row must conform to the schema. Invalid data rejected on INSERT. Data always consistent and structured. Changes require schema migrations (ALTER TABLE). Advantages: Data quality guaranteed. Queries are predictable — all rows have the same structure. Optimization possible because structure is known. Schema-on-read (NoSQL/document stores): No schema enforcement at write time. Any document structure is accepted. Schema is applied implicitly when reading (application code interprets the structure). Advantages: Flexible — evolve data structure without migrations. Handles heterogeneous data naturally. Good for rapidly changing requirements. Disadvantages: Data quality is the application's responsibility. Reading requires handling missing fields, wrong types. Querying on nested or variable fields is harder. MySQL perspective: MySQL is schema-on-write. You can partially simulate schema-on-read using JSON columns — store flexible data in a JSON field without schema enforcement. But querying/indexing that JSON data is more complex than structured columns. Hybrid: Many modern systems use schema-on-write for core entities (users, orders) and schema-on-read for auxiliary data (event payloads, configuration blobs stored in JSON).

MySQL timezone settings: • global: Set when MySQL server starts (--default-time-zone or system_time_zone) • session: SET time_zone = '+05:30'; or SET time_zone = 'Asia/Kolkata'; Named timezones (Asia/Kolkata) require the timezone tables to be populated: mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql TIMESTAMP behavior: On insert, MySQL converts from session timezone to UTC for storage. On select, converts from UTC back to session timezone. If different clients have different session timezones, they see the same UTC data displayed in their local time. DATETIME behavior: No timezone conversion. Stored and returned exactly as given. All clients see the same value regardless of their session timezone. Best practice: • Set server timezone to UTC (default-time-zone = '+00:00') • Set application connection timezone to UTC • Use DATETIME type (not TIMESTAMP) • Handle timezone display in application layer • Never rely on implicit timezone conversion Common bugs: Developer inserts in IST (+05:30), query returns different value to user in UTC. Or TIMESTAMP values shift when server timezone changes. Avoid by standardizing on UTC at every layer.

Practically identical in MySQL: Creating a UNIQUE KEY (constraint) automatically creates a unique index. MySQL enforces uniqueness via the index. Creating: ALTER TABLE users ADD UNIQUE KEY uk_email (email); -- constraint syntax ALTER TABLE users ADD UNIQUE INDEX uk_email (email); -- index syntax Both create the same physical structure. Behavior: On INSERT or UPDATE, MySQL checks the unique index. If a duplicate value is found, ER_DUP_ENTRY error is returned. INSERT IGNORE or INSERT ... ON DUPLICATE KEY UPDATE can handle conflicts gracefully. NULL handling: Unique indexes allow multiple NULL values — NULL is not considered equal to NULL in SQL standard. So (email = NULL) can appear multiple times in a unique column. Exception: MySQL's behavior for multi-column unique indexes with NULLs differs from some other DBs. Composite unique: UNIQUE KEY (user_id, product_id) — combination must be unique, individual values can repeat. Foreign key requirement: A foreign key's referenced column(s) must be indexed (primary key or unique key). InnoDB automatically creates an index for the FK column if none exists. Difference from application validation: Unique constraints in the DB are the final safety net — they enforce uniqueness even with concurrent writes (where application-level checks can have race conditions).

Key metrics to monitor: Queries: Queries per second (QPS), slow query count, error rate (Aborted_connects, connection errors). Connections: Threads_connected vs max_connections. Alert when approaching limit. Threads_running (actively executing vs sleeping). InnoDB: InnoDB buffer pool hit ratio (should be > 99%). InnoDB rows read/inserted/updated/deleted per second. History list length (unpurged undo — high value = long transactions). Replication: Seconds_Behind_Master / Seconds_Behind_Source (replica lag). Should be < 1 second normally. Disk: Data directory disk usage, binlog disk usage, IOPS. Tools: • Prometheus + mysqld_exporter: Scrapes MySQL metrics, 400+ metrics available. Dashboards in Grafana (official MySQL dashboard available). • Percona Monitoring and Management (PMM): Complete MySQL monitoring solution. Query analytics, explain plans, slow query analysis. • CloudWatch (AWS RDS): Managed metrics for RDS instances. • pt-stalk: Collects diagnostic data during performance problems. Alerting thresholds: CPU > 80%, disk > 85%, replication lag > 5s, connection usage > 80%, buffer pool hit rate < 98%. Once anomaly detected: Check SHOW PROCESSLIST (active queries), SHOW ENGINE INNODB STATUS (lock waits, buffer pool, recent deadlocks), performance_schema.

Window functions (MySQL 8.0+): Perform calculations across a set of rows related to the current row (the "window") without grouping. Unlike GROUP BY, they do not collapse rows. Syntax: function() OVER (PARTITION BY col ORDER BY col ROWS/RANGE frame) Ranking functions: • ROW_NUMBER(): Unique sequential number per partition (1,2,3 — no ties) • RANK(): Rank with gaps on ties (1,2,2,4) • DENSE_RANK(): Rank without gaps (1,2,2,3) • NTILE(n): Divide rows into n buckets Offset functions: • LAG(col, n): Value from n rows before current row — compare with previous period • LEAD(col, n): Value from n rows after current row • FIRST_VALUE(col): First value in window frame • LAST_VALUE(col): Last value in window frame Aggregate as window functions: SUM() OVER (), AVG() OVER (), COUNT() OVER () — running totals, moving averages. Example — running total: SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS running_total FROM transactions; Example — rank within group: SELECT name, dept, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank FROM employees;

MySQL does not have built-in Row-Level Security (unlike PostgreSQL's RLS). Must be implemented at application or proxy layer. Application-level: Include tenant_id or user_id filter in every query. WHERE tenant_id = :current_tenant. Risk: any query that misses this filter leaks data across tenants. MySQL views: Create views that filter rows based on a function: CREATE VIEW my_orders AS SELECT * FROM orders WHERE user_id = get_current_user_id(); Grant users access to the view, not the table. Function get_current_user_id() returns a session variable set at login. Session variables: At connection time, set a session variable: SET @current_tenant = ?. Create a stored procedure or trigger that checks this variable. Fragile — variable must be set consistently. ProxySQL rewriting: ProxySQL intercepts queries and appends WHERE tenant_id = ? dynamically based on the authenticated user. Virtual Private Database pattern: Wrap all data access in stored procedures that enforce security checks. Best practice: Use PostgreSQL if RLS is a core requirement — native row-level policies are cleaner and more robust. For MySQL: use ORM query scopes/global filters (Hibernate filters, Spring Data specifications) that automatically append tenant conditions to all queries.

Primary Key: • Uniquely identifies each row — no two rows can have the same PK value • Cannot contain NULL values • Only ONE primary key per table (can be composite) • In InnoDB, data rows are physically stored in PK order (clustered index) • Automatically creates a clustered unique index • Typically used for the main row identifier used in JOIN operations Unique Key (Unique Index): • Enforces uniqueness across the column(s) • CAN contain NULL values (and multiple NULL values are allowed — NULL ≠ NULL in SQL) • A table can have MULTIPLE unique keys • Creates a non-clustered (secondary) unique index • Used for business uniqueness constraints (email, username, code) Example table: CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, -- clustered index, not null email VARCHAR(255) NOT NULL UNIQUE, -- secondary unique index, allows one null username VARCHAR(50) NOT NULL UNIQUE -- another secondary unique index ); Secondary unique indexes in InnoDB: Leaf nodes store index key + primary key. To fetch the full row, InnoDB does a bookmark lookup to the clustered index using the PK. This is why a compact primary key matters — all secondary indexes carry it.

Event scheduler: MySQL's built-in cron-like mechanism to execute SQL statements or stored procedures on a schedule. Enable: SET GLOBAL event_scheduler = ON; (or in my.cnf: event_scheduler=ON) Creating an event: CREATE EVENT purge_old_sessions ON SCHEDULE EVERY 1 HOUR STARTS NOW() DO DELETE FROM sessions WHERE expires_at < NOW() - INTERVAL 24 HOUR; One-time event: CREATE EVENT send_report ON SCHEDULE AT '2024-12-31 23:59:59' DO CALL generate_year_end_report(); Recurring with end: CREATE EVENT temp_banner ON SCHEDULE EVERY 1 DAY STARTS '2024-01-01' ENDS '2024-01-07' DO UPDATE banners SET active = 0 WHERE id = 42; Managing: SHOW EVENTS; ALTER EVENT ... ENABLE/DISABLE; DROP EVENT; View event status: SELECT * FROM information_schema.EVENTS; Limitations: Only one instance of each event can run at a time (no overlap protection beyond that). Runs on the DB server — moves compute to DB. For complex scheduling needs, use application-level schedulers (Quartz, cron + application code) which are easier to test and monitor.

EXPLAIN ANALYZE (MySQL 8.0.18+): Executes the query for real and shows the actual execution statistics alongside the estimated ones. Unlike regular EXPLAIN which only shows the plan without executing. Syntax: EXPLAIN ANALYZE SELECT * FROM orders JOIN users ON orders.user_id = users.id WHERE orders.status = 'pending'; Output shows per node: • Estimated rows vs actual rows: Huge difference means stale statistics (ANALYZE TABLE to fix) • Estimated cost vs actual time: Identifies which part of the plan is actually slow • Loops: How many times the node was executed (inner table in nested loop join) • actual time: X..Y — X is time to first row, Y is total time for all rows Example output: -> Nested loop inner join (cost=120.5 rows=100) (actual time=0.5..15.2 rows=87 loops=1) -> Filter: orders.status='pending' (cost=50.2 rows=200) (actual time=0.2..8.1 rows=150 loops=1) -> Index scan on orders using idx_status -> Single-row index lookup on users using PRIMARY (actual time=0.02..0.03 rows=1 loops=150) Warning: Since EXPLAIN ANALYZE executes the query, avoid on write queries (UPDATE/DELETE/INSERT) unless using EXPLAIN ANALYZE SELECT subquery form. EXPLAIN ANALYZE on slow queries will be slow.

Transient failures: Network hiccup, DB restart, connection timeout. Must retry — these often self-resolve. Permanent failures: Wrong credentials, DB host unreachable permanently, DB down. Retrying is futile — alert and fail fast. Connection pool behavior (HikariCP): Pool validates connections before handing them out (connectionTestQuery or isValid()). Stale connections are detected and replaced. Pool health check runs background threads. HikariCP configuration for resilience: • connectionTimeout: Max wait for a connection from pool (30s) • idleTimeout: Close idle connections (10 min) • maxLifetime: Recycle connections before DB timeout kicks in (29 min, less than DB wait_timeout of 30 min) • keepaliveTime: Send keepalive ping to prevent idle connections from being dropped by firewall (2 min) Application retry: Catch SQLTransientException (transient — retry) vs SQLNonTransientException (permanent — don't retry). Spring Retry or Resilience4j Retry with exponential backoff. Circuit breaker: After N consecutive DB failures, open the circuit. Don't hammer a DB that's down. Return error immediately during open state. Allow recovery probe after timeout. Health checks: Actuator /health endpoint checks DB connectivity. Load balancer removes unhealthy instances from rotation. Alert on DB health check failures.

SQL query clauses execute in a logical order that differs from the written order: Logical execution order: 1. FROM — identify source tables 2. JOIN — combine tables based on join conditions 3. WHERE — filter rows (operates on individual rows before grouping) 4. GROUP BY — group rows by specified columns 5. Aggregate functions — compute SUM, COUNT, AVG, etc. per group 6. HAVING — filter groups based on aggregate results 7. SELECT — select and compute output columns (aliases defined here) 8. DISTINCT — remove duplicate rows 9. ORDER BY — sort the result set (can reference SELECT aliases) 10. LIMIT / OFFSET — restrict number of rows returned Why this matters: • WHERE cannot reference SELECT aliases (aliases not yet defined in step 3). Use a subquery or CTE. • HAVING can reference SELECT aliases in MySQL (MySQL extension) but not in standard SQL. • WHERE is evaluated before GROUP BY — use HAVING for aggregate filters. • ORDER BY can reference SELECT aliases — it executes after SELECT. Performance implication: Apply filters as early as possible (WHERE vs HAVING). WHERE reduces rows before grouping — less data to aggregate. HAVING filters after full aggregation — more work done before filtering.

Read replicas: Most common approach. Primary handles all writes. One or more replicas receive async replication from primary. Route read queries to replicas. Read/write routing: • ProxySQL: Automatically routes SELECT to replicas, writes to primary • Application-level: Use two DataSource beans (primary + replica), annotate read-only methods with @Transactional(readOnly=true) routed to replica • Spring: AbstractRoutingDataSource to switch DataSource per transaction Caching: Redis/Memcached in front of DB. Cache popular queries. Reduces DB read load by 80%+ for read-heavy workloads. CDN: For public data (product catalog, blog posts), cache API responses at CDN. Near-zero DB reads for anonymous users. Read replica considerations: • Replication lag: Replicas may serve slightly stale data. Must be acceptable for the use case (never use replica for: auth checks, payment status, inventory counts during checkout) • Connection overhead: More replicas = more replica connections. Each replica needs monitoring. • Consistency: Writes go to primary; if you immediately read from replica, you may miss your own write (read-your-writes consistency issue). Route reads after writes to primary for a short window. Sharding: If reads + writes both exceed single-node capacity, shard — partition data across multiple primaries.

Statement-based replication (SBR): Logs the SQL statement that caused the change. Replica re-executes the statement. Advantages: Compact log — one statement can represent millions of row changes (UPDATE ... WHERE). Readable log — you can see what changed. Disadvantages: Non-deterministic functions produce different results on replica: NOW(), RAND(), UUID(), LOAD_FILE(), USER(). Statements with ORDER BY without LIMIT may produce different row ordering on replica. Requires more CPU on replica (re-executing queries). Row-based replication (RBR, recommended): Logs actual before/after images of changed rows. Replica applies row changes directly without re-executing SQL. Advantages: Always correct — deterministic regardless of functions used. Less CPU on replica (no query parsing/execution). Better for complex queries where re-execution on replica would be expensive. Disadvantages: Larger binlog for bulk operations (each changed row logged individually). Harder to read (binary format, need mysqlbinlog to decode). Mixed: Uses SBR normally, switches to RBR for non-deterministic statements automatically. MySQL default: MIXED in 5.7, ROW in 8.0. For CDC (Debezium): Requires ROW-based replication — needs actual row values to publish change events. Best practice: Use ROW format for production. Enables CDC, correct replication, lower replica CPU for most workloads.

CTE (MySQL 8.0+): Named subquery defined at the top of a query, reusable within the same query. Improves readability and enables recursive queries. Non-recursive CTE: WITH active_users AS ( SELECT id, name FROM users WHERE status = 'active' ), recent_orders AS ( SELECT user_id, COUNT(*) orders FROM orders WHERE created_at > NOW() - INTERVAL 30 DAY GROUP BY user_id ) SELECT u.name, COALESCE(o.orders, 0) order_count FROM active_users u LEFT JOIN recent_orders o ON u.id = o.user_id; Recursive CTE (WITH RECURSIVE): Essential for hierarchical data. WITH RECURSIVE subordinates AS ( SELECT id, name, manager_id FROM employees WHERE id = 1 -- anchor UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e JOIN subordinates s ON e.manager_id = s.id -- recursive ) SELECT * FROM subordinates; Add LIMIT or depth counter to prevent infinite recursion on cyclic graphs. Performance: CTEs in MySQL 8.0 are NOT automatically optimized as inline views — they are materialized (computed once into a temp table). A subquery in FROM is often rewritten by the optimizer; a CTE may not be. For performance-critical paths, test both CTE and subquery form.

DELETE: Removes rows matching the WHERE clause. Row by row — each deletion is logged individually in binlog and undo log. Slow for large tables. Can be rolled back (within a transaction). Fires DELETE triggers. Returns the count of deleted rows. Keeps table structure and auto-increment value. TRUNCATE: Removes all rows by deallocating data pages — much faster than DELETE for large tables (no row-by-row logging). DDL operation — implicit commit, cannot be rolled back. Does NOT fire triggers. Resets AUTO_INCREMENT counter to 1. Table structure remains. In MySQL, TRUNCATE requires the table to have no FK constraints referencing it. DROP TABLE: Removes the table and all its data, indexes, triggers, constraints permanently. Cannot be rolled back (DDL). Fast — just removes metadata and deallocates pages. When to use: • DELETE: Remove specific rows, within a transaction, needs rollback capability • TRUNCATE: Clear all data from a table quickly (log rotation, test data cleanup, staging reset) • DROP: Remove the table entirely (decommissioning a feature, dropping a temp table) Danger zone: TRUNCATE and DROP on production tables are irreversible without a backup. Always take a backup or export before running on large production tables.

Pattern: Add a version (or updated_at) column. On update, include the current version in the WHERE clause and increment it. If 0 rows affected, another transaction modified the row first. Schema: CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(100), stock INT, version INT NOT NULL DEFAULT 0 ); Application flow: 1. Read: SELECT id, stock, version FROM products WHERE id = 42; Result: id=42, stock=100, version=5 2. Business logic: new_stock = 100 - quantity 3. Update: UPDATE products SET stock = 90, version = 6 WHERE id = 42 AND version = 5; 4. Check affected rows: - affectedRows = 1 → success - affectedRows = 0 → conflict (someone else updated between our read and write) → retry or error JPA implementation: @Entity public class Product { @Id private Long id; private int stock; @Version private int version; // JPA manages this automatically } // JPA throws OptimisticLockException if version mismatch Vs timestamp: Using updated_at as version has millisecond granularity — two updates in the same millisecond could conflict silently. Integer version is safer. Retry strategy: Catch OptimisticLockException, re-read the entity, re-apply business logic, attempt update again. Maximum N retries to prevent infinite loops.

CREATE TABLE ... SELECT: Copies data and creates new table with same columns, but does NOT copy indexes, constraints, auto_increment, or foreign keys. CREATE TABLE new_table SELECT * FROM old_table WHERE 1=0; -- structure only, no data CREATE TABLE new_table SELECT * FROM old_table; -- structure + data CREATE TABLE ... LIKE: Copies exact table structure including indexes, constraints, auto_increment — but no data. CREATE TABLE new_table LIKE old_table; INSERT INTO new_table SELECT * FROM old_table; -- then copy data separately This is the correct way to create an identical copy. MySQL Workbench / mysqldump: Export and reimport specific table. mysqldump mydb old_table | mysql mydb new_table -- after renaming in SQL With different database: CREATE TABLE new_db.new_table LIKE old_db.old_table; INSERT INTO new_db.new_table SELECT * FROM old_db.old_table; Use cases: • Testing: Create a copy of production data for safe testing • Archiving: Copy table to archive_orders before running a batch delete • Schema changes: Create new_table with new schema, migrate data, rename • Backup: Quick snapshot before a risky operation Large tables: Copy in batches to avoid long-running transactions: INSERT INTO new_table SELECT * FROM old_table WHERE id BETWEEN ? AND ?;

information_schema: A virtual database built into MySQL that provides metadata about all other databases, tables, columns, indexes, constraints, users, and privileges. Read-only — cannot insert/update/delete. Queries it like regular tables. Key tables: • SCHEMATA: All databases • TABLES: All tables with row count estimates, data size, index size, engine • COLUMNS: All columns with types, defaults, nullable, character set • STATISTICS: Index statistics including cardinality per index key • KEY_COLUMN_USAGE: Foreign key relationships • TABLE_CONSTRAINTS: All constraints (PK, UK, FK, CHECK) • ROUTINES: Stored procedures and functions • TRIGGERS: All triggers • VIEWS: All view definitions • PROCESSLIST: Currently running queries (same as SHOW PROCESSLIST) • USER_PRIVILEGES: User permission grants Common queries: -- Find tables without a primary key: SELECT t.TABLE_NAME FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA='mydb' AND t.TABLE_NAME NOT IN (SELECT TABLE_NAME FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY'); -- Find large tables: SELECT TABLE_NAME, ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024) size_mb FROM information_schema.TABLES WHERE TABLE_SCHEMA='mydb' ORDER BY size_mb DESC;

innodb_flush_log_at_trx_commit: Controls how InnoDB flushes the redo log to disk on transaction commit. The most important durability/performance tradeoff parameter. Value = 1 (default, ACID compliant): On every transaction commit, InnoDB writes and fsyncs the log buffer to the redo log file on disk. Guarantees no committed transaction is lost on crash. Slowest — disk fsync on every commit. Value = 0: Log buffer flushed to disk once per second (background thread). Up to 1 second of committed transactions can be lost on mysqld crash (not just OS crash). Fastest — no fsync per commit. Value = 2 (balanced): On every transaction commit, log buffer written to OS file cache (write() syscall, not fsync). OS flushes to disk once per second. Committed data survives mysqld crash (in OS cache). Lost only on OS/power crash. Faster than 1 for write-heavy workloads. Recommendation: • Production OLTP: Value = 1 (no data loss) • Read-heavy with infrequent writes: Value = 1 (default) • Batch import / high-write temporary workloads: Value = 2 (dramatically faster, small risk window) • Development: Value = 2 or 0 (speed) Related: sync_binlog = 1 (sync binlog on every commit) pairs with flush = 1 for full durability guarantee.

For millions of rows, a naive LIKE '%keyword%' query is O(n) — scans every row. Unacceptable for production. MySQL FULLTEXT index: For text columns. Tokenizes words, builds inverted index. MATCH(col) AGAINST ('keyword') uses the index. Fast for word-level search. Limitations: no fuzzy matching, no substring, minimum word length, English-optimized by default. Elasticsearch / OpenSearch (recommended for scale): External search engine with its own inverted index. Sync data from MySQL via: • Logstash JDBC input plugin (polling) • Debezium CDC → Kafka → Elasticsearch consumer • Application-level dual write (write to MySQL + ES on every change) ES advantages: Fuzzy matching (typo tolerance), multi-language analyzers, synonym support, relevance scoring (BM25), faceted search, autocomplete, geospatial search. MySQL 8.0 improvements for text search: Full-text search with custom plugins, ngram parser (for CJK languages), MeCab parser (Japanese). For exact prefix search: B-Tree index supports LIKE 'prefix%' (no leading wildcard). WHERE username LIKE 'john%' uses index efficiently. For numeric/date range: Already handled efficiently by B-Tree indexes. Rule: Full-text search → Elasticsearch. Prefix search → MySQL index. Substring/fuzzy → Elasticsearch.

max_connections: Maximum number of simultaneous client connections MySQL accepts. Default: 151. Connections beyond this receive "Too many connections" error. Each connection consumes: • Thread stack: ~1MB per connection thread • Per-connection buffers: sort_buffer_size, join_buffer_size, read_buffer_size (allocated per-query as needed) • Connection overhead: Memory for state, query cache, authentication Calculation: max_connections × per-thread memory should not exceed available RAM minus OS + MySQL base memory. With 8GB RAM and 1MB per thread: ~4000 connections feasible (leaving room for other processes). High connection counts: If you need 5000+ connections, use connection pooling middleware (ProxySQL, PgBouncer equivalent). Multiplexes thousands of app connections to dozens of DB connections. Much more efficient. Monitoring: SHOW STATUS LIKE 'Max_used_connections'; shows the peak connections since last restart. If this is close to max_connections, increase the limit or add connection pooling. Related settings: • wait_timeout: How long to wait before closing idle connections (default 8 hours — reduce to 1-5 min for web apps) • interactive_timeout: Same for interactive connections • thread_cache_size: Cache N threads for reuse (avoid thread creation overhead on new connections)

Spatial index: R-Tree index for geospatial data types (GEOMETRY, POINT, LINESTRING, POLYGON). Enables efficient spatial queries: find all points within a bounding box, find geometries that intersect. Setup: CREATE TABLE locations ( id INT PRIMARY KEY, name VARCHAR(100), coordinates POINT NOT NULL SRID 4326, -- SRID 4326 = WGS84 (GPS coordinates) SPATIAL INDEX idx_coordinates (coordinates) ); Inserting: INSERT INTO locations VALUES (1, 'Coffee Shop', ST_SRID(POINT(77.5946, 12.9716), 4326)); Querying: -- Within radius (bounding box approximation, then filter): SELECT name FROM locations WHERE MBRContains( ST_GeomFromText('POLYGON(...)' ), -- bounding box coordinates ); -- More precise: ST_Distance_Sphere for actual distance: SELECT name, ST_Distance_Sphere(coordinates, ST_SRID(POINT(77.59,12.97), 4326)) AS dist_m FROM locations HAVING dist_m < 1000 -- within 1 km ORDER BY dist_m; MySQL 8.0: Full SRS (Spatial Reference System) support. Functions: ST_Contains, ST_Intersects, ST_Within, ST_Distance, ST_Buffer. For production geospatial at scale: Use PostGIS (PostgreSQL extension) — far more complete spatial capabilities. Or Redis GEOADD/GEORADIUS for simple lat/lon proximity queries.

Both are GROUP BY extensions for generating subtotals and grand totals. GROUP BY ... WITH ROLLUP (MySQL supported): Generates a result set with subtotals at each GROUP BY level plus a grand total row. Example: SELECT year, quarter, SUM(revenue) FROM sales GROUP BY year, quarter WITH ROLLUP; Produces: 2024, Q1, 100000 2024, Q2, 120000 2024, NULL, 220000 -- subtotal for 2024 2023, Q1, 90000 2023, NULL, 90000 -- subtotal for 2023 NULL, NULL, 310000 -- grand total NULL values in ROLLUP represent the "all" grouping level. Use GROUPING(col) function to distinguish real NULLs from ROLLUP-generated NULLs. CUBE: Generates all possible combinations of subtotals (all dimensions). MySQL does not support CUBE natively (PostgreSQL does: GROUP BY CUBE(a,b,c)). Simulate in MySQL with multiple UNION ALL ROLLUP statements. GROUPING SETS: More precise control — specify exactly which grouping combinations to compute. MySQL 8.0 does not support GROUPING SETS natively either. Use case: Reporting and analytics — sales dashboards showing yearly/quarterly/monthly subtotals in a single query.

NULL means "unknown" or "missing" — it is not a value. NULL is not equal to anything, including itself. NULL compared to NULL returns NULL (not TRUE). Comparisons: • col = NULL → always NULL (not TRUE or FALSE) — never use this • col IS NULL → correct way to check for NULL • col IS NOT NULL → correct way to check non-NULL • col <=> NULL → NULL-safe equality operator (returns TRUE if both are NULL) Aggregate functions: SUM, COUNT, AVG ignore NULL values. COUNT(*) counts all rows; COUNT(col) skips NULLs. Handling NULLs: • COALESCE(col, default): Returns first non-NULL value. COALESCE(phone, 'N/A') • IFNULL(col, default): Returns default if col is NULL (MySQL-specific, less portable) • NULLIF(a, b): Returns NULL if a = b, else a. Prevents division by zero: total / NULLIF(count, 0) • ISNULL(col): Returns 1 if NULL, 0 if not (MySQL-specific) NULL in indexes: NULL values are indexed in MySQL (unlike some other DBs). Multiple NULLs allowed in a UNIQUE index. NULL in string concatenation: CONCAT('Hello, ', NULL) → NULL. Use COALESCE: CONCAT('Hello, ', COALESCE(name, 'Guest')). Best practice: Avoid NULLs where they aren't semantically needed — use meaningful defaults. But use NULL when absence of data is genuinely meaningful.

Read transaction (read-only): Starts with START TRANSACTION READ ONLY or SET TRANSACTION READ ONLY. Only SELECT statements allowed — no DML. InnoDB can optimize: no need to track in the global transaction list (reduces overhead). Can use a snapshot without reservation. Benefits of marking as read-only: • Reduced InnoDB overhead — no transaction ID reservation from global list • Spring @Transactional(readOnly=true): Hints to Hibernate to disable dirty checking (no need to track entity changes for flush), can route to read replica, reduces JPA overhead • MySQL 5.7+: Performance improvement for large read-only transactions Write transaction: Can contain both SELECT and DML (INSERT/UPDATE/DELETE). InnoDB assigns a transaction ID. Acquires locks as needed. Changes written to undo log (for MVCC) and redo log. Spring JPA best practice: @Transactional(readOnly = true) // on service class default public class OrderService { public Order findById(Long id) { ... } // inherits readOnly=true @Transactional // overrides to readOnly=false for writes public Order createOrder(...) { ... } } This reduces Hibernate flush overhead on all read methods and enables read replica routing.

MySQL as a job queue: Sometimes simpler than adding Kafka/RabbitMQ for low-throughput use cases. Schema: CREATE TABLE job_queue ( id BIGINT AUTO_INCREMENT PRIMARY KEY, payload JSON NOT NULL, status ENUM('pending', 'processing', 'done', 'failed') DEFAULT 'pending', attempts INT DEFAULT 0, scheduled_at DATETIME DEFAULT CURRENT_TIMESTAMP, locked_until DATETIME, worker_id VARCHAR(100), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_poll (status, scheduled_at) ); Polling with SELECT FOR UPDATE SKIP LOCKED (MySQL 8.0+): Atomic — only one worker gets each job. SELECT id, payload FROM job_queue WHERE status = 'pending' AND scheduled_at <= NOW() ORDER BY scheduled_at LIMIT 1 FOR UPDATE SKIP LOCKED; SKIP LOCKED: Skips rows locked by other workers — no blocking, multiple workers can poll simultaneously without coordination. Retry: UPDATE status='pending', attempts=attempts+1, scheduled_at=NOW()+INTERVAL 5 MINUTE WHERE id=? AND attempts < 3; After max attempts: status='failed'. Dead letter: Move failed jobs to a separate table for investigation. Limitations: MySQL queue works for low-medium throughput (< 1000 jobs/sec). For higher throughput, use Redis Queues (BullMQ), RabbitMQ, or Kafka.

mysql.user: System table that stores all MySQL user accounts, their passwords (hashed), and global privileges. Authentication flow: 1. Client connects to MySQL with username, password, and host 2. MySQL looks up matching row in mysql.user (matched on user + host pattern) 3. Host matching: '192.168.1.%' matches any IP in that range, '%' matches any host, 'localhost' matches only local socket/loopback 4. Verifies password using the auth plugin (caching_sha2_password in MySQL 8.0, mysql_native_password in 5.7) 5. If authenticated, checks global privileges in mysql.user, then database-level in mysql.db, then table-level in mysql.tables_priv Creating users: CREATE USER 'appuser'@'%' IDENTIFIED BY 'strong_password'; GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'appuser'@'%'; FLUSH PRIVILEGES; -- not needed in MySQL 8.0 for GRANT/CREATE USER MySQL 8.0 auth change: Default plugin changed to caching_sha2_password. Old clients may need: ALTER USER ... IDENTIFIED WITH mysql_native_password. Security best practices: • Never use root for application connections • Create separate users per application with minimal privileges (principle of least privilege) • Restrict host ('appuser'@'app-server-ip' not 'appuser'@'%') • Use strong passwords • Disable remote root login

Index merge: An optimization where MySQL uses multiple indexes on the same table for a single query and merges the results. Useful when the optimal single index doesn't exist. Types: • Index merge union: For OR conditions. SELECT * FROM t WHERE a=1 OR b=2; — use index on a, use index on b, merge (union) the row sets. EXPLAIN shows Extra: Using union(idx_a, idx_b). • Index merge intersection: For AND conditions. SELECT * FROM t WHERE a=1 AND b=2; — use index on a, use index on b, intersect the row sets. EXPLAIN shows Extra: Using intersect(idx_a, idx_b). • Index merge sort-union: For OR with ranges. More complex merge. When you see index merge: It often indicates a missing composite index. Index merge has overhead — two index scans + merge operation. A well-designed composite index (a, b) is usually faster than merging two single-column indexes. Example: Instead of two indexes idx_a(a) and idx_b(b), add composite idx_ab(a, b). The optimizer will likely prefer the composite index over index merge. Force or disable: Use FORCE INDEX to force a specific index. Or ignore: IGNORE INDEX(idx_a, idx_b) to prevent index merge and fall back to a single index or table scan. Diagnosis: If EXPLAIN shows index merge, evaluate adding a composite index that covers both columns.

Replication lag: The delay between a change on the primary and its application on the replica. High lag means reads from replica are stale. Detecting: • SHOW REPLICA STATUS (MySQL 8.0) / SHOW SLAVE STATUS (5.7): Seconds_Behind_Source — seconds the replica is behind. 0 = caught up, high value = falling behind. • Monitoring: Alert if lag > 5 seconds. Graph over time to catch trends. • Heartbeat tables: pt-heartbeat (Percona) inserts timestamps to primary and measures replica lag more accurately than Seconds_Behind_Source (which has edge cases). Common causes: 1. Write-heavy primary: Replica can't apply changes as fast as primary generates them — replica has fewer resources or single-threaded applier. 2. Long-running query on replica: SHOW PROCESSLIST on replica. 3. Large transactions: A 10-minute batch delete on primary blocks the replica applier until the entire transaction is replicated. 4. Network bandwidth: High write volume saturates replication network. Solutions: • Parallel replication (MySQL 5.6+): slave_parallel_workers > 1. Apply multiple transactions in parallel. slave_parallel_type=LOGICAL_CLOCK for fine-grained parallelism. • Avoid large transactions on primary: Break batch operations into smaller chunks. • Hardware: More I/O on replica, faster disk. • Reduce replica read load: Fewer analytics queries competing with replication applier.

InnoDB row formats control how rows are stored on disk — affects compression, off-page storage, and performance. ROW_FORMAT=COMPACT (InnoDB 5.0-5.7 default): Variable-length column lengths stored in a header. NULL columns represented with a bit field — saves space for NULL-heavy tables. ROW_FORMAT=DYNAMIC (MySQL 5.7.9+ default): Default in 8.0. Similar to COMPACT but stores long variable-length columns (TEXT, BLOB, VARCHAR > ~8KB) fully off-page. Row itself stores only a 20-byte pointer. Enables storing large values without page splitting. All TEXT/BLOB are always stored off-page (unlike COMPACT which stores the first 768 bytes inline). ROW_FORMAT=COMPRESSED: Compresses B-Tree pages (default 8KB → compressed with zlib). 30-60% storage reduction. More CPU for compress/decompress. Useful for I/O-bound workloads with CPU headroom. KEY_BLOCK_SIZE controls compression page size. ROW_FORMAT=REDUNDANT (legacy): Older format, pre-5.0. More storage overhead. Avoid. Practical impact: • DYNAMIC (default): Best choice for most tables. Efficient for tables with both short and long columns. • COMPRESSED: Consider for large tables where storage cost is high and CPU is available. • COMPACT: Legacy — tables created before 5.7.9 default may have this. No need to convert unless hit specific issues.

Database-level rate limiting: Control how many operations a user or application can execute per time window — protects the DB from abuse or noisy neighbors. MySQL MAX_QUERIES_PER_HOUR and related: ALTER USER 'appuser'@'%' WITH MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 200 MAX_CONNECTIONS_PER_HOUR 100 MAX_USER_CONNECTIONS 50; Blunt instrument — limits apply to all queries from that user regardless of which queries. ProxySQL rate limiting: More sophisticated. ProxySQL can enforce query rules with max_connections_count per rule or per user. More granular — rate limit specific query patterns. Application-level (better approach): Rate limit in application code or API gateway using Redis token bucket/sliding window before the request reaches the DB. Advantages: lower latency rejection (no DB connection needed for rejected requests), more flexible policies, user-level granularity. Redis implementation: LUA script: INCR key + EXPIRE if first increment. If count > limit, reject. All atomic via Lua. MySQL resource groups (MySQL 8.0): Assign connections to resource groups with CPU affinity limits: CREATE RESOURCE GROUP analytics VCPU = 2, 3 THREAD_PRIORITY = 10; -- Assign heavy analytics user to limited CPU cores Best for limiting CPU consumption of specific query types without fully blocking them.

Both are snapshot-based (MVCC) in InnoDB — regular SELECTs don't block. READ COMMITTED (PostgreSQL default, many apps prefer): Each statement within a transaction takes a fresh snapshot. If another transaction commits while your transaction is open, subsequent statements in your transaction see the newly committed data. Behavior: T1 starts → T2 updates row → T2 commits → T1 SELECTs → T1 sees T2's update. Non-repeatable read: the same SELECT in T1 can return different results at different points in the transaction. REPEATABLE READ (InnoDB default): Snapshot is taken at the START of the transaction. All reads in the transaction see the database as it was when the transaction began, regardless of committed changes by other transactions. Behavior: T1 starts → takes snapshot → T2 updates and commits → T1 SELECTs → T1 still sees pre-T2 data. Consistent snapshot throughout the transaction. When REPEATABLE READ matters: Long analytical transactions that need consistent view of all data. Report generation: don't want data changing mid-report. When READ COMMITTED is preferred: Application code that expects to see latest data after each statement. Reduces lock conflicts (gap locks are mostly unnecessary in READ COMMITTED since phantoms are allowed). Better for high-concurrency OLTP where RR's stricter locking causes more deadlocks. Change to READ COMMITTED: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Storing money in MySQL requires exact decimal arithmetic — floating-point types (FLOAT, DOUBLE) must never be used for monetary values. DECIMAL(M, D) (recommended): M = total digits, D = digits after decimal. DECIMAL(15, 2) stores values up to 9,999,999,999,999.99. Exact arithmetic — no floating-point rounding errors. Storage: variable (up to 17 bytes for DECIMAL(65,30)). Example: price DECIMAL(10, 2) — up to 99,999,999.99, suitable for most retail prices. Why not FLOAT/DOUBLE: SELECT 0.1 + 0.2 in floating point = 0.30000000000000004. Multiply this across millions of transactions and you get financial discrepancies. Illegal for financial ledgers. Alternative — integer cents: Store amounts in the smallest currency unit (cents, paise). price BIGINT (or INT) stores 100 = $1.00. Application divides by 100 for display. Avoids any decimal arithmetic in DB. Very fast — integer operations. Used by many payment processors internally. For multi-currency: Store amount (DECIMAL or integer) + currency_code (CHAR(3) ISO 4217: USD, EUR, INR). Never combine amounts in different currencies without explicit conversion. BIGINT vs DECIMAL: BIGINT cents is faster for arithmetic and comparison. DECIMAL is more readable and handles arbitrary precision. Either is correct — DECIMAL(10,2) is more conventional in application code.

Schema evolution challenge: Each microservice owns its DB. Deploying new code with DB changes requires careful sequencing to avoid downtime or errors. Expand-contract (parallel change) pattern — the standard approach: Phase 1 (expand): Add new columns/tables as nullable or with defaults. Old and new code both work. Deploy DB change. Phase 2 (migrate): Deploy new application code that writes to both old and new columns. Backfill existing data in batches. Phase 3 (verify): Confirm all rows have new column populated. Run during soak period. Phase 4 (contract): Deploy code that only reads/writes new column. Remove old column from schema. Migration tools: Flyway (sequential versioned SQL scripts: V1__init.sql, V2__add_email.sql) or Liquibase (XML/YAML changesets with checksums). Run at application startup or CI/CD pipeline. Versioned APIs: API contract changes need versioning. Old service versions may still be running during rolling deploy — must be backward compatible until all instances updated. Consumer-driven contracts (Pact): Test that your service's API still satisfies consumer expectations before deploying. Catches breaking changes early. Forbidden operations during live deployments: Dropping a column still read by old code. Adding a NOT NULL column without default. Renaming a column without alias. All break old deployed code. Blue-green deployment: Eliminates mixed-version window — full swap. But DB changes must still be backward compatible for rollback.

TIMESTAMP: Stores a point in time as UTC. Range: 1970-01-01 00:00:01 UTC to 2038-01-19 (Year 2038 problem). 4 bytes. When you insert a value, MySQL converts it from connection timezone to UTC for storage. On retrieval, converts from UTC back to connection timezone. DATETIME: Stores a calendar date and time without timezone. Range: 1000-01-01 to 9999-12-31. 8 bytes. Stored and returned exactly as entered — no timezone conversion. Which to use: • Use DATETIME for: Dates far in the future (post-2038), dates where timezone context is irrelevant (birthdate, business hours), larger range. • Use TIMESTAMP for: Audit timestamps (created_at, updated_at) where UTC consistency matters. Automatic initialization: DEFAULT CURRENT_TIMESTAMP, automatic update: ON UPDATE CURRENT_TIMESTAMP. Best practice: Store timestamps in UTC always. Use DATETIME and convert explicitly in application code. This avoids timezone conversion surprises when DB server timezone changes. MySQL 5.6.4+: Both support fractional seconds: DATETIME(6), TIMESTAMP(6) — microsecond precision. Common bug: Mixing DATETIME and TIMESTAMP in the same application — comparisons and sorting behave correctly only when both are in UTC.