How to find and optimize slow MySQL queries - Rocketeers app

  [ Rocketeers ](/)   

[Login](https://rocketeersapp.com/login) 

 On this page

 Knowledge
---------

How to find and optimize slow MySQL queries
===========================================

### [\#Performance](https://rocketeersapp.com/knowledge/performance)

A repeatable process for MySQL query optimization, find slow MySQL queries with the slow query log, diagnose them with EXPLAIN, and fix them with the right indexes and query rewrites.

 Published by [Mark van Eijk](https://rocketeersapp.com/author/mark-van-eijk) on June 23, 2026 · 4 minute read

1. [Find slow queries with the slow query log](#content-find-slow-queries-with-the-slow-query-log)
2. [Diagnose with EXPLAIN](#content-diagnose-with-explain)
3. [Add the right index](#content-add-the-right-index)
4. [Rewrite index-unfriendly queries](#content-rewrite-index-unfriendly-queries)
5. [EXPLAIN ANALYZE on MySQL 8.0](#content-explain-analyze-on-mysql-80)
6. [Conclusion](#content-conclusion)

When a request is slow, the database is usually to blame, and the database is usually slow because of a missing index or a query written in a way the optimizer can't use. MySQL query optimization isn't guesswork: there's a repeatable loop. Find the slow queries with the slow query log, diagnose each one with `EXPLAIN`, then fix it with an index or a rewrite. This guide walks through that loop end to end.

[\#](#content-find-slow-queries-with-the-slow-query-log "Permalink")Find slow queries with the slow query log
-------------------------------------------------------------------------------------------------------------

You can't optimize slow MySQL queries until you know which ones are slow. The slow query log records every statement that takes longer than a threshold you set.

Check the current settings:

 ```
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

```

Enable it at runtime (no restart needed) and log anything over half a second:

 ```
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

```

To make it survive a restart, set the same values in `my.cnf` (usually `/etc/mysql/mysql.conf.d/mysqld.cnf`):

 ```
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

```

`log_queries_not_using_indexes` is worth turning on temporarily, it catches full table scans even when they're currently fast, before the table grows.

The raw log is noisy. Use `mysqldumpslow` to aggregate it, so you see the worst offenders first instead of one line per execution:

 ```
# Top 10 queries by total time spent
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

```

`-s t` sorts by total time (the queries actually costing you the most), `-t 10` limits to ten results. Start at the top of that list.

[\#](#content-diagnose-with-explain "Permalink")Diagnose with EXPLAIN
---------------------------------------------------------------------

Once you have a slow query, put `EXPLAIN` in front of it to see how MySQL plans to run it:

 ```
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'paid';

```

Three columns tell you almost everything:

- **`type`** — the access method. `ALL` means a **full table scan**, every row read, and is the clearest sign of trouble. `ref`, `eq_ref`, `range`, or `const` mean an index is doing the work.
- **`key`** — the index MySQL chose. `NULL` means no index is being used for this query.
- **`rows`** — the estimated number of rows MySQL expects to examine. A number close to the table's total row count confirms a scan.

So `type = ALL`, `key = NULL`, and a large `rows` estimate together mean the query is scanning the whole table. That's the query to fix next.

[\#](#content-add-the-right-index "Permalink")Add the right index
-----------------------------------------------------------------

Most of the time, the fix for a scanning query is an index on the columns in the `WHERE` clause. For the query above, a composite index covering both filtered columns lets MySQL jump straight to the matching rows:

 ```
ALTER TABLE orders ADD INDEX idx_orders_customer_status (customer_id, status);

```

Re-run `EXPLAIN` and confirm `type` is now `ref` and `key` shows your new index. Don't index blindly, though: indexes slow down writes and the column order matters. For when to add a composite index, the leftmost-prefix rule, and the write cost, see the full walkthrough in [how database indexing works](/database-indexing).

[\#](#content-rewrite-index-unfriendly-queries "Permalink")Rewrite index-unfriendly queries
-------------------------------------------------------------------------------------------

An index only helps if the query is written so MySQL can use it. These rewrites fix the most common cases where `key` stays `NULL` even after you add an index.

**Don't `SELECT *`.** Selecting only the columns you need keeps result sets small and can let a covering index satisfy the query without touching the table at all:

 ```
-- Reads every column, can't be covered by an index
SELECT * FROM orders WHERE customer_id = 42;

-- Only what you need
SELECT id, total, created_at FROM orders WHERE customer_id = 42;

```

**Don't wrap an indexed column in a function.** The index on `created_at` is useless here because MySQL has to compute `DATE()` for every row:

 ```
-- Can't use the index
SELECT id FROM orders WHERE DATE(created_at) = '2026-06-23';

-- Range condition, uses the index
SELECT id FROM orders
WHERE created_at >= '2026-06-23' AND created_at