Cómo leer el EXPLAIN de MySQL: possible_keys, key, Extra y el orden de las columnas de un índice

Al principio de mi carrera como DBA sufrí mucho con las consultas lentas. Veía que una consulta tardaba, pero no sabía cómo averiguar qué se podía optimizar en ella. Con el tiempo fui aprendiendo a leer EXPLAIN, que es la forma de preguntarle al motor cómo piensa ejecutar una consulta antes de ejecutarla, y esta entrada es lo que me habría gustado que alguien me explicara entonces.

A diferencia de la anterior, ésta es sólo de MySQL. MariaDB, que nació de MySQL, devuelve casi la misma tabla, y donde cambia lo digo.

La tabla de los ejemplos

Doscientos mil pedidos de cinco mil clientes: el 90 % entregados, el 7 % enviados y el 3 % pendientes, con fechas repartidas entre 2022 y 2025. Las filas salen de una serie generada con una CTE recursiva, que expliqué en la entrada sobre cómo llenar una tabla con datos de prueba:

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;

En MariaDB la variable del SET se llama max_recursive_iterations.

Por ahora pedidos sólo tiene su clave primaria. Los índices los voy creando por el camino, para ver qué cambia en el plan con cada uno. Los datos son aleatorios, así que si repites los ejemplos, los números de rows no te van a salir idénticos a los míos; las salidas son de MySQL 8.0.

Pedirle el plan a MySQL

Basta con escribir EXPLAIN delante del 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 no ejecuta la consulta: devuelve el plan, con una fila por cada tabla que la consulta lee. Lo que dice cada columna:

• id y select_type: a qué SELECT pertenece la fila. Importan cuando hay subconsultas o UNION; en una consulta simple son 1 y SIMPLE.
• table: la tabla, o su alias.
• type: cómo se accede a ella. De mejor a peor, los que más se ven son const (una sola fila, por clave), eq_ref (una fila por cada fila de la tabla anterior), ref (las filas con un valor del índice), range (un tramo del índice), index (el índice entero) y ALL (la tabla entera).
• possible_keys y key: los índices que podrían servir y el que eligió. Tienen su sección más abajo.
• key_len: cuántos bytes del índice usa; con un índice de varias columnas dice cuántas columnas usó.
• ref: con qué se compara el índice: una constante (const) o una columna de otra tabla.
• rows: cuántas filas calcula que va a examinar. Es una estimación que sale de las estadísticas de la tabla, no una cuenta.
• filtered: qué porcentaje de esas filas calcula que sobrevive al WHERE.
• Extra: lo que no cabe en las demás: si filtra, si ordena, si crea una tabla temporal.

En este caso, type es ALL, key está vacía y rows es 200 000: para devolver los 40 pedidos de un cliente, MySQL va a leer los doscientos mil.

Si tu MySQL devuelve un árbol de texto en vez de esta tabla, es que la variable explain_format (existe desde MySQL 8.0.32) está en TREE; en el MySQL más reciente que tengo en el testbed viene así por omisión. EXPLAIN FORMAT=TRADITIONAL pide la tabla.

possible_keys y key: tener índices no es usarlos

Éstas son las dos columnas que más me costó entender. possible_keys son los índices que el optimizador considera para la consulta; key, el que de verdad va a usar. Puedo tener diez índices en possible_keys, y si en key no aparece ninguno, la consulta no usa índices.

Hay dos razones para que key salga vacía. La primera es que no haya ningún índice que sirva, y entonces possible_keys también sale vacía, como en el ejemplo de arriba. La segunda es que lo haya y el optimizador decida no usarlo. Para verlo, creo un índice sobre la fecha:

CREATE INDEX idx_fecha ON pedidos (fecha);

Los pedidos de un mes lo usan:

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 |
+-------+---------------+-----------+---------+------+------+----------+-----------------------+

Los de un año entero, no:

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 está en possible_keys, pero key sale vacía y type vuelve a ser ALL. Un año es una cuarta parte de los pedidos, y leer una cuarta parte de la tabla a saltos, buscando cada fila desde el índice, le sale más caro que leerla entera en orden. No es un fallo: es una decisión, y a veces es la correcta. Fíjate también en filtered: calcula el 50 % cuando la cuenta real es el 26 %. Son estimaciones, y pueden equivocarse.

La trampa más común es otra: tener el índice y escribir la condición de manera que no se pueda usar. Los mismos pedidos de octubre, pidiéndolos por año y mes:

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 |
+------+---------------+------+---------+------+--------+----------+-------------+

