EXPLAIN & query optimisation

Read EXPLAIN and EXPLAIN ANALYZE, spot full scans and filesorts, and rewrite slow queries.

When a query is slow, don't guess: ask MySQL how it runs the query. EXPLAIN shows the execution plan (which indexes, which join order, how many rows it expects to read), and EXPLAIN ANALYZE shows what actually happened. This lesson teaches you to read plans and fix the most common problems.

Setup#

SQL

CREATE TABLE customers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(40) NOT NULL,
    country CHAR(2) NOT NULL
);
CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    status VARCHAR(10) NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL
);

SET SESSION cte_max_recursion_depth = 200000;
INSERT INTO customers (name, country)
WITH RECURSIVE s (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM s WHERE n < 20000)
SELECT CONCAT('Customer ', n), ELT(1 + n % 4, 'IN', 'GB', 'US', 'DE') FROM s;

INSERT INTO orders (customer_id, status, total, created_at)
WITH RECURSIVE s (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM s WHERE n < 200000)
SELECT 1 + n % 20000, ELT(1 + n % 10, 'paid','paid','paid','paid','paid','paid','shipped','shipped','pending','refunded'),
       (n % 500) + 0.99, '2025-01-01' + INTERVAL (n % 600) DAY + INTERVAL (n % 86400) SECOND
FROM s;
ANALYZE TABLE customers, orders;

Note that we deliberately didn't declare a foreign key, so orders.customer_id has no index yet.

Reading EXPLAIN#

SQL

EXPLAIN
SELECT c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id = 42;

Output

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | c     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |      1 |   100.00 | NULL        |
|  1 | SIMPLE      | o     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | 199656 |    10.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+--------+----------+-------------+

Each row of the output is one table access, in the order MySQL performs them. The key columns are:

Column	Meaning
`table`	which table (or alias) this step reads
`type`	how it's accessed. Best to worst: `const`/`eq_ref` (one row by unique key), `ref` (index lookup), `range` (index range), `index` (full index scan), `ALL` (full table scan)
`possible_keys` / `key`	indexes considered / actually chosen
`rows`	estimated rows to examine at this step
`filtered`	estimated % of those rows that survive the `WHERE`
`Extra`	extra work: `Using where`, `Using index` (covering), `Using filesort`, `Using temporary`, `Using join buffer`

Here orders is read with type: ALL, scanning roughly 200,000 rows to find 10 orders. customers uses const through the primary key, which is fine. Multiply rows down the steps for a rough idea of the work. Fix the scan:

SQL

CREATE INDEX idx_orders_customer ON orders (customer_id);
EXPLAIN SELECT c.name, o.total FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id = 42;

Output

+----+-------------+-------+------------+-------+---------------------+---------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | c     | NULL       | const | PRIMARY             | PRIMARY             | 4       | const |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | o     | NULL       | ref   | idx_orders_customer | idx_orders_customer | 4       | const |   10 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------------+---------------------+---------+-------+------+----------+-------+

Now orders uses ref on idx_orders_customer with about 10 rows. Always index foreign-key columns (InnoDB does it automatically when you declare the foreign key).

Tree format and EXPLAIN ANALYZE#

EXPLAIN FORMAT=TREE shows the plan as nested operations, which is often easier to follow for joins. EXPLAIN ANALYZE (MySQL 8.0.18+) runs the query and adds the real numbers:

SQL

EXPLAIN ANALYZE
SELECT status, COUNT(*) FROM orders WHERE created_at >= '2026-06-01' GROUP BY status;

Output

-> Table scan on <temporary>  (actual time=36.8..36.8 rows=4 loops=1)
    -> Aggregate using temporary table  (actual time=36.8..36.8 rows=4 loops=1)
        -> Filter: (orders.created_at >= TIMESTAMP'2026-06-01 00:00:00')  (cost=20118 rows=66545) (actual time=0.228..31.2 rows=27972 loops=1)
            -> Table scan on orders  (cost=20118 rows=199656) (actual time=0.167..21.3 rows=200000 loops=1)

