Índices en SQL: qué son y cómo acelerar una consulta

Lección 7 de 8. Una consulta correcta puede ser lentísima. La diferencia la marcan casi siempre los índices en SQL: qué son, cómo comprobar con EXPLAIN si el motor los usa y por qué a veces no puede. Con una tabla de un millón de filas y tiempos medidos, no supuestos.

Buscar sin índice: leerlo todo

Con las 12 filas de clientes, cualquier consulta es instantánea. Pero las tablas reales tienen millones de filas, y ahí aparece la pregunta: ¿cómo encuentra el motor las filas que pides? Si no tiene ayuda, de la única forma posible: leyendo la tabla entera, fila por fila, y quedándose con las que cumplen el WHERE. A eso se le llama un recorrido completo o scan.

Para medirlo hace falta una tabla grande. Esta crea ventas con un millón de filas, generadas con la CTE recursiva de la lección 5 y fórmulas en vez de números al azar, para que los resultados se puedan repetir:

CREATE TABLE ventas (
  id          INTEGER PRIMARY KEY,
  cliente_id  INTEGER NOT NULL,
  producto_id INTEGER NOT NULL,
  fecha       TEXT NOT NULL,
  importe     REAL NOT NULL
);

INSERT INTO ventas (cliente_id, producto_id, fecha, importe)
WITH RECURSIVE n(i) AS (
  SELECT 1
  UNION ALL
  SELECT i + 1 FROM n WHERE i < 1000000
)
SELECT (i * 7919) % 50000 + 1,
       (i * 31) % 15 + 1,
       date('2024-01-01', '+' || (i % 1000) || ' days'),
       ((i * 37) % 200000) / 100.0
FROM n;

en este equipo1,000,000 filas, 50,000 clientes distintos, fechas de 2024-01-01 a 2026-09-26
creada en 0.8 s; la base ocupa 33.4 MB

Ahora, una consulta corriente: cuántas ventas tiene el cliente 4242 y por cuánto.

SELECT COUNT(*) AS ventas, ROUND(SUM(importe), 2) AS total
FROM ventas
WHERE cliente_id = 4242;

sin índiceventas: 20, total: 22128.6
mediana de 7 ejecuciones: 38.6 ms

Unos milisegundos parece poco, pero es el tiempo de leer un millón de filas para quedarse con 20. Si esa consulta la hace cada visita de una web, con cientos de visitas por segundo, el servidor no hace otra cosa.

Qué es un índice

Un índice es una estructura aparte, ordenada por una o varias columnas, que dice dónde está cada fila. Funciona como el índice alfabético de un libro: en vez de leer todas las páginas buscando una palabra, vas a la letra, encuentras la palabra y saltas a su página. Casi todos los motores usan para eso un árbol B, que se mantiene ordenado y equilibrado aunque se inserten y borren filas.

CREATE INDEX idx_ventas_cliente ON ventas (cliente_id);

con índiceventas: 20, total: 22128.6
mediana de 7 ejecuciones: 0.039 ms

El mismo resultado, unas 994 veces más rápido. Los milisegundos exactos dependen de la máquina, pero la proporción entre leerlo todo y bajar por un árbol se mantiene: y cuanto más grande la tabla, mayor la diferencia.

EXPLAIN QUERY PLAN: cómo piensa buscar el motor

No hace falta cronometrar para saber si una consulta usa un índice. Anteponiendo EXPLAIN QUERY PLAN, SQLite no ejecuta la consulta: dice cómo la ejecutaría. Antes y después de crear el índice:

EXPLAIN QUERY PLAN
SELECT COUNT(*) AS ventas, ROUND(SUM(importe), 2) AS total
FROM ventas
WHERE cliente_id = 4242;

sin índiceQUERY PLAN
`--SCAN ventas

con índiceQUERY PLAN
`--SEARCH ventas USING INDEX idx_ventas_cliente (cliente_id=?)

SCAN significa recorrerla entera; SEARCH ... USING INDEX, ir directamente con el índice. Es lo primero que hay que mirar cuando una consulta va lenta. En la base «tienda» ya hay índices que no creamos nosotros: la clave primaria es en sí misma un índice, y SQLite crea uno automático para cada columna UNIQUE, como el email de clientes:

EXPLAIN QUERY PLAN SELECT nombre FROM clientes WHERE id = 5;
EXPLAIN QUERY PLAN SELECT nombre FROM clientes WHERE email = 'ana.torres@ejemplo.com';
EXPLAIN QUERY PLAN SELECT nombre FROM clientes WHERE pais = 'México';

Ábrela en el editor y cámbiala →

resultadoQUERY PLAN
`--SEARCH clientes USING INTEGER PRIMARY KEY (rowid=?)

resultadoQUERY PLAN
`--SEARCH clientes USING INDEX sqlite_autoindex_clientes_1 (email=?)

resultadoQUERY PLAN
`--SCAN clientes

Para buscar por pais no hay índice y toca recorrer la tabla. Con 12 filas da igual: un índice solo compensa cuando la tabla es grande y la consulta se queda con una parte pequeña de ella.

Índices compuestos: el orden importa

Un índice puede abarcar varias columnas. Se ordena por la primera y, dentro de cada valor de la primera, por la segunda, como una guía telefónica por apellido y nombre. Sirve para buscar por las dos columnas a la vez, o por la primera sola:

CREATE INDEX idx_ventas_cliente_fecha ON ventas (cliente_id, fecha);

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM ventas
WHERE cliente_id = 4242 AND fecha >= '2025-01-01';

resultadoQUERY PLAN
`--SEARCH ventas USING COVERING INDEX idx_ventas_cliente_fecha (cliente_id=? AND fecha>?)

