Slow Query? How to Read EXPLAIN and Put Indexes in the Right Place

An index is like the index of a book

You need to find the word "Redis" in a 300-page book. The slow way: turn every page from the start. The fast way: open the index at the back, see "Redis: page 211", and go straight there. A database index is that same back-of-the-book list, for a table.

The database can read all 300,000 rows to find one customer (a full table scan), or follow the index straight to the spot. On a small table the gap is barely visible. On a big one it is enormous.

The index has a price too. Every time an order is added or changed, the database must update the index as well. The more indexes, the slower the writes. So the goal is not "index every column" but "exactly the indexes your real queries use".

EXPLAIN asks the database "what are you about to do?"

Put EXPLAIN in front of a SELECT and the database does not run it, it describes its plan. Four things to read:

• type: how it searches. ALL means reading the whole table (bad). ref and range mean following an index (good).
• key: the index it picked. NULL means no index.
• rows: how many rows it expects to read.
• Extra: notes. Using filesort means extra sorting outside the index. Using index means the answer came entirely from the index, which is great.

The rows figure is only a guess, and sometimes a very wrong one. So I always follow up with ANALYZE (MariaDB; MySQL 8 has EXPLAIN ANALYZE), which runs the query for real and reports the rows actually read and the time. The pagination section below has an example of why.

How to do it

1. Find the genuinely slow query from your app or the slow query log. Do not optimize by feel.
2. Run EXPLAIN and read type, key, rows, Extra. Then run ANALYZE for the real numbers.
3. Fix the query first, because it costs nothing: remove functions wrapped around columns, compare with the right data type.
4. If you still need an index, add one. For several conditions: equality columns (=) first, range columns (>, <) next, and columns used only for sorting last.
5. Run ANALYZE again to confirm the index is really used, and remember each new index slows writes a little.
6. Test with data close to real. On a table of a few hundred rows everything is fast.

For readers who write code

My test table:

CREATE TABLE orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  status      TINYINT UNSIGNED NOT NULL,   -- 0..4, status 0 is half the rows
  total       DECIMAL(10,2) NOT NULL,
  created_at  DATETIME NOT NULL,           -- spread over 2 years
  email       VARCHAR(100) NOT NULL,
  phone       VARCHAR(20) NOT NULL
) ENGINE=InnoDB;

300,000 rows, 20,000 customers. All timings below come from ANALYZE (the fastest of three runs, with data already in memory), so compare them with each other, not with your own machine.

One column, one index

The query SELECT id, total FROM orders WHERE customer_id = 3396 with no index: type=ALL, all 300,000 rows read, about 110 ms. After ALTER TABLE orders ADD INDEX idx_customer (customer_id): type=ref, 32 rows read, about 0.1 ms. That is roughly a thousand times faster with one column and one statement.

Column order in a multi-column index

Counting status-4 orders in the first half of 2026:

SELECT COUNT(*) FROM orders
WHERE status = 4 AND created_at >= '2026-01-01' AND created_at < '2026-07-01';

Same data, same query, and only the column order changed, for nearly an 8x difference. The reason: an index with the date range first forces the database to read every entry in that range and then filter by status. With status first it jumps straight to the right group. Because an index is sorted by its first column, (status, created_at) also does not help a query that filters only on created_at: I tried it and EXPLAIN showed type=ALL with Using filesort, about 130 ms.

An index that holds the whole meal

If every column the query needs is already in the index, the database never touches the table (Extra says Using index). The query SELECT customer_id, created_at, total ... WHERE customer_id = 3396 ORDER BY created_at with an index on (customer_id) shows Using filesort, about 0.15 ms. With (customer_id, created_at, total) it is only Using index, about 0.03 ms. Use this for hot, narrow queries only, because every extra column makes the index bigger.

When an index turns invisible

These mistakes stop the database from using an index that exists:

-- A function wrapped around the column: ALL, about 249 ms
SELECT id, total FROM orders WHERE DATE_FORMAT(created_at, '%Y-%m-%d') = '2026-03-03';
-- Rewritten as a range: range, 398 rows, about 1.5 ms
SELECT id, total FROM orders WHERE created_at >= '2026-03-03' AND created_at < '2026-03-04';

The rule is to leave the bare column on one side and move the calculation to the other. The second mistake is a type mismatch: phone is a string, and WHERE phone = 912345678 (a number, no quotes) forces the database to convert every value to a number before comparing, about 166 ms. With quotes, '0912345678', it takes about 0.02 ms. The two statements look almost identical. The third is a leading %: LIKE 'user12345%' follows the index in 0.04 ms, while LIKE '%12345@example.com' has to read the whole index, about 253 ms. An index is sorted from the start of the text, so it cannot search by the ending.

Deep pagination

LIMIT 20 OFFSET 250000 does not jump to row 250,000: the database still reads and discards the first 250,000. I measured about 164 ms with Using filesort, and it gets worse the deeper you go. The alternative is keyset pagination: instead of counting rows, say "give me the rows after the last row of the previous page".

SELECT id, total, created_at FROM orders
WHERE created_at < '2025-05-02 22:39:43'
   OR (created_at = '2025-05-02 22:39:43' AND id < 251509)
ORDER BY created_at DESC, id DESC
LIMIT 20;

With an index on (created_at, id): type=range, exactly 20 rows read, about 0.1 ms, over a thousand times faster at the same position. You need the extra id column because many rows can share a created_at; without it you lose or repeat rows at the page boundary. The trade-off is that keyset cannot jump straight to "page 5000". It suits infinite scroll and a "next page" button.

Mistakes I keep seeing

Some people add an index to every column "just in case". The result is slow writes, a bloated disk, and an optimizer that struggles to pick the right one. If both (a) and (a, b) exist, the first is usually redundant.

Another is putting an index on a column with only a few distinct values, like status or a true/false flag, then wondering why the database ignores it. Such a column helps only as the first column of a multi-column index, always paired with another condition.

The most common is trusting the rows column without running ANALYZE. I once tried pagination with a row comparison, WHERE (created_at, id) < (..., ...). On MariaDB 11.8, EXPLAIN said rows=20, but ANALYZE showed it really read 250,020 rows and took over half a second, worse than OFFSET. The OR form above is the safe one.

What to do next

Take your slowest query, run EXPLAIN and then ANALYZE, and read the type column. If you see ALL, look for a function around a column or a type mismatch before you think about an index. And if the query is still costly after all that, put a short-term memory layer in front of it, as in Make Your API Faster With Redis: Save Answers So the Database Gets a Break, which is often cheaper than more tuning. Keyset pagination is also how APIs should return lists; see Designing an API Other People Will Use: Errors, Paging, Safe Retries and Limits.