Cómo optimizar consultas SQL lentas: EXPLAIN y estrategias reales

Una consulta que tarda 50 milisegundos con 10.000 filas puede tardar 45 segundos con 10 millones. El problema no es que SQL sea lento: es que el motor de base de datos tiene que tomar decisiones sobre cómo ejecutar cada consulta, y sin la información o los índices correctos, esas decisiones pueden ser muy costosas.

En esta guía vas a aprender a identificar qué hace lenta una consulta, cómo interpretar el plan de ejecución, y qué estrategias concretas aplicar para resolver los problemas más habituales.

El esquema de ejemplo

CREATE TABLE clientes (
  id        INT PRIMARY KEY AUTO_INCREMENT,
  email     VARCHAR(100),
  nombre    VARCHAR(100),
  ciudad    VARCHAR(50),
  pais      VARCHAR(50),
  activo    BOOLEAN DEFAULT TRUE,
  creado_en DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE pedidos (
  id          INT PRIMARY KEY AUTO_INCREMENT,
  cliente_id  INT,
  producto_id INT,
  estado      VARCHAR(20),
  total       DECIMAL(10,2),
  fecha       DATETIME,
  INDEX idx_cliente (cliente_id)   -- solo este índice por ahora
);

CREATE TABLE productos (
  id        INT PRIMARY KEY AUTO_INCREMENT,
  nombre    VARCHAR(100),
  categoria VARCHAR(50),
  precio    DECIMAL(10,2),
  stock     INT
);

-- Supongamos: 500.000 clientes, 5.000.000 pedidos, 10.000 productos

El primer paso: encontrar las consultas lentas

Antes de optimizar nada, hay que saber qué optimizar. La mayoría de problemas de rendimiento vienen de un puñado de consultas, no de todas.

Slow query log en MySQL

-- Activar el log de consultas lentas (en MySQL)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;          -- registrar consultas que tarden más de 1 segundo
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- también registrar sin índice

-- Ver la configuración actual
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- Analizar el log con mysqldumpslow (herramienta de línea de comandos)
-- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
-- -s t: ordenar por tiempo total
-- -t 10: mostrar las 10 peores consultas

pg_stat_statements en PostgreSQL

-- Habilitar la extensión (en postgresql.conf):
-- shared_preload_libraries = 'pg_stat_statements'

-- Activar en la base de datos:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Ver las 10 consultas más lentas en total
SELECT
  query,
  calls,
  ROUND(total_exec_time::numeric, 2)  AS tiempo_total_ms,
  ROUND(mean_exec_time::numeric, 2)   AS tiempo_medio_ms,
  ROUND(stddev_exec_time::numeric, 2) AS desviacion_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Resetear las estadísticas
SELECT pg_stat_statements_reset();

EXPLAIN: leer el plan de ejecución

El comando EXPLAIN muestra cómo planea el motor ejecutar una consulta, sin ejecutarla realmente. EXPLAIN ANALYZE la ejecuta y muestra el plan junto con los tiempos reales.

EXPLAIN en MySQL

EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 123;

-- Salida de EXPLAIN (columnas más importantes):
-- +----+-------------+---------+--------+---------------------+-----------+------+-------+
-- | id | select_type | table   | type   | key                 | rows      | Extra        |
-- +----+-------------+---------+--------+---------------------+-----------+--------------+
-- |  1 | SIMPLE      | pedidos | ref    | idx_cliente         | 12        |              |
-- +----+-------------+---------+--------+---------------------+-----------+--------------+

-- Las columnas que más importan:
-- type:  el tipo de acceso (de mejor a peor):
--        system → const → eq_ref → ref → range → index → ALL
-- key:   qué índice se usó (NULL = full table scan)
-- rows:  estimación de filas que el motor va a examinar
-- Extra: información adicional (Using index, Using filesort, Using temporary...)

-- Consulta sin índice → full table scan (ALL): muy lento en tablas grandes
EXPLAIN SELECT * FROM pedidos WHERE estado = 'completado';

-- type: ALL     ← escanea TODAS las filas de la tabla
-- key:  NULL    ← no usa ningún índice
-- rows: 5000000 ← el motor examina 5 millones de filas

-- Lo que buscas:
-- type = ref o range: usa un índice, bien
-- type = ALL con rows alto: problema serio, necesita índice

-- EXPLAIN con formato JSON (más detallado) en MySQL
EXPLAIN FORMAT=JSON SELECT * FROM pedidos WHERE estado = 'completado';

-- EXPLAIN ANALYZE (ejecuta la consulta y muestra tiempos reales) en MySQL 8.0+
EXPLAIN ANALYZE SELECT * FROM pedidos WHERE cliente_id = 123;

EXPLAIN ANALYZE en PostgreSQL

EXPLAIN ANALYZE SELECT * FROM pedidos WHERE cliente_id = 123;

-- Salida de EXPLAIN ANALYZE en PostgreSQL:
-- Index Scan using idx_cliente on pedidos  (cost=0.56..45.23 rows=12 width=48)
--                                          (actual time=0.043..0.087 rows=12 loops=1)
--   Index Cond: (cliente_id = 123)
-- Planning Time: 0.312 ms
-- Execution Time: 0.124 ms

-- Cómo leer los números:
-- cost=0.56..45.23:  coste estimado (inicio..total), en unidades arbitrarias
-- rows=12:           filas estimadas
-- actual time=0.043..0.087: tiempo real (primera fila..última fila) en ms
-- rows=12:           filas reales devueltas
-- loops=1:           cuántas veces se ejecutó este nodo

-- Las señales de alarma en EXPLAIN ANALYZE de PostgreSQL:
-- Seq Scan en tabla grande:         full table scan, falta índice
-- Sort con alta memoria:            ordenación costosa, considera índice en ORDER BY
-- Hash Join vs Nested Loop:         el optimizador eligió el join menos eficiente
-- rows estimadas muy distintas de reales: estadísticas desactualizadas (ejecuta ANALYZE)
-- loops alto:                       el nodo se ejecuta muchas veces (problema en subqueries correlacionadas)

-- Ver el plan sin ejecutar (solo la estimación)
EXPLAIN SELECT * FROM pedidos WHERE estado = 'completado';

-- Ver el plan con estadísticas de buffers (muy útil para I/O)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM pedidos WHERE estado = 'completado';

Estrategia 1: añadir los índices correctos

La mayoría de consultas lentas se resuelven añadiendo un índice en la columna o combinación de columnas usadas en WHERE, JOIN u ORDER BY.

-- Problema: consulta lenta por full table scan en 'estado'
EXPLAIN SELECT * FROM pedidos WHERE estado = 'completado';
-- type: ALL, rows: 5000000

-- Solución: crear un índice en la columna
CREATE INDEX idx_pedidos_estado ON pedidos(estado);

-- Verificar que ahora usa el índice
EXPLAIN SELECT * FROM pedidos WHERE estado = 'completado';
-- type: ref, key: idx_pedidos_estado, rows: 1200000

Índices compuestos: el orden importa mucho

-- Consulta que filtra y ordena por varias columnas
SELECT * FROM pedidos
WHERE estado = 'completado'
  AND fecha >= '2026-01-01'
ORDER BY total DESC;

-- Índice compuesto: el orden de las columnas define su efectividad
-- Regla general: primero las columnas de igualdad (=), luego las de rango (>, <, BETWEEN)
CREATE INDEX idx_pedidos_estado_fecha ON pedidos(estado, fecha);

-- Con este índice, la consulta anterior puede usar el índice para:
-- 1. Filtrar por estado = 'completado'
-- 2. Filtrar por fecha >= '2026-01-01'
-- El ORDER BY total DESC no puede aprovechar el índice y necesita ordenación extra

-- Si el ORDER BY es muy frecuente, considera:
CREATE INDEX idx_pedidos_estado_fecha_total ON pedidos(estado, fecha, total);

-- Índice para un JOIN frecuente
SELECT c.nombre, COUNT(p.id) AS total_pedidos
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id
WHERE c.pais = 'España'
GROUP BY c.id, c.nombre;

-- idx_cliente en pedidos ya existe → el JOIN está cubierto
-- El problema puede estar en el WHERE sobre clientes
CREATE INDEX idx_clientes_pais ON clientes(pais);
-- Ahora el motor puede filtrar los clientes de España eficientemente antes del JOIN

Índices de cobertura (covering index)

-- Una consulta que solo necesita ciertas columnas puede beneficiarse
-- de un índice que las incluya todas, evitando acceder a la tabla

-- Consulta:
SELECT email, nombre FROM clientes WHERE ciudad = 'Madrid' AND activo = TRUE;

-- Sin covering index: el motor usa el índice para filtrar, luego va a la tabla
-- a buscar 'email' y 'nombre' (doble acceso)

-- Con covering index: todas las columnas que necesita están en el índice
CREATE INDEX idx_clientes_ciudad_activo_cover
  ON clientes(ciudad, activo, email, nombre);   -- incluye las columnas del SELECT

-- Ahora EXPLAIN muestra "Using index" en la columna Extra de MySQL,
-- o "Index Only Scan" en PostgreSQL → no accede a la tabla, solo al índice

PostgreSQL: índices parciales

-- Un índice parcial indexa solo las filas que cumplen una condición
-- Mucho más pequeño y eficiente cuando las consultas siempre filtran por ese valor

-- Si el 90% de consultas buscan pedidos 'completados':
CREATE INDEX idx_pedidos_completados
  ON pedidos(fecha, total)
  WHERE estado = 'completado';   -- solo indexa las filas completadas

-- El índice es mucho más pequeño que uno en toda la tabla
-- y las consultas WHERE estado = 'completado' lo aprovechan al máximo

Estrategia 2: reescribir las consultas problemáticas

Evitar funciones en la columna indexada del WHERE

-- ❌ El índice en 'fecha' no puede usarse: la función YEAR() transforma la columna
SELECT * FROM pedidos WHERE YEAR(fecha) = 2026;
-- type: ALL → full table scan

-- ✅ Usar un rango de fechas equivalente: el índice sí se usa
SELECT * FROM pedidos
WHERE fecha >= '2026-01-01' AND fecha < '2027-01-01';
-- type: range → usa el índice

-- Lo mismo aplica con cualquier función:
-- ❌  WHERE LOWER(email) = 'ana@mail.com'
-- ✅  WHERE email = 'ana@mail.com'  (si el email ya está en minúsculas)
-- ✅  O usar un índice funcional (PostgreSQL): CREATE INDEX ON clientes(LOWER(email))

Subconsultas correlacionadas vs JOIN

-- ❌ Subconsulta correlacionada: se ejecuta UNA VEZ POR FILA del resultado exterior
SELECT c.nombre,
  (SELECT COUNT(*) FROM pedidos p WHERE p.cliente_id = c.id) AS total_pedidos
FROM clientes c
WHERE c.pais = 'España';
-- Si hay 50.000 clientes en España, ejecuta 50.000 subconsultas

-- ✅ JOIN con GROUP BY: se ejecuta una sola vez
SELECT c.nombre, COUNT(p.id) AS total_pedidos
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
WHERE c.pais = 'España'
GROUP BY c.id, c.nombre;
-- Mucho más eficiente: un solo recorrido con índice en el JOIN

EXISTS vs IN para subqueries grandes

-- ❌ IN con subconsulta grande: puede ser lento con muchos valores
SELECT * FROM clientes
WHERE id IN (
  SELECT DISTINCT cliente_id FROM pedidos WHERE total > 1000
);

-- ✅ EXISTS es más eficiente: para en cuanto encuentra el primer resultado
SELECT * FROM clientes c
WHERE EXISTS (
  SELECT 1 FROM pedidos p
  WHERE p.cliente_id = c.id AND p.total > 1000
);

-- ✅ Alternativa con JOIN (a menudo la más rápida):
SELECT DISTINCT c.*
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id
WHERE p.total > 1000;

SELECT * vs columnas específicas

-- ❌ SELECT * trae todas las columnas, incluyendo las que no necesitas
-- Impide los covering indexes y transfiere más datos por la red
SELECT * FROM clientes WHERE pais = 'España';

-- ✅ Seleccionar solo lo que necesitas
SELECT id, nombre, email FROM clientes WHERE pais = 'España';
-- Permite covering index, menos datos transferidos, plan más eficiente

Estrategia 3: estadísticas actualizadas

El optimizador de la base de datos toma decisiones basadas en estadísticas sobre la distribución de los datos: cuántas filas tiene la tabla, cuántos valores distintos hay en cada columna, etc. Si esas estadísticas están desactualizadas, el optimizador puede elegir un plan de ejecución incorrecto.

-- MySQL: actualizar estadísticas de una tabla
ANALYZE TABLE pedidos;
ANALYZE TABLE clientes;

-- Ver estadísticas actuales en MySQL
SHOW TABLE STATUS LIKE 'pedidos';
SELECT * FROM information_schema.STATISTICS WHERE TABLE_NAME = 'pedidos';

-- PostgreSQL: actualizar estadísticas
ANALYZE pedidos;
ANALYZE;   -- analizar toda la base de datos

-- Ver cuándo fue el último ANALYZE
SELECT schemaname, relname, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_analyze NULLS FIRST;

-- PostgreSQL tiene autovacuum que ejecuta ANALYZE automáticamente,
-- pero en tablas con muchos cambios puede quedarse atrás
-- Puedes configurarlo por tabla:
ALTER TABLE pedidos SET (autovacuum_analyze_scale_factor = 0.01);
-- ANALYZE cuando el 1% de filas han cambiado (en lugar del 20% por defecto)

Estrategia 4: paginación eficiente

-- ❌ OFFSET grande es muy lento: el motor descarta millones de filas
SELECT * FROM pedidos ORDER BY fecha DESC LIMIT 20 OFFSET 500000;
-- El motor lee y descarta 500.000 filas antes de devolver las 20 que quieres

-- ✅ Paginación por cursor (keyset pagination): más eficiente
-- Primera página:
SELECT * FROM pedidos ORDER BY fecha DESC, id DESC LIMIT 20;

-- Siguiente página: usar el último id y fecha de la página anterior como cursor
SELECT * FROM pedidos
WHERE (fecha, id) < ('2026-05-15 14:32:00', 98765)   -- cursor de la página anterior
ORDER BY fecha DESC, id DESC
LIMIT 20;

-- El índice compuesto en (fecha, id) hace que esta consulta sea instantánea
-- sin importar en qué "página" estés
CREATE INDEX idx_pedidos_fecha_id ON pedidos(fecha DESC, id DESC);

Estrategia 5: identificar y eliminar índices inútiles

-- Los índices no son gratis: ralentizan los INSERT, UPDATE y DELETE
-- y consumen espacio en disco. Hay que eliminar los que no se usan.

-- MySQL: ver el uso de índices (requiere performance_schema activo)
SELECT
  object_schema,
  object_name,
  index_name,
  count_star AS veces_usado
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mi_base_datos'
  AND index_name IS NOT NULL
  AND count_star = 0   -- índices que NUNCA se han usado desde el último reset
ORDER BY object_name;

-- PostgreSQL: índices no usados
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan AS veces_usado
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE '%pkey%'   -- excluir claves primarias
ORDER BY tablename;

-- Antes de eliminar un índice, verificar que lleva suficiente tiempo en producción
-- y que no se usa estacionalmente (ej: índice para reportes mensuales)
DROP INDEX idx_pedidos_columna_obsoleta ON pedidos;          -- MySQL
DROP INDEX idx_pedidos_columna_obsoleta;                     -- PostgreSQL

Estrategia 6: analizar joins costosos

-- Consulta con múltiples joins que puede ser lenta
SELECT
  c.nombre,
  pr.nombre AS producto,
  p.total,
  p.fecha
FROM pedidos p
JOIN clientes c   ON c.id = p.cliente_id
JOIN productos pr ON pr.id = p.producto_id
WHERE c.pais = 'España'
  AND pr.categoria = 'Electrónica'
  AND p.fecha >= '2026-01-01';

-- EXPLAIN para ver cómo el motor ejecuta el join:
EXPLAIN this_query;

-- Índices que hacen este join eficiente:
CREATE INDEX idx_clientes_pais      ON clientes(pais);
CREATE INDEX idx_productos_categoria ON productos(categoria);
CREATE INDEX idx_pedidos_fecha      ON pedidos(fecha);
-- idx_cliente ya existe en pedidos(cliente_id)
-- La PK de productos cubre el JOIN desde pedidos

-- Con estos índices, el motor puede:
-- 1. Filtrar clientes de España por índice (pocos resultados)
-- 2. Filtrar productos de Electrónica por índice (pocos resultados)
-- 3. Hacer el JOIN eficientemente usando los índices en ambos lados

Checklist de diagnóstico

Cuando una consulta es lenta, sigue este orden:

1. Ejecuta EXPLAIN (ANALYZE) y busca type: ALL en MySQL o Seq Scan en PostgreSQL con muchas filas.
2. Revisa la columna key: si es NULL en MySQL o no aparece índice en PostgreSQL, falta un índice.
3. Compara filas estimadas con reales: si hay una gran diferencia, ejecuta ANALYZE TABLE.
4. Busca Using filesort o Using temporary en MySQL (o Sort / HashAggregate en PostgreSQL) con muchas filas: puede necesitar un índice en la columna de ORDER BY o GROUP BY.
5. Verifica si hay funciones en el WHERE sobre columnas indexadas: impiden el uso del índice.
6. Busca subconsultas correlacionadas: reemplázalas con JOINs siempre que sea posible.
7. Revisa el OFFSET: si es grande, migra a paginación por cursor.
8. Comprueba índices duplicados o no usados: elimínalos para mejorar las escrituras.

Resumen

• Identifica las consultas lentas con el slow query log de MySQL o pg_stat_statements en PostgreSQL antes de optimizar.
• EXPLAIN muestra el plan de ejecución estimado. EXPLAIN ANALYZE lo ejecuta y muestra tiempos reales. Busca type: ALL / Seq Scan en tablas grandes y key: NULL como señales de alerta.
• La solución más habitual es añadir el índice correcto en las columnas del WHERE, JOIN y ORDER BY. En índices compuestos, el orden importa: primero las columnas de igualdad, luego las de rango.
• Las funciones sobre columnas en el WHERE inutilizan los índices. Reescribe la condición para que la columna quede "sola".
• Las subconsultas correlacionadas se ejecutan una vez por fila: reemplázalas con JOINs.
• Mantén las estadísticas actualizadas con ANALYZE TABLE / ANALYZE: el optimizador las necesita para elegir el mejor plan.
• El OFFSET grande es muy lento: usa paginación por cursor para listas grandes.
• Elimina los índices que no se usen: ralentizan las escrituras sin aportar nada.