MySQL EXPLAIN Explained: How to Read Query Plans and Fix Slow Queries

It started with a routine Black Friday deploy. The checkout flow — identical to the staging environment — began timing out within minutes of peak traffic hitting the production database. Response times climbed from 200ms to 2.3 seconds, then beyond. No schema changes, no application code changes. When the on-call DBA ran EXPLAIN SELECT * FROM orders WHERE customer_id = 12345 AND status = 'pending' ORDER BY created_at DESC LIMIT 20 against the production orders table, the output showed type: ALL and rows: 40,247,891 — a full scan of forty million rows on every single checkout request. Staging had 50,000 rows; the missing composite index on (customer_id, status, created_at) was invisible there. After adding it, execution time dropped to 2ms and the incident was over.

MySQL's EXPLAIN command is the single most powerful diagnostic tool available to any developer or DBA working with MySQL. It reveals exactly how the query optimizer plans to execute a statement — which indexes it will use, how many rows it expects to examine, and where it may be sorting or creating temporary tables. Understanding its output is the difference between guessing at performance problems and diagnosing them with surgical precision.

EXPLAIN vs EXPLAIN ANALYZE vs EXPLAIN FORMAT=JSON

MySQL offers three distinct forms of the EXPLAIN command, each serving a different diagnostic purpose. Knowing which to use and when saves significant time during a performance investigation.

Plain EXPLAIN shows the optimizer's estimated execution plan without actually running the query. It is safe to run on any query at any time — including expensive ones — because no rows are read or modified. It works on SELECT, and since MySQL 8.0, also on INSERT, UPDATE, and DELETE statements.

-- Basic EXPLAIN — safe on any query, no rows touched
EXPLAIN SELECT o.id, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 100;

-- Also works on DML (MySQL 8.0+)
EXPLAIN UPDATE orders SET status = 'shipped' WHERE carrier_id = 99;

EXPLAIN FORMAT=JSON emits the same plan as structured JSON, adding cost estimates, used columns, and attached conditions that the tabular output omits. It is particularly useful for programmatic analysis or when you need to see cost_info to compare two competing plan alternatives.

EXPLAIN FORMAT=JSON
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = 12345
  AND o.status = 'pending';

EXPLAIN ANALYZE actually executes the query and reports both the optimizer's estimates and the real measured counters — actual time, actual rows, and loop count. Use it only when you can afford to run the query, and never against a destructive DML statement without a wrapping transaction you can roll back.

-- MySQL 8.0.18+ only — runs the query for real
EXPLAIN ANALYZE
SELECT o.id, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 100;

Reading the EXPLAIN Output — Column by Column

The tabular EXPLAIN output contains twelve columns. Most investigations focus on five: type, key, key_len, rows, and Extra. Understanding each in depth lets you read a plan at a glance.

type — The Access Method

The type column describes how MySQL accesses rows in each table. It is the fastest shorthand for plan quality. The full ordering from best to worst is: system, const, eq_ref, ref, fulltext, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, ALL.

key and key_len

The key column shows the index MySQL actually chose. The possible_keys column shows all candidates the optimizer considered. If key is NULL and possible_keys is also NULL, no index exists that could help this query.

key_len shows how many bytes of the chosen index are being used. This tells you how many columns of a composite index are in play. Understanding the byte math is essential for diagnosing partial index use:

• INT NOT NULL: 4 bytes
• INT NULL: 5 bytes (4 + 1 byte for the NULL flag)
• VARCHAR(255) NOT NULL with utf8mb4: 255 × 4 + 2 = 1,022 bytes
• VARCHAR(255) NULL with utf8mb4: 1,023 bytes
• DATETIME NOT NULL: 5 bytes; DATETIME NULL: 6 bytes
• TINYINT NOT NULL: 1 byte; CHAR(10) utf8mb4 NOT NULL: 40 bytes

