How to Fix Slow MySQL Queries: A Practical Guide

When a MySQL query suddenly gets slow, the cause is usually one of four things: a missing index, a query written in a way the planner cannot optimize, a schema change that quietly invalidated an index, or a table that has grown until its old indexes stopped being selective. This guide walks through each one with the diagnostics you'd actually run and the fix that follows.

If you want the short version: add indexes on the columns you filter on, rewrite queries that wrap an indexed column in a function, and re-check your plans after any major schema change. For the slow query that won't go away, EXPLAIN tells you which tables are read inefficiently, the slow query log tells you whether the problem is one query or a pattern across many, and monitoring catches the next regression before a customer does.

What makes a MySQL query slow#

MySQL runs a query by first building an execution plan: which tables to read, in what order, and which indexes (if any) to use. When the planner picks a bad plan, the query ends up reading far more rows than it needs.

The signals that a query is doing too much work all show up in EXPLAIN:

• type: ALL: a full table scan. MySQL is reading every row regardless of your WHERE conditions.
• key: NULL: no index was used at all.
• Extra: Using filesort: MySQL had to sort the results itself, in memory or on disk, instead of reading rows that were already in order from an index.
• Extra: Using temporary: MySQL built an internal temp table to satisfy the query. Common with GROUP BY or DISTINCT on non-indexed columns.
• A very large rows estimate: MySQL expects to examine tens of thousands of rows to hand you back a handful.

Any one of these on its own can be fine. All of them together, on a table with millions of rows, is your slow query.

How to enable and read the slow query log#

The slow query log records every query that runs longer than long_query_time seconds. The default is 10, which is high enough that you'll miss almost everything worth seeing. For a production investigation, drop it to 1 or 2 seconds. In development, set it to 0 and catch everything.

-- Check current settings
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- Enable globally (affects all connections, no restart required, requires SYSTEM_VARIABLES_ADMIN)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Or lower long_query_time for your session only (does not affect other connections)
SET SESSION long_query_time = 1;

log_queries_not_using_indexes catches any query that skips an index, even when it finishes fast because the table is still small. That's exactly the query you want to find in development, before the table grows and the same query becomes a problem.

The log file location lives in slow_query_log_file:

SHOW VARIABLES LIKE 'slow_query_log_file';

You don't have to read the raw log by hand. Two tools parse and group it by total time:

# Built-in summarizer
mysqldumpslow -s t -t 10 /path/to/slow.log

# More detailed output from Percona Toolkit
pt-query-digest /path/to/slow.log

mysqldumpslow -s t sorts by total execution time and shows the top 10 offenders. pt-query-digest goes further: it groups normalized queries and reports average time, rows examined, and the row-send ratio. That last one is the number to watch. The ratio of rows examined to rows returned tells you how selective the query is, and a ratio of 100:1, where MySQL read 100 rows for every 1 it gave back, almost always means a missing index.

Using EXPLAIN to read the execution plan#

Once you have a slow query in hand, run it through EXPLAIN before you change anything:

EXPLAIN SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC;

The columns worth your attention:

In MySQL 8.0+, EXPLAIN ANALYZE actually runs the query and gives you real execution times next to the estimates:

EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC;

When the estimated and actual row counts are far apart, MySQL is planning against stale table statistics. Refresh them with:

ANALYZE TABLE orders;

Common causes and fixes#

Missing indexes#

This is the one you'll hit most often. If EXPLAIN shows type: ALL and key: NULL on a large table, the fix is almost always an index on the column(s) in your WHERE or JOIN clause.

-- Before: full table scan on customer_id
SELECT * FROM orders WHERE customer_id = 42;

-- Add the index
ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);

-- Confirm it's used
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

When a query filters on more than one column, a composite index beats two separate indexes. The column order matters: put the equality column first, then the range column.

-- Query filters on both customer_id (equality) and created_at (range)
SELECT * FROM orders
WHERE customer_id = 42 AND created_at >= '2025-01-01';

-- Composite index: equality column first
ALTER TABLE orders ADD INDEX idx_customer_created (customer_id, created_at);

MySQL can use the leftmost prefix of a composite index, so an index on (customer_id, created_at) also serves a query that filters on customer_id alone. It will not help a query that filters on created_at alone.

Full table scans from functions on indexed columns#

Wrap an indexed column in a function and MySQL can no longer use the index:

-- Bad: index on created_at is ignored
SELECT * FROM orders WHERE YEAR(created_at) = 2025;

-- Good: rewrite as a range condition
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

Implicit type coercion does the same thing in a way that's easy to miss. If user_id is INT and you pass a string, MySQL converts every row before it can compare:

-- Bad: coerces every row
SELECT * FROM users WHERE user_id = '42';

-- Good: types match
SELECT * FROM users WHERE user_id = 42;

N+1 queries#

N+1 is what happens when code runs one query to fetch N parent rows, then runs N more queries to fetch related data, one per parent. One round trip per row instead of one round trip total.

-- One query to get 100 orders
SELECT id FROM orders WHERE customer_id = 42;

-- Then 100 queries, one per order
SELECT * FROM order_items WHERE order_id = ?;

A JOIN folds it back into a single query:

SELECT o.id, oi.*
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.customer_id = 42;