COVERING INDEX significa que el índice tiene todo lo que la consulta necesita, y el motor ni siquiera va a la tabla. Pero ese índice no ayuda a buscar solo por fecha, igual que la guía telefónica no sirve para buscar a todos los que se llaman Ana:

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM ventas
WHERE fecha >= '2025-01-01';

resultadoQUERY PLAN
`--SCAN ventas USING COVERING INDEX idx_ventas_cliente_fecha

Dice SCAN: recorre el índice entero, de principio a fin, porque las fechas están desperdigadas dentro de cada cliente. Lo usa solo porque es más pequeño que la tabla. Para buscar por fecha hace falta un índice que empiece por la fecha. Por eso el orden de las columnas de un índice compuesto se elige según las consultas que lo van a usar.

Cuándo el índice no sirve

Un índice ordena los valores tal como están guardados. Si la condición aplica una función a la columna, el motor ya no puede usar ese orden. Con un índice sobre fecha, estas dos consultas cuentan las mismas ventas de marzo de 2025:

CREATE INDEX idx_ventas_fecha ON ventas (fecha);

SELECT COUNT(*) FROM ventas
WHERE strftime('%Y-%m', fecha) = '2025-03';

planQUERY PLAN
`--SCAN ventas USING COVERING INDEX idx_ventas_fecha

con strftimefilas: 31,000
mediana: 205.5 ms

SELECT COUNT(*) FROM ventas
WHERE fecha >= '2025-03-01' AND fecha < '2025-04-01';

planQUERY PLAN
`--SEARCH ventas USING COVERING INDEX idx_ventas_fecha (fecha>? AND fecha<?)

con un rangofilas: 31,000
mediana: 0.795 ms

La primera tiene que calcular strftime para el millón de fechas y compararlas una a una. La segunda pregunta por un intervalo de valores, que en un índice ordenado es un tramo seguido. Es la misma razón por la que LIKE '%texto', con el comodín al principio, no puede aprovechar un índice: el principio del valor es lo que está ordenado.

Lo que cuesta un índice

Si los índices aceleran tanto, ¿por qué no indexar todas las columnas? Porque no son gratis. Ocupan espacio y, sobre todo, cada INSERT, UPDATE o DELETE tiene que actualizar la tabla y todos sus índices. El de cliente_id costó esto:

en este equipocrear el índice: 0.27 s
tamaño de la base: 33.4 MB sin el índice, 44.8 MB con él (+11.4 MB)

La regla práctica: crea índices para las columnas por las que filtras, unes (las claves foráneas) u ordenas en las consultas frecuentes, comprueba con EXPLAIN QUERY PLAN que se usan, y no crees los que nadie usa.

EXPLAIN SELECT COUNT(*), ROUND(SUM(importe), 2) FROM ventas WHERE cliente_id = 4242;

PostgreSQL 17                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Aggregate  (cost=81.80..81.82 rows=1 width=40)
   ->  Bitmap Heap Scan on ventas  (cost=4.58..81.70 rows=20 width=6)
         Recheck Cond: (cliente_id = 4242)
         ->  Bitmap Index Scan on idx_ventas_cliente  (cost=0.00..4.58 rows=20 width=0)
               Index Cond: (cliente_id = 4242)
(5 rows)

EXPLAIN FORMAT=TREE SELECT COUNT(*), ROUND(SUM(importe), 2) FROM ventas WHERE cliente_id = 4242;

MySQL 8.4+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                  |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: sum(ventas.importe), count(0)  (cost=9 rows=1)
    -> Index lookup on ventas using idx_ventas_cliente (cliente_id=4242)  (cost=7 rows=20)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+

Resumen

Retos

EXPLAIN QUERY PLAN SELECT * FROM pedidos WHERE cliente_id = 1;
CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);
EXPLAIN QUERY PLAN SELECT * FROM pedidos WHERE cliente_id = 1;

resultadoQUERY PLAN
`--SCAN pedidos

QUERY PLAN
`--SEARCH pedidos USING INDEX idx_pedidos_cliente (cliente_id=?)

CREATE INDEX idx_clientes_alta ON clientes (alta);
EXPLAIN QUERY PLAN SELECT nombre FROM clientes WHERE substr(alta, 1, 4) = '2025';
EXPLAIN QUERY PLAN SELECT nombre FROM clientes WHERE alta >= '2025-01-01' AND alta < '2026-01-01';

resultadoQUERY PLAN
`--SCAN clientes

QUERY PLAN
`--SEARCH clientes USING INDEX idx_clientes_alta (alta>? AND alta<?)

Todas las consultas de esta lección se ejecutaron en SQLite 3.46.1 sobre la base de ejemplo «tienda» antes de publicarla, y los resultados están copiados de esa ejecución; las diferencias con PostgreSQL 17 y MySQL 8.4 se comprobaron en esos motores. ¿Quieres probar algo? Usa el editor de SQL, que ya trae la base cargada.