Use EXPLAIN #

A query that looks ordinary can become a time bomb in production. When data is still in the thousands of rows, everything feels fast — but once it hits millions of rows with hundreds of concurrent users, a wrong query can bring the whole application to a crawl. The problem is, you can’t fix what you don’t understand. EXPLAIN is the command that lets you see how the database actually executes a query, not just what the result is. This article covers how to read EXPLAIN output, the most common problem patterns, and how to fix them with a data-driven approach — not guesswork.

Why Slow Queries Are Hard to Diagnose Without Tools #

Most engineers first notice a performance problem from user reports, not from monitoring. That alone shows a serious gap: the problem has existed for a long time but went undetected. Slow queries have characteristics that make them hard to track down without the right tools.

First, slow queries aren’t always consistent. In staging with small data, a query finishes in 2ms. In production with 5 million rows, the same query takes 8 seconds. The team blames the network, then blames the server, when the source of the problem is one line of SQL.

Second, ORMs hide the actual SQL. You write code that looks clean at the application level, but behind the scenes the ORM generates inefficient queries — and you never see them.

Third, the common causes of slow queries are very diverse:

Common Causes of Slow Queries
─────────────────────────────────────────────────────
  ✗ Full table scan — no index being used
  ✗ Functions on WHERE columns — the index goes unused too
  ✗ SELECT * — unneeded columns being pulled along
  ✗ JOIN without an index on the join column — huge nested loop
  ✗ ORDER BY without an index — filesort in memory/disk
  ✗ Unoptimized subqueries — executed repeatedly
  ✗ LIMIT without an index on the ORDER column — scans everything first

Without EXPLAIN, you’re only guessing at the cause. With EXPLAIN, the answer is visible directly.

What EXPLAIN Is and How to Use It #

EXPLAIN is the command that asks the database to explain the execution plan of a query — not run it, but explain what steps it will take, which indexes it will use, how many rows it will read, and in what order the operations will run.

Basic Syntax #

-- MySQL / MariaDB
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- PostgreSQL (table format)
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- PostgreSQL (more detailed format)
EXPLAIN (FORMAT JSON) SELECT * FROM orders WHERE user_id = 42;

EXPLAIN ANALYZE — Real Execution #

-- MySQL 8.0+
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

The important difference between EXPLAIN and EXPLAIN ANALYZE:

flowchart TD
    Explain["EXPLAIN"] --> e1["Planner estimates only"]
    Explain --> e2["Doesn't execute the query"]
    Explain --> e3["Safe to use in production"]

    ExplainAnalyze["EXPLAIN ANALYZE"] --> ea1["Actually executes the query"]
    ExplainAnalyze --> ea2["Shows actual vs estimated times"]
    ExplainAnalyze --> ea3["⚠ Be careful in production (the query still runs)"]

EXPLAIN ANALYZE with an UPDATE or DELETE query will really change data. Always wrap it in a transaction and roll back if unintended:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'expired';
ROLLBACK;

EXPLAIN Formats in MySQL vs PostgreSQL #

MySQL and PostgreSQL have different output formats, but the concepts are the same:

-- MySQL: tabular output
EXPLAIN SELECT id, name FROM users WHERE email = '[email protected]';
-- +----+-------------+-------+------+---------------+-------------+---------+-------+------+-------+
-- | id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra |

-- PostgreSQL: tree output
EXPLAIN SELECT id, name FROM users WHERE email = '[email protected]';
-- Index Scan using idx_users_email on users  (cost=0.43..8.45 rows=1 width=36)
--   Index Cond: ((email)::text = '[email protected]'::text)

Reading EXPLAIN Output — The Most Important Columns #

MySQL’s EXPLAIN output has several columns. You don’t need to memorize all of them — but there are four columns you must read every time you look at EXPLAIN output.

The type Column — How the Database Accesses Data #

This is the most critical column. type shows the access method the database uses to read data.

Order from WORST to BEST:
──────────────────────────────────────────────────────
  ALL      → Full table scan. Reads every row. Red flag.
  index    → Scans the entire index. Still bad on large tables.
  range    → Scans the index within a range. Decent.
  ref      → Reads using a non-unique index. Good.
  eq_ref   → Reads one row via a unique index in each JOIN. Good.
  const    → Reads exactly one row. Excellent.
  system   → The table has only one row. System-only.
──────────────────────────────────────────────────────
  Goal: always aim for ref, eq_ref, or const
  Warning: ALL on a table > 10,000 rows = a performance problem

The key Column — The Index Actually Used #

If key is NULL, no index was used for this query. The possible_keys column shows indexes that could be used, but key shows what’s actually used.

