How to find and fix MySQL performance bottlenecks in production

The triage runbook for MySQL performance bottlenecks#

This guide is written for the backend software engineer or tech lead who owns a slow route in production. Your day starts with a high p95 on one endpoint, not with a config file. Most guides on MySQL performance bottlenecks open at the bottom of the stack, on innodb_buffer_pool_size and a list of server variables. Therefore they answer a question you have not reached yet.

Instead, treat this as a triage runbook. First you diagnose, then you fix, and the order matters because each step rules out a whole class of cause. The flow below is the whole method on one page. Read it top to bottom, and notice that my.cnf sits at the very end.

Each branch below is one step. Moreover, the steps run cheapest-diagnosis first. You only move down when the current step confirms the database is genuinely at fault. Because the method is subtractive, you avoid the common trap of tuning a config knob that was never the problem.

Step 1: Prove it is the database (not the app, N+1, or the network)#

Before you optimize a single query, prove that MySQL is where the time goes. A slow route has many suspects. The router and middleware, the ORM hydrating objects, the network, and the SQL itself all add latency. Therefore attributing the p95 across those layers is the first move, and it is the layer most guides skip entirely.

The waterfall below splits one route's p95 into proportional spans. Toggle between a healthy trace and the slow trace. Watch the SQL-execution span balloon until it dominates the request. That domination is your evidence that the database, not the app, owns the cost.

When the SQL span dominates like that, you have earned the right to blame MySQL. However, if the router or hydration span were the tall one, your fix would live in the application, not the database. First prove it, then fix it.

Is the cost the ORM hydration or the SQL itself?#

A slow Eloquent call has two costs bundled together. There is the query MySQL runs, and there is the work of turning rows into model objects. These are different problems with different fixes. Therefore measure them separately before you assume the database is slow.

// Separate SQL execution time from Eloquent hydration time.
// DB::listen reports the pure query duration for every statement.
DB::listen(function ($query) {
    Log::info('sql', [
        'ms'  => $query->time,   // execution time only, no hydration
        'sql' => $query->sql,
    ]);
});

// Wrap the call to see the object-building cost on top of the query.
$start   = microtime(true);
$orders  = Order::where('customer_id', 42)->get();  // query + hydrate
$totalMs = (microtime(true) - $start) * 1000;

// If $totalMs is far larger than the summed $query->time values,
// the cost is hydration, not the database.

Because DB::listen reports pure execution time, the gap between it and the wall clock is your hydration cost. When that gap is large, the query is fine and the fix is to select fewer columns or hydrate fewer models. In contrast, when the query time itself is large, you move on to Step 2.

Rule out the network and the connection pool first#

Step 2: Rank offenders by TOTAL time, not the single slowest#

Here is the counterintuitive number that reorders every triage. The slowest single query is rarely your biggest MySQL performance bottleneck. A query that runs in 1.8 seconds but fires three times a day costs you about five seconds daily. A query that runs in 40 milliseconds but fires 50,000 times a day costs you over half an hour. Therefore rank by total time, not by the worst single run.

Because the fast-often query hides in plain sight, you need MySQL's own accounting to surface it. Two tools give you that ranking directly. First, the performance schema and the sys schema. Second, pt-query-digest over the slow query log.

Ask MySQL's own books: performance_schema and the sys schema#

MySQL already tracks every statement digest and its summed latency. Therefore you do not need to guess. Query events_statements_summary_by_digest ordered by total wait, and the top rows are your real offenders.

-- Rank statements by TOTAL latency, not the single slowest run.
SELECT
  DIGEST_TEXT                       AS query,
  COUNT_STAR                        AS calls,
  ROUND(SUM_TIMER_WAIT / 1e12, 1)   AS total_seconds,
  ROUND(AVG_TIMER_WAIT / 1e9,  1)   AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC        -- total time is the number that matters
LIMIT 10;

-- The sys schema wraps the same data in a readable view.
SELECT * FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

The SUM_TIMER_WAIT column is the one that matters, because it is total time across every call. Meanwhile the average column tells you the per-run cost. Read them together and the frequent-fast query rises to the top where it belongs.

pt-query-digest on the slow query log#

When you want a shareable report, Percona's pt-query-digest aggregates the slow query log and ranks by total time consumed. First set long_query_time to zero for a capture window. Then digest the log.

# Turn the slow log on and capture everything for a window.
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0;   # log all statements, then digest

# Aggregate the log and rank queries by total time consumed.
pt-query-digest /var/log/mysql/slow.log > digest.txt

