MySQL Performance Tuning in 2026: EXPLAIN, Indexing, and Query Optimization

Why Bother Tuning MySQL in 2026

Despite the rise of NewSQL and vector databases, MySQL still carries the vast majority of global OLTP traffic. Most "slow" problems aren't hardware limits but missing indexes, wrong execution plans, or chaotic connection management. This article focuses on actionable tuning.

Symptom	Common root cause
Intermittent lag	Missing index, full table scan
Slows over time	Tiny buffer pool, disk IO spikes
Peak-hour collapse	Uncontrolled connections, thread contention
Single SQL timeout	Deep pagination / bad JOIN

Step 1: See the Plan with EXPLAIN

Every tuning starts with EXPLAIN. Focus on type, key, rows, Extra.

EXPLAIN SELECT id, user_id, amount
FROM orders
WHERE user_id = 10086 AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

Reading the key columns:

Column	Healthy	Red flag
type	ref / range / const	ALL (full scan)
key	expected index	NULL
rows	near actual hits	far larger than expected
Extra	Using index	Using filesort / Using temporary

For complex SQL, first beautify it with the SQL Formatter tool to spot nested queries and JOIN order.

Three Core Indexing Rules

Rule 1: Composite Index Follows Leftmost Prefix

Index (user_id, status, created_at) serves user_id=, and user_id+status, but not status alone.

-- Uses index
SELECT * FROM orders WHERE user_id = 1 AND status = 'PAID';

-- Cannot use the status part of that composite index (except index skip scan)
SELECT * FROM orders WHERE status = 'PAID';

Rule 2: Prefer Covering Indexes

If all queried columns are in the index, MySQL avoids the table lookup — a huge speedup.

-- Create a covering index
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, created_at);

-- Using index: returned straight from the index, no lookup
EXPLAIN SELECT user_id, status, created_at
FROM orders WHERE user_id = 10086;

Rule 3: Put High-Cardinality Columns First

Put user_id (high cardinality) at the front of the composite index, and low-cardinality columns (e.g., status with few values) later.

Slow Query Log: Let Problems Surface Themselves

-- Enable slow query log (log > 200ms)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Pair it with pt-query-digest or the built-in sys.statement_analysis view to find Top SQL.

InnoDB Buffer Pool Tuning

The buffer pool is the performance heart of InnoDB — keep hot data in memory.

-- Check buffer pool hit rate (should be > 99%)
SHOW ENGINE INNODB STATUS\G
-- Buffer pool hit rate close to 1000/1000 is ideal

-- Production: set buffer pool to 60%~75% of available RAM
-- in my.cnf:
-- innodb_buffer_pool_size = 12G
-- innodb_buffer_pool_instances = 8   -- reduce multicore contention

Query-Level Optimization

Avoid Deep Pagination "LIMIT 100000, 20"

Deep pagination makes MySQL scan 100k rows then discard them. Rewrite with a cursor / deferred join:

-- Anti-pattern
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- Optimized: fetch PKs first, then join back
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) t ON o.id = t.id;

Use EXISTS Instead of IN with Large Lists

-- When the subquery result set is large
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000);

Select Only the Columns You Need

SELECT * wastes IO and breaks covering indexes. Naming columns is the highest-ROI optimization.

JOIN Optimization Notes

Pick the smaller table as the driving table; the joined column on the driven table must be indexed.
Join column types must match (implicit conversion disables indexes).
For large-table JOINs, consider denormalizing dimension columns to reduce cross-table access.

Connection Pooling: Don't Let Connections Be the Bottleneck

Chaotic app-side connection management is the main cause of peak-hour collapse. Node.js example:

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: "db.example.com",
  user: "app",
  database: "shop",
  connectionLimit: 20,      // cap concurrent connections
  waitForConnections: true,
  queueLimit: 50,           // max queued requests beyond limit
  enableKeepAlive: true
});

// Always release connections via try/finally to avoid leaks
const [rows] = await pool.query("SELECT * FROM orders WHERE user_id = ?", [uid]);

Use the Hash Calculator tool to generate cache keys and cache hot query results, offloading the database.

FAQ

Q1: I added an index but it's still slow?

Possibly: poor index selectivity, stale statistics (ANALYZE TABLE), or the query doesn't follow the leftmost prefix. Verify the actual key hit with EXPLAIN.

Q2: More indexes = better?

No. Indexes slow writes and consume space. Follow "index only hot read-heavy queries" principle.

Q3: Is a bigger buffer pool always better?

Not blindly. Exceeding physical RAM triggers swap, which is worse. Typically 60%~75% of available RAM.

Q4: What about VARCHAR indexes?

Use prefix indexes for long strings: INDEX(col(20)), but prefix indexes can't be used for exact-match covering scans — weigh the tradeoff.

Q5: When to shard?

Only when a single table exceeds hundreds of millions of rows, hot-row lock contention is severe, or backup windows are too long. Prioritize indexing and query optimization first.

Recommended Tools

For MySQL tuning, these ToolsKu tools help:

SQL Formatter — Beautify and compress complex queries for EXPLAIN analysis
JSON Formatter — Format EXPLAIN FORMAT=JSON execution plans
Hash Calculator — Generate query cache keys for the cache layer

There's no silver bullet for MySQL tuning, but there is a methodology: use EXPLAIN to find the bottleneck, fix IO with indexes and the buffer pool, then stabilize peaks with a connection pool. Get these three layers right and most slow queries disappear.