How to Fix Slow MySQL Queries
A Step-by-Step Guide
Written By ROMAN AGABEKOV | APRIL 11, 2025
Last update | SEP 15, 2026
To fix a slow MySQL query, identify the SQL statement, check whether it is waiting or doing too much work, and inspect its execution plan. Then test the fix that matches the cause: a better index, a query rewrite, fewer application calls, or a change to how the application handles transactions. Compare the results and execution time before deploying the change.
Already have the slow SQL statement? Skip to Step 3: Read the execution plan. If the query hangs or suddenly became slow, start with Step 2: Check for blocking.
Not sure which query is slow? Start with Step 1 to find it.
Not sure which query is slow? Start with Step 1 to find it.
The examples use MySQL 8.4 with InnoDB. Run diagnostics on the server handling the slow request, and test query changes on a separate database first. Managed services can differ in logging controls and permissions.
Table of contents
1. Find the query that needs attention
Start with the slow request: its endpoint, approximate time, and SQL parameters. Separate the time MySQL spends running the query from time spent waiting for a connection, transferring results, or processing them in application code.
For a query you have not identified yet, use existing slow-query logs or statement statistics.
Inspect the slow-query log settings
The slow query log records completed statements based on the configured limits for execution time and rows examined. It will not show a statement that is still running.
This read-only check shows whether logging is enabled and which thresholds apply. It also displays the current connection's threshold, which can differ from the global value.
SHOW GLOBAL VARIABLES
WHERE Variable_name IN (
'slow_query_log', 'log_output',
'long_query_time', 'min_examined_row_limit'
);
SHOW SESSION VARIABLES LIKE 'long_query_time';If slow_query_log is OFF, continue with the statement-statistics query below. You do not need to change logging settings to use that route. If logging is ON, check log_output for the configured destination.
In a log entry, compare Query_time, Rows_examined and Rows_sent. Reading many rows to return a few is a useful lead, although an aggregate can legitimately read many rows and return one. Keep sensitive SQL literals out of shared reports.
Find frequently executed queries too
A query can run quickly each time but still create a lot of work for the database.
For illustration, 50 ms multiplied by 10,000 executions is 500 seconds of total query time. A five-second query executed ten times contributes 50 seconds. These are arithmetic examples, not measurements; concurrent statement time is not CPU utilization.
MySQL's sys schema provides grouped statement statistics from Performance Schema. This requires statement collection to be active and an account with access to the relevant monitoring views. The x$ view keeps numeric timing values, so the query below sorts numerically before converting picoseconds to seconds and milliseconds.
SELECT /*+ MAX_EXECUTION_TIME(2000) */
db,
query,
exec_count,
ROUND(total_latency / 1000000000000, 2) AS total_seconds,
ROUND(avg_latency / 1000000000, 2) AS avg_ms,
rows_examined_avg,
rows_sent_avg
FROM sys.x$statement_analysis
ORDER BY total_latency DESC
LIMIT 10;These totals reflect the data still held in the summary, not necessarily “the last hour.” Gaps in collection and counter resets affect them. For a before-and-after comparison, use matching time windows in your monitoring tool or differences between two valid counter snapshots. An empty view does not prove that no slow queries exist.
The MAX_EXECUTION_TIME(2000) hint requests a two-second limit for this diagnostic SELECT. LIMIT 10 limits the number of rows returned, not the work needed to sort the summary. If the diagnostic stalls, cancel it in your SQL client rather than repeatedly retrying during overload; cancellation may take time.
2. Check whether another transaction is blocking your query
A query can be slow because another transaction holds a lock it needs. Before changing indexes, check whether your query is waiting for that lock.
Run this read-only check while the slow request is still running. Use the same MySQL server and an account with access to its monitoring views.
SELECT
waiting_pid,
waiting_query,
blocking_pid,
wait_age_secs
FROM sys.innodb_lock_waits;Find your SQL in waiting_query. For that row:
- waiting_pid identifies the connection that is waiting.
- blocking_pid identifies the connection holding the lock.
- wait_age_secs shows how long the wait has lasted.
For example, if connection 42 is waiting on connection 17, investigate connection 17 first rather than the execution plan of connection 42.
If your query is blocked
Find the application request or background job using the blocking connection. Check for an unfinished transaction, an error path that missed a rollback, or slow external work inside the transaction.
Resolve the blocking transaction through the application’s normal transaction handling. Don’t blindly kill the connection: interrupting work can cause failures, and cancelling only its current statement can leave the transaction open.
Then repeat the check and confirm that the affected request completes.
If your query is not listed
If no current InnoDB row-lock wait is found for your query, continue to step 3: read the execution plan. An empty result does not rule out other waits or a problem that already ended.
3. Read the execution plan
An execution plan shows how MySQL plans to find the rows: where it starts, which index it uses, and how much filtering or sorting follows.
Consider this illustrative order-history request. The example orders table uses InnoDB, has a unique primary key id, an integer customer_id, and a DATETIME column created_at containing valid dates. The application wants the most recent 20 orders for one customer on one calendar day.
Start with ordinary EXPLAIN. For this simple SELECT, it shows the plan without running the query itself. Specifying the format keeps the output in a familiar table.
EXPLAIN FORMAT=TRADITIONAL
SELECT id, created_at
FROM orders
WHERE customer_id = 42
AND DATE(created_at) = '2026-09-01'
ORDER BY created_at DESC, id DESC
LIMIT 20;Focus on four fields:
A full scan can be reasonable for a small table or a query returning much of it. key = NULL is a clue, not a diagnosis. Using filesort means an additional sort; it does not by itself prove disk I/O.
For a deeper explanation of plan fields, see how to read MySQL EXPLAIN.
4. Fix the work the query is doing
Rewrite the date filter as a range
The original condition calculates DATE(created_at) instead of directly comparing the indexed datetime value. For this example, use a half-open interval: include the start of September 1 and exclude the start of September 2.
This includes values with fractional seconds and gives MySQL a range on created_at that a suitable B-tree index can use. The two filters match when the DATETIME values are valid and both refer to the same calendar day. If your application stores UTC but searches by local day, calculate the start and end times for that timezone.
EXPLAIN FORMAT=TRADITIONAL
SELECT id, created_at
FROM orders
WHERE customer_id = 42
AND created_at >= '2026-09-01 00:00:00'
AND created_at < '2026-09-02 00:00:00'
ORDER BY created_at DESC, id DESC
LIMIT 20;Evaluate an index for the complete query
For this query, a candidate composite index has the key order customer_id, created_at, id. “Composite” simply means that one index contains multiple columns.
Here is why this column order fits:
- customer_id locates one customer's entries.
- created_at narrows the date interval.
- created_at and id provide the requested ordering within that customer.
An ascending index can be scanned backwards for both descending sort columns. Because the query selects only these keys, the candidate can also cover its selected columns. Check the existing indexes first: InnoDB secondary indexes already include the primary key, so an existing customer-and-date index may provide the needed ordering without another index.
Test the candidate index on representative data, then run the rewritten EXPLAIN again. Compare key, type, rows and Extra with the original plan. The optimizer may still choose another plan; the number of matching rows and the existing indexes matter. Reordering conditions in the SQL text does not reorder the index.
Check these other common causes
Batching and eager loading can help, but they do not replace joins or caching in every situation. In Laravel, Symfony, WordPress or custom PHP code, inspect the SQL actually sent to MySQL and count calls per request.
If estimated rows differ sharply from actual rows, investigate data distribution and optimizer statistics. Outdated statistics are one possible cause, but a mismatch alone does not prove it.
5. Check before changing production
Adding an index changes the table, not just one query.
Before applying it:
- Check existing indexes. You may already have an index that supports the query.
- Test the change first. Use a staging database with representative data. Confirm that the query becomes faster and still returns the expected results.
- Check your backup. Make sure you have a recent, verified backup and know how to restore it.
- Check free disk space. Allow room for the new index, temporary files and any required backup. The space needed depends on the operation.
- Choose a quiet period and monitor the change. Watch application response times, disk usage and replica lag. Decide beforehand when and how to stop if performance suffers.
“Online” does not mean lock-free. Even online index changes can wait for long-running transactions and briefly require exclusive metadata locks. See MySQL online DDL limitations.
6. Verify that the query became faster
Compare the original and revised query against the same data, parameter values and comparable load. Test more than one customer or date: an index that helps a small result set may behave differently for a much larger one.
Use EXPLAIN ANALYZE to see how long each step takes, how many rows it returns and how often it runs. It runs the query, so start on a separate test database with realistic data and make sure you can cancel it. Avoid rerunning an expensive production query during an incident just to collect a plan.
For the order-history example, compare these checks before and after the change:
Record whether runs used a warm cache, and avoid comparing a cold first run with a warm second run as if the index caused the whole difference. A different plan shows that MySQL changed how it runs the query. Correct results and a repeatable speedup tell you whether the change helped.
After deployment, monitor performance during a typical busy period. If it gets worse, undo the application change through your normal release process and investigate the index separately. Reverting the SQL does not remove an index added during deployment.
Frequently asked questions
What is considered a slow MySQL query?
Start with how quickly the application needs to respond and how often the query runs. There is no single time limit that makes every query slow. The slow-log threshold controls what gets recorded; it does not define acceptable performance for your application.
Why is my query slow even though it has an index?
The index may not match the query's filters, column order or sorting. MySQL may also choose a scan because many rows qualify. Check the actual plan, parameter types and lock waits before adding another index.
Will LIMIT make a slow query fast?
It limits returned rows, but MySQL may still scan or sort many candidates first. An index that supports both filtering and ordering can make an early stop possible.
Should I increase MySQL memory first?
Only when measurements point to memory or I/O pressure. More memory does not resolve an open blocking transaction or remove unnecessary application calls. Follow the query evidence first; investigate broader server tuning if many statements share the slowdown.
How Releem helps you fix slow MySQL queries
The manual workflow becomes harder to maintain as SQL, table sizes and traffic change. Releem can help organize the next investigation:
- Prioritize queries. Query Analytics groups similar statements and shows execution count, average execution time and total time, helping you find both slow statements and frequent ones.
- Inspect execution plans. With SQL Query Optimization enabled, Releem collects EXPLAIN output for selected queries, so you can inspect how MySQL plans to execute them.
- Review a proposed fix. Releem's optimization workflow provides explanations and recommendations for query and index changes. Evaluate each recommendation against your application and deploy through your team's change process.
- Apply approved indexes. Releem can apply approved index and other schema changes from the dashboard. This feature is disabled by default. Once enabled, the agent checks the requirements before execution and skips changes that fail those checks.
The same verification still matters: compare correct results, query duration and workload impact after a change. An optimization recommendation is the start of that check.
Explore Releem SQL Query Optimization to see how Releem helps you find and fix slow queries.
Article by
- Founder & CEORoman Agabekov has 17 years of experience managing and optimizing MySQL and MariaDB in high-load environments. He founded Releem to automate routine database management tasks like performance monitoring, tuning, and query optimization. His articles share practical insights to help others maintain and improve their databases.
Get your own database advisor
Try Releem premium today for FREE!
Try Releem premium today for FREE!