If your composite index is (customer_id INT, status VARCHAR(20), created_at DATETIME) and key_len shows 5 bytes, only the first column (customer_id NOT NULL = 4 bytes, or 5 if nullable) is being used. A key_len of 9 means the first two columns are active (4 + 5 for a nullable TINYINT status equivalent).

rows and filtered

The rows column is the optimizer's estimate of how many rows it must examine to produce the result. On a JOIN plan with three tables, multiply the rows values across all join lines to get the total estimated row-examinations. This is the metric that reveals whether an index is dramatically reducing the search space.

The filtered column is expressed as a percentage and represents the estimated fraction of the rows examined that will survive the WHERE clause after the index filter has been applied. A rows: 50000 with filtered: 1.00 means the optimizer thinks only 500 rows will make it through the remaining conditions — the effective row count passed to the next join step is rows × (filtered/100).

Extra — The Detail Column

The Extra column contains supplementary information about how MySQL executes each step. The three values that demand immediate attention are:

• Using index: The query is satisfied entirely from the index without reading actual table rows. This is the best possible outcome — a covering index. No I/O to the clustered index is required.
• Using filesort: MySQL must perform an additional sort pass after retrieval because the ORDER BY columns do not match the index order. On small result sets this is fast; on millions of rows it can be catastrophic.
• Using temporary: MySQL created an internal temporary table to process the query, typically for GROUP BY or DISTINCT on columns that differ from the index order. Combined with Using filesort, this is the most expensive EXPLAIN signature.
• Using where: A WHERE filter is applied after index lookup, meaning not all conditions are handled by the index itself.
• Using index condition: Index Condition Pushdown (ICP) is active — range conditions are evaluated at the storage engine level before row fetches, reducing I/O.

The Most Dangerous EXPLAIN Values

EXPLAIN ANALYZE — Real Execution Stats (MySQL 8.0.18+)

EXPLAIN ANALYZE introduces iterator-model output that shows both estimated and actual statistics for every node in the execution tree. The format is a hierarchical text block rather than the familiar table.

EXPLAIN ANALYZE
SELECT o.id, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 50\G

A typical output section looks like this:

-> Limit: 50 row(s)  (actual time=12.451..12.453 rows=50 loops=1)
    -> Sort: o.created_at DESC  (actual time=12.440..12.443 rows=50 loops=1)
        -> Nested loop inner join  (actual time=0.182..11.821 rows=3842 loops=1)
            -> Filter: ((o.status = 'pending') and (o.created_at >= '2024-01-01'))
               (actual time=0.124..6.442 rows=3842 loops=1)
                -> Index range scan on o using idx_orders_status_date
                   (actual time=0.112..4.231 rows=3842 loops=1)
            -> Single-row index lookup on c using PRIMARY (id=o.customer_id)
               (actual time=0.001..0.001 rows=1 loops=3842)

Each node reports three key measurements: actual time=start..end (milliseconds to first and last row), rows (actual rows produced), and loops (how many times this node was invoked). The critical diagnostic signal is when actual rows diverges significantly from the optimizer's estimated rows — a large mismatch indicates stale statistics. Run ANALYZE TABLE orders to refresh them.

Cost estimates appear in the FORMAT=JSON output rather than ANALYZE, but together they answer two different questions: ANALYZE tells you what actually happened in wall-clock time; FORMAT=JSON tells you what the optimizer thought would happen in cost units before execution.

EXPLAIN FORMAT=JSON — Machine-Readable Deep Dive

The JSON format exposes fields that the tabular output cannot display. The most useful are cost_info, used_columns, and attached_condition.

EXPLAIN FORMAT=JSON
SELECT o.id, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id = 12345
  AND o.status = 'pending'\G