Ahora possible_keys sale vacía. El índice guarda las fechas ordenadas, no sus años ni sus meses, y para saber qué fechas cumplen YEAR(fecha) = 2025 MySQL tiene que calcularlo fila por fila. Lo mismo pasa con cualquier función o cuenta sobre la columna: DATE(fecha), LOWER(email), total * 1.13. La salida es escribir la condición como un rango sobre la columna, como en el primer ejemplo de octubre.

La columna Extra

Es la última columna, y la que más dice cuando todo lo demás parece en orden. Los valores que más me encuentro:

• Using where: el WHERE se aplica a las filas después de leerlas. Con type en ALL, quiere decir que lee la tabla entera y descarta lo que no cumple.
• Using index: todo lo que pide la consulta está dentro del índice, así que no llega a leer la tabla. Es lo mejor que puede salir aquí.
• Using index condition: parte del WHERE se comprueba dentro del índice, antes de ir a buscar la fila.
• Using filesort: tiene que ordenar el resultado aparte, porque no lo lee ya ordenado de un índice. El nombre engaña: no quiere decir que escriba en disco.
• Using temporary: crea una tabla temporal para resolver la consulta, típicamente con un GROUP BY y un ORDER BY por columnas distintas.

Contar los pedidos de octubre se resuelve sin tocar la tabla, porque la fecha está en el índice y no hace falta nada más:

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 |
+-------+---------------+-----------+---------+------+------+----------+--------------------------+

Los pedidos de un día, del más caro al más barato, se leen por el índice de la fecha pero hay que ordenarlos por el 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 |
+------+---------------+-----------+---------+-------+------+----------+----------------+

Y los diez clientes que más gastaron en octubre necesitan las dos cosas: una tabla temporal para sumar por cliente y una ordenación por la suma:

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 |
+-------+---------------+-----------+---------+------+------+----------+--------------------------------------------------------+

Ninguno de los dos es malo por sí mismo. Lo que miro es si aparecen en una consulta que se ejecuta miles de veces al día, o sobre muchas filas.

Cuando la consulta lee varias tablas, el plan tiene una fila por cada una, en el orden en que las va a leer:

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  |
+-------+--------+---------------+-----------+---------+--------------------------------+------+----------+-------+

Primero los pedidos del día, por idx_fecha; y por cada uno, su cliente por la clave primaria (eq_ref), comparándola con p.cliente_id, como dice ref.

El orden de las columnas de un índice

Un índice puede tener varias columnas. Creo uno con tres, pensado para buscar los pedidos de un cliente en un estado y una fecha:

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

Con las tres columnas en el WHERE, lo usa entero:

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  |
+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+

Durante mucho tiempo creí que las condiciones del WHERE tenían que ir en el mismo orden que las columnas del índice, y que si no, el índice no servía. Me equivocaba: el optimizador empareja cada condición con su columna, la escribas donde la escribas. Con el orden al revés, el plan es idéntico:

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  |
+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+

Lo que sí importa es el orden de las columnas en el índice. Un índice de varias columnas está ordenado por la primera; dentro de cada valor de la primera, por la segunda; y así. Es como una guía telefónica ordenada por apellido y nombre: sirve para buscar un apellido, o un apellido con su nombre, pero no para buscar a todos los que se llaman Ana. El manual de MySQL lo llama el prefijo izquierdo: un índice sobre (cliente_id, estado, fecha) sirve para buscar por cliente_id, por cliente_id y estado, o por las tres. No sirve para buscar por estado o por fecha sin cliente_id.

Los pedidos pendientes de este año, sin decir de qué cliente:

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 |
+------+---------------+------+---------+------+--------+----------+-------------+

La tabla tiene un índice que contiene estado y fecha, pero empieza por cliente_id, y ni siquiera aparece en possible_keys. Sólo está idx_fecha, que el optimizador descarta por la misma razón que antes: casi un año de pedidos.

key_len: cuántas columnas usó

Cuando el índice tiene varias columnas, key_len dice hasta dónde llegó. Cada columna ocupa un número fijo de bytes en el índice: cliente_id es un INT, 4 bytes; estado es un VARCHAR(20) en utf8mb4, 20 caracteres de hasta 4 bytes más 2 de longitud, 82; y fecha es un DATE, 3. Las tres juntas son las 89 de arriba. Una columna que admite NULL suma un byte más.

Con el cliente y el estado, sin la fecha, usa las dos primeras columnas, 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  |
+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+