Read it from the innermost (most indented) line outwards. For each step, compare the estimated rows with the actual rows. Big differences mean the optimiser's statistics are off (ANALYZE TABLE refreshes them). actual time=first..last is in milliseconds, and loops tells you how many times a step ran. A step with many loops inside a join is a classic hotspot. Your timings will differ from these.

Remember that EXPLAIN ANALYZE really executes the query, so be careful with expensive statements on production servers.

Fixing filesort with the right index#

SQL

EXPLAIN SELECT id, total FROM orders
WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 5\G

Output

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ref
possible_keys: idx_orders_customer
          key: idx_orders_customer
      key_len: 4
          ref: const
         rows: 10
     filtered: 100.00
        Extra: Using filesort

Using filesort: MySQL fetches all of the customer's orders and sorts them. An index that matches both the filter and the order lets it read rows already sorted and stop after 5:

SQL

CREATE INDEX idx_orders_cust_created ON orders (customer_id, created_at);
EXPLAIN SELECT id, total FROM orders
WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 5\G

Output

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ref
possible_keys: idx_orders_customer,idx_orders_cust_created
          key: idx_orders_cust_created
      key_len: 4
          ref: const
         rows: 10
     filtered: 100.00
        Extra: Backward index scan

The filesort is gone. Backward index scan means MySQL simply reads the index from the end to satisfy DESC. (MySQL 8 also supports real descending indexes, e.g. (customer_id, created_at DESC).) The new index begins with customer_id, so it also makes idx_orders_customer redundant. Drop the older one.

Ranges: make filters sargable#

A condition is sargable (Search-ARGument-ABLE) when MySQL can use an index for it. Keep the indexed column bare on one side of the comparison:

Not sargable ❌	Sargable ✅
`WHERE DATE(created_at) = '2026-03-01'`	`WHERE created_at >= '2026-03-01' AND created_at < '2026-03-02'`
`WHERE total * 1.18 > 500`	`WHERE total > 500 / 1.18`
`WHERE LEFT(name, 3) = 'Cus'`	`WHERE name LIKE 'Cus%'`
`WHERE customer_id + 0 = 42`	`WHERE customer_id = 42`

A checklist for slow queries#

Find them. Turn on the slow query log (slow_query_log = ON, long_query_time = 1) and summarise it with mysqldumpslow or pt-query-digest. The sys schema view sys.statements_with_runtimes_in_95th_percentile helps too.
EXPLAIN them. Look for ALL on big tables, large rows estimates, Using filesort and Using temporary on large row counts.
Index for the query: equality columns first, then range or sort columns. Make the index covering if the query is very hot.
Read less data: select only the columns you need, add LIMIT, and use keyset pagination instead of huge OFFSETs.
Rewrite when needed:
- Turn OR across different columns into a UNION ALL of two indexed queries.
- Replace a per-row correlated subquery with a join or a window function.
- Aggregate in a derived table or CTE before joining, so you don't join millions of rows only to group them.
- Use EXISTS rather than COUNT(*) > 0.
Fix the application: the "N+1 queries" problem (one query per item in a loop) is often worse than any single slow query. Fetch in batches with IN (...) or a join.
Re-measure with EXPLAIN ANALYZE or real timings. Undo changes that didn't help, because every index slows down writes.

Optimiser statistics and hints#

MySQL picks plans from statistics about your data. After big data changes, run ANALYZE TABLE t;. For skewed columns, MySQL 8 can store histograms, which help with columns that aren't indexed:

SQL

ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS;

As a last resort, you can steer the optimiser with index hints (FORCE INDEX (idx), USE INDEX, IGNORE INDEX) or optimiser hints (SELECT /*+ JOIN_ORDER(o, c) */ ...). Hints freeze today's assumptions into your code, so prefer fixing indexes and statistics first.

What's next#

Fast queries are only half of production readiness. They must also be correct when many users change data at the same time. Next: transactions, ACID and isolation levels.

Setup#

Reading EXPLAIN#

Tree format and EXPLAIN ANALYZE#

Fixing filesort with the right index#

Ranges: make filters sargable#

A checklist for slow queries#

Optimiser statistics and hints#

What's next#

Quick quiz