MySQL Performance Tuning: Where to Start

When an application starts to feel sluggish, the database is often the first suspect. The temptation is to search for a list of "magic" configuration settings and paste them into my.cnf. That rarely helps much. Good MySQL performance tuning follows an order: measure what is slow, fix the queries and indexes causing it, and only then adjust server settings and hardware. This article walks through that order so you know where to spend your first hour.

Step 1: Measure before you change anything

Tuning without measurement is guesswork. Before touching settings, collect evidence about which queries take the time.

Turn on the slow query log

The slow query log records every statement that runs longer than a threshold you choose. You can enable it without restarting the server:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;          -- seconds
SET GLOBAL log_queries_not_using_indexes = 'ON';

Leave it running through a normal business day, then summarise it with mysqldumpslow (bundled with MySQL) or pt-query-digest from Percona Toolkit. Both group similar queries together so you can see which statement shapes consume the most total time, not just which single run was slowest.

Use the Performance Schema

MySQL 8 ships with the Performance Schema and the sys schema, which expose statistics collected inside the server. A useful starting query lists statements by total latency:

SELECT query, exec_count, total_latency, rows_examined_avg
FROM sys.statements_with_runtimes_in_95th_percentile
ORDER BY total_latency DESC
LIMIT 10;

A query that takes 50 milliseconds but runs 200,000 times a day usually matters more than a report that takes 30 seconds once a night. Optimise for total impact.

Step 2: Fix the worst queries

Most real-world gains come from a handful of queries. For each one on your list, run EXPLAIN to see how MySQL plans to execute it:

EXPLAIN SELECT id, total
FROM orders
WHERE customer_id = 4821 AND status = 'paid'
ORDER BY created_at DESC;

Look at three columns in the output:

• type: ALL means a full table scan. ref, range or const are generally good.
• rows: an estimate of how many rows MySQL will read. If it reads 400,000 rows to return 12, something is missing.
• Extra: Using filesort or Using temporary on a large result set is a warning sign.

In MySQL 8.0.18 and later, EXPLAIN ANALYZE actually runs the query and reports real timings for each step, which is more trustworthy than estimates.

Step 3: Add the right indexes

An index is a sorted lookup structure that lets MySQL jump to matching rows instead of reading the whole table. For the query above, a composite index covering the filter columns and the sort column removes both the scan and the sort:

CREATE INDEX idx_orders_customer_status_created
    ON orders (customer_id, status, created_at);

Column order matters: put equality filters first and the range or sort column last. Do not index every column "just in case". Each index slows down inserts and updates and takes memory. Check for unused indexes periodically:

SELECT * FROM sys.schema_unused_indexes;

Step 4: Tune memory and InnoDB settings

Once queries are sensible, server configuration starts to matter. Almost every modern MySQL install uses the InnoDB storage engine, and a few of its settings dominate.

The buffer pool is the big one. If your working data fits in memory, reads rarely touch disk. You can check how often MySQL has to go to disk:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Compare Innodb_buffer_pool_reads (disk reads) to Innodb_buffer_pool_read_requests (all reads). A steadily growing disk-read count during normal traffic suggests the pool is too small for your working set.

Change one setting at a time and measure again. The official MySQL optimization documentation explains each variable in detail.

Step 5: Look at the application and the hardware

Some problems are not inside MySQL at all:

• The N+1 pattern. An application loads 100 orders, then runs one extra query per order to fetch the customer. That is 101 round trips where one join would do.
• No caching. Data that changes rarely, such as product categories or settings, can be cached in the application or in Redis instead of being queried on every page view.
• Connection churn. Opening a new connection for every request is expensive. Connection pools reuse them.
• Slow storage. MySQL on network storage with low IOPS (input/output operations per second) will struggle with write-heavy loads no matter how it is tuned. SSD or NVMe storage makes a real difference.

Hardware upgrades are a legitimate fix, but they belong at the end of the list. Doubling the server size can hide a missing index for a few months; adding the index fixes it permanently. If you would like a second pair of eyes on a struggling server, our database management team handles exactly this kind of review, and our server management service covers the underlying machine.

A MySQL performance tuning checklist

1. Enable the slow query log and collect a day of data.
2. Rank queries by total time, not by single worst run.
3. Run EXPLAIN on the top five and add or adjust indexes.
4. Remove duplicate and unused indexes.
5. Size the InnoDB buffer pool for your working data.
6. Check the application for N+1 queries and missing caching.
7. Re-measure, and repeat.

Key takeaways

• MySQL performance tuning starts with measurement, not configuration tweaks.
• A small number of queries usually cause most of the load; fix them first with EXPLAIN and good indexes.
• innodb_buffer_pool_size is the most important memory setting on a dedicated server.
• Change one thing at a time and keep the measurement running so you know what actually helped.