Optimizar consultas

Identificar que una consulta es lenta con EXPLAIN o los slow query logs es solo el primer paso. El verdadero trabajo consiste en aplicar patrones concretos de optimización que transformen esas consultas lentas en operaciones eficientes. En esta guía recorreremos las técnicas más efectivas para optimizar consultas en MySQL, cada una con ejemplos realistas que muestran la versión lenta, la versión optimizada y la diferencia visible en el plan de ejecución.

Todas las técnicas que veremos comparten un principio fundamental: reducir la cantidad de trabajo que MySQL necesita hacer. Eso puede significar leer menos filas, leer menos columnas, evitar operaciones temporales en disco o aprovechar mejor las estructuras de índice existentes. Cuando domines estos patrones, podrás diagnosticar y corregir la mayoría de problemas de rendimiento sin necesidad de cambiar la arquitectura de tu base de datos.

Evitar SELECT *

La costumbre de escribir SELECT * es probablemente el anti-patrón de rendimiento más extendido. Aunque resulta cómodo durante el desarrollo, tiene consecuencias reales en producción que van mucho más allá de traer columnas innecesarias.

Cuando usas SELECT *, MySQL debe leer todas las columnas de cada fila que coincida con la condición. Esto impide que el motor utilice índices de cobertura (covering indexes), que son índices que contienen todas las columnas necesarias para resolver la consulta sin tocar la tabla base. Además, SELECT * transfiere más datos por la red, consume más memoria en el buffer pool y hace que tu aplicación sea frágil ante cambios de esquema.

Ejemplo lento con SELECT *

Imagina una tabla pedidos con 15 columnas y un índice compuesto sobre (cliente_id, fecha). Si solo necesitas el identificador y la fecha de los pedidos de un cliente:

-- Lento: lee todas las columnas de la tabla base
SELECT * FROM pedidos WHERE cliente_id = 1042;

+----+-------------+---------+------+---------------+------------+---------+-------+------+-------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows | Extra |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------+
|  1 | SIMPLE      | pedidos | ref  | idx_cli_fecha | idx_cli_fecha | 4    | const |  127 | NULL  |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------+

Aunque MySQL usa el índice para localizar las filas, la ausencia de Using index en la columna Extra indica que necesita volver a la tabla base (lookup) para leer las demás columnas.

Ejemplo optimizado seleccionando columnas

-- Rápido: solo las columnas que necesitas (cubiertas por el índice)
SELECT id, fecha FROM pedidos WHERE cliente_id = 1042;

+----+-------------+---------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------------+
|  1 | SIMPLE      | pedidos | ref  | idx_cli_fecha | idx_cli_fecha | 4    | const |  127 | Using index |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------------+

Ahora Extra: Using index confirma que MySQL resuelve toda la consulta desde el índice sin acceder a la tabla. En tablas con millones de filas, esta diferencia puede reducir el tiempo de ejecución en un orden de magnitud.

Índices de cobertura

Un índice de cobertura (covering index) es aquel que contiene todas las columnas que la consulta necesita: las del WHERE, las del SELECT, las del ORDER BY y las del GROUP BY. Cuando MySQL puede resolver la consulta exclusivamente desde el índice, evita los costosos accesos aleatorios a la tabla base.

Para que un índice compuesto funcione como índice de cobertura, debe incluir todas las columnas referenciadas en la consulta. El orden de las columnas dentro del índice importa: coloca primero las columnas de filtrado (WHERE), después las de ordenación (ORDER BY) y finalmente las que solo aparecen en el SELECT.

Ejemplo sin índice de cobertura

-- Consulta que necesita nombre del producto y cantidad
SELECT p.nombre, dp.cantidad
FROM detalle_pedidos dp
JOIN productos p ON p.id = dp.producto_id
WHERE dp.pedido_id = 5012;

Si detalle_pedidos tiene un índice solo sobre (pedido_id), MySQL localiza las filas por pedido_id pero necesita volver a la tabla para leer producto_id y cantidad.

Ejemplo con índice de cobertura

-- Crear un índice que cubra la consulta completamente
CREATE INDEX idx_detalle_cobertura
ON detalle_pedidos (pedido_id, producto_id, cantidad);

Ahora la misma consulta muestra Using index porque pedido_id (filtro), producto_id (join) y cantidad (selección) están todos dentro del índice. Puedes verificarlo con EXPLAIN:

