MySQL 8.x Performance Optimization: Indexes, Query Cache, and InnoDB Tuning

MySQL 8.x Performance Optimization

MySQL powers millions of applications. Here's how to squeeze maximum performance out of it.

Index Strategy

-- Covering index: all columns needed are in the index
CREATE INDEX idx_orders_cover ON orders (user_id, status, created_at, total);
-- SELECT user_id, status, total FROM orders WHERE user_id=1 AND status='paid'
-- doesn't touch the main table at all!

-- Invisible indexes: test impact before dropping
ALTER TABLE users ALTER INDEX idx_email INVISIBLE;
-- Run queries, check if they got slower
ALTER TABLE users ALTER INDEX idx_email VISIBLE;

-- Descending indexes (MySQL 8+)
CREATE INDEX idx_posts_recent ON posts (created_at DESC, id DESC);
-- Efficient for: ORDER BY created_at DESC, id DESC LIMIT 20

-- Functional indexes (MySQL 8.0.13+)
CREATE INDEX idx_lower_email ON users ((LOWER(email)));
-- Supports: WHERE LOWER(email) = 'user@example.com'

EXPLAIN Analysis

-- Use EXPLAIN FORMAT=TREE for better readability (MySQL 8+)
EXPLAIN FORMAT=TREE
SELECT u.name, COUNT(o.id) as orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.active = 1
GROUP BY u.id;

-- Check for:
-- "Using filesort" → add covering index with ORDER BY column last
-- "Using temporary" → GROUP BY needs sorting
-- "Using index" → good! covering index hit
-- type = "ALL" → full table scan
-- rows estimate vs actual

-- Optimizer hints when planner makes wrong choices
SELECT /*+ INDEX(u idx_users_active) */ *
FROM users u
WHERE u.active = 1 AND u.created_at > '2026-01-01';

InnoDB Configuration

# my.cnf
[mysqld]
# Buffer pool: 70-80% of available RAM
innodb_buffer_pool_size = 12G

# Multiple buffer pool instances (for high concurrency)
innodb_buffer_pool_instances = 8  # One per GB

# I/O tuning
innodb_io_capacity = 2000        # IOPS your disk can do
innodb_io_capacity_max = 4000
innodb_flush_log_at_trx_commit = 2  # 1=safest, 2=faster (1 sec data loss risk)
innodb_flush_method = O_DIRECT

# Redo log size (larger = fewer checkpoints)
innodb_redo_log_capacity = 4G   # MySQL 8.0.30+

# Connection settings
max_connections = 500
thread_cache_size = 50

Common Slow Query Patterns

-- Bad: Function on indexed column defeats index
-- Bad:
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- Good:
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- Bad: LIKE with leading wildcard
-- Bad:
SELECT * FROM products WHERE name LIKE '%wireless%';
-- Good: Use FULLTEXT index
ALTER TABLE products ADD FULLTEXT(name, description);
SELECT * FROM products WHERE MATCH(name, description) AGAINST('wireless' IN BOOLEAN MODE);

-- Bad: SELECT *
-- Bad:
SELECT * FROM users JOIN orders ON ...;
-- Good: Only select needed columns
SELECT u.id, u.name, o.total FROM users u JOIN orders o ON ...;

-- Bad: Correlated subquery
-- Bad:
SELECT * FROM users WHERE (SELECT COUNT(*) FROM orders WHERE user_id = users.id) > 5;
-- Good: JOIN with aggregation
SELECT u.* FROM users u
JOIN (SELECT user_id, COUNT(*) cnt FROM orders GROUP BY user_id HAVING cnt > 5) o
ON o.user_id = u.id;

Table Partitioning

-- Range partitioning by date (efficient for time-series data)
CREATE TABLE events (
  id BIGINT NOT NULL AUTO_INCREMENT,
  event_type VARCHAR(50),
  created_at DATETIME NOT NULL,
  payload JSON,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p2026 VALUES LESS THAN (2027),
  PARTITION pFuture VALUES LESS THAN MAXVALUE
);

-- Drop old partition instantly (vs. DELETE which is slow)
ALTER TABLE events DROP PARTITION p2024;

Replication Lag Reduction

# On replica
slave_parallel_workers = 8          # Parallel apply
slave_parallel_type = LOGICAL_CLOCK # Group commit aware
binlog_transaction_dependency_tracking = WRITESET

Query Cache Replacement (MySQL 8 removed it)

-- Use ProxySQL query cache, or application-level cache
-- Or use MySQL's built-in result caching via generated columns
ALTER TABLE products ADD COLUMN search_tokens TEXT GENERATED ALWAYS AS (
  CONCAT(name, ' ', IFNULL(description, ''), ' ', brand)
) STORED;
CREATE FULLTEXT INDEX idx_search ON products (search_tokens);