Índices: acelerar las búsquedas

El problema: el escaneo de tabla completo

Cuando ejecutas una consulta con un filtro, por defecto la base de datos hace un escaneo de tabla completo (full table scan): recorre todas las filas una a una para comprobar cuáles cumplen la condición.

SELECT * FROM pedidos WHERE cliente_id = 11;

Con 10 filas no se nota. Con 10 millones de filas, recorrerlas todas en cada consulta es inviable. El coste crece de forma lineal: O(n).

La solución: un índice

Un índice es una estructura de datos auxiliar, ordenada, que la base de datos mantiene aparte de la tabla. Funciona como el índice alfabético de un libro: en vez de leer el libro entero buscando una palabra, vas al índice (ordenado) y saltas directamente a la página.

Internamente, la mayoría de motores (SQLite, PostgreSQL, MySQL) usan un árbol B+ (B-tree): un árbol equilibrado donde cada nivel divide el espacio de búsqueda. Buscar un valor cuesta O(log n): con 10 millones de filas, en lugar de 10.000.000 de comparaciones, bastan ~24.

Crear un índice

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

A partir de ese momento, las consultas que filtran (WHERE), ordenan (ORDER BY) o agrupan por cliente_id pueden usar el índice. La clave primaria (PRIMARY KEY) está indexada automáticamente; por eso buscar por id ya es rápido sin hacer nada.

Un índice puede abarcar varias columnas (índice compuesto). El orden importa: idx (pais, estado) sirve para filtrar por pais o por pais AND estado, pero no para filtrar solo por estado.

CREATE INDEX idx_pedidos_pais_estado ON pedidos (pais, estado);

¿Cuándo crear un índice?

Crea índices en las columnas que aparecen con frecuencia en:

• WHERE (filtros), especialmente sobre tablas grandes.
• JOIN ... ON (las claves foráneas casi siempre merecen un índice).
• ORDER BY y GROUP BY.

El coste: no son gratis

Los índices no son magia gratuita. Tienen un precio:

• Escrituras más lentas. Cada INSERT, UPDATE o DELETE debe actualizar también todos los índices afectados. Una tabla con muchos índices escribe más despacio.
• Espacio en disco. El índice es una copia ordenada de las columnas; ocupa memoria y disco adicionales.
• Selectividad. Un índice sobre una columna con pocos valores distintos (p. ej. estado con solo 3 valores) aporta poco: el motor podría preferir un escaneo igualmente.

Regla práctica: indexa lo que consultas mucho, no todo. Un índice no usado es coste puro sin beneficio.

Ver qué hace el planificador: EXPLAIN QUERY PLAN

Para saber si una consulta usa un índice o escanea la tabla, antepón EXPLAIN QUERY PLAN (sintaxis de SQLite; en PostgreSQL/MySQL es EXPLAIN):

EXPLAIN QUERY PLAN
SELECT * FROM pedidos WHERE cliente_id = 11;

• SCAN pedidos → escaneo completo (sin índice útil).
• SEARCH pedidos USING INDEX idx_pedidos_cliente → ¡usa el índice!

Es la herramienta nº 1 para diagnosticar consultas lentas: primero mide, no adivines.

Ejemplos

Crear un índice y consultar usándolo

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

SELECT id, total
FROM pedidos
WHERE cliente_id = 11
ORDER BY id;

Diagnosticar el plan de ejecución

EXPLAIN QUERY PLAN
SELECT * FROM pedidos WHERE cliente_id = 11;