EXPLAIN SELECT dp.producto_id, dp.cantidad
FROM detalle_pedidos dp
WHERE dp.pedido_id = 5012;

+----+-------------+-------+------+------------------------+------------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys          | key                    | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+------------------------+------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | dp    | ref  | idx_detalle_cobertura  | idx_detalle_cobertura  | 4       | const |    3 | Using index |
+----+-------------+-------+------+------------------------+------------------------+---------+-------+------+-------------+

El coste adicional del índice de cobertura es el espacio en disco y un ligero overhead en las escrituras, pero en consultas de lectura frecuente el beneficio es enorme.

Reescribir subconsultas correlacionadas como JOINs

Las subconsultas correlacionadas se ejecutan una vez por cada fila de la consulta externa. En tablas grandes esto genera miles o millones de ejecuciones internas, convirtiendo una operación que debería tardar milisegundos en una que tarda minutos. Reescribirlas como JOINs permite a MySQL ejecutar la operación en un solo paso.

Ejemplo lento con subconsulta correlacionada

Supongamos que quieres obtener cada cliente junto con el total de sus pedidos:

-- Lento: subconsulta ejecutada una vez por cada cliente
SELECT c.nombre, c.email,
       (SELECT SUM(total) FROM pedidos p WHERE p.cliente_id = c.id) AS total_gastado
FROM clientes c;

Para una tabla clientes con 50,000 filas, MySQL ejecuta la subconsulta 50,000 veces. Si interpretas el EXPLAIN, verás DEPENDENT SUBQUERY en la subconsulta, lo que confirma la correlación.

Ejemplo optimizado con JOIN

-- Rápido: un solo paso con JOIN + GROUP BY
SELECT c.nombre, c.email, COALESCE(SUM(p.total), 0) AS total_gastado
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
GROUP BY c.id, c.nombre, c.email;

Con un índice sobre pedidos(cliente_id), MySQL lee la tabla de pedidos una sola vez usando un hash join o un nested loop eficiente. El plan de ejecución ya no muestra DEPENDENT SUBQUERY, y el tiempo de ejecución pasa de segundos a milisegundos.

No todas las subconsultas correlacionadas deben convertirse en JOINs. En algunos casos MySQL optimiza la subconsulta internamente, especialmente cuando la subconsulta referencia una clave primaria con alta selectividad. Usa EXPLAIN ANALYZE para medir el impacto real antes y después de la reescritura.

Usar EXISTS en lugar de IN para conjuntos grandes

Cuando filtras filas basándote en la existencia de registros en otra tabla, tanto IN como EXISTS pueden lograr el mismo resultado, pero su rendimiento difiere significativamente cuando la subconsulta devuelve un conjunto grande de valores.

Con IN, MySQL evalúa la subconsulta completa, materializa todos los resultados en una tabla temporal y luego compara cada fila de la consulta externa contra esa tabla. Con EXISTS, MySQL detiene la búsqueda en la subconsulta en cuanto encuentra la primera fila que cumple la condición. Esta diferencia se amplifica cuando la subconsulta devuelve miles de filas pero la existencia es lo único que importa.

Ejemplo lento con IN

-- Lento: materializa todos los cliente_id que tienen pedidos recientes
SELECT nombre, email
FROM clientes
WHERE id IN (
    SELECT cliente_id FROM pedidos WHERE fecha >= '2026-01-01'
);

Si la subconsulta devuelve 30,000 valores de cliente_id, MySQL crea una tabla temporal con esos 30,000 IDs y luego escanea clientes comparando cada id contra la tabla temporal.

Ejemplo optimizado con EXISTS

-- Rápido: detiene la búsqueda en la primera coincidencia
SELECT c.nombre, c.email
FROM clientes c
WHERE EXISTS (
    SELECT 1 FROM pedidos p
    WHERE p.cliente_id = c.id AND p.fecha >= '2026-01-01'
);

Con un índice sobre pedidos(cliente_id, fecha), MySQL busca en el índice para cada cliente y se detiene en cuanto encuentra un pedido que cumpla la condición. Para clientes con muchos pedidos, la diferencia es notable porque EXISTS no necesita contar ni recopilar todos los registros coincidentes.

Paginación eficiente

La paginación con OFFSET es una de las trampas de rendimiento más comunes en aplicaciones web. Aunque SELECT ... LIMIT 20 OFFSET 10000 parece inocente, MySQL debe leer y descartar las primeras 10,000 filas antes de devolver las 20 que necesitas. Cuanto mayor es el offset, peor es el rendimiento.

