100 Performance Tips
MySQL 100 Performance Tips
100 production-tested MySQL tuning tips — each with a one-liner rule plus executable SQL or command. Covering indexes, queries, schema, configuration, and operations.
I. Index Tips (1-25)
1
Always EXPLAIN before and after any SQL/index change
EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
2
Use BIGINT AUTO_INCREMENT primary keys, not UUIDs
Random UUIDs cause frequent B+Tree page splits (3-5x slower writes) and double secondary index size.
CREATE TABLE t (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, ...);
3
Put highest-selectivity equality columns first in composite indexes
-- user_id has millions of values (high selectivity), put it first CREATE INDEX idx ON orders (user_id, status, created_at);
4
Use covering indexes to eliminate table lookups
Include all SELECT columns in the index — EXPLAIN should show "Using index".
CREATE INDEX idx_cover ON orders (user_id, status, amount);
5
Never apply functions to indexed columns in WHERE
-- Bad: WHERE YEAR(created_at) = 2024 -- Good: WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
6
Clean up unused indexes regularly with sys.schema_unused_indexes
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'mydb'; -- First set INVISIBLE: ALTER TABLE t ALTER INDEX idx INVISIBLE; -- Then drop after confirming: ALTER TABLE t DROP INDEX idx;
7
Use EXPLAIN ANALYZE for actual execution data (8.0.18+)
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1 ORDER BY created_at;
II. Query Tips (26-50)
8
Avoid SELECT *, only fetch needed columns
SELECT * breaks covering indexes, wastes Buffer Pool, and doubles JOIN memory usage.
9
Replace deep LIMIT OFFSET with cursor pagination
SELECT * FROM orders WHERE (created_at, id) > (:last_time, :last_id) ORDER BY created_at, id LIMIT 10;
10
Batch INSERTs are 10-100x faster than individual INSERTs
INSERT INTO logs (user_id, action) VALUES (1,'login'),(2,'logout'), ...;
11
Delete large datasets in batches to avoid long locks
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 5000; -- Loop until affected rows = 0, sleep 100ms between iterations
12
Rewrite correlated subqueries as JOINs
-- Slow (O(n²)) SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100); -- Fast (JOIN) SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount > 100;
III. Schema Tips (51-70)
13
Use DECIMAL for money, never FLOAT/DOUBLE
amount DECIMAL(12, 2) -- Or store as integer (cents): amount INT COMMENT 'amount in cents'
14
Use DATETIME for cross-timezone systems, TIMESTAMP for local-only
TIMESTAMP auto-converts timezones and has 2038 limit. DATETIME stores raw value, no timezone conversion, good until year 9999.
15
Prefer TINYINT over ENUM for status/type columns
Adding an ENUM value requires ALTER TABLE. TINYINT + application mapping is more flexible and cheaper to change.
IV. Configuration Tips (71-85)
16
Set innodb_buffer_pool_size to 70-80% of RAM (dedicated DB server)
-- Check hit ratio (should be > 0.99) SELECT ROUND(1 - ( SELECT variable_value FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_reads') / (SELECT variable_value FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_read_requests' ), 4) AS hit_ratio;
17
innodb_flush_log_at_trx_commit=1 in production (never lose committed data)
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'; -- must be 1
18
Enable slow query log in production
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = ON;
V. Operations Tips (86-100)
19
Analyze slow query logs with pt-query-digest daily
pt-query-digest /var/log/mysql/slow.log --limit=20 --order-by Query_time:sum
20
SHOW FULL PROCESSLIST as first response to high CPU
SELECT * FROM information_schema.PROCESSLIST WHERE Time > 5 ORDER BY Time DESC;
21
Check replication lag regularly
SHOW SLAVE STATUS\G -- Check Seconds_Behind_Master; NULL means replication broken
22
Use SHOW ENGINE INNODB STATUS to debug deadlocks
SHOW ENGINE INNODB STATUS\G -- Find LATEST DETECTED DEADLOCK section