Cómo acelerar consultas lentas en MySQL con índices y EXPLAIN

El historial de pedidos de una tienda suele empezar rápido. Meses después, abrir la ficha de un cliente puede tardar varios segundos aunque la pantalla solo muestre 20 pedidos. EXPLAIN ayuda a descubrir cuánto trabajo estima MySQL para encontrar esas filas y si un índice puede reducirlo.

1. Revisa el plan de la consulta lenta

Supongamos que pedidos es una tabla InnoDB y que id es su clave primaria. Queremos los últimos 20 pedidos pagados de un cliente. El segundo criterio de ordenación, id, resuelve los empates cuando dos pedidos tienen la misma fecha.

EXPLAIN FORMAT=TRADITIONAL
SELECT id, fecha_creacion, total
FROM pedidos
WHERE cliente_id = 42
  AND estado = 'pagado'
ORDER BY fecha_creacion DESC, id DESC
LIMIT 20;

En la salida, revisa estas columnas:

• type indica cómo accede MySQL a la tabla. ALL indica un recorrido completo y index puede indicar un recorrido completo del índice.
• possible_keys lista índices candidatos para localizar filas. key muestra el índice que eligió el optimizador, si eligió alguno.
• rows estima cuántas filas examina ese acceso. filtered estima el porcentaje que pasa los filtros restantes.
• Extra muestra detalles del plan. Using filesort indica una fase adicional de ordenación, que puede ejecutarse en memoria.

Si ves un recorrido amplio y una ordenación adicional en una consulta frecuente que devuelve pocas filas, revisa primero los índices existentes con SHOW INDEX FROM pedidos;.

2. Diseña un índice para el filtro y el orden

En MySQL 8 con InnoDB, este índice compuesto es un candidato para la consulta:

CREATE INDEX idx_pedidos_cliente_estado_fecha_id
ON pedidos (cliente_id, estado, fecha_creacion DESC, id DESC);

MySQL puede localizar el bloque correspondiente a cliente_id = 42 y estado = 'pagado'. Dentro de ese bloque, las entradas siguen el orden de fecha e identificador que pide la consulta. Si el optimizador elige ese acceso, puede obtener los primeros 20 resultados sin una fase adicional de ordenación. Como total queda fuera del índice, todavía necesita leer las filas correspondientes para recuperar ese valor.

El orden de las columnas define qué otras consultas pueden aprovechar el índice. Este empieza por cliente_id, así que también puede servir para buscar pedidos de un cliente sin indicar el estado. Si quitas el filtro de estado y ordenas todos sus pedidos por fecha, la posición de estado puede impedir que el índice resuelva ese orden global. Comprueba el plan de cada consulta importante.

3. Compara el antes y el después con EXPLAIN ANALYZE

En MySQL 8.0.18 o posterior, ejecuta este comando antes y después de crear el índice en un entorno de pruebas con datos representativos. EXPLAIN ANALYZE ejecuta realmente la consulta.

EXPLAIN ANALYZE
SELECT id, fecha_creacion, total
FROM pedidos
WHERE cliente_id = 42
  AND estado = 'pagado'
ORDER BY fecha_creacion DESC, id DESC
LIMIT 20;

1. Busca si el plan pasó de un recorrido amplio a un acceso por idx_pedidos_cliente_estado_fecha_id.
2. Comprueba si desapareció la operación de ordenación y cuántas filas devuelve cada paso. Revisa loops para identificar operaciones que se repiten.
3. Compara actual time, expresado en milisegundos. Repite la medición con varios clientes y en condiciones similares, ya que la caché y la distribución de datos cambian los resultados.

Si MySQL sigue prefiriendo otro plan, revisa si la tabla es pequeña o si el filtro devuelve gran parte de ella. Si sospechas que las estadísticas están desactualizadas, prueba ANALYZE TABLE pedidos; en tu entorno de pruebas y vuelve a comparar.

4. Escribe filtros que permitan buscar por rango

Para buscar pedidos de un día, comparar directamente la columna de fecha con un intervalo permite que MySQL considere un rango en un índice compatible. Esto funciona aunque la columna almacene fracciones de segundo.

-- Filtro que aplica una función a cada fecha
WHERE DATE(fecha_creacion) = '2026-11-01'

-- Intervalo desde el inicio del día hasta antes del siguiente
WHERE fecha_creacion >= '2026-11-01 00:00:00'
  AND fecha_creacion <  '2026-11-02 00:00:00'

Para aprovechar el índice del ejemplo con ese intervalo, conserva las condiciones de igualdad sobre cliente y estado. Una búsqueda global por fecha tiene otro patrón de acceso y merece su propio EXPLAIN.

Antes de aplicar el cambio en producción, mide también el impacto en las escrituras. Cada índice ocupa espacio y añade trabajo al insertar, borrar o actualizar columnas indexadas. Conserva el índice cuando la mejora de la consulta justifique ese costo para tu aplicación.

Documentación oficial de MySQL

• Sintaxis de EXPLAIN y EXPLAIN ANALYZE
• Índices de varias columnas
• Optimización de ORDER BY