Eleven Seconds to Eighty Milliseconds: Reading MySQL EXPLAIN Like a Pro

It's 2 a.m. and your phone is buzzing. The order dashboard, which loaded in 80 milliseconds last spring, now takes eleven seconds. Nothing in the code changed. What changed is the table: it went from fifty thousand rows to twelve million, and the database that used to cope by brute force has run out of brute.

If you've been there, you already know the fix usually isn't "more hardware." It's an index and, more importantly, knowing whether MySQL is actually using it. This guide shows you how to read EXPLAIN in MySQL 8, spot the handful of patterns that cause most slow queries, and fix them without guessing.

A query that scans the whole table isn't slow because MySQL is lazy. It's slow because you never told it where to look.

What an index really is (and what it costs)

An index in InnoDB is a sorted B-tree copy of one or more columns, with a pointer back to the full row. Looking up a value in a sorted tree takes a handful of steps, even with millions of rows. Without an index, MySQL has to read every row and check it: a full table scan.

Think of a phone book. Finding "Martinez" is instant because the book is sorted by last name. Finding everyone whose phone number ends in 4417 means reading the entire book. The sort order is the index, and it only helps for the question it was built to answer.

Indexes aren't free. Every INSERT, UPDATE and DELETE has to maintain each index on the table, and every index takes disk space and buffer pool memory. So the goal isn't "index everything." It's index what your slowest, most frequent queries filter, join and sort on.

Reading EXPLAIN without panic

Put EXPLAIN in front of any SELECT and MySQL shows its plan instead of running the query. Here's a sample table we'll use throughout (values are made up):

CREATE TABLE orders (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  status      VARCHAR(20)  NOT NULL,
  total       DECIMAL(10,2) NOT NULL,
  created_at  DATETIME     NOT NULL
) ENGINE=InnoDB;

EXPLAIN SELECT * FROM orders WHERE customer_id = 4821;

The output has many columns, but five do most of the talking:

On our unindexed table you'd see type: ALL, key: NULL and rows close to the table size. That is the 2 a.m. signature.

Fix #1: Add an index that matches the filter

The cure for the query above is one line:

ALTER TABLE orders ADD INDEX idx_customer (customer_id);

Run the same EXPLAIN again. You should now see type: ref, key: idx_customer, and rows dropping from millions to a few dozen. In practice, that's the difference between seconds and milliseconds.

A tip for production: on MySQL 8, adding an index to an InnoDB table is an online operation in most cases, so reads and writes can continue. Still, test on a copy first, and run big changes off-peak. Building an index on a hundred-million-row table is real work for the server.

Fix #2: Composite indexes and the leftmost-prefix rule

Real queries rarely filter on one column. Suppose your dashboard runs this:

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

A single-column index on customer_id helps, but MySQL must still pick out the paid rows and then sort them. That's where Using filesort shows up in Extra. A well-designed composite index handles filtering and ordering in one pass:

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

Column order matters, and here's the rule of thumb: put equality-filtered columns first, then the column you sort or range-filter on last. MySQL can use an index from its left edge only, the leftmost prefix. This index serves queries on customer_id, on customer_id + status, and on all three. It does not help a query that filters on status alone, because status isn't the leftmost column.

Order the columns of a composite index like you'd order a dictionary: first by what you always know, then by what you want sorted.

With the new index, EXPLAIN should show key: idx_cust_status_created and no Using filesort. MySQL walks the index in order and stops after 20 rows, which is why LIMIT queries get so fast with the right index.

Fix #3: Stop hiding columns from the index

Sometimes the index exists but MySQL refuses to use it. The usual culprit is wrapping the indexed column in a function:

-- Slow: the function hides created_at from the index
SELECT * FROM orders WHERE DATE(created_at) = '2026-10-01';

-- Fast: a range the index can follow
SELECT * FROM orders
WHERE created_at >= '2026-10-01'
  AND created_at <  '2026-10-02';

The same trap appears with arithmetic (WHERE total + 0 > 100), with leading wildcards (LIKE '%smith'), and with mismatched types, such as comparing a string column to a bare number, which forces implicit conversion. The pattern is always the same: keep the bare column on one side of the comparison and put the computation on the other.

If you truly must search on an expression, MySQL 8.0.13 and later supports functional index parts, so you can index (DATE(created_at)) directly. Treat that as a fallback, though. Rewriting the condition is usually simpler.

Measuring instead of hoping: EXPLAIN ANALYZE

EXPLAIN shows the plan and an estimate. Since MySQL 8.0.18 you can also run EXPLAIN ANALYZE, which executes the query and reports real row counts and timings for each step:

EXPLAIN ANALYZE
SELECT id, total FROM orders
WHERE customer_id = 4821 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

Compare the estimated rows with the actual rows. A big gap means the optimizer's statistics are stale, and running ANALYZE TABLE orders; often corrects it. Because it really runs the query, be careful using it on heavy UPDATE or DELETE-style statements; stick to SELECT while learning.

A safer way to remove indexes

Over time, tables collect indexes nobody uses, each one taxing every write. MySQL 8 gives you a safety net: invisible indexes.

ALTER TABLE orders ALTER INDEX idx_customer INVISIBLE;

The optimizer ignores an invisible index, but MySQL still maintains it. Watch your slow query log for a week. If nothing regresses, drop it for good. If something does, flip it back with VISIBLE instantly, with no expensive rebuild.

You can find candidates through the sys schema, for example SELECT * FROM sys.schema_unused_indexes;. Remember that usage counters reset on server restart, so check after a representative period of uptime.

A quick checklist you can reuse

• Run EXPLAIN on the slowest query and check type, key, rows and Extra.
• Index columns used in WHERE, JOIN ... ON and ORDER BY, equality columns first.
• Keep indexed columns bare: no functions, no leading wildcards, matching data types.
• Verify with EXPLAIN ANALYZE, and refresh statistics with ANALYZE TABLE if estimates look off.
• Retire unused indexes via INVISIBLE before dropping them.

Wrapping up

Most slow-query emergencies come down to the same story: MySQL was asked a question the table wasn't organized to answer. EXPLAIN tells you exactly where it's guessing, and a well-placed index, designed around your real query, turns eleven seconds back into eighty milliseconds.

Start with just one slow query this week. Read its plan, change one thing, read the plan again. Do that a few times and the 2 a.m. calls get a lot rarer. Happy tuning.