MySQL Performance Bottlenecks: Queries, Indexes, and Connections

MySQL performance problems are usually easier to solve when the team identifies which layer is actually constraining work instead of resizing the database first.

For small production teams, the highest-value bottleneck sequence is usually: slow or repeated queries, ineffective indexes, connection pressure, memory pressure, storage latency, and only then raw CPU capacity. A larger instance can hide poor access patterns temporarily, but it rarely fixes them.

This guide owns MySQL performance diagnosis across queries, indexes, connections, memory, and storage. For the hosting decision, use MySQL Hosting: Managed vs Self-Hosted for Production Apps. For backup and recovery, use MySQL Binary Logs, Backups, and Point-in-Time Recovery.

Start with the symptom, not the server size

A useful performance investigation begins by describing what changed:

• request latency increased;
• one endpoint became slow;
• CPU rose at the same traffic level;
• connections reached their ceiling;
• lock waits increased;
• disk latency spiked;
• temporary tables moved to disk;
• replication lag grew;
• a deployment caused a sudden regression;
• a report or background job now competes with customer traffic.

That symptom narrows the search space.

The goal is to identify the dominant constraint before changing capacity.

Slow-query analysis should be the first diagnostic layer

MySQL's slow query log can record statements whose execution time exceeds the configured threshold and, optionally, queries that do not use indexes.

It is most useful when combined with workload context rather than treated as a list of “bad queries.”

Prioritize statements by a combination of:

frequency
× execution time
× rows examined
× business importance

A query that takes two seconds once per day is different from a 150 ms query executed thousands of times per minute.

Look for:

• full table scans;
• repeated N+1 queries;
• large joins with weak predicates;
• unbounded ORDER BY or GROUP BY work;
• functions applied to indexed columns in ways that prevent efficient access;
• %prefix wildcard patterns that cannot use a normal leading index effectively;
• unnecessary SELECT * over wide tables;
• repeated reporting queries against transactional tables;
• queries that return few rows but examine very many.

MySQL's EXPLAIN and EXPLAIN ANALYZE help show how the optimizer intends to execute a query and, with ANALYZE, how the real execution behaved.

Do not optimize from the SQL text alone. Inspect the plan and the actual data distribution.

Indexes should match predicates and access order

An index is valuable only when it reduces the work needed for important reads without creating excessive write and storage overhead.

Common indexing mistakes include:

• missing indexes on frequent filters or joins;
• redundant single-column indexes when a composite index already covers them;
• composite indexes with columns in the wrong order for the workload;
• indexes on low-selectivity fields that do little pruning;
• adding every suggested index without measuring write cost;
• indexing fields that are rarely queried;
• keeping obsolete indexes after workload changes.

For a composite index such as:

(account_id, status, created_at)

queries beginning with account_id can often benefit strongly, while queries filtering only on created_at may not use the composite index effectively.

A practical index-design process is:

1. identify the high-value query;
2. list equality predicates;
3. list range predicates;
4. list join columns;
5. inspect sort/group requirements;
6. build the narrowest useful index;
7. compare execution plan and measured latency;
8. check write and storage impact.

Indexes should be justified by real access patterns, not by the number of columns available.

Connection pressure can break a database with low CPU

A MySQL instance can become unavailable even when CPU and memory graphs look moderate if every allowed connection is occupied.

Connection demand is cumulative across the whole application:

application replicas × pool size
+ workers
+ scheduled jobs
+ migrations
+ monitoring
+ admin sessions
= possible connection demand

Eight application replicas with a pool of 20 connections can create 160 possible connections before background workers or administrative sessions are counted.

Monitor:

• current connections;
• maximum observed connections;
• Threads_connected;
• Threads_running;
• aborted connections;
• connection creation rate;
• idle-session duration;
• pool wait time on the application side;
• deployments that temporarily double application replicas.

A large pool is not automatically faster. Excessive concurrency can increase memory use, lock contention, and context switching.

Keep an operational reserve so administrators and monitoring can still connect during an incident.

InnoDB buffer-pool behavior explains many storage symptoms

For InnoDB workloads, memory is especially important because the buffer pool caches table and index pages.

When the active working set fits the buffer pool well, many reads avoid storage. When it does not, storage I/O can rise sharply and query latency can become more variable.

Useful signals include:

• buffer-pool hit behavior;
• reads from disk versus logical reads;
• dirty-page pressure;
• flushing activity;
• page churn;
• overall memory pressure and swapping.

Do not chase a universal buffer-pool ratio. Workloads differ. Instead, correlate buffer-pool misses with storage latency and query slowdown.

A database with a large total dataset can still perform well if the frequently accessed working set is smaller and well-indexed. Conversely, a modest database can perform poorly if queries repeatedly scan cold data.

Temporary tables and sorting reveal query-shape pressure

