How to read MySQL's EXPLAIN: possible_keys, key, Extra and the column order of an index

Early in my career as a DBA I struggled a lot with slow queries. I could see that a query was taking too long, but I didn't know how to work out what could be optimized in it. Over time I learned to read EXPLAIN, which is how you ask the database engine how it plans to run a query before running it, and this entry is what I wish someone had explained to me back then.

Unlike the previous one, this entry is about MySQL only. MariaDB, which was born from MySQL, returns almost the same table, and I point out where it differs.

The example tables

Two hundred thousand orders from five thousand customers: 90% delivered, 7% shipped and 3% pending, with dates spread between 2022 and 2025. The rows come from a series generated with a recursive CTE, which I explained in the entry on how to fill a table with test data:

CREATE TABLE clientes (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    pais   CHAR(2)      NOT NULL
);

CREATE TABLE pedidos (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT           NOT NULL,
    estado     VARCHAR(20)   NOT NULL,
    fecha      DATE          NOT NULL,
    total      DECIMAL(10,2) NOT NULL
);

SET SESSION cte_max_recursion_depth = 200000;

INSERT INTO clientes (nombre, pais)
WITH RECURSIVE serie (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM serie WHERE n < 5000
)
SELECT CONCAT('Cliente ', n), ELT(1 + n % 5, 'CR', 'MX', 'ES', 'AR', 'CO')
FROM serie;

INSERT INTO pedidos (cliente_id, estado, fecha, total)
WITH RECURSIVE serie (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM serie WHERE n < 200000
)
SELECT 1 + FLOOR(RAND() * 5000),
       CASE WHEN n % 100 < 90 THEN 'entregado'
            WHEN n % 100 < 97 THEN 'enviado'
            ELSE 'pendiente' END,
       DATE '2022-01-01' + INTERVAL FLOOR(RAND() * 1400) DAY,
       ROUND(RAND() * 1000, 2)
FROM serie;

In MariaDB the variable in the SET is called max_recursive_iterations.

For now pedidos only has its primary key. I create the indexes along the way, to see what each one changes in the plan. The data is random, so if you repeat the examples, your rows numbers won't match mine exactly; the outputs are from MySQL 8.0.

Asking MySQL for the plan

All it takes is writing EXPLAIN in front of the SELECT:

EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 1234;

+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | pedidos | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 200000 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+

EXPLAIN doesn't run the query: it returns the plan, with one row for each table the query reads. What each column says:

• id and select_type: which SELECT the row belongs to. They matter when there are subqueries or a UNION; in a simple query they are 1 and SIMPLE.
• table: the table, or its alias.
• type: how the table is accessed. From best to worst, the ones you see most are const (a single row, by key), eq_ref (one row for each row of the previous table), ref (the rows with one value of the index), range (a stretch of the index), index (the whole index) and ALL (the whole table).
• possible_keys and key: the indexes that could help and the one it chose. They get their own section below.
• key_len: how many bytes of the index it uses; with a multi-column index it tells you how many columns it used.
• ref: what the index is compared with: a constant (const) or a column of another table.
• rows: how many rows it expects to examine. It's an estimate based on the table's statistics, not a count.
• filtered: what percentage of those rows it expects to survive the WHERE.
• Extra: whatever doesn't fit in the other columns: whether it filters, whether it sorts, whether it creates a temporary table.

In this case, type is ALL, key is empty and rows is 200,000: to return one customer's 40 orders, MySQL is going to read all two hundred thousand.

If your MySQL returns a text tree instead of this table, the explain_format variable (available since MySQL 8.0.32) is set to TREE; the most recent MySQL in my testbed comes that way by default. EXPLAIN FORMAT=TRADITIONAL asks for the table.

possible_keys and key: having indexes isn't using them

These are the two columns that took me longest to understand. possible_keys are the indexes the optimizer considers for the query; key is the one it is actually going to use. I can have ten indexes in possible_keys, and if none shows up in key, the query uses no index.

There are two reasons for key to come out empty. The first is that no index helps, and then possible_keys is empty too, as in the example above. The second is that there is one and the optimizer decides not to use it. To see it, I create an index on the date:

CREATE INDEX idx_fecha ON pedidos (fecha);

One month's orders use it:

EXPLAIN SELECT * FROM pedidos
WHERE fecha >= '2025-10-01' AND fecha < '2025-11-01';

+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| range | idx_fecha     | idx_fecha | 3       | NULL | 4399 |   100.00 | Using index condition |
+-------+---------------+-----------+---------+------+------+----------+-----------------------+

A whole year's don't:

EXPLAIN SELECT * FROM pedidos
WHERE fecha >= '2024-01-01' AND fecha < '2025-01-01';

