How to read MySQL EXPLAIN and EXPLAIN ANALYZE to fix a slow query

A page that took 40 milliseconds in development takes four seconds in production, and the only thing that changed is the amount of data. Almost every MySQL performance problem an application developer meets starts this way: a query that was fine on a few thousand rows is now reading millions of them. The fix is usually an index or a small rewrite, but guessing which one wastes time and can make things worse.

MySQL tells you exactly how it plans to run a statement through EXPLAIN, and how it actually ran it through EXPLAIN ANALYZE. This tutorial walks through both on MySQL 8.4 LTS and 9.7 LTS (the syntax shown also works on the 26.7 Innovation release), using a realistic orders table. You will learn which columns matter, how to read the tree format, and a repeatable loop for turning a slow query into a fast one.

Everything here was checked against the MySQL 9.7 Reference Manual in October 2026. Where output is shown, numbers are replaced with ... because the exact costs and timings depend on your data and hardware.

Set up a table worth explaining

Use a scratch schema on a development server, never production. The table below is typical of a small SaaS or shop: an auto-increment primary key, a foreign key to customers, a status and a timestamp.

CREATE TABLE orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  status      VARCHAR(20)     NOT NULL,
  total_cents INT UNSIGNED    NOT NULL,
  created_at  DATETIME        NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Load enough rows to make the optimizer's choices visible: a few hundred thousand is plenty. A recursive common table expression is a quick way to generate them without external tools:

SET SESSION cte_max_recursion_depth = 500000;

INSERT INTO orders (customer_id, status, total_cents, created_at)
WITH RECURSIVE seq (n) AS (
  SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 300000
)
SELECT
  1 + (n % 5000),
  ELT(1 + (n % 4), 'pending', 'paid', 'shipped', 'refunded'),
  100 + (n % 90000),
  NOW() - INTERVAL (n % 730) DAY
FROM seq;

ANALYZE TABLE orders;

ANALYZE TABLE refreshes the index statistics the optimizer uses for its row estimates. Run it after large loads so the plans you see reflect the real data.

The query we will tune is the one behind a customer's "recent orders" screen:

SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Read the traditional EXPLAIN output

Prefix the statement with EXPLAIN. The query is not executed; MySQL only plans it. The default format is TRADITIONAL, a table with one row per table access. With no index on customer_id, you will see something like this (abridged):

EXPLAIN SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20\G

           id: 1
  select_type: SIMPLE
        table: orders
         type: ALL
possible_keys: NULL
          key: NULL
         rows: ...
     filtered: ...
        Extra: Using where; Using filesort

Not every column deserves equal attention. These are the ones to read first:

The manual lists access types from best to worst: system, const, eq_ref, ref, fulltext, ref_or_null, index_merge, unique_subquery, index_subquery, range, index and ALL. For a lookup by a non-unique column you want ref; for a date window, range is normal.

In Extra, Using index is good news: the columns came from the index tree alone, without reading the full row. Using index condition means index condition pushdown is filtering index entries before rows are fetched. Using filesort means MySQL needs an extra pass to sort, and Using temporary means it built a temporary table, typically for GROUP BY or DISTINCT.

Switch to the tree format

The table format flattens the plan. FORMAT=TREE shows the actual iterator structure the executor will run, nested from the outermost operation inwards. The manual notes that it is the only format that shows hash join usage, and it is the format EXPLAIN ANALYZE uses by default.

EXPLAIN FORMAT=TREE
SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20\G

EXPLAIN: -> Limit: 20 row(s)  (cost=... rows=20)
    -> Sort: orders.created_at DESC, limit input to 20 row(s) per chunk  (cost=... rows=...)
        -> Filter: ((orders.customer_id = 42) and (orders.status = 'paid'))  (cost=... rows=...)
            -> Table scan on orders  (cost=... rows=...)

Read it from the deepest line upwards: scan every row, filter, sort, then keep 20. The whole table is read to return 20 rows, which is exactly the problem.

If you prefer this format all the time, set the explain_format system variable for your session: SET @@explain_format = TREE;. Valid values are TRADITIONAL (the default), JSON and TREE. For tooling, FORMAT=JSON is machine-readable; since MySQL 9.5 the default explain_json_format_version is 2, an access-path-based layout that matches the tree.

Measure with EXPLAIN ANALYZE

EXPLAIN shows estimates. EXPLAIN ANALYZE actually runs the statement and adds measurements to each iterator: the time to the first row, the time per loop in milliseconds, the actual number of rows and the number of loops. It works with SELECT, multi-table UPDATE and DELETE, and TABLE statements.

EXPLAIN ANALYZE
SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20\G

-> Limit: 20 row(s)  (cost=... rows=20) (actual time=...  rows=20 loops=1)
    -> Sort: orders.created_at DESC, limit input to 20 row(s) per chunk
         (cost=... rows=...) (actual time=... rows=20 loops=1)
        -> Filter: ((orders.customer_id = 42) and (orders.status = 'paid'))
             (cost=... rows=...) (actual time=... rows=... loops=1)
            -> Table scan on orders  (cost=... rows=...) (actual time=... rows=300000 loops=1)