If possible_keys isn’t empty but key is NULL, the database decided a full scan is more efficient — this happens when the table is small, or the index selectivity is low (many duplicate values).

The rows Column — Estimated Rows Read #

Not the number of rows in the query result, but the number of rows the database must read to produce the answer. The smaller this number, the more efficient the query.

A query returning 1 row but having to read 500,000 rows to find it is a very inefficient query.

The Extra Column — Critical Additional Information #

Extra values to watch out for:
──────────────────────────────────────────────────────
  Using filesort    → Sorting done outside the index.
                      Means ORDER BY isn't leveraging an index.
                      Expensive on large data.

  Using temporary   → MySQL creates a temporary table.
                      Often appears in complex GROUP BY or DISTINCT.
                      Very expensive.

  Using where       → The WHERE filter happens after data is fetched.
                      Not always bad, but worth noting.

Good Extra values:
──────────────────────────────────────────────────────
  Using index       → The query can be answered from the index alone,
                      no need to read the main table (covering index).
                      Very efficient.

  Using index condition → Index condition pushdown active.
                          Filtering happens at the storage engine level.

Case 1: Full Table Scan Because There’s No Index #

This is the most classic and most frequently found case. The query looks simple, but because the filtered column has no index, the database must read the entire table.

The Problem #

-- Query: find a user by email
SELECT id, name, email FROM users WHERE email = '[email protected]';

EXPLAIN output:

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 482000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

Diagnosis: type = ALL, key = NULL, rows = 482000. The database reads almost half a million rows to find a single email.

The Solution #

-- ANTI-PATTERN: no index on the filtered column
SELECT id, name, email FROM users WHERE email = '[email protected]';
-- → type: ALL, rows: 482000, every request reads the entire table

-- CORRECT: add an index on the email column
CREATE INDEX idx_users_email ON users(email);

-- Now run the same query
SELECT id, name, email FROM users WHERE email = '[email protected]';
-- → type: ref, key: idx_users_email, rows: 1

EXPLAIN output after optimization:

+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | users | ref  | idx_users_email | idx_users_email | 767     | const |    1 | NULL  |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+

From 482,000 rows down to 1 row. The complexity changed from O(n) to O(log n).

Case 2: An Index Exists But Isn’t Used Because of a Function in WHERE #

This is a subtler trap, and more often unnoticed. You already have an index, you feel safe — but the query is still slow because the way the WHERE condition is written blocks the index from being used.

The Problem #

-- Query: get all orders on a specific date
SELECT id, user_id, total FROM orders WHERE DATE(created_at) = '2026-01-15';

EXPLAIN output:

+------+------+---------+------+-----------+-------------+
| type | key  | key_len | ref  | rows      | Extra       |
+------+------+---------+------+-----------+-------------+
| ALL  | NULL | NULL    | NULL | 1,280,000 | Using where |
+------+------+---------+------+-----------+-------------+

But created_at has an index! Why is key = NULL?

Why This Happens #

The created_at column has an index → ✓ But the query uses DATE(created_at)

What happens in the database:

flowchart TD
    Start["For every row:"] --> Step1["1. Get the created_at value"]
    Step1 --> Step2["2. Apply the DATE() function to that value"]
    Step2 --> Step3["3. Compare the result with '2026-01-15'"]

The index stores the original created_at value, not the result of DATE(created_at). The database can’t use an index for values that have been transformed. A function on the column = the index can’t be used.

The Solution #

-- ANTI-PATTERN: a function on the column kills the index
SELECT id, user_id, total
FROM orders
WHERE DATE(created_at) = '2026-01-15';
-- → type: ALL, rows: 1,280,000

-- CORRECT: rewrite the condition so the column isn't wrapped in a function
SELECT id, user_id, total
FROM orders
WHERE created_at >= '2026-01-15 00:00:00'
  AND created_at <  '2026-01-16 00:00:00';
-- → type: range, key: idx_orders_created_at, rows: 14200

The same rule applies to all other functions:

-- ANTI-PATTERN: other functions that kill the index
WHERE YEAR(created_at) = 2026              -- ✗
WHERE UPPER(name) = 'BUDI'                -- ✗
WHERE LENGTH(description) > 100           -- ✗
WHERE SUBSTRING(code, 1, 3) = 'INV'      -- ✗

-- CORRECT: rewrite without functions on the column
WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31'  -- ✓
WHERE name = 'Budi'  -- (use a case-insensitive collation)  -- ✓
WHERE code LIKE 'INV%'                                      -- ✓

PostgreSQL supports functional indexes that let you index the result of a function:

CREATE INDEX idx_orders_date ON orders (DATE(created_at));

With this, WHERE DATE(created_at) = '2026-01-15' can leverage the index. But the more universal approach remains avoiding functions on WHERE columns.

