Contenido de esta guía
Qué es un índice
Un índice guarda una representación ordenada o especializada de una o varias columnas. El optimizador puede utilizarla para resolver ciertos filtros, relaciones y ordenaciones con menos trabajo.
El índice no cambia las filas ni el resultado lógico de la consulta. Cambia las rutas que el motor puede considerar para encontrarlas.
Cuándo suele ayudar
- Columnas usadas con frecuencia en
WHERE. - Claves empleadas para unir tablas.
- Ordenaciones o búsquedas por rangos compatibles con el índice.
- Restricciones
UNIQUE, que suelen apoyarse en una estructura de índice.
No todos los índices son útiles. Una tabla pequeña puede recorrerse completa con menor coste; una columna con pocos valores diferentes puede aportar poca selectividad.
Ejemplo guiado: un índice para un patrón de consulta
Supón que una pantalla consulta repetidamente los pedidos de un cliente y los muestra en orden cronológico:
SELECT id, fecha, total
FROM pedidos
WHERE cliente_id = 1
ORDER BY fecha, id;Un índice compuesto puede empezar por la columna de igualdad y continuar con las columnas de ordenación:
CREATE INDEX idx_pedidos_cliente_fecha
ON pedidos (cliente_id, fecha, id);
PRAGMA index_list('pedidos');| name | unique | origin |
|---|---|---|
| idx_pedidos_cliente_fecha | 0 | c |
El orden de las columnas expresa el patrón que quieres ayudar: primero filtrar por cliente_id y después recorrer las filas por fecha e id. La presencia del índice no obliga al optimizador a utilizarlo; la tabla educativa es pequeña y un recorrido completo puede seguir siendo razonable.
No diseñes un índice mirando solo una columna. Parte de una consulta frecuente, comprueba el plan y mide con datos representativos.
El coste que también debes considerar
Cada INSERT, UPDATE o DELETE puede necesitar actualizar el índice. Además, ocupa espacio y requiere mantenimiento. Crear índices «por si acaso» puede empeorar las escrituras y complicar el sistema.
Error frecuente
Suponer que un índice siempre acelera
Comprueba el plan con EXPLAIN y mide una carga representativa. Un índice que no se usa, que duplica otro o que empieza por columnas poco útiles añade coste sin beneficio claro.
Comprueba lo aprendido
Ayuda a localizar las líneas de un pedido
Crea un índice llamado idx_detalle_pedido_pedido_producto sobre detalle_pedido. Debe servir para filtrar por pedido_id y devolver los productos en orden de producto_id. Comprueba después que SQLite lo registró.
producto_id como segunda columna.PRAGMA index_list('detalle_pedido').Solución razonada
CREATE INDEX idx_detalle_pedido_pedido_producto
ON detalle_pedido (pedido_id, producto_id);
PRAGMA index_list('detalle_pedido');| name | unique |
|---|---|
| idx_detalle_pedido_pedido_producto | 0 |
El índice empieza por la columna del filtro y conserva producto_id a continuación. El valor de seq puede variar si la tabla ya tiene otros índices.
Compatibilidad entre motores
CREATE INDEX está muy extendido, pero opciones como índices parciales, expresiones, métodos, inclusión de columnas o creación concurrente son específicas de cada motor. Los catálogos y el formato de los planes también cambian.
Qué debes recordar
- Un índice ofrece rutas de acceso; no modifica el resultado.
- Puede mejorar lecturas y encarecer escrituras.
- El optimizador decide si lo utiliza.
- EXPLAIN y las mediciones confirman si aporta valor.
Conceptos relacionados
Fuentes técnicas
La explicación se ha contrastado con documentación oficial: