Índices y el plan, o por qué tarda

Llega el día. La consulta que corrías todas las mañanas en dos segundos hoy tarda cuarenta, o directamente no vuelve. No cambiaste nada: creció la tabla.

Este capítulo va de por qué pasa eso y de cómo preguntarle a la base qué piensa hacer antes de que lo haga. Porque adivinar por qué una consulta es lenta es perder la tarde, y preguntarle son diez segundos 🔍

Preguntarle a la base qué va a hacer

EXPLAIN QUERY PLAN
SELECT id, fecha, monto FROM pedidos WHERE canal = 'WhatsApp' AND monto > 1200;

id  parent  notused  detail
--  ------  -------  ------------
2   0       0        SCAN pedidos

De esas cuatro columnas la única que importa es detail; las otras tres son numeración interna. Y lo que dice es SCAN pedidos.

SCAN quiere decir que va a leer las 900 filas, una por una, y mirar en cada una si el canal es WhatsApp. Con 900 no lo notas. Con 90 millones, ahí está tu tarde.

Es como buscar a alguien en un edificio tocando todas las puertas. Funciona, y no hace falta que te explique por qué no es la mejor idea 🚪

Un índice

CREATE INDEX idx_pedidos_canal ON pedidos (canal);
EXPLAIN QUERY PLAN
SELECT id, fecha, monto FROM pedidos WHERE canal = 'WhatsApp' AND monto > 1200;

id  parent  notused  detail
--  ------  -------  ------------------------------------------------------
3   0       0        SEARCH pedidos USING INDEX idx_pedidos_canal (canal=?)

SCAN se convirtió en SEARCH. Esa palabra es la que quieres ver.

Un índice es exactamente el índice de un libro: una lista ordenada de valores con la página donde está cada uno. La base ya no toca las 900 puertas, va directo a las 231 de WhatsApp.

Y el nombre no es decoración: idx_tabla_columna se lee solo cuando alguien mire los índices dentro de seis meses.

Cuando el orden también cuesta

EXPLAIN QUERY PLAN
SELECT id FROM pedidos WHERE canal = 'WhatsApp' ORDER BY monto DESC LIMIT 5;

id  parent  notused  detail
--  ------  -------  ------------------------------------------------------
5   0       0        SEARCH pedidos USING INDEX idx_pedidos_canal (canal=?)
21  0       0        USE TEMP B-TREE FOR ORDER BY

Dos líneas ahora. La primera es la buena; la segunda dice USE TEMP B-TREE FOR ORDER BY, y eso significa que la base tuvo que armarse una estructura temporal aparte solo para ordenar. Es trabajo extra que no se ve en el resultado pero sí en el reloj.

CREATE INDEX idx_pedidos_canal_monto ON pedidos (canal, monto);
EXPLAIN QUERY PLAN
SELECT id FROM pedidos WHERE canal = 'WhatsApp' ORDER BY monto DESC LIMIT 5;

id  parent  notused  detail
--  ------  -------  ---------------------------------------------------------------------
4   0       0        SEARCH pedidos USING COVERING INDEX idx_pedidos_canal_monto (canal=?)

Desapareció el temp b-tree. Un índice sobre (canal, monto) ya guarda los montos ordenados dentro de cada canal, así que el ORDER BY sale gratis.

Y fíjate en la palabra nueva: COVERING INDEX. Significa que todo lo que la consulta necesitaba estaba en el índice y ni siquiera hizo falta abrir la tabla. Es lo más rápido que se puede pedir 🏎️

EXPLAIN QUERY PLAN
SELECT canal, monto FROM pedidos WHERE canal = 'Web';

id  parent  notused  detail
--  ------  -------  ---------------------------------------------------------------------
2   0       0        SEARCH pedidos USING COVERING INDEX idx_pedidos_canal_monto (canal=?)

Otro covering: pedí canal y monto, y las dos están en el índice.

La regla del orden de las columnas

Un índice sobre (canal, monto) sirve para buscar por canal, y para buscar por canal y monto juntos. Pero mira qué pasa buscando solo por la segunda.

EXPLAIN QUERY PLAN
SELECT id FROM pedidos WHERE monto > 1200;

id  parent  notused  detail
--  ------  -------  ---------------------------------------------------------
2   0       0        SCAN pedidos USING COVERING INDEX idx_pedidos_canal_monto

Volvió el SCAN. Lee el índice entero en vez de la tabla entera, que es un poquito mejor, pero sigue siendo leerlo todo.

Es una guía telefónica ordenada por apellido y después por nombre: buscar "Flores" es instantáneo, buscar "todos los que se llamen Gera" es leer la guía completa. Un índice compuesto sirve desde la primera columna hacia la derecha, nunca al revés, y eso es igual en los cuatro motores.