Ejemplo lento con OFFSET grande

-- Página 500 con 20 resultados por página
SELECT id, fecha, total
FROM pedidos
ORDER BY fecha DESC
LIMIT 20 OFFSET 9980;

MySQL ordena todos los pedidos por fecha, recorre los primeros 9,980, los descarta y devuelve los siguientes 20. Con millones de filas, esta operación puede tardar varios segundos incluso con un índice sobre fecha.

Ejemplo optimizado con paginación por cursor (keyset)

La paginación por cursor o keyset pagination utiliza el último valor visto como punto de partida, eliminando la necesidad de recorrer filas anteriores:

-- Primera página
SELECT id, fecha, total
FROM pedidos
ORDER BY fecha DESC, id DESC
LIMIT 20;
 
-- Siguientes páginas: usar el último fecha e id de la página anterior
SELECT id, fecha, total
FROM pedidos
WHERE (fecha, id) < ('2026-03-15', 48230)
ORDER BY fecha DESC, id DESC
LIMIT 20;

Con un índice sobre (fecha DESC, id DESC), MySQL salta directamente al punto de continuación sin leer ni descartar filas previas. El rendimiento es constante independientemente de en qué página te encuentres.

La paginación por cursor tiene una limitación: no permite saltar a una página arbitraria (ej: "ir a la página 47"). Si tu aplicación necesita esa funcionalidad, considera combinar keyset pagination para la navegación secuencial (siguiente/anterior) con una consulta de conteo separada para mostrar el total de páginas. Para la gran mayoría de interfaces tipo "cargar más" o scroll infinito, la paginación por cursor es ideal.

Optimizar GROUP BY con índices

Las operaciones de GROUP BY pueden ser costosas cuando MySQL necesita crear una tabla temporal y ordenar los datos antes de agruparlos. Si las columnas de agrupación coinciden con un índice, MySQL puede agrupar directamente durante el recorrido del índice, evitando la tabla temporal.

Ejemplo lento sin índice adecuado

-- Total de ventas por categoría y mes
SELECT p.categoria_id,
       DATE_FORMAT(pe.fecha, '%Y-%m') AS mes,
       SUM(dp.cantidad * dp.precio_unitario) AS total_ventas
FROM detalle_pedidos dp
JOIN pedidos pe ON pe.id = dp.pedido_id
JOIN productos p ON p.id = dp.producto_id
GROUP BY p.categoria_id, mes;

Sin un índice que cubra las columnas de agrupación, el EXPLAIN muestra Using temporary; Using filesort, lo que significa que MySQL crea una tabla temporal en memoria (o en disco si es grande) y luego la ordena.

Ejemplo optimizado con índice para GROUP BY

Para consultas de agrupación frecuentes, crea índices que coincidan con las columnas del GROUP BY:

-- Índice para agrupar pedidos por cliente y estado
CREATE INDEX idx_pedidos_cliente_estado ON pedidos (cliente_id, estado);
 
-- Esta consulta ahora agrupa sin tabla temporal
SELECT cliente_id, estado, COUNT(*) AS total
FROM pedidos
GROUP BY cliente_id, estado;

+----+-------------+---------+-------+----------------------------+----------------------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys              | key                        | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+----------------------------+----------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | pedidos | index | idx_pedidos_cliente_estado | idx_pedidos_cliente_estado | 8       | NULL | 8500 | Using index |
+----+-------------+---------+-------+----------------------------+----------------------------+---------+------+------+-------------+

Using index sin Using temporary ni Using filesort confirma que MySQL agrupa directamente desde el índice. En tablas con millones de filas, esta optimización puede reducir el tiempo de ejecución de varios segundos a unos pocos milisegundos.

No aplicar funciones sobre columnas indexadas en WHERE

Cuando aplicas una función a una columna en la cláusula WHERE, MySQL no puede usar el índice sobre esa columna. Esto se conoce como "romper el índice" y es una de las causas más frecuentes de consultas lentas que parecen tener los índices correctos.

El problema es que MySQL almacena en el índice los valores originales de la columna, no los valores transformados por funciones. Si le pides buscar YEAR(fecha) = 2026, MySQL no puede recorrer el índice de fecha de forma eficiente porque necesitaría aplicar YEAR() a cada valor del índice para compararlo.

Ejemplo lento con función en columna indexada