Con el cliente y la fecha, saltándose el estado, sólo puede usar la primera:

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 dice idx_cliente_estado_fecha, y alguien que mire sólo esa columna pensaría que el índice está funcionando entero. key_len dice 4: busca por cliente_id y nada más. La fecha se comprueba dentro del índice (Using index condition), pero no sirve para acotar la búsqueda, porque entre el cliente y la fecha está el estado.

Cuando key tiene nombre y aun así lee todo

Hay un caso más que conviene conocer. Si la consulta pide sólo columnas que están en el índice, MySQL puede leer el índice entero en vez de la tabla, aunque el filtro no empiece por la primera columna:

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 tiene nombre, pero type es index y rows es 199 728: recorre las doscientas mil entradas del índice, una por una. Es más rápido que recorrer la tabla, porque el índice ocupa menos, pero no es una búsqueda. En MariaDB este mismo plan sale con possible_keys vacía y key con nombre, que es justo el caso que el manual de MySQL describe: el índice no sirve para encontrar las filas, pero contiene todas las columnas que se piden. Por eso key nunca se lee sola: va junto con type y rows.

La solución

Si la consulta frecuente es por estado y fecha, el índice tiene que empezar por el estado:

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 |
+-------+----------------------------+------------------+---------+------+------+----------+-----------------------+

De 199 728 filas a 1 321, y key_len 85: el estado entero, 82, más la fecha, 3. La regla que sigo al decidir el orden es poner primero las columnas que se comparan con = y al final la que se compara con un rango (>=, BETWEEN): después de un rango, las columnas siguientes del índice ya no acotan la búsqueda.

¿Pasa lo mismo en otros motores?

Sí. El prefijo izquierdo es una propiedad de los índices B-tree, que son los de por omisión en todos los motores relacionales, y la documentación de PostgreSQL, SQLite y Oracle lo explica igual. En ninguno importa el orden en que se escriben las condiciones del WHERE.

Lo que algunos han aprendido con los años es a saltarse la primera columna cuando tiene muy pocos valores distintos: buscan por la segunda una vez por cada valor de la primera. Se llama skip scan, y lo hacen MySQL desde la 8.0.13, PostgreSQL desde la 18, Oracle y SQLite (en SQLite sólo si se ha ejecutado ANALYZE). En MySQL aparece en Extra como Using index for skip scan, y sólo cuando la consulta lee una tabla y pide únicamente columnas del índice, entre otras condiciones. Aquí no habría servido de nada: la primera columna es el cliente, y hay cinco mil.

Medir en vez de estimar

Todo lo anterior son estimaciones: EXPLAIN no ejecuta la consulta. Desde MySQL 8.0.18, EXPLAIN ANALYZE sí la ejecuta, y al lado de cada estimación pone lo que pasó de verdad:

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 entre los primeros paréntesis es lo que calculó; actual time y rows=1321 en los segundos, lo que tardó en milisegundos y las filas que leyó. Cuando los dos números de filas se parecen poco, las estadísticas de la tabla están desactualizadas, y ANALYZE TABLE pedidos las recalcula.

MariaDB no tiene EXPLAIN ANALYZE. Su equivalente es ANALYZE delante de la consulta, desde la 10.1, que devuelve la tabla de siempre con dos columnas más, r_rows y r_filtered, con lo que pasó:

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

Las dos ejecutan la consulta, así que tardan lo mismo que ella. Y en MariaDB, un ANALYZE UPDATE o un ANALYZE DELETE modifica los datos de verdad.

En Calíope

En el Editor SQL, con la consulta escrita, el botón Visual EXPLAIN de la barra pide el plan al servidor sin ejecutar la consulta y lo dibuja como un árbol, con un color por tipo de acceso: rojo para el recorrido completo de una tabla, naranja para el de un índice entero, amarillo para un tramo de índice y verde para una búsqueda por clave. Lo pide en JSON, así que no depende de cómo tengas explain_format. Y el botón Índices de la barra de resultados abre el Asesor de índices, que busca en ese plan los recorridos completos y, si la consulta filtra por una columna que ningún índice cubre, propone el CREATE INDEX.

Lo que miro primero

Cuando una consulta tarda, éste es el orden en que leo su EXPLAIN:

• key: si está vacía, no usa ningún índice. Si possible_keys tiene alguno, el optimizador lo descartó; si también está vacía, no hay ninguno que sirva, o la condición no deja usarlo.
• type y rows juntos: ALL o index con muchas filas es leer la tabla o el índice enteros, aunque key tenga nombre.
• key_len, si el índice tiene varias columnas: dice cuántas usó de verdad.
• Extra: Using temporary y Using filesort en una consulta que se ejecuta a menudo merecen una segunda mirada.