MySQL Performance Root Causes
Missing indexes, wrong index order, or queries that bypass indexes.
EXPLAIN Analysis
EXPLAIN SELECT u.name, COUNT(o.id) cnt
FROM users u LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id ORDER BY cnt DESC;
-- type: ALL = bad. ref, eq_ref, const = good
-- key: NULL = no index used
-- Extra: "Using filesort", "Using temporary" = warning
Index Strategies
-- Composite: selective first, range/sort last
CREATE INDEX idx_orders ON orders(user_id, created_at DESC);
-- Covering: all columns in index (avoids table lookup)
CREATE INDEX idx_products ON products(category_id, price, name, id);
-- Partial: only for a subset of rows
CREATE INDEX idx_active ON users(email) WHERE status = 'active';
Anti-Patterns to Avoid
-- 1. Functions on indexed columns → can't use index
-- BAD: WHERE YEAR(created_at) = 2025
-- GOOD: WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'
-- 2. Large OFFSET → use keyset pagination
-- BAD: LIMIT 20 OFFSET 10000
-- GOOD: WHERE id < :lastId ORDER BY id DESC LIMIT 20
InnoDB Config
[mysqld]
innodb_buffer_pool_size = 12G # 70-80% of RAM
innodb_buffer_pool_instances = 8
innodb_log_file_size = 2G
-> Format query results with the JSON Viewer.