Complex joins, sorts, and grouping operations can require temporary work.

When temporary work spills to disk, latency may rise substantially compared with in-memory execution.

Watch for:

• large GROUP BY operations;
• large sorts;
• reporting queries over long ranges;
• joins that multiply row counts unexpectedly;
• query plans using temporary tables and filesorts;
• application requests that request entire datasets and sort them at the database layer.

The fix may be a better index, narrower query, precomputed summary, pagination strategy, or moving analytical work away from the transactional database.

Increasing memory can help only when the query shape is already reasonable.

Lock waits and long transactions create hidden latency

Not every slow query is computationally expensive. A fast statement can wait behind another transaction.

Investigate:

• long-running transactions;
• row-lock waits;
• deadlocks;
• application code that opens a transaction before doing remote work;
• bulk updates touching many rows;
• schema changes that create metadata-lock contention;
• jobs that update rows in inconsistent order.

Keep transactions as short as business correctness allows.

A useful application rule is:

begin transaction
→ read/write only the data needed
→ commit

Avoid holding a database transaction open while waiting for user input, external APIs, large file operations, or unrelated application work.

CPU pressure is often a query symptom

High CPU should trigger a query investigation before a capacity increase.

Common causes include:

• full scans;
• inefficient joins;
• missing indexes;
• repeated identical queries;
• expensive expressions;
• large sorts or aggregations;
• retry storms;
• too much concurrency;
• reporting mixed with transactional traffic.

Compare CPU growth with:

• query rate;
• rows examined;
• statement latency;
• active connections;
• deployment timing;
• traffic level.

If CPU doubles while traffic is flat after a release, the most likely explanation is a workload regression rather than a sudden need for twice the hardware.

Storage latency matters when memory cannot hide I/O

Database storage performance affects reads, writes, log flushing, temporary work, backups, and recovery.

Watch:

• read/write latency;
• IOPS and throughput saturation;
• disk queueing;
• free space;
• binary-log growth;
• temporary-file growth;
• backup overlap with peak traffic.

A database running near full disk capacity is also an operational risk. Large schema changes, index creation, temporary tables, binary logs, and restore procedures all require headroom.

Keep a margin for maintenance and growth rather than sizing storage to current table size alone.

Performance tuning should be evidence-driven

A safe tuning workflow is:

measure baseline
→ identify dominant bottleneck
→ change one major variable
→ compare the same workload
→ keep or revert

Avoid changing many server parameters at once. If latency improves or degrades, you should know why.

For production tuning, keep a short change record:

• timestamp;
• workload state;
• parameter or index changed;
• before/after query plan;
• before/after latency;
• CPU/memory/storage impact;
• rollback step.

This makes future incidents easier to understand.

Managed MySQL reduces platform work, not query ownership

Raff Managed MySQL currently includes monitoring and slow-query visibility together with managed backups, PITR, TLS, private networking, storage expansion, maintenance workflows, and optional HA.

That helps reduce the operating work around the database host and service.

The application team still owns:

• query design;
• indexes;
• schema shape;
• connection pools;
• transaction boundaries;
• workload separation;
• validating a scaling decision.

For self-hosted MySQL on Raff VMs, the team also owns MySQL configuration, host metrics, storage behavior, patching, backups, and incident response.

The performance rule we use at Raff is simple: resize after you can name the resource constraint; optimize first when the evidence points to unnecessary work.

MySQL performance checklist

• The user-facing symptom is defined.
• Slow statements are ranked by frequency and impact.
• EXPLAIN / EXPLAIN ANALYZE is reviewed for important queries.
• Indexes match actual filter and join patterns.
• Redundant or unused indexes are reviewed.
• Application pool capacity is summed across all replicas.
• Administrative connection reserve exists.
• Long transactions and lock waits are monitored.
• Buffer-pool misses are correlated with storage latency.
• Temporary work and large sorts are monitored.
• Storage has capacity and maintenance headroom.
• Parameter changes are tested one major variable at a time.
• Performance baselines are captured before scaling.

Conclusion

MySQL performance tuning works best when the team diagnoses the bottleneck in layers: query work, indexing, connections, locking, memory, storage, and finally compute capacity.

The most expensive mistake is often scaling a database to compensate for work that should not be happening. Measure the workload, inspect the execution path, and change only what the evidence supports.

Continue with MySQL Binary Logs, Backups, and Point-in-Time Recovery for recovery design, or Database Monitoring for Small Teams for the cross-engine monitoring framework.

Sources

• MySQL 8.4: Optimization
• MySQL 8.4: The Slow Query Log
• MySQL 8.4: EXPLAIN
• MySQL 8.4: InnoDB Buffer Pool
• MySQL 8.4: Server Status Variables
• Raff Managed MySQL
• Sources reviewed: 2026-08-24