Two habits make this output useful:

• Compare estimated rows with actual rows. A large gap means the optimizer is working from bad statistics or cannot estimate a condition well. Run ANALYZE TABLE, and consider a histogram for unindexed columns used in filters.
• Multiply by loops. The actual time is an average per loop. An inner iterator in a nested-loop join that takes 0.05 ms but runs 200,000 times is the real cost.

Because EXPLAIN ANALYZE executes the query, be careful on production: it takes the same time and locks as the real statement. It can be stopped with KILL QUERY or Ctrl-C. It cannot be combined with FOR CONNECTION, and FORMAT=TRADITIONAL is rejected.

Fix the query with the right composite index

The query filters on two equality conditions and sorts by a third column. A composite index that puts the equality columns first and the sort column last lets MySQL jump straight to the matching rows and read them already in order:

ALTER TABLE orders
  ADD INDEX idx_orders_customer_status_created (customer_id, status, created_at);

This follows the leftmost-prefix rule documented in the manual: an index on (col1, col2, col3) can serve lookups on (col1), (col1, col2) and (col1, col2, col3). Because both customer_id and status are constants in the WHERE clause, the remaining entries are already in created_at order and no sort is needed. Run the analysis again:

EXPLAIN ANALYZE
SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20\G

-> Limit: 20 row(s)  (cost=... rows=20) (actual time=... rows=20 loops=1)
    -> Index lookup on orders using idx_orders_customer_status_created
         (customer_id=42, status='paid') (reverse)  (cost=... rows=...)
         (actual time=... rows=20 loops=1)

The sort has gone, the table scan has gone, and the lookup stops after 20 rows. In the traditional format, type becomes ref, key names the new index, and Extra shows Backward index scan instead of Using filesort.

Go one step further: a covering index

The query still reads total_cents from the clustered index (the row). If this query is very hot, add that column to the end of the index so every selected column comes from the index itself. id does not need adding: in InnoDB every secondary index entry already contains the primary key columns. Then Extra shows Using index. Only do this for queries that justify it; every extra index column costs space and slows writes.

Common patterns that defeat an index

If EXPLAIN still shows a full scan after you add an index, look for one of these:

• A function wrapped around the column. WHERE DATE(created_at) = '2026-10-01' cannot use an index on created_at. Rewrite as a range: created_at >= '2026-10-01' AND created_at < '2026-10-02', or create a functional index such as INDEX ((DATE(created_at))) (note the double parentheses).
• Skipping the leading column. An index on (customer_id, status) does not help WHERE status = 'paid' alone.
• An OR across different columns. WHERE customer_id = 42 OR status = 'paid' cannot use a single composite index; the optimizer may use index merge or scan. A UNION of two indexed queries is often faster.
• Leading wildcards. LIKE '%term' cannot use a B-tree index; LIKE 'term%' can.
• Type or collation mismatches in joins. Joining a VARCHAR to an INT, or two string columns with different collations, can prevent index use on one side.
• Low selectivity. If the condition matches a large share of the table, a scan may genuinely be cheaper and the optimizer is right to choose it.

Explain from inside your application

Two features help when the slow query lives in application code rather than a console.

Capture the plan as JSON. EXPLAIN FORMAT=JSON INTO @var stores the plan in a user variable, which you can inspect with JSON functions, for example in an automated check that a critical query still uses its index:

EXPLAIN FORMAT=JSON INTO @plan
  SELECT id FROM orders WHERE customer_id = 42 AND status = 'paid';

SELECT JSON_EXTRACT(@plan, '$.query_block.table.key') AS chosen_key;

The JSON path depends on explain_json_format_version; the example above uses version 1 paths. With version 2 (the default since 9.5) the structure follows the access-path tree, so inspect the document once before writing paths into a test.

Explain a statement that is running now. Find the connection ID in SHOW PROCESSLIST, then run EXPLAIN FOR CONNECTION 1234;. You need the PROCESS privilege to explain another user's connection.

A repeatable tuning loop

1. Find the expensive statements first. The sys.statement_analysis view and the slow query log show which queries take the most total time; tune those, not the ones that look ugly.
2. Copy the statement with real parameter values and run EXPLAIN FORMAT=TREE.
3. Run EXPLAIN ANALYZE on a production-sized copy and compare estimated with actual rows.
4. Change one thing: an index, a rewrite, or fresh statistics.
5. Re-run EXPLAIN ANALYZE and confirm the change removed the expensive step.
6. Ship the index through your normal reviewed migration process, and watch write latency afterwards.

Next steps

EXPLAIN tells you what MySQL will do; EXPLAIN ANALYZE tells you what it did and where the time went. Read the tree from the inside out, compare estimates with reality, and design composite indexes around equality columns first, then range or sort columns. Our companion guide to composite, covering and invisible indexes goes deeper into index design, and the manual's optimization chapter is the authoritative reference for every access type and Extra value.