Case 3: Slow JOIN Because the Join Column Isn’t Indexed #

JOIN queries are where performance problems most often hide — and also the hardest to spot without EXPLAIN. Because JOINs involve multiple tables, one missing index can result in a huge nested loop.

The Problem #

-- Query: get orders with user names, filtered by country
SELECT o.id, o.total, u.name, u.country
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'ID'
ORDER BY o.created_at DESC
LIMIT 20;

EXPLAIN output (simplified):

+----+-------+-------+------+---------------+------+------+------------------+
| id | table | type  | key  | possible_keys | rows | ref  | Extra            |
+----+-------+-------+------+---------------+------+------+------------------+
|  1 | u     | ALL   | NULL | PRIMARY       | 85000| NULL | Using where;     |
|    |       |       |      |               |      |      | Using temporary; |
|    |       |       |      |               |      |      | Using filesort   |
+----+-------+-------+------+---------------+------+------+------------------+
|  1 | o     | ALL   | NULL | NULL          | 1.2M | NULL | Using where      |
+----+-------+-------+------+---------------+------+------+------------------+

Three red flags at once: type: ALL on both tables, Using temporary, and Using filesort.

Why This Happens #

The execution without indexes:

flowchart TD
    Step1["1. Scan the entire users table (85,000 rows)"] --> Filter1["Filter WHERE country = 'ID'<br>(12,000 users in Indonesia)"]
    Filter1 --> Step2["2. For every Indonesian user, scan the entire orders table (1.2 million rows)"]
    Step2 --> Filter2["Filter ON o.user_id = u.id<br>(12,000 x 1,200,000 = 14.4 BILLION comparisons)"]
    Filter2 --> Step3["3. Sort the results in a temporary table (Using filesort)"]
    Step3 --> Step4["4. Take the top 20 rows"]

The Solution #

-- ANTI-PATTERN: join without indexes on the filter and join columns
SELECT o.id, o.total, u.name, u.country
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'ID'
ORDER BY o.created_at DESC;
-- → type: ALL on both tables, huge nested loop

-- STEP 1: index the filtered column (WHERE)
CREATE INDEX idx_users_country ON users(country);

-- STEP 2: index the join column on the orders table
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- STEP 3: index the ORDER BY column
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- CORRECT: the same query, now leveraging all indexes
SELECT o.id, o.total, u.name, u.country
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'ID'
ORDER BY o.created_at DESC
LIMIT 20;
-- → type: ref on both tables, rows drastically reduced

EXPLAIN output after optimization:

+----+-------+--------+------------------+------+------+----------------+
| id | table | type   | key              | rows | ref  | Extra          |
+----+-------+--------+------------------+------+------+----------------+
|  1 | u     | ref    | idx_users_country|12000 | const| Using index    |
|  1 | o     | ref    | idx_orders_user_id|  8  | u.id | Using filesort |
+----+-------+--------+------------------+------+------+----------------+

Using filesort still appears, but now it operates on a far smaller dataset — not on 1.2 million raw rows.

Case 4: Unnecessary SELECT * #

SELECT * feels convenient during development, but in production it can become an unnecessary burden — especially if the table has columns with large data types like TEXT, BLOB, or JSON.

The Problem and Solution #

-- ANTI-PATTERN: SELECT * pulls every column including unused ones
SELECT * FROM products WHERE category_id = 5;
-- Pulls: id, name, description (10KB TEXT), image_url, price, stock,
--         metadata (JSON), created_at, updated_at, deleted_at, ...
-- But we only need id, name, price

-- CORRECT: select only the columns truly needed
SELECT id, name, price FROM products WHERE category_id = 5;

-- Bonus benefit: a covering index becomes possible
-- If an index (category_id, id, name, price) exists, the query can be answered
-- from the index alone without reading the main table → Extra: Using index

Besides performance, SELECT * is also dangerous for maintainability: the query will automatically pick up new columns added to the table in the future, which can change application behavior unexpectedly.

Case 5: ORDER BY Without the Right Index #

Sorting is an expensive operation when it can’t leverage an index. EXPLAIN will show Using filesort — this doesn’t mean the sort happens on disk, but that sorting happens outside the index.

The Problem and Solution #

-- ANTI-PATTERN: ORDER BY on a column without an index
SELECT id, name, created_at FROM articles
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 10;
-- If no index covers (status, created_at):
-- → Extra: Using where; Using filesort

-- CORRECT: create a composite index covering both WHERE and ORDER BY
CREATE INDEX idx_articles_status_created ON articles(status, created_at DESC);

-- Now the database can scan the index directly, already in the right order
-- → Extra: Using index condition (no more filesort)

