Quiz

# ============================================================
# Q1) Why is your composite index on (status, created_at) not used?
# ============================================================
# ANSWER: the planner only uses the index PREFIX. If your query filters by
# created_at WITHOUT status, the index is unusable. Either change the query
# to include status OR build a separate index on created_at.

# ============================================================
# Q2) utf8 vs utf8mb4 — which to pick?
# ============================================================
# ANSWER: utf8mb4 ALWAYS in 2026. Legacy 'utf8' is 3-byte and cannot store
# emoji or many CJK characters. Use COLLATE utf8mb4_0900_ai_ci on MySQL 8.

# ============================================================
# Q3) AUTO_INCREMENT gaps after a rollback — bug or feature?
# ============================================================
# ANSWER: feature. AUTO_INCREMENT advances on INSERT attempt; rollback does
# not reclaim the value. Gaps are normal and not a problem; do NOT try to
# compact them.

# ============================================================
# Q4) When to use REPLACE INTO vs ON DUPLICATE KEY UPDATE?
# ============================================================
# ANSWER: prefer ON DUPLICATE KEY UPDATE.
# REPLACE INTO does DELETE + INSERT, which:
#   - resets AUTO_INCREMENT
#   - fires DELETE + INSERT triggers (often surprising)
#   - cascades FK deletes
# ON DUPLICATE KEY UPDATE is an UPDATE on conflict; cleaner, faster.

# ============================================================
# Q5) Your COUNT(*) on a giant table is slow. Why?
# ============================================================
# ANSWER: InnoDB has no quick row count; it must scan the index. Workarounds:
# - Use a counter table updated by triggers (eventually consistent)
# - Estimate with information_schema.TABLES.TABLE_ROWS (approximate)
# - Use SQL_CALC_FOUND_ROWS only when you NEED the exact count (slow)

# ============================================================
# Q6) Why is your transaction deadlocking on a hot row?
# ============================================================
# ANSWER: two sessions trying to lock the same row in different orders.
# Fix by:
# - Ordering locks consistently (always lock customer then order, not the
#   reverse)
# - Shortening transactions (do the work, commit, do not let users 'think'
#   inside a transaction)
# - SKIP LOCKED for queue-style workloads
# Inspect with: SHOW ENGINE INNODB STATUS\G

# ============================================================
# Bonus — when is a CTE wrong?
# ============================================================
# ANSWER: prior to MySQL 8, the CTE was materialised every reference, which
# made multi-reference CTEs expensive. MySQL 8 fixes this. Still, recursive
# CTEs need a sensible depth limit (cte_max_recursion_depth) or the optimiser
# stops.

# ============================================================
# Scoring
# ============================================================
# 6 / 6 -> code review at speed
# 4 / 6 -> bookmark mysql/cheatsheet
# < 4   -> read 'High Performance MySQL' (Schwartz)

-- 3 questions per lesson.