+------+---------------+------+---------+------+--------+----------+-------------+
| type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+------+---------------+------+---------+------+--------+----------+-------------+
| ALL  | idx_fecha     | NULL | NULL    | NULL | 200000 |    50.00 | Using where |
+------+---------------+------+---------+------+--------+----------+-------------+

idx_fecha is in possible_keys, but key is empty and type is back to ALL. A year is a quarter of the orders, and reading a quarter of the table by jumping around, looking up each row from the index, costs more than reading the whole table in order. It isn't a bug: it's a decision, and sometimes it's the right one. Look at filtered too: it estimates 50% when the real count is 26%. These are estimates, and they can be wrong.

The most common trap is a different one: having the index and writing the condition in a way that can't use it. The same October orders, asked for by year and month:

EXPLAIN SELECT * FROM pedidos
WHERE YEAR(fecha) = 2025 AND MONTH(fecha) = 10;

+------+---------------+------+---------+------+--------+----------+-------------+
| type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+------+---------------+------+---------+------+--------+----------+-------------+
| ALL  | NULL          | NULL | NULL    | NULL | 200000 |   100.00 | Using where |
+------+---------------+------+---------+------+--------+----------+-------------+

Now possible_keys is empty. The index stores the dates in order, not their years or months, and to know which dates satisfy YEAR(fecha) = 2025 MySQL has to compute it row by row. The same happens with any function or arithmetic on the column: DATE(fecha), LOWER(email), total * 1.13. The way out is to write the condition as a range on the column, as in the first October example.

The Extra column

It's the last column, and the one that says the most when everything else looks fine. The values I run into most:

• Using where: the WHERE is applied to the rows after reading them. With type at ALL, it means it reads the whole table and discards what doesn't match.
• Using index: everything the query asks for is inside the index, so it never reads the table. It's the best thing that can show up here.
• Using index condition: part of the WHERE is checked inside the index, before fetching the row.
• Using filesort: it has to sort the result separately, because it doesn't read it already sorted from an index. The name is misleading: it doesn't mean it writes to disk.
• Using temporary: it creates a temporary table to resolve the query, typically with a GROUP BY and an ORDER BY on different columns.

Counting October's orders is resolved without touching the table, because the date is in the index and nothing else is needed:

EXPLAIN SELECT COUNT(*) FROM pedidos
WHERE fecha >= '2025-10-01' AND fecha < '2025-11-01';

+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| range | idx_fecha     | idx_fecha | 3       | NULL | 4399 |   100.00 | Using where; Using index |
+-------+---------------+-----------+---------+------+------+----------+--------------------------+

One day's orders, from most to least expensive, are read through the date index but have to be sorted by total:

EXPLAIN SELECT * FROM pedidos
WHERE fecha = '2024-05-10'
ORDER BY total DESC;

+------+---------------+-----------+---------+-------+------+----------+----------------+
| type | possible_keys | key       | key_len | ref   | rows | filtered | Extra          |
+------+---------------+-----------+---------+-------+------+----------+----------------+
| ref  | idx_fecha     | idx_fecha | 3       | const |  124 |   100.00 | Using filesort |
+------+---------------+-----------+---------+-------+------+----------+----------------+

And the ten customers who spent the most in October need both: a temporary table to add up per customer and a sort by the sum:

EXPLAIN SELECT cliente_id, SUM(total) AS gastado
FROM pedidos
WHERE fecha >= '2025-10-01' AND fecha < '2025-11-01'
GROUP BY cliente_id
ORDER BY gastado DESC
LIMIT 10;

+-------+---------------+-----------+---------+------+------+----------+--------------------------------------------------------+
| type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                                                  |
+-------+---------------+-----------+---------+------+------+----------+--------------------------------------------------------+
| range | idx_fecha     | idx_fecha | 3       | NULL | 4399 |   100.00 | Using index condition; Using temporary; Using filesort |
+-------+---------------+-----------+---------+------+------+----------+--------------------------------------------------------+

Neither of them is bad in itself. What I look at is whether they show up in a query that runs thousands of times a day, or over many rows.

When the query reads several tables, the plan has one row for each, in the order it will read them:

EXPLAIN SELECT c.nombre, p.fecha, p.total
FROM pedidos AS p
JOIN clientes AS c ON c.id = p.cliente_id
WHERE p.fecha = '2024-05-10';

+-------+--------+---------------+-----------+---------+--------------------------------+------+----------+-------+
| table | type   | possible_keys | key       | key_len | ref                            | rows | filtered | Extra |
+-------+--------+---------------+-----------+---------+--------------------------------+------+----------+-------+
| p     | ref    | idx_fecha     | idx_fecha | 3       | const                          |  124 |   100.00 | NULL  |
| c     | eq_ref | PRIMARY       | PRIMARY   | 4       | tienda.p.cliente_id |    1 |   100.00 | NULL  |
+-------+--------+---------------+-----------+---------+--------------------------------+------+----------+-------+

