When a query is slow, EXPLAIN shows how MySQL plans to execute it: which indexes it uses, how many rows it expects to read, and whether it needs extra work like sorting on disk. Reading that plan is the key skill for turning a 3-second query into a 3-millisecond one, usually by adding or changing an index.

Find the slow queries first with the slow query log.

Run EXPLAIN

Put EXPLAIN in front of a SELECT (or UPDATE/DELETE):

EXPLAIN SELECT id, total FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;
+----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 498211 | Using where; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+

This plan reads all ~500k rows (type: ALL, no key), then sorts them (Using filesort). That’s slow, and it gets slower as the table grows.

The columns that matter

type: how rows are accessed (best to worst)

type Meaning
system / const At most one row via a primary or unique key
eq_ref One row per join row via a unique index
ref Rows matching a non-unique index value
range Index range scan (BETWEEN, >, IN, LIKE 'abc%')
index Full index scan (better than a table scan, still reads everything)
ALL Full table scan. Usually the problem on large tables

key and possible_keys

  • possible_keys lists indexes that could be used.
  • key is the index actually chosen. NULL means no index.

rows

The optimizer’s estimate of rows to examine. Compare it with the rows returned. Reading 500,000 rows to return 20 is the signal to fix.

Extra: warning signs

Extra Meaning
Using index Covering index: answered from the index alone (great)
Using where Rows filtered after reading
Using filesort Extra sort step. Can often be avoided with an index matching the ORDER BY
Using temporary A temporary table, common with GROUP BY / DISTINCT on unindexed columns
Using join buffer A join without a usable index on the joined table

Fix the example with a composite index

The query filters on customer_id and status, and sorts by created_at. One composite index covers all three:

ALTER TABLE orders ADD INDEX idx_cust_status_created (customer_id, status, created_at);
| type | key                     | rows | Extra       |
| ref  | idx_cust_status_created |   37 | Using where |

Now MySQL jumps straight to the matching rows, already in created_at order, with no filesort and 37 rows read instead of 500,000.

Column order in composite indexes: equality conditions first (customer_id, status), then the range or sort column (created_at). An index on (a, b, c) helps queries on a, a+b and a+b+c, but not on b alone. See database indexing basics.

EXPLAIN ANALYZE: actual numbers

MySQL 8.0.18+ and MariaDB (ANALYZE SELECT …) can run the query and report real timings and row counts:

EXPLAIN ANALYZE SELECT id, total FROM orders WHERE customer_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;
-> Limit: 20 row(s)  (actual time=0.081..0.093 rows=20 loops=1)
    -> Index lookup on orders using idx_cust_status_created (customer_id=42, status='paid') (reverse)  (cost=… rows=37) (actual time=0.079..0.090 rows=20 loops=1)

Large gaps between estimated and actual rows mean outdated statistics. Run ANALYZE TABLE orders;.

EXPLAIN FORMAT=JSON and EXPLAIN FORMAT=TREE give more detail, including cost estimates.

Common reasons an index isn’t used

  • Functions on the column: WHERE DATE(created_at) = '2026-10-14' can’t use an index on created_at. Rewrite it as a range: created_at >= '2026-10-14' AND created_at < '2026-10-15'.
  • Leading wildcard: LIKE '%term' can’t use a B-tree index. Consider full-text search. See Meilisearch.
  • Type mismatch: comparing a VARCHAR column to a number (WHERE phone = 12345) forces conversion. Quote strings.
  • Low selectivity: an index on a column with few distinct values (like a boolean flag) may be ignored, because scanning is cheaper.
  • OR across different columns: may need separate indexes (index merge), or a UNION.
  • Small tables: a full scan of 100 rows is fine. Don’t over-index.

Joins

Each row in the EXPLAIN output is one table, in join order. Make sure joined columns are indexed on the inner table:

EXPLAIN SELECT o.id, c.email FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'open';

eq_ref on customers (the primary key) is ideal. ALL with Using join buffer means a missing index. See SQL joins explained.

Don’t over-index

Every index speeds up reads but slows down writes, and uses disk and buffer pool memory. Remove unused indexes. MySQL’s sys schema helps:

SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

Quick reference

Look for Means Typical fix
type: ALL on a big table Full scan Index the WHERE columns
key: NULL No index used Add or adjust an index, rewrite the condition
rows ≫ result size Reading too much More selective or composite index
Using filesort Extra sort Index matching WHERE + ORDER BY
Using temporary Temp table Index GROUP BY columns
Using index Covering index Good

Related: MySQL performance tuning and InnoDB buffer pool.

  • MySQL
  • Databases
  • Performance