N+1 is sneaky because each individual query is fast, so none of them land in the slow query log on their own. The damage is cumulative, and it only becomes obvious when the count gets high. This is where pt-query-digest earns its keep: it groups normalized queries and shows execution count, so an N+1 pattern stands out the moment you look.

Correlated subqueries#

A correlated subquery re-runs for every row in the outer query:

-- Slow: subquery runs once per order row
SELECT * FROM orders o
WHERE (SELECT COUNT(*) FROM order_items WHERE order_id = o.id) > 5;

Rewrite it as a JOIN against a pre-aggregated set:

SELECT o.*
FROM orders o
JOIN (
  SELECT order_id, COUNT(*) AS item_count
  FROM order_items
  GROUP BY order_id
) counts ON counts.order_id = o.id
WHERE counts.item_count > 5;

SELECT *#

SELECT * forces MySQL to read the whole row even when you only wanted a couple of columns. On tables carrying large TEXT or BLOB columns, that's a real I/O cost for nothing. List the columns you actually need. And when a query needs only indexed columns, a covering index lets MySQL answer it straight from the index without ever touching the row.

-- All columns, reads full row
SELECT * FROM users WHERE email = 'user@example.com';

-- Only what's needed
SELECT id, name FROM users WHERE email = 'user@example.com';

How schema changes affect query performance#

Schema changes can quietly break a query plan in a way that doesn't surface until weeks later, as the table grows. A few patterns to watch:

Adding a column with a default value rewrites the whole table on a large table in older MySQL versions. In MySQL 8.0+, most ADD COLUMN operations are online, but adding a NOT NULL column with no default still locks the table. MySQL Online DDL: A Practical Guide covers which operations are safe.

Dropping an index takes away optimization that existing application queries may quietly depend on. Check sys.schema_unused_indexes first, and read the slow query log before you remove anything.

Changing a column type (INT to BIGINT, a longer VARCHAR) can invalidate an existing index or shift its selectivity. Run EXPLAIN on the affected queries after the change to confirm the plan still holds.

Table growth is the most common reason a once-fast query goes slow. An index on status with values like pending/shipped/cancelled has low cardinality. At 1,000 rows it was fine; at 50 million rows MySQL may decide a full scan is actually cheaper. Re-check EXPLAIN on large tables after each order-of-magnitude jump.

Tracking schema changes in version control makes it much easier to pin down when a regression started. MySQL Schema Migration Best Practice covers how to structure migrations so that's possible.

Monitoring slow queries in production#

Reading the slow query log by hand doesn't scale past a small team. A few ways to keep an eye on things continuously:

Performance Schema (on by default in MySQL 5.6+) keeps per-query execution statistics in memory:

-- Top 10 queries by total execution time
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1e12 AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

The sys schema wraps Performance Schema in views that are much easier to read:

-- Queries with full table scans
SELECT * FROM sys.statements_with_full_table_scans
ORDER BY total_latency DESC LIMIT 10;

-- Queries with high rows-examined to rows-sent ratio
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile
LIMIT 10;

Dedicated monitoring tools pull these metrics on a schedule and alert when latency spikes. Top SQL Server Monitoring Tools covers the SQL Server side; on the MySQL side the usual options are Percona Monitoring and Management, VividCortex, and Datadog's MySQL integration.

For timeouts that come and go rather than happening every time, MySQL Timeout Explained walks through wait_timeout, interactive_timeout, innodb_lock_wait_timeout, and net_read_timeout. Each one governs a different failure mode, so it's worth knowing which is firing.

How Bytebase helps prevent slow queries#

Most production slow-query incidents trace back to a schema change, an added column, a dropped index, a changed foreign key, that was reviewed casually or not at all. Bytebase folds SQL review into the schema migration workflow, flagging missing indexes, full-table-scan risks, and schema anti-patterns before the change reaches production.

The SQL review engine looks for the patterns that tend to produce slow queries: tables without primary keys, columns your application filters on that lack an index, SELECT * in views. Pair that with Top 4 MySQL Schema Compare Tools to diff schema state across environments, and a regression gets caught at review time instead of discovered at 2am.

If you're already doing MySQL process list monitoring, Bytebase's audit log records which schema change preceded a query plan change, which makes the post-incident investigation a lot shorter.

FAQ#

What is the default value of long_query_time? 10 seconds, which is too high for most applications. Set it to 1 or 2 seconds for production monitoring so you catch the queries that are slow but not yet catastrophic.

Why does EXPLAIN show a good plan but the query is still slow? EXPLAIN's row counts are estimates based on table statistics. When those statistics are stale, the estimates are wrong. Run ANALYZE TABLE to refresh them, then re-check with EXPLAIN ANALYZE to see the actual row counts.

When should I use a composite index vs separate indexes? A composite index when a query consistently filters on the same set of columns together, with equality conditions before range conditions in the column order. Separate indexes when different queries filter on different column subsets.

Can adding too many indexes slow MySQL down? Yes. Every index adds write overhead, since each insert, update, and delete has to maintain it, and indexes take storage too. Audit usage with sys.schema_unused_indexes and drop the ones nothing has touched lately.

What is a covering index? A covering index holds every column a query needs, so MySQL answers from the index alone without reading the row. It shows up as Using index in the Extra column of EXPLAIN, and it's the single most effective optimization for read-heavy queries on large tables.