MySQL Performance Guide
Slow Query Log
-- Enable slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- log queries > 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = ON;
-- Analyze with pt-query-digest
pt-query-digest /var/log/mysql/slow.log
-- Find top slow queries
SELECT query_time, lock_time, rows_sent, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 10;
EXPLAIN Output
EXPLAIN SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending' AND o.created_at > '2024-01-01';
-- Key columns to check:
-- type: ALL (full scan) | ref | range | const (best)
-- key: index being used (NULL = no index)
-- rows: estimated rows examined (lower = better)
-- Extra: Using filesort / Using temporary (red flags)
-- Get actual execution stats (MySQL 8.0+)
EXPLAIN ANALYZE SELECT ...;
Index Best Practices
-- Composite index โ column order matters!
-- Good for: WHERE status = 'active' AND user_id = 42
-- Good for: WHERE status = 'active'
-- Not for: WHERE user_id = 42 alone (must start from left)
CREATE INDEX idx_status_user ON orders(status, user_id);
-- Covering index (includes all selected columns)
CREATE INDEX idx_order_cover ON orders(user_id, status, created_at, total);
-- Partial index (MySQL 8.0 functional index)
CREATE INDEX idx_year ON orders((YEAR(created_at)));
-- Check index usage
SELECT index_name, stat_value
FROM mysql.innodb_index_stats
WHERE database_name = 'mydb' AND table_name = 'orders';
-- Find unused indexes
SELECT * FROM sys.schema_unused_indexes;
Key Configuration Parameters
| Parameter | Recommendation | Impact |
|---|---|---|
| innodb_buffer_pool_size | 70-80% of RAM (e.g., 8G on 16G server) | Most critical โ caches data and indexes |
| innodb_log_file_size | 1-4 GB | Write performance for transactions |
| innodb_flush_log_at_trx_commit | 2 (for performance) or 1 (for ACID) | Write durability vs performance |
| max_connections | 150-500 (use connection pooler) | Prevent resource exhaustion |
| query_cache_type | 0 (disabled โ deprecated in 8.0) | Avoid cache mutex contention |