-- Lento: YEAR() sobre la columna impide usar el índice de fecha
SELECT id, cliente_id, total
FROM pedidos
WHERE YEAR(fecha) = 2026 AND MONTH(fecha) = 3;

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

type: ALL indica un full table scan: MySQL lee las 85,000 filas aplicando las funciones YEAR() y MONTH() a cada una.

Ejemplo optimizado con rango de fechas

La solución es reescribir la condición para que compare directamente con el valor de la columna, usando rangos:

-- Rápido: comparación directa que aprovecha el índice de fecha
SELECT id, cliente_id, total
FROM pedidos
WHERE fecha >= '2026-03-01' AND fecha < '2026-04-01';

+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | pedidos | range | idx_fecha     | idx_fecha | 3       | NULL |  720 | Using where |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+

Ahora MySQL usa un range scan sobre el índice y solo lee las 720 filas del rango. El mismo principio aplica a otras funciones habituales:

-- Lento: LOWER() impide usar el índice
SELECT * FROM clientes WHERE LOWER(email) = 'ana.garcia@correo.com';
 
-- Rápido: si el collation ya es case-insensitive (utf8mb4_0900_ai_ci)
SELECT * FROM clientes WHERE email = 'ana.garcia@correo.com';
 
-- Lento: DATE() sobre datetime impide usar el índice
SELECT * FROM pedidos WHERE DATE(fecha_hora) = '2026-03-15';
 
-- Rápido: rango sobre la columna original
SELECT * FROM pedidos WHERE fecha_hora >= '2026-03-15' AND fecha_hora < '2026-03-16';

Si realmente necesitas indexar el resultado de una función, MySQL 8.0 permite crear índices funcionales con CREATE INDEX idx ON tabla ((EXPRESION)).

FORCE INDEX como último recurso

En ocasiones MySQL elige un plan de ejecución subóptimo porque sus estadísticas de tabla están desactualizadas o porque el optimizador sobreestima el coste de usar un índice concreto. FORCE INDEX le indica a MySQL que use un índice específico, anulando la decisión del optimizador.

Esta técnica es un último recurso y no una práctica habitual. Antes de usar FORCE INDEX, asegúrate de que has actualizado las estadísticas con ANALYZE TABLE, de que has comprobado con EXPLAIN ANALYZE que el problema real es la elección del índice y de que no puedes resolver el problema creando un índice más adecuado.

Ejemplo donde FORCE INDEX es necesario

-- MySQL elige un full table scan en lugar del índice de estado
EXPLAIN SELECT id, fecha, total
FROM pedidos
WHERE estado = 'pendiente' AND fecha >= '2026-03-01'
ORDER BY fecha DESC;

Si MySQL estima que el 40% de los pedidos tienen estado = 'pendiente', puede decidir que un full table scan es más eficiente que usar el índice. Pero si en realidad solo el 5% está pendiente (las estadísticas están desactualizadas), el índice sería mucho mejor:

-- Forzar el uso del índice específico
SELECT id, fecha, total
FROM pedidos FORCE INDEX (idx_estado_fecha)
WHERE estado = 'pendiente' AND fecha >= '2026-03-01'
ORDER BY fecha DESC;

Recuerda siempre ejecutar ANALYZE TABLE pedidos primero para actualizar las estadísticas. Si eso resuelve el problema, elimina el FORCE INDEX de tu código porque las estadísticas actualizadas permitirán al optimizador tomar la decisión correcta por sí solo.

Operaciones por lotes en lugar de fila por fila

Insertar, actualizar o eliminar filas una por una en un bucle de la aplicación genera un overhead enorme: cada operación individual implica una ida y vuelta por la red, un análisis de la consulta, una búsqueda en el plan de ejecución y un commit al log de transacciones. Agrupar operaciones en lotes reduce drásticamente ese overhead.

Ejemplo lento: inserciones individuales

-- Lento: 1000 inserciones individuales (1000 round-trips)
INSERT INTO log_accesos (usuario_id, pagina, fecha) VALUES (101, '/inicio', '2026-03-24');
INSERT INTO log_accesos (usuario_id, pagina, fecha) VALUES (102, '/productos', '2026-03-24');
INSERT INTO log_accesos (usuario_id, pagina, fecha) VALUES (103, '/carrito', '2026-03-24');
-- ... 997 inserciones más

Ejemplo optimizado: inserción por lotes