First the day's orders, through idx_fecha; and for each one, its customer through the primary key (eq_ref), compared with p.cliente_id, as ref says.

The column order of an index

An index can have several columns. I create one with three, meant for finding a customer's orders in a given status and on a given date:

CREATE INDEX idx_cliente_estado_fecha ON pedidos (cliente_id, estado, fecha);

With all three columns in the WHERE, it uses the whole index:

EXPLAIN SELECT * FROM pedidos
WHERE cliente_id = 1234 AND estado = 'pendiente' AND fecha = '2024-05-10';

+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
| type | possible_keys                      | key                      | key_len | ref               | rows | filtered | Extra |
+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
| ref  | idx_fecha,idx_cliente_estado_fecha | idx_cliente_estado_fecha | 89      | const,const,const |    1 |   100.00 | NULL  |
+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+

For a long time I believed that the conditions in the WHERE had to be in the same order as the columns of the index, and that otherwise the index was useless. I was wrong: the optimizer matches each condition to its column, wherever you write it. With the order reversed, the plan is identical:

EXPLAIN SELECT * FROM pedidos
WHERE fecha = '2024-05-10' AND estado = 'pendiente' AND cliente_id = 1234;

+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
| type | possible_keys                      | key                      | key_len | ref               | rows | filtered | Extra |
+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
| ref  | idx_fecha,idx_cliente_estado_fecha | idx_cliente_estado_fecha | 89      | const,const,const |    1 |   100.00 | NULL  |
+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+

What does matter is the order of the columns in the index. A multi-column index is sorted by the first column; within each value of the first, by the second; and so on. It's like a phone book sorted by last name and first name: it helps you find a last name, or a last name with its first name, but not everyone whose first name is Ana. The MySQL manual calls it the leftmost prefix: an index on (cliente_id, estado, fecha) serves searches by cliente_id, by cliente_id and estado, or by all three. It doesn't serve searches by estado or by fecha without cliente_id.

This year's pending orders, without saying which customer:

EXPLAIN SELECT * FROM pedidos
WHERE estado = 'pendiente' AND fecha >= '2025-01-01';

+------+---------------+------+---------+------+--------+----------+-------------+
| type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+------+---------------+------+---------+------+--------+----------+-------------+
| ALL  | idx_fecha     | NULL | NULL    | NULL | 199728 |     4.34 | Using where |
+------+---------------+------+---------+------+--------+----------+-------------+

The table has an index that contains estado and fecha, but it starts with cliente_id, and it doesn't even show up in possible_keys. Only idx_fecha is there, and the optimizer discards it for the same reason as before: almost a year of orders.

key_len: how many columns it used

When the index has several columns, key_len tells you how far it got. Each column takes a fixed number of bytes in the index: cliente_id is an INT, 4 bytes; estado is a VARCHAR(20) in utf8mb4, 20 characters of up to 4 bytes plus 2 for the length, 82; and fecha is a DATE, 3. All three together are the 89 above. A column that allows NULL adds one more byte.

With the customer and the status, without the date, it uses the first two columns, 4 + 82:

EXPLAIN SELECT * FROM pedidos
WHERE cliente_id = 1234 AND estado = 'pendiente';

+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| type | possible_keys            | key                      | key_len | ref         | rows | filtered | Extra |
+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| ref  | idx_cliente_estado_fecha | idx_cliente_estado_fecha | 86      | const,const |    1 |   100.00 | NULL  |
+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+

With the customer and the date, skipping the status, it can only use the first:

EXPLAIN SELECT * FROM pedidos
WHERE cliente_id = 1234 AND fecha >= '2024-01-01';

+------+------------------------------------+--------------------------+---------+-------+------+----------+-----------------------+
| type | possible_keys                      | key                      | key_len | ref   | rows | filtered | Extra                 |
+------+------------------------------------+--------------------------+---------+-------+------+----------+-----------------------+
| ref  | idx_fecha,idx_cliente_estado_fecha | idx_cliente_estado_fecha | 4       | const |   40 |    50.00 | Using index condition |
+------+------------------------------------+--------------------------+---------+-------+------+----------+-----------------------+

key says idx_cliente_estado_fecha, and someone looking only at that column would think the whole index is working. key_len says 4: it searches by cliente_id and nothing else. The date is checked inside the index (Using index condition), but it can't narrow the search, because the status sits between the customer and the date.

When key has a name and it still reads everything

There's one more case worth knowing. If the query asks only for columns that are in the index, MySQL can read the whole index instead of the table, even if the filter doesn't start with the first column:

EXPLAIN SELECT cliente_id, estado, fecha FROM pedidos
WHERE estado = 'pendiente';

