How to Find and Fix Slow MySQL Queries Before They Affect Performance

Every slow query has a reason. The database is doing more work than it should because something is wrong with how the query is structured, how the data is indexed, or how the server is configured. Finding the slow queries, understanding why they are slow, and fixing them is a systematic process that does not require guesswork.

This article covers how to identify slow queries using EXPLAIN and the slow query log, how to read the output to find the problem, and how to apply the most common fixes. The goal is to move from slow queries that take seconds to fast queries that take milliseconds.

Using EXPLAIN to Understand How a Query Executes

EXPLAIN is the first tool to reach for when diagnosing a slow query. It shows how MySQL executes a query, including whether it uses an index, how many rows it examines, and the order in which it processes tables. Running EXPLAIN on a query before optimising tells you exactly where to focus.

EXPLAIN SELECT * FROM bookings
WHERE customer_id = 123 AND status = 'confirmed';

The output shows several fields that matter for performance. The type field describes how the table is accessed. A value of ALL means a full table scan, which is what you want to avoid for large tables. A value of ref or range means the query is using an index to narrow the results.

The key field shows which index MySQL actually chose to use. If the key field is NULL, the query is not using any index and is scanning the entire table. The rows field shows how many rows MySQL examined to execute the query. If the query returns one row but examines ten thousand, the index strategy needs review.

The Extra field contains additional information about the query execution. Values like Using filesort and Using temporary indicate that MySQL is performing expensive operations that should be avoided when possible. A query that requires a temporary table to sort results is slower than one that can use an index for the sort order.

Understanding the EXPLAIN Output Fields

The id field shows the order in which tables are processed in a join. Higher IDs are processed first. The select_type field shows the type of select: SIMPLE for a simple query, DERIVED for a subquery in the FROM clause, and SUBQUERY for a subquery in the WHERE clause.

The table field shows which table the row refers to. In a query with multiple joined tables, each table in the output refers to the table being accessed at that point in the execution plan.

The partitions field shows which partitions are being accessed if the table is partitioned. For non-partitioned tables, this is NULL.

The type field values, from worst to best: ALL is a full table scan. INDEX is a full index scan, still slow for large tables. RANGE is a range scan using an index, acceptable for filtered queries. REF is an index lookup that returns all rows matching a value, good for equality conditions. EQ_REF is an index lookup that returns exactly one row per match, the best possible for joins. CONST is when the entire table is read once and treated as a constant.

Aim for type values of ref or better. A type of ALL on a large table is the primary signal that a query needs optimisation. Understanding these values helps you prioritise which queries to fix first.

Enabling and Using the Slow Query Log

The slow query log records queries that exceed a time threshold. Enabling it captures the queries that are consistently slow in production, which are the ones worth optimising. Queries that are slow in development but fast in production matter less than queries that are slow in production under real load.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Setting long_query_time to 1 logs queries that take longer than one second. Adjust this based on the acceptable threshold for your application. Setting log_queries_not_using_indexes to ON ensures that queries that do full table scans are logged even if they are fast, because a query that does a full table scan on a large table will be slow under load.

Before enabling the slow query log on a production server, check available disk space. The log file can grow large on busy databases. Monitor the file size and rotate it regularly to prevent it from consuming all available storage.

Make this configuration persistent by adding it to the MySQL configuration file so it survives restarts.

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

Review the slow query log regularly, ideally weekly. The queries that appear most frequently in the log are the highest-priority optimisation targets. A query that takes two seconds but runs once a day matters less than a query that takes half a second but runs ten thousand times per day.

Tools for Analysing the Slow Query Log

The mysqldumpslow command parses the slow query log and summarises the results. It groups similar queries together and shows which ones appear most frequently and which have the longest average execution time.

mysqldumpslow -s r /var/log/mysql/slow.log

The -s r flag sorts by row count, showing which queries examine the most rows. Other useful sort options are -s t for total time and -s l for lock time.

mysqldumpslow -s t /var/log/mysql/slow.log | head -20

The pt-query-digest tool from the Percona Toolkit provides more detailed analysis. It identifies the queries that have the highest impact on performance, shows their execution frequency, and provides a breakdown of where the time is spent in each query.

pt-query-digest /var/log/mysql/slow.log

This output shows the slowest queries ranked by impact, with details on whether they use indexes, how many rows they examine, and how their performance changes over time. The tool also highlights queries that have recently become slower, which can indicate a data distribution change or a schema modification that affected query plans.

Indexing Strategy: How to Fix Most Slow Queries

Most slow queries are slow because they are scanning too many rows. An index allows MySQL to find the rows it needs without scanning the entire table. The correct indexes can reduce query time from seconds to milliseconds. A solid database indexing strategy is the foundation of good query performance.

Columns that appear in WHERE clauses should usually be indexed. For a query with multiple conditions, a composite index that includes all the WHERE columns in the correct order is more efficient than separate indexes on each column. The column order in a composite index matters: the index can be used for a query that filters on the first column, or on the first and second columns, but not for a query that filters only on the second column.

CREATE INDEX idx_bookings_customer_status ON bookings(customer_id, status);

This composite index supports queries that filter on customer_id alone, on customer_id and status together, or on status alone if MySQL decides to use the index for that. For queries that only filter on status, a separate index on status alone would be more effective.