Los cuatro sitios donde el índice deja de servir

Esto es lo más útil del capítulo. Tienes el índice, lo ves creado, y la consulta sigue haciendo SCAN. Casi siempre es una de estas cuatro.

CREATE INDEX idx_clientes_nombre ON clientes (nombre);
EXPLAIN QUERY PLAN
SELECT id FROM clientes WHERE nombre = 'Market Central 087';

id  parent  notused  detail
--  ------  -------  -------------------------------------------------------------------
2   0       0        SEARCH clientes USING COVERING INDEX idx_clientes_nombre (nombre=?)

Ese funciona: SEARCH. Ahora el mismo con una función encima.

EXPLAIN QUERY PLAN
SELECT id FROM clientes WHERE UPPER(nombre) = 'MARKET CENTRAL 087';

id  parent  notused  detail
--  ------  -------  ------------------------------------------------------
2   0       0        SCAN clientes USING COVERING INDEX idx_clientes_nombre

1. Una función sobre la columna mata el índice. El índice guarda Market Central 087, no MARKET CENTRAL 087, así que no puede ir directo. Volvió a SCAN: lee el índice entero, igual que en el caso de arriba, y eso es leerlo todo.

Y esto duele porque en el capítulo 5 dijimos que UPPER(columna) es la forma de buscar igual en los cuatro motores. Las dos cosas son verdad, y por eso hay una tercera 🌟

EXPLAIN QUERY PLAN
SELECT id FROM clientes WHERE nombre LIKE '%Central%';

id  parent  notused  detail
--  ------  -------  ------------------------------------------------------
2   0       0        SCAN clientes USING COVERING INDEX idx_clientes_nombre

2. Un LIKE que empieza por % mata el índice. Y no hay arreglo posible: buscar "algo que contenga Central" es como buscar en el índice de un libro las palabras que tengan una "c" en el medio. Para eso hacen falta índices de texto completo, que son otro invento.

EXPLAIN QUERY PLAN
SELECT id FROM clientes WHERE nombre GLOB 'Market*';

id  parent  notused  detail
--  ------  -------  --------------------------------------------------------------------------------
2   0       0        SEARCH clientes USING COVERING INDEX idx_clientes_nombre (nombre>? AND nombre<?)

En cambio buscar por el principio sí funciona: SEARCH, y encima te dice cómo lo hizo, nombre>? AND nombre<?. Convirtió el "empieza por Market" en un rango.

Aquí usé GLOB y no LIKE a propósito, y es un detalle precioso: el LIKE de SQLite no distingue mayúsculas (capítulo 5), y por eso tampoco puede usar el índice ni para buscar por el principio. La comodidad se paga. En PostgreSQL, donde LIKE sí distingue, LIKE 'Market%' usa el índice sin problema.

Y ahora la solución al problema del UPPER: si vas a buscar siempre en mayúsculas, indexa las mayúsculas.

CREATE INDEX idx_upper ON clientes (UPPER(nombre));
EXPLAIN QUERY PLAN
SELECT id FROM clientes WHERE UPPER(nombre) = 'MARKET CENTRAL 087';

id  parent  notused  detail
--  ------  -------  ------------------------------------------------
3   0       0        SEARCH clientes USING INDEX idx_upper (<expr>=?)

SEARCH clientes USING INDEX idx_upper. Un índice puede estar hecho sobre una expresión y no solo sobre una columna, y eso resuelve el choque entre "escribo igual para los cuatro motores" y "quiero que sea rápido".

La tercera y la cuarta no se ven bien en SQLite, así que van dichas:

3. Comparar una columna con otro tipo. Si id_cliente es un número y escribes WHERE id_cliente = '58' entre comillas, algunos motores convierten la columna entera y ahí se fue el índice. En MySQL pasa muchísimo.

4. Un OR entre columnas distintas. WHERE canal = 'Web' OR ciudad = 'Lima' no puede usar un solo índice para las dos. A veces se arregla partiéndolo en dos consultas con UNION, que es del capítulo 8.

Los índices y los JOIN

EXPLAIN QUERY PLAN
SELECT c.nombre, p.monto
FROM clientes c JOIN pedidos p ON p.id_cliente = c.id
WHERE c.ciudad = 'Lima';

id  parent  notused  detail
--  ------  -------  --------------------------------------------
3   0       0        SCAN p
5   0       0        SEARCH c USING INTEGER PRIMARY KEY (rowid=?)

Lee así: recorre pedidos entera y por cada fila va a buscar su cliente por la clave primaria. O sea 900 búsquedas. Funciona porque clientes.id es PRIMARY KEY y por tanto ya tiene índice sin que nadie lo pida.