+-------+--------------------------+--------------------------+---------+------+--------+----------+--------------------------+
| type  | possible_keys            | key                      | key_len | ref  | rows   | filtered | Extra                    |
+-------+--------------------------+--------------------------+---------+------+--------+----------+--------------------------+
| index | idx_cliente_estado_fecha | idx_cliente_estado_fecha | 89      | NULL | 199728 |    10.00 | Using where; Using index |
+-------+--------------------------+--------------------------+---------+------+--------+----------+--------------------------+

key has a name, but type is index and rows is 199,728: it goes through all two hundred thousand entries of the index, one by one. It's faster than going through the table, because the index is smaller, but it isn't a lookup. In MariaDB this same plan comes out with possible_keys empty and key named, which is exactly the case the MySQL manual describes: the index doesn't help find the rows, but it contains every column being asked for. That's why key is never read alone: it goes together with type and rows.

The fix

If the frequent query is by status and date, the index has to start with the status:

CREATE INDEX idx_estado_fecha ON pedidos (estado, fecha);

EXPLAIN SELECT * FROM pedidos
WHERE estado = 'pendiente' AND fecha >= '2025-01-01';

+-------+----------------------------+------------------+---------+------+------+----------+-----------------------+
| type  | possible_keys              | key              | key_len | ref  | rows | filtered | Extra                 |
+-------+----------------------------+------------------+---------+------+------+----------+-----------------------+
| range | idx_fecha,idx_estado_fecha | idx_estado_fecha | 85      | NULL | 1321 |   100.00 | Using index condition |
+-------+----------------------------+------------------+---------+------+------+----------+-----------------------+

From 199,728 rows to 1,321, and key_len 85: the whole status, 82, plus the date, 3. The rule I follow when deciding the order is to put first the columns compared with = and last the one compared with a range (>=, BETWEEN): after a range, the following columns of the index no longer narrow the search.

Does the same happen in other engines?

Yes. The leftmost prefix is a property of B-tree indexes, which are the default in every relational engine, and the PostgreSQL, SQLite and Oracle documentation explains it the same way. In none of them does the order in which you write the conditions of the WHERE matter.

What some have learned over the years is to skip the first column when it has very few distinct values: they search by the second once for each value of the first. It's called skip scan, and MySQL has it since 8.0.13, PostgreSQL since 18, as well as Oracle and SQLite (in SQLite only if ANALYZE has been run). In MySQL it shows up in Extra as Using index for skip scan, and only when the query reads one table and asks only for columns of the index, among other conditions. It wouldn't have helped here: the first column is the customer, and there are five thousand of them.

Measuring instead of estimating

Everything above is an estimate: EXPLAIN doesn't run the query. Since MySQL 8.0.18, EXPLAIN ANALYZE does run it, and next to each estimate it puts what actually happened:

EXPLAIN ANALYZE SELECT * FROM pedidos
WHERE estado = 'pendiente' AND fecha >= '2025-01-01';

-> Index range scan on pedidos using idx_estado_fecha over (estado = 'pendiente' AND '2025-01-01' <= fecha), with index condition: ((pedidos.estado = 'pendiente') and (pedidos.fecha >= DATE'2025-01-01'))  (cost=595 rows=1321) (actual time=1.01..12.6 rows=1321 loops=1)

rows=1321 in the first parentheses is what it estimated; actual time and rows=1321 in the second, how long it took in milliseconds and the rows it read. When the two row numbers are far apart, the table's statistics are out of date, and ANALYZE TABLE pedidos recalculates them.

MariaDB doesn't have EXPLAIN ANALYZE. Its equivalent is ANALYZE in front of the query, since 10.1, which returns the usual table with two more columns, r_rows and r_filtered, with what happened:

ANALYZE SELECT * FROM pedidos
WHERE estado = 'pendiente' AND fecha >= '2025-01-01';

Both run the query, so they take as long as the query does. And in MariaDB, an ANALYZE UPDATE or an ANALYZE DELETE really modifies the data.

In Calíope

In the SQL Editor, with the query written, the Visual EXPLAIN button in the toolbar asks the server for the plan without running the query and draws it as a tree, with one color per access type: red for a full table scan, orange for a full index scan, yellow for a stretch of an index and green for a lookup by key. It asks for it in JSON, so it doesn't depend on how your explain_format is set. And the Indexes button in the results bar opens the Index advisor, which looks for full scans in that plan and, if the query filters by a column that no index covers, proposes the CREATE INDEX.

What I look at first

When a query is slow, this is the order in which I read its EXPLAIN:

• key: if it's empty, no index is used. If possible_keys has one, the optimizer discarded it; if it's empty too, there's none that helps, or the condition doesn't allow using it.
• type and rows together: ALL or index with many rows means reading the whole table or the whole index, even if key has a name.
• key_len, if the index has several columns: it tells you how many it really used.
• Extra: Using temporary and Using filesort in a query that runs often deserve a second look.