The order of columns in the composite index should reflect the most common query patterns. If most queries filter on customer_id first and then status, put customer_id first. If status is the primary filter and customer_id is secondary, reverse the order. Getting this right means the index is used more often and provides better performance gains.

When Composite Indexes Do Not Help

A composite index cannot be used for a query that does not include the leftmost column. If the index is on customer_id, status, and the query filters only on status, MySQL cannot use the index and will do a full table scan. The query planner decides based on statistics about the data distribution, so the only way to be certain an index is used is to examine the EXPLAIN output.

Index selectivity matters. An index on a column with few distinct values, such as a status column with only three values, is not very selective. MySQL may decide that scanning a large portion of the table is faster than using the index because the index does not narrow the results enough to be useful. For low-selectivity columns, a composite index that includes the low-selectivity column as the second or third column is more effective.

Covering indexes can make a query faster by including all the columns the query needs in the index itself. When MySQL can satisfy the entire query from the index without reading the table rows, the query is faster because it reads only index data rather than reading the table and then looking up additional columns in the index.

CREATE INDEX idx_bookings_covering ON bookings(customer_id, status, created_at);

This index can satisfy a query that selects customer_id, status, and created_at from the bookings table without reading any table rows, only the index. Covering indexes are particularly useful for queries that run frequently and return a small number of columns from a large table.

Query Structure Problems and How to Fix Them

Some slow queries are slow not because of missing indexes but because of how the query is written. A query that selects more columns than necessary forces MySQL to read more data than needed. A query that uses functions on indexed columns prevents the index from being used.

SELECT * FROM bookings WHERE YEAR(created_at) = 2024;

This query cannot use an index on created_at because the YEAR function wraps the column. A range query on the date column directly is faster.

SELECT * FROM bookings WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

This query can use an index on created_at and is faster because MySQL can scan the index rather than applying a function to every row. Functions on indexed columns are a common source of preventable slow queries, especially in date-based reporting.

Queries with OR conditions can be slow when they combine indexed and non-indexed columns. Rewriting with UNION can allow MySQL to use the index for the part that can use it.

SELECT * FROM bookings WHERE customer_id = 123 OR status = 'confirmed';

This query cannot use a single index effectively if customer_id is indexed but status is not. Rewriting as a UNION lets MySQL use the index on customer_id for the first part and accept a table scan for the second.

SELECT * FROM bookings WHERE customer_id = 123
UNION ALL
SELECT * FROM bookings WHERE status = 'confirmed' AND customer_id != 123;

Using UNION ALL instead of UNION avoids the deduplication step, which adds overhead. Only use UNION if you need to remove duplicate rows. The second SELECT in this example explicitly excludes customer_id 123 to prevent those rows from appearing twice in the results.

When to Add Indexes and When Not To

Indexes improve read performance but slow down writes. Each INSERT, UPDATE, and DELETE that affects an indexed column must also update the index. A table with many indexes is faster to read from but slower to write to. The performance gain from a read index usually outweighs the write overhead for most applications, but very high-write tables may need fewer indexes.

Drop unused indexes. An index that is never used in a query is a cost with no benefit. The MySQL performance schema and sys schema have views that show which indexes are used and how often. Indexes that appear in EXPLAIN but not in actual query execution plans over a representative period can usually be removed.

SELECT index_name, cardinality, column_name
FROM information_schema.statistics
WHERE table_schema = 'your_database' AND table_name = 'bookings';

Review this periodically to identify indexes that are not serving any query. Removing unnecessary indexes reduces storage use and speeds up write operations without affecting query performance.

Always test index changes on a staging environment before applying them to production. Adding or removing an index changes the query execution plan, and the new plan may not always be faster for all query variations.

Monitoring Query Performance Over Time

Optimising slow queries is not a one-time task. Data volumes grow, query patterns change, and new queries are added. A slow query that was acceptable when the table had ten thousand rows becomes a problem when it has ten million. Setting up ongoing monitoring ensures that performance problems are caught before they affect users.

The key metrics to track are average query execution time per endpoint, the percentage of queries that exceed an acceptable threshold, and the number of queries that do full table scans. These can be collected from the slow query log, from MySQL's performance schema, or from application-level instrumentation. Tracking these metrics over time helps you spot trends before they become critical issues.

Set up alerts for when the slow query count spikes above a threshold. A sudden increase in slow queries usually indicates a deployment that introduced a new slow query, a data volume that crossed a threshold, or a configuration change that affected performance. Regular monitoring combined with alerting means you find out about problems from your monitoring tools rather than from frustrated users.

When to Contact an IT Professional

Some query optimisation tasks are straightforward, particularly when the slow query log points clearly to a missing index or a simple query structure problem. Other situations call for more experience: queries that involve multiple joins across many tables, performance issues that appear only under concurrent load, or optimisation work on production systems where mistakes can cause downtime.

If you have reviewed the EXPLAIN output, added appropriate indexes, and query performance is still not meeting expectations, the issue may be deeper than the query itself. Server-level configuration, memory allocation, table fragmentation, or locking contention can all cause performance problems that look like slow queries at the application level.

A technical specialist can review the full stack including database configuration, server resources, and query patterns to identify issues that are difficult to spot from the query output alone. This kind of review is particularly useful when preparing for increased traffic or launching new features that will change query patterns significantly.

If you want a practical review of your current database performance, you can get in touch with details of your setup, the database size, and the specific performance problems you are seeing.