Cómo funcionan los índices de una base de datos: del full table scan al B-Tree

Buscas un solo email en una tabla con un millón de filas. La consulta tarda cuatro segundos. La base de datos no es lenta; simplemente no le diste indicaciones.

Full table scan

Sin un índice, la base de datos no tiene alternativa: lee las filas una por una, de principio a fin. El registro que buscas podría estar en la última fila, así que tiene que revisarlas todas. Esto se llama full table scan: un millón de filas, un millón de comparaciones.

La analogía de la guía telefónica

Cuando buscas a alguien en una guía telefónica, ¿vas pasando las páginas una por una? No, te guías por el orden alfabético y saltas directo a la letra. Un índice es exactamente eso: una copia de los datos ordenada de antemano. Esa copia vive en una estructura de árbol llamada B-Tree y reduce el espacio de búsqueda a la mitad en cada paso. En lugar de un millón de comparaciones, bastan unas veinte.

La solución en una línea

CREATE INDEX idx_users_email ON users (email);

La misma consulta pasa de cuatro segundos a dos milisegundos, es decir, unas dos mil veces más rápida. No está mal para una migración de una sola línea.

Los índices no son una bala de plata

Pero ponerle un índice a cada columna no es la solución: cada operación de escritura también tiene que actualizar ese índice, así que las consultas INSERT/UPDATE se vuelven más lentas. Además, ciertos patrones de consulta no usan el índice en absoluto; estos son los dos más clásicos:

• Un patrón LIKE '%istanbul' que empieza con el signo de porcentaje (al buscar desde el final el índice se desactiva; LIKE 'istanbul%' no tiene problema)
• Envolver una columna en una función: WHERE LOWER(email) = ...: el índice está sobre email, no sobre LOWER(email)

Conclusión

Cuando veas una consulta lenta, ejecuta primero EXPLAIN ANALYZE. La mayoría de las veces, el problema no está en tu código, sino en un índice que falta o está mal configurado.