A simplified excerpt of the JSON output:

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "4.16"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "o",
          "access_type": "ref",
          "possible_keys": ["idx_customer_status", "idx_customer_id"],
          "key": "idx_customer_status",
          "key_length": "9",
          "used_key_parts": ["customer_id", "status"],
          "rows_examined_per_scan": 12,
          "rows_produced_per_join": 12,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.20",
            "eval_cost": "1.20",
            "prefix_cost": "2.40",
            "data_read_per_join": "3K"
          },
          "used_columns": ["id", "customer_id", "status", "total"],
          "attached_condition": "(`shop`.`o`.`status` = 'pending')"
        }
      }
    ]
  }
}

The query_cost value is an abstract optimizer cost unit, not a time measurement — but it is directly comparable between two plans for the same query. The lower-cost plan is what the optimizer will choose. The used_columns array tells you exactly which columns are being read from the table, which helps when designing covering indexes. The attached_condition shows any residual WHERE filter applied after the index lookup.

Common Query Anti-Patterns and Fixes

The following patterns appear repeatedly in slow query logs across MySQL deployments of all sizes. Each one causes the optimizer to silently abandon an otherwise usable index.

Functions on Indexed Columns

Wrapping an indexed column in a function makes the index unusable because the function transforms each column value before comparison, and the index stores the raw column values — not the function results.

-- BAD: YEAR() on indexed created_at forces a full scan
SELECT COUNT(*) FROM orders WHERE YEAR(created_at) = 2024;
-- EXPLAIN shows type=ALL, key=NULL

-- GOOD: rewrite as a range condition — index is used
SELECT COUNT(*) FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- EXPLAIN shows type=range, key=idx_created_at

-- BAD: function on join column
SELECT * FROM orders o
JOIN customers c ON LOWER(c.email) = LOWER(o.billing_email);

-- GOOD: normalize data at write time, or use a generated column with index
ALTER TABLE customers ADD COLUMN email_lower VARCHAR(255)
  GENERATED ALWAYS AS (LOWER(email)) STORED;
CREATE INDEX idx_email_lower ON customers(email_lower);

Leading Wildcard LIKE

A LIKE pattern beginning with % cannot use a B-tree index because the index is sorted by prefix — a trailing wildcard is unknown. The optimizer must scan every row and apply the pattern as a post-filter.

-- BAD: leading wildcard — full table scan
SELECT id, name FROM products WHERE name LIKE '%wireless%';
-- EXPLAIN: type=ALL, key=NULL, Extra=Using where

-- GOOD option 1: trailing wildcard only — uses the index
SELECT id, name FROM products WHERE name LIKE 'wireless%';

-- GOOD option 2: full-text search for genuine substring matching
ALTER TABLE products ADD FULLTEXT INDEX ft_name (name);
SELECT id, name FROM products WHERE MATCH(name) AGAINST ('wireless' IN BOOLEAN MODE);
-- EXPLAIN: type=fulltext

OR Conditions Defeating Indexes

An OR across different columns usually prevents index use unless the optimizer can apply index_merge. Even when index_merge is used, it is often less efficient than a redesigned query.

-- POTENTIALLY PROBLEMATIC: OR across different indexed columns
SELECT * FROM orders
WHERE customer_id = 100 OR carrier_id = 200;
-- EXPLAIN may show type=index_merge or type=ALL depending on selectivity

-- GOOD: UNION ALL to force separate index seeks per branch
SELECT * FROM orders WHERE customer_id = 100
UNION ALL
SELECT * FROM orders WHERE carrier_id = 200 AND customer_id != 100;
-- Each branch shows type=ref with its respective index

SELECT * with LIMIT on Large Tables

Fetching all columns forces MySQL to read the full row from the clustered index for every qualifying row, even when a covering index could have answered the query entirely from the index structure. The interaction with LIMIT is particularly dangerous on tables where the WHERE clause is poorly selective.

-- BAD: SELECT * defeats covering index, forces clustered index lookups
SELECT * FROM orders WHERE status = 'pending' LIMIT 20;

-- GOOD: select only needed columns — may enable covering index
SELECT id, customer_id, total, created_at
FROM orders WHERE status = 'pending' LIMIT 20;