CREATE INDEX idx_pedidos_cliente ON pedidos (id_cliente);
EXPLAIN QUERY PLAN
SELECT c.nombre, p.monto
FROM clientes c JOIN pedidos p ON p.id_cliente = c.id
WHERE c.ciudad = 'Lima';

id  parent  notused  detail
--  ------  -------  -------------------------------------------------------
4   0       0        SCAN c
8   0       0        SEARCH p USING INDEX idx_pedidos_cliente (id_cliente=?)

Se dio vuelta el plan: ahora recorre clientes (que son 120) y por cada uno busca sus pedidos con el índice nuevo. 120 búsquedas en vez de 900.

Y de aquí sale la regla que más rendimiento regala por menos trabajo: indexa siempre las columnas de clave foránea. La clave primaria la indexa el motor solo; la foránea, no (salvo en MySQL con InnoDB, que es el único de los cuatro que lo hace por su cuenta) 🔑

Lo que un índice no arregla

EXPLAIN QUERY PLAN
SELECT STRFTIME('%Y-%m', fecha) AS mes, COUNT(*) FROM pedidos GROUP BY mes;

id  parent  notused  detail
--  ------  -------  ----------------------------
6   0       0        SCAN pedidos
8   0       0        USE TEMP B-TREE FOR GROUP BY

SCAN y TEMP B-TREE FOR GROUP BY. Y está bien: si tienes que contar todos los pedidos de todos los meses, hay que leerlos todos, con índice o sin él.

Un índice sirve para encontrar pocas filas entre muchas. Si la consulta necesita casi todas, leer la tabla entera es lo más rápido que hay y el motor lo sabe. Por eso a veces creas un índice, el plan no cambia, y el motor tiene razón 🤷‍♀️

Lo que cuesta un índice

SELECT name, tbl_name FROM sqlite_master WHERE type = 'index' AND sql IS NOT NULL ORDER BY name;

name                     tbl_name
-----------------------  --------
idx_clientes_nombre      clientes
idx_pedidos_canal        pedidos
idx_pedidos_canal_monto  pedidos
idx_pedidos_cliente      pedidos
idx_upper                clientes

Cinco índices llevamos. Y aquí va el aviso, porque a todo el mundo le pasa: la primera reacción cuando algo va lento es crear índices, y cada índice ocupa espacio y hace más lento cada INSERT, UPDATE y DELETE, porque hay que mantenerlos todos al día.

Una tabla que se escribe mucho y se consulta poco no quiere índices. Una que se consulta mucho y se escribe de noche, sí. Y los que no usa nadie son solo peso muerto.

DROP INDEX idx_pedidos_canal;
SELECT COUNT(*) AS indices FROM sqlite_master WHERE type = 'index' AND sql IS NOT NULL;

indices
-------
4

Borré idx_pedidos_canal porque el de (canal, monto) ya hace su trabajo: un índice compuesto sirve también para buscar solo por su primera columna, así que el de una sola sobraba.

Un índice también puede ser UNIQUE, y ahí deja de ser solo velocidad y pasa a ser una promesa, como las del capítulo 10.

CREATE UNIQUE INDEX idx_producto_unico ON productos (nombre);
INSERT INTO productos (id, nombre, categoria, precio) VALUES (41, 'Producto 01', 'Snacks', 10);

IntegrityError: UNIQUE constraint failed: productos.nombre

Y uno mal escrito falla al crearse, que es la mejor forma de fallar.

CREATE INDEX idx_malo ON pedidos (id_no_existe);

OperationalError: no such column: id_no_existe

Las estadísticas, que es de lo que vive el planificador

ANALYZE;
SELECT COUNT(*) AS filas_de_estadisticas FROM sqlite_stat1;

filas_de_estadisticas
---------------------
7

El motor no elige el plan al azar: estima cuántas filas va a devolver cada paso y elige el camino más barato. Esa estimación sale de unas estadísticas que hay que mantener al día.

Si cargaste un millón de filas de golpe y las consultas se pusieron raras, esto es lo primero que hay que probar: el motor sigue creyendo que la tabla tiene cien filas y está eligiendo planes para cien filas.

Ejercicios

Siete sobre tu copia de tienda.db. Intenta antes de abrir 💛

EXPLAIN QUERY PLAN SELECT id, nombre FROM clientes WHERE ciudad = 'Cusco';

id  parent  notused  detail
--  ------  -------  -------------
2   0       0        SCAN clientes

CREATE INDEX idx_clientes_ciudad ON clientes (ciudad);
EXPLAIN QUERY PLAN SELECT id, nombre FROM clientes WHERE ciudad = 'Cusco';

