A page loads quickly during testing, then takes five seconds once real customers arrive. The application code looks fine. The server still has spare memory. Somewhere underneath, MySQL is reading far more data than the page actually needs.
That’s a familiar pattern, but it isn’t the only explanation. A query can also stall behind a locked row or run hundreds of times during one request.
Learning how to fix slow MySQL queries starts with finding out where the time goes. Once you can see that, the next change becomes much easier to choose.
Table of Contents
- Identify the Queries That Slow Things Down the Most
- Read the Execution Plan Before Making Changes
- Build Indexes Around Real Queries
- Make Your Filters Easier to Search
- Fetch Less Data and Make Fewer Requests
- Investigate Locks When Queries Suddenly Stall
- Match Server Resources to the Workload
- Check That the Improvement Lasts
Identify the Queries That Slow Things Down the Most
Start with measurements from the database and the application. A slow page doesn’t automatically mean its SQL is slow; connection waits, network delays, and application processing also consume time.
Enable MySQL’s slow query log through your hosting controls or database configuration. Its long_query_time setting controls the duration threshold, while min_examined_row_limit can filter entries by rows examined. A one-second threshold can be a useful starting point, depending on your workload.
Capture a busy period, then look for recurring query patterns. Compare their execution times, frequency, and rows examined.
Here’s the thing: the single slowest query may deserve less attention than a moderately slow query running constantly. A report that takes three seconds once a day has a different impact from a lookup taking 200 milliseconds several thousand times.
When available, sys.statement_analysis provides aggregated statement statistics, including execution counts, total latency, and rows examined. Use those figures to choose your first target. dev.mysql.com
Read the Execution Plan Before Making Changes
EXPLAIN shows how MySQL plans to retrieve the data. For a customer’s recent orders, you might inspect:
EXPLAIN
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;
Look at the selected index, estimated rows, and access method. A full scan of a large table deserves investigation, especially when the query returns only a few records. Scanning a tiny table can be perfectly reasonable.
For actual execution measurements, MySQL 8.0.18 and later offer EXPLAIN ANALYZE. It executes the statement and reports timing, rows, and loops. Run expensive examples on representative test data first.
Compare estimated row counts with actual counts. If MySQL expects a handful of matches but processes thousands, investigate data distribution and statistics. Repeated operations within a query can also indicate a costly join.
Avoid adding together every timing in the output: parent operations include time spent in their children.
If statistics appear stale, ANALYZE TABLE orders; can refresh key distribution statistics. Plan that operation around traffic because it takes a read lock during analysis.
Build Indexes Around Real Queries
An index helps MySQL locate relevant rows without searching everything. The useful question is which combination of columns supports the query you’re actually running.
For the recent-orders example, test this candidate:
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);
It groups entries by customer, then creation time. MySQL can potentially use that arrangement to find one customer’s orders and read them in reverse date order. Confirm the result with the execution plan.
Column order matters. An index on (customer_id, created_at) supports lookups beginning with customer_id; it doesn’t provide the same straightforward lookup for queries filtering only by creation time. Two separate indexes also aren’t automatically equivalent to one composite index.
For joins, inspect how MySQL accesses each table. Check whether useful indexes exist on the columns used to locate matching rows, and verify that the join conditions express the intended relationship.
Let’s be honest, adding indexes everywhere is tempting. Each extra index takes space and adds maintenance work when data changes. Check existing indexes before creating another, then measure the effect on reads and writes.
Make Your Filters Easier to Search
A useful index can lose much of its value when the filter makes MySQL transform the indexed column before comparing it.
Consider a DATETIME column holding order timestamps:
WHERE DATE(created_at) = '2026-10-24'
With an ordinary index on created_at, a direct range is usually a better candidate:
WHERE created_at >= '2026-10-24 00:00:00'
AND created_at < '2026-10-25 00:00:00'
The second form exposes a searchable interval. It also includes fractional seconds without guessing the final possible timestamp of the day. Make sure the boundaries match the timezone your application uses.
Search patterns need similar care. A conventional B-tree index can support a suitable prefix search such as LIKE 'camera%'. A leading wildcard, as in LIKE '%camera%', prevents that same range lookup.
Choose the search behavior your users need. Changing a contains search into a prefix search may improve performance while quietly making the feature less useful.
Fetch Less Data and Make Fewer Requests
A product listing usually needs a name, price, and thumbnail. Fetching long descriptions and large metadata fields for every product adds transfer and processing work.
Replace SELECT * with the columns the screen uses. Apply suitable filters and limits inside SQL so the application receives a manageable result. Remember that LIMIT 20 doesn’t guarantee MySQL examines only 20 rows, particularly when sorting requires more work.
Deep pagination deserves attention too. With LIMIT 100000, 20, MySQL still needs to work through the preceding results. For a feed ordered by a unique ID, a cursor can offer a better access pattern:
SELECT id, total
FROM orders
WHERE id < 900000
ORDER BY id DESC
LIMIT 20;
Here, 900000 represents the last ID from the previous page. This assumes ID ordering suits the feature.
Now check the application’s query count. Loading 50 products and requesting each product’s supplier separately creates 51 requests. A suitable join or batched lookup can remove those repeated trips.
Investigate Locks When Queries Suddenly Stall
Sometimes the SQL stays unchanged, yet response times jump during busy periods. Investigate whether statements are waiting for other transactions.
Imagine two checkout requests updating the same inventory row. One transaction holds the lock while it waits for an external payment response. The other request waits behind it. An extra index won’t resolve that unnecessarily long transaction.
MySQL’s Performance Schema data_lock_waits table exposes relationships between waiting and blocking data locks. Use it to investigate the sessions involved.
Keep transactions focused and finish them promptly. Move unrelated network calls outside the transaction where the application’s consistency requirements allow it. For bulk work, smaller batches may reduce the time locks remain held.
Check application traces alongside database timing. The slow query log excludes the time spent acquiring initial locks, so it can’t explain every delay a user experiences. dev.mysql.com
Match Server Resources to the Workload
Once you understand the query and its waits, check whether the server has enough capacity.
For InnoDB, the buffer pool caches data and indexes in memory. When frequently accessed pages don’t fit well, disk activity can become a bigger part of query time. Review buffer pool behavior alongside storage latency and memory pressure.
Avoid copying a memory percentage from another server without considering what else runs on yours. A dedicated database machine and a small server sharing resources with a website need different budgets.
Also inspect CPU use and concurrent activity. A reporting job that behaves well alone may compete heavily with checkout traffic.
If you cache application results, decide how fresh they must be and how updates invalidate them. A stale inventory count is a poor trade for a faster page.
Check That the Improvement Lasts
Change one meaningful thing at a time, then repeat the same workload. Keep the original execution plan and timings so you have something concrete to compare.
Test different parameter values. One customer may have five orders while another has fifty thousand. Check repeated runs and realistic concurrency, rather than celebrating one fast result on an idle server.
Verify correctness as carefully as speed. A rewritten join can duplicate rows; pagination changes can skip records when the ordering isn’t handled consistently. An index that improves browsing may also add cost to imports.
Track typical response times and the slower end of the distribution, such as the 95th percentile. Watch rows examined, query counts, and write performance after deployment.
The most dependable improvement comes from understanding the work behind the wait. Find the costly query, inspect its plan, remove unnecessary work, then measure again. That habit keeps paying off as your database grows.