-- WITH covering index: (status, id, customer_id, total, created_at)
-- EXPLAIN shows Extra=Using index (no table row access at all)

Implicit Type Conversions

When the column type and the comparison value type differ, MySQL applies an implicit cast that prevents index use. This is one of the most insidious anti-patterns because it produces no error — just silently degraded performance.

-- BAD: user_id is INT but value is a string — index abandoned
SELECT * FROM orders WHERE user_id = '12345';

-- BAD: comparing INT column to DECIMAL forces conversion
SELECT * FROM products WHERE price = 19;  -- price is DECIMAL(10,2)

-- BAD: date column vs string without explicit cast
SELECT * FROM orders WHERE created_at = '2024-03-15';
-- Actually fine for DATETIME if literal is unambiguous, but risky

-- GOOD: always match the column's data type exactly
SELECT * FROM orders WHERE user_id = 12345;
SELECT * FROM products WHERE price = 19.00;

-- Check for implicit conversions via EXPLAIN + SHOW WARNINGS
EXPLAIN SELECT * FROM orders WHERE user_id = '12345';
SHOW WARNINGS\G
-- Warning 1739: Cannot use ref access on index 'idx_user_id' due to type or
-- collation conversion on field 'user_id'

Index Design from EXPLAIN Findings

EXPLAIN output tells you not just what went wrong, but exactly what index would fix it. The methodology is straightforward once you know what to look for.

Composite index column order follows the "equality first, range last" rule. Columns used in equality conditions (col = value) must come before columns used in range conditions (col BETWEEN x AND y, col > value). If your query is WHERE status = 'pending' AND created_at >= '2024-01-01' ORDER BY created_at DESC, the optimal index is (status, created_at) — the equality column first, the range/sort column second.

-- Query to optimize
SELECT id, customer_id, total FROM orders
WHERE status = 'shipped'
  AND carrier_id = 5
  AND created_at BETWEEN '2024-01-01' AND '2024-03-31'
ORDER BY created_at DESC;

-- Optimal composite index: equality columns first, range column last
-- (also covers ORDER BY via index order)
CREATE INDEX idx_orders_ship_carrier_date
  ON orders (status, carrier_id, created_at);

-- After adding index, EXPLAIN should show:
-- type=range, key=idx_orders_ship_carrier_date
-- Extra=Using index condition  (no filesort)

Covering indexes are indexes that contain all columns the query needs to read, eliminating trips to the clustered index entirely. When EXPLAIN shows Extra: Using index, you have a covering index. Design them by listing all columns in SELECT, WHERE, JOIN ON, and ORDER BY clauses — put the WHERE/JOIN columns first, then the SELECT-only columns at the end of the index definition.

-- Query: fetch order summaries for a customer dashboard
SELECT id, status, total, created_at FROM orders
WHERE customer_id = 12345
ORDER BY created_at DESC
LIMIT 20;

-- Covering index: WHERE column, ORDER BY column, then SELECT-only columns
CREATE INDEX idx_orders_customer_covering
  ON orders (customer_id, created_at, id, status, total);

-- EXPLAIN now shows: type=ref, key=idx_orders_customer_covering
-- Extra=Using index  ← no table row access

Index hints (USE INDEX, IGNORE INDEX, FORCE INDEX) override the optimizer's choice. Use them sparingly and only as a last resort after verifying the statistics are fresh. USE INDEX suggests a preference but the optimizer can still ignore it; FORCE INDEX mandates the hint and disables the optimizer's cost model for that table. Hints embedded in application code become maintenance hazards as data distributions change over time.

-- USE INDEX: hint, optimizer can still override
SELECT * FROM orders USE INDEX (idx_created_at)
WHERE created_at >= '2024-01-01';

-- FORCE INDEX: mandatory — overrides optimizer cost model
SELECT * FROM orders FORCE INDEX (idx_customer_status)
WHERE customer_id = 12345 AND status = 'pending';