-- Rápido: una sola sentencia con múltiples filas (1 round-trip)
INSERT INTO log_accesos (usuario_id, pagina, fecha) VALUES
    (101, '/inicio', '2026-03-24'),
    (102, '/productos', '2026-03-24'),
    (103, '/carrito', '2026-03-24'),
    -- ... hasta 1000 filas en un solo INSERT
    (1100, '/checkout', '2026-03-24');

El mismo principio aplica a las actualizaciones. En lugar de ejecutar mil UPDATE individuales, puedes usar CASE o una tabla temporal con JOIN:

-- Lento: actualización fila por fila
UPDATE productos SET precio = 29.99 WHERE id = 1;
UPDATE productos SET precio = 49.99 WHERE id = 2;
UPDATE productos SET precio = 19.99 WHERE id = 3;
 
-- Rápido: actualización por lotes con CASE
UPDATE productos SET precio = CASE id
    WHEN 1 THEN 29.99
    WHEN 2 THEN 49.99
    WHEN 3 THEN 19.99
END
WHERE id IN (1, 2, 3);

Para eliminaciones masivas, evita DELETE FROM tabla WHERE condicion sin LIMIT cuando la condición afecta a millones de filas. Un DELETE masivo bloquea la tabla durante toda la operación y genera un log de transacciones enorme. En su lugar, elimina en lotes:

-- Eliminar registros antiguos en lotes de 5000
DELETE FROM log_accesos
WHERE fecha < '2025-01-01'
ORDER BY fecha
LIMIT 5000;
-- Repetir hasta que no queden filas

Usar LIMIT con inteligencia

La cláusula LIMIT parece una herramienta de optimización obvia, pero su efectividad depende enormemente de cómo se combine con el resto de la consulta. Un LIMIT sin un ORDER BY indexado no evita que MySQL procese todas las filas internamente antes de recortar el resultado.

Ejemplo donde LIMIT no ayuda

-- LIMIT no salva esta consulta: MySQL ordena TODAS las filas primero
SELECT id, nombre, total
FROM pedidos
WHERE estado = 'completado'
ORDER BY total DESC
LIMIT 10;

Si no existe un índice sobre (estado, total), MySQL lee todas las filas con estado = 'completado', las ordena por total (filesort) y solo entonces aplica el LIMIT. El coste real es proporcional al número total de filas filtradas, no a 10.

Ejemplo donde LIMIT sí optimiza

-- Con un índice sobre (estado, total), LIMIT es efectivo
CREATE INDEX idx_estado_total ON pedidos (estado, total DESC);
 
SELECT id, nombre, total
FROM pedidos
WHERE estado = 'completado'
ORDER BY total DESC
LIMIT 10;

Ahora MySQL recorre el índice en orden descendente de total para filas con estado = 'completado' y se detiene tras encontrar las primeras 10. No necesita leer ni ordenar el resto. El plan de ejecución lo confirma con la ausencia de Using filesort.

Otra situación donde LIMIT resulta muy efectivo es para comprobar la existencia de registros. En lugar de contar todos los resultados, puedes usar LIMIT 1:

-- Lento: cuenta TODOS los pedidos pendientes
SELECT COUNT(*) FROM pedidos WHERE estado = 'pendiente';
 
-- Rápido (si solo necesitas saber si hay alguno):
SELECT 1 FROM pedidos WHERE estado = 'pendiente' LIMIT 1;

Resumen de patrones de optimización

Cada patrón que hemos visto ataca un tipo específico de ineficiencia. Evitar SELECT * y crear índices de cobertura reduce la cantidad de datos leídos. Reescribir subconsultas correlacionadas como JOINs y usar EXISTS en lugar de IN reduce el número de operaciones repetidas. La paginación por cursor elimina el desperdicio del OFFSET grande. Indexar las columnas de GROUP BY evita tablas temporales y ordenaciones costosas. Mantener las columnas indexadas libres de funciones en el WHERE permite que los índices hagan su trabajo. Y las operaciones por lotes reducen el overhead de comunicación y transacciones.

El flujo de trabajo recomendado cuando detectas una consulta lenta es: primero analiza el plan con EXPLAIN e EXPLAIN ANALYZE, identifica cuál de estos patrones aplica, implementa la optimización y verifica que el plan de ejecución ha mejorado. Si el plan no mejora, revisa si necesitas crear un índice diferente o si el problema está en un nivel más profundo que puedes diagnosticar con profiling.