The column order in a composite index matters: the column used in WHERE (equality) must come first, followed by the column used in ORDER BY.

Composite index rules for WHERE + ORDER BY:
──────────────────────────────────────────────────────
  WHERE columns (equality)   → first position
  WHERE columns (range)      → position before ORDER BY
  ORDER BY columns           → last position

Example:
  WHERE status = 'published' AND created_at > '2026-01-01'
  ORDER BY created_at DESC

  The right index: (status, created_at)
  Not: (created_at, status)

Anti-Patterns to Avoid #

After understanding how EXPLAIN works, here are the most common anti-patterns to always check during query reviews:

-- ✗ Anti-pattern 1: SELECT * on tables with large columns
SELECT * FROM users WHERE id = 42;
-- ✓ Solution: select the needed columns
SELECT id, name, email FROM users WHERE id = 42;

-- ✗ Anti-pattern 2: functions on WHERE columns
SELECT * FROM logs WHERE DATE(created_at) = CURDATE();
-- ✓ Solution: use a range without functions on the column
SELECT * FROM logs WHERE created_at >= CURDATE() AND created_at < CURDATE() + INTERVAL 1 DAY;

-- ✗ Anti-pattern 3: LIKE with a leading wildcard
SELECT * FROM products WHERE name LIKE '%keyboard%';
-- ✓ Solution: use Full-Text Search for keyword searches
SELECT * FROM products WHERE MATCH(name) AGAINST('keyboard' IN BOOLEAN MODE);

-- ✗ Anti-pattern 4: NOT IN with a large subquery
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned_users);
-- ✓ Solution: use LEFT JOIN ... IS NULL
SELECT u.* FROM users u
LEFT JOIN banned_users b ON u.id = b.user_id
WHERE b.user_id IS NULL;

-- ✗ Anti-pattern 5: OR across different columns without separate indexes
SELECT * FROM orders WHERE status = 'pending' OR user_id = 42;
-- ✓ Solution: use UNION if both columns need different indexes
SELECT * FROM orders WHERE status = 'pending'
UNION
SELECT * FROM orders WHERE user_id = 42;

An Effective EXPLAIN Workflow #

Reading EXPLAIN is a skill — the more often you do it, the faster you identify problems. Here’s a workflow you can apply regularly:

Query Optimization Workflow with EXPLAIN:

flowchart TD
    Step1["1. Identify slow queries<br>(slow query log / pg_stat_statements)"] --> Step2["2. Run EXPLAIN<br>(Look at type, key, rows, Extra)"]
    Step2 --> Step3["3. Hypothesize<br>(type = ALL, functions in WHERE, filesort)"]
    Step3 --> Step4["4. Apply the fix (index / query rewrite)"]
    Step4 --> Step5["5. Run EXPLAIN again<br>(Verify type & rows)"]
    Step5 --> Step6["6. Test in staging with realistic data"]
    Step6 --> Step7["7. Deploy and monitor"]

Query Review Checklist #

Use this checklist every time you write or review a query touching large tables:

BEFORE EXECUTION:
  □ EXPLAIN already run?
  □ No type = ALL on tables > 10,000 rows?
  □ WHERE columns have indexes?
  □ No functions wrapping WHERE columns?
  □ JOIN columns indexed on both sides?
  □ SELECT only takes the needed columns?

ORDER BY AND PAGINATION:
  □ ORDER BY columns indexed?
  □ Composite indexes in the right order?
  □ For cursor-based pagination: the cursor column indexed?

EXPLAIN OUTPUT:
  □ Extra doesn't contain Using temporary?
  □ Extra doesn't contain Using filesort on large datasets?
  □ Estimated rows reasonable (not millions for simple queries)?
  □ Key shows the right index, not NULL?

Summary #

• EXPLAIN isn’t optional — every query touching large production tables must have been checked with EXPLAIN at least once.
• type = ALL on a large table is a red flag — always look for ways to turn it into ref, range, or const by adding the right index.
• Functions on WHERE columns kill indexes — rewrite conditions so columns aren’t wrapped in functions; use ranges or direct conditions instead.
• JOIN columns must be indexed on both sides — the index on the join column of the “many” table is often forgotten, causing very expensive nested loops.
• Using filesort and Using temporary are high-cost signals — fix them with a composite index covering the WHERE and ORDER BY columns in the right order.
• SELECT * is a bad habit — select only the needed columns; this enables covering indexes and significantly reduces I/O.
• EXPLAIN ANALYZE actually executes the query — use it carefully in production, always wrapping data-changing queries in a transaction.
• Data-driven optimization, not assumptions — performance measured with EXPLAIN is far more reliable than intuition or experience from other databases.

← Previous: Sharding   Next: Type on Join →