-- IGNORE INDEX: exclude a specific index from consideration
SELECT * FROM orders IGNORE INDEX (idx_status)
WHERE status = 'pending' AND created_at >= '2024-01-01';

EXPLAIN in Practice: A Complete Walkthrough

Let's trace a real optimization from first symptom to verified fix. The slow query log (slow_query_log=ON, long_query_time=1) flagged this query taking an average of 3.8 seconds, identified via pt-query-digest:

-- Original slow query (3.8s average, flagged in slow query log)
SELECT
  o.id,
  o.total,
  o.created_at,
  c.email,
  c.name AS customer_name,
  COUNT(oi.id) AS item_count
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status IN ('pending', 'processing')
  AND YEAR(o.created_at) = 2024
  AND c.country = 'US'
GROUP BY o.id, o.total, o.created_at, c.email, c.name
ORDER BY o.created_at DESC
LIMIT 50;

-- Step 1: Run EXPLAIN
EXPLAIN SELECT o.id, o.total, o.created_at, c.email, c.name,
  COUNT(oi.id) AS item_count
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status IN ('pending', 'processing')
  AND YEAR(o.created_at) = 2024
  AND c.country = 'US'
GROUP BY o.id, o.total, o.created_at, c.email, c.name
ORDER BY o.created_at DESC
LIMIT 50\G

-- EXPLAIN output (simplified):
-- id=1, table=o,  type=ALL, rows=40247891, key=NULL,    Extra=Using where; Using temporary; Using filesort
-- id=1, table=c,  type=eq_ref, rows=1,    key=PRIMARY,  Extra=Using where
-- id=1, table=oi, type=ref,  rows=4,      key=idx_order_id, Extra=NULL

The diagnosis is immediate: orders is doing a full table scan of 40 million rows because YEAR(o.created_at) = 2024 prevents index use. The Using temporary; Using filesort on top of a 40M-row scan explains the 3.8-second runtime.

-- Step 2: Apply the fixes

-- Fix 1: Replace YEAR() function with explicit range
-- Fix 2: Add composite index for the rewritten WHERE clause
CREATE INDEX idx_orders_status_date
  ON orders (status, created_at);

-- Also add index for customers.country if not present
CREATE INDEX idx_customers_country ON customers (country);

-- Rewritten query — no functions on indexed columns
SELECT
  o.id,
  o.total,
  o.created_at,
  c.email,
  c.name AS customer_name,
  COUNT(oi.id) AS item_count
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status IN ('pending', 'processing')
  AND o.created_at >= '2024-01-01'
  AND o.created_at < '2025-01-01'
  AND c.country = 'US'
GROUP BY o.id, o.total, o.created_at, c.email, c.name
ORDER BY o.created_at DESC
LIMIT 50;

-- Step 3: Verify with EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT o.id, o.total, o.created_at, c.email, c.name,
  COUNT(oi.id) AS item_count
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status IN ('pending', 'processing')
  AND o.created_at >= '2024-01-01'
  AND o.created_at < '2025-01-01'
  AND c.country = 'US'
GROUP BY o.id, o.total, o.created_at, c.email, c.name
ORDER BY o.created_at DESC
LIMIT 50\G

-- After fix:
-- type=range on orders (idx_orders_status_date), rows=12483 (was 40M)
-- No more: Using temporary; Using filesort
-- Execution time: 18ms (was 3,800ms)
-- Speedup: 211x

The key changes: the function removal reduced the examined row count from 40 million to 12,483. The index allowed the ORDER BY to be satisfied by index order, eliminating both the temporary table and the filesort pass.

Working with JusDB on MySQL Performance

JusDB's MySQL performance engineers run EXPLAIN analysis across your entire slow query log, identify index gaps, and implement fixes without downtime. Most teams see 60–80% query time reduction within the first week of engagement.

Explore JusDB MySQL Services →  |  Talk to a DBA

Related reading:

• MySQL Indexes: The Complete Guide
• MySQL Architecture Deep Dive
• ProxySQL Load Balancing Guide