id  parent  notused  detail
--  ------  -------  ----------------------------------------------------------
3   0       0        SEARCH clientes USING INDEX idx_clientes_ciudad (ciudad=?)

CREATE INDEX idx_clientes_ciudad_seg ON clientes (ciudad, segmento);
EXPLAIN QUERY PLAN SELECT id FROM clientes WHERE ciudad = 'Lima' AND segmento = 'Bodega';

id  parent  notused  detail
--  ------  -------  --------------------------------------------------------------------------------------
2   0       0        SEARCH clientes USING COVERING INDEX idx_clientes_ciudad_seg (ciudad=? AND segmento=?)

EXPLAIN QUERY PLAN SELECT id FROM clientes WHERE segmento = 'Bodega';

id  parent  notused  detail
--  ------  -------  ----------------------------------------------------------
2   0       0        SCAN clientes USING COVERING INDEX idx_clientes_ciudad_seg

EXPLAIN QUERY PLAN SELECT id, monto FROM pedidos WHERE id = 25;

id  parent  notused  detail
--  ------  -------  --------------------------------------------------
2   0       0        SEARCH pedidos USING INTEGER PRIMARY KEY (rowid=?)

EXPLAIN QUERY PLAN SELECT id FROM clientes WHERE LOWER(ciudad) = 'cusco';

id  parent  notused  detail
--  ------  -------  ------------------------------------------------------
2   0       0        SCAN clientes USING COVERING INDEX idx_clientes_ciudad

EXPLAIN QUERY PLAN
SELECT d.id, pr.nombre FROM detalle d JOIN productos pr ON pr.id = d.id_producto;

id  parent  notused  detail
--  ------  -------  ---------------------------------------------
3   0       0        SCAN d
5   0       0        SEARCH pr USING INTEGER PRIMARY KEY (rowid=?)

CREATE INDEX idx_detalle_producto ON detalle (id_producto);
EXPLAIN QUERY PLAN
SELECT pr.nombre, SUM(d.cantidad) FROM productos pr JOIN detalle d ON d.id_producto = pr.id
GROUP BY pr.id, pr.nombre;

id  parent  notused  detail
--  ------  -------  ---------------------------------------------------------
8   0       0        SCAN pr USING COVERING INDEX idx_producto_unico
10  0       0        SEARCH d USING INDEX idx_detalle_producto (id_producto=?)

CREATE UNIQUE INDEX idx_cliente_nombre_unico ON clientes (nombre);
INSERT INTO clientes (id, nombre, ciudad, segmento, fecha_alta)
VALUES (121, 'Market Central 087', 'Lima', 'Bodega', '2026-06-24');

IntegrityError: UNIQUE constraint failed: clientes.nombre

-- El CREATE INDEX es igual en los cuatro. Esta es de las buenas.
CREATE INDEX idx_pedidos_canal_fecha ON pedidos (canal, fecha);

-- PostgreSQL
EXPLAIN ANALYZE SELECT id FROM pedidos WHERE canal = 'Web' AND fecha >= '2026-01-01';

-- MySQL
EXPLAIN SELECT id FROM pedidos WHERE canal = 'Web' AND fecha >= '2026-01-01';

-- SQL Server
SET SHOWPLAN_ALL ON;
GO
SELECT id FROM pedidos WHERE canal = 'Web' AND fecha >= '2026-01-01';

-- SQLite
EXPLAIN QUERY PLAN SELECT id FROM pedidos WHERE canal = 'Web' AND fecha >= '2026-01-01';

Lo que te llevas

• 🔍 Pregúntale el plan antes de adivinar. En SQLite es EXPLAIN QUERY PLAN y la única columna que importa es detail.
• 🚪 SCAN es leer todo, SEARCH es ir directo. Los cuatro motores lo llaman distinto y significan lo mismo.
• 📚 Un índice compuesto sirve de izquierda a derecha: (canal, monto) vale para canal, no para monto solo.
• 🏎️ COVERING INDEX es cuando todo lo que pediste estaba en el índice y no hizo falta abrir la tabla.
• 💀 Lo que mata un índice: una función sobre la columna, un LIKE '%algo%', comparar con otro tipo y un OR entre columnas distintas.
• 🔑 Indexa las claves foráneas. La primaria la indexa el motor solo; la foránea solo MySQL con InnoDB.
• ⚖️ Cada índice hace más lenta cada escritura. Los que no usa nadie son peso muerto.
• 📊 Si los planes se pusieron raros después de una carga grande, actualiza las estadísticas.

En el capítulo 13 vienen las vistas, los procedimientos y los disparadores, o sea las cosas que viven guardadas dentro de la base en vez de en tu archivo de consultas.

Que tengas lindo día! 🌸