# Read the top of the "Profile" section. Rank 1 is the query eating
# the most TOTAL time across the window, which is rarely the
# slowest single run.

The report's profile section ranks queries by their share of total response time. Consequently rank one is almost never the slowest single statement. It is the query whose frequency times its per-run cost adds up to the most wall-clock time, which is exactly the offender Step 2 exists to find. Percona documents the report format in the pt-query-digest reference.

The real ranking of your top offenders#

Put five representative queries side by side by their total daily time and the priority order changes shape. The chart below ranks by aggregate time, not by per-run duration. Notice where the 14-second nightly job lands.

Step 3: Read the EXPLAIN ANALYZE plan properly#

Once you have the offending query, run EXPLAIN ANALYZE and read the plan node by node. This is the step most software engineers rush. However, the plan tells you exactly why the query is slow, and it tells you whether your fix worked. Therefore learn to read it properly.

The inspector below shows a real-shaped plan for one query. Toggle between the pre-index and post-index plan. Then step through each node to see its estimated rows, its actual rows, and its actual time. Watch the full table scan become an index range scan and the filesort disappear.

Estimated rows versus actual rows: the tell#

The single most useful signal in a plan is the gap between estimated and actual rows. When the optimizer estimates one row and the node returns thousands, its whole plan is built on a bad guess. Therefore a wide estimate-to-actual gap points you straight at the missing statistics or the missing index. In practice, a healthy plan shows the two numbers close together, as the after-index node does above.

Step 4: Fix the query pattern, the index, the schema#

Now you fix the thing you proved is at fault. Most MySQL performance bottlenecks resolve at one of three layers. First the ORM query pattern. Second a missing or wrong index. Third the schema shape itself. Work them in that order, because a pattern fix is cheaper than an index and an index is cheaper than a migration.

The N+1 query: one route, thousands of round trips#

The most common ORM pattern bug is the N+1 query. Your code loads a list, then touches a relation inside a loop, and the ORM fires one extra query per row. At a few rows nobody notices. At a few thousand rows the route falls over. Compare the trap and the fix below.

// One query for the orders...
$orders = Order::where('status', 'shipped')->get();

foreach ($orders as $order) {
    // ...then ONE more query per order for its customer.
    echo $order->customer->name;   // lazy load: 1 + N queries
}

// 1 orders query plus N customer queries.
// At 3,842 orders that is 3,843 round trips to MySQL.

// Tell Eloquent to load the customers up front.
$orders = Order::with('customer')
    ->where('status', 'shipped')
    ->get();

foreach ($orders as $order) {
    echo $order->customer->name;   // already loaded, no extra query
}

// 2 queries total: one for orders, one for all customers
// via WHERE id IN (...). The N round trips are gone.

Because eager loading batches the relation into a single WHERE id IN (...) query, the N round trips collapse to one. This is the same shape of bug that appears when structuring a WordPress backend at scale, where meta-query joins fan out over one fat table. In both cases the fix is to stop asking the database the same question thousands of times.

Composite and covering indexes: column order is everything#

When the pattern is fine but the query still scans the table, you need the right index. A composite index serves equality, then range, then order, in the column sequence you define. Therefore column order is not cosmetic. The diagram below walks how one composite index answers a real query.

Notice the covering-index branch. When the index holds every column the query selects, MySQL answers from the index alone and never touches the table rows. Consequently a well-shaped covering index turns a slow scan into a fast seek. The order of columns must match how the query filters and sorts, or the index sits unused.

Before and after the index#

Here is the payoff: the same query, measured before and after the composite index lands. The plan you read in Step 3 predicted exactly this result.

Step 5: Only now tune my.cnf and the hardware#

If, and only if, the query patterns and indexes are sound and the p95 is still out of budget, you reach for the server config. By this point you have ruled out the causes that config cannot fix. Therefore the knobs below can finally help, because the workload underneath them is already lean. The table lists the handful that matter and the risk of cargo-culting each one.

Because these knobs shape how the server runs a workload, they cannot rescue a workload that is doing too much work. In short, tuning my.cnf before Step 4 is optimizing the wrong layer. The reference values live in the MySQL server system variables documentation, and every server deserves values fitted to its own RAM and workload.

When this triage runbook is the wrong tool#

Where to go next#

Diagnosing MySQL performance bottlenecks is one skill in keeping a backend fast under load. If you are shaping a busy backend, the same top-down instinct applies to structuring a WordPress backend at scale and to choosing a rendering strategy on Cloudflare Workers. For the broader discipline, see how we approach performance and Core Web Vitals. When you want a second set of hands on a slow production database, you can hire Laravel developers from our team.