--- title: "Slow Query? How to Read EXPLAIN and Put Indexes in the Right Place" url: https://phatdev.net/en/blog/mysql-index-explain lang: en updated: 2026-10-21 alternate: vi: https://phatdev.net/vi/blog/mysql-index-explain markdown: https://phatdev.net/en/blog/mysql-index-explain.md --- # Slow Query? How to Read EXPLAIN and Put Indexes in the Right Place > Use EXPLAIN to see what the database plans to do with a slow query, then order index columns correctly, avoid the mistakes that hide an index, and paginate deep without slowing down. Categories: Backend | Published: 2026-10-19 | Level: Intermediate | 8 min read Tags: mysql, mariadb, index, explain, analyze, covering-index, keyset-pagination, performance The order list page of a shop takes several seconds to open. Someone says "add an index". You add one, and the page is still slow. Or worse: the page speeds up, but saving an order starts to drag. The trouble is that most people add an index without looking at what the database plans to do with the query. This post teaches that look, with a command called `EXPLAIN`. To avoid guessing, I built an orders table with 300,000 rows on MariaDB 11.8, ran every example, and read the real results. The ideas apply to MySQL 8 as well. ![An index tree: walk from the root down to exactly one leaf instead of scanning every row](https://phatdev.net/uploads/blog/mysql-index-explain/index-tree-1920.webp) ## 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: ```sql 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: ```sql SELECT COUNT(*) FROM orders WHERE status = 4 AND created_at >= '2026-01-01' AND created_at < '2026-07-01'; ``` | Index | type | Estimated rows | Time | |---|---|---|---| | none | ALL | 298,782 | 123 ms | | `(created_at, status)` | range | 161,860 | 34 ms | | `(status, created_at)` | range | 7,328 | 4.5 ms | 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: ```sql -- 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". ![A stack of row slabs with a beam jumping straight to a violet marker slab in the middle](https://phatdev.net/uploads/blog/mysql-index-explain/keyset-1920.webp) ```sql 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](https://phatdev.net/en/blog/redis-cache-aside-api), 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](https://phatdev.net/en/blog/rest-api-design-practical). ## References - [MariaDB: EXPLAIN](https://mariadb.com/kb/en/explain/) - [MariaDB: ANALYZE statement](https://mariadb.com/kb/en/analyze-statement/) - [MySQL 8.4 Reference Manual: Optimization and Indexes](https://dev.mysql.com/doc/refman/8.4/en/optimization-indexes.html) - [MySQL 8.4 Reference Manual: EXPLAIN output format](https://dev.mysql.com/doc/refman/8.4/en/explain-output.html) - [MySQL 8.4 Reference Manual: Multiple-column indexes](https://dev.mysql.com/doc/refman/8.4/en/multiple-column-indexes.html) - [MySQL 8.4 Reference Manual: Clustered and secondary indexes (InnoDB)](https://dev.mysql.com/doc/refman/8.4/en/innodb-index-types.html) ## Related posts - [Make Your API Faster With Redis: Save Answers So the Database Gets a Break](https://phatdev.net/en/blog/redis-cache-aside-api.md) - [Designing an API Other People Will Use: Errors, Paging, Safe Retries and Limits](https://phatdev.net/en/blog/rest-api-design-practical.md) - [Packing Many On/Off Switches Into One Number: Bitmasks for Permissions and IPs](https://phatdev.net/en/blog/bitwise-in-practice.md)