Aprende SQL · lección gratuita

Lección 41 · Índices: CREATE INDEX

Resumen

Un índice es una estructura de datos auxiliar (un árbol B-tree ordenado) que el motor mantiene en paralelo a la tabla para localizar filas sin recorrerla entera. Acelera drásticamente las búsquedas por igualdad (WHERE x = ?), rangos (BETWEEN, <, >), ordenamientos (ORDER BY) y joins, a cambio de algo más de espacio en disco y un pequeño coste extra en cada INSERT/UPDATE/DELETE.

• Acelera lecturas: convierte un SCAN (recorrido completo, O(n)) en un SEARCH (búsqueda dirigida, O(log n)) sobre la columna indexada.
• Coste de escritura: cada índice debe actualizarse al modificar datos, así que indexar de más penaliza las escrituras y ocupa espacio.
• Índice compuesto: CREATE INDEX i ON t(a, b) indexa por a y, dentro de cada a, por b. Respeta el orden de columnas (prefijo más selectivo a la izquierda).
• Índice parcial: CREATE INDEX i ON t(col) WHERE condición indexa solo las filas que cumplen la condición; más pequeño y rápido para consultas filtradas.
• PRIMARY KEY ya está indexada: en SQLite la clave primaria (rowid) tiene índice implícito; no hace falta crearlo.
• Idempotencia: usa DROP INDEX IF EXISTS i; CREATE INDEX i ON ...;. Crear un índice no altera los datos de la tabla, solo añade una estructura auxiliar.

Sintaxis / Conceptos

La forma básica es CREATE INDEX nombre ON tabla(columna1, columna2, ...);. Con UNIQUE (CREATE UNIQUE INDEX) además se impone que la combinación de columnas no se repita, funcionando como restricción de unicidad.

Selectividad: un índice es útil cuando filtra muchas filas. Indexar una columna booleana (pocos valores distintos) rara vez ayuda; indexar una clave foránea muy usada en joins (pedidos.cliente_id) sí. El optimizador decide si usar el índice mirando la cardinalidad estimada; ejecutar ANALYZE actualiza esas estadísticas.

Orden de columnas en índices compuestos: (a, b) sirve para WHERE a = ?, WHERE a = ? AND b = ? y ORDER BY a, b, pero NO para WHERE b = ? aislado (no hay prefijo a). Coloca a la izquierda la columna por la que más filtras.

Índice cubridor (covering): si todas las columnas que la consulta necesita están en el índice, el motor responde sin tocar la tabla (USING COVERING INDEX), lo más rápido posible.

SELECT e.nombre, d.nombre AS depto, e.salario FROM empleados e JOIN departamentos d ON e.departamento_id = d.id ORDER BY e.salario DESC LIMIT 6

Ejemplos

-- Índice simple sobre una clave foránea muy usada en joins
DROP INDEX IF EXISTS idx_pedidos_cliente;
CREATE INDEX idx_pedidos_cliente ON pedidos(cliente_id);

-- Verificamos que el índice quedó registrado
SELECT name FROM sqlite_master
WHERE type='index' AND name='idx_pedidos_cliente';

-- Índice compuesto: optimiza filtros por pedido y por (pedido, producto)
DROP INDEX IF EXISTS idx_detalle_pedido_prod;
CREATE INDEX idx_detalle_pedido_prod ON detalle_pedidos(pedido_id, producto_id);

SELECT name, sql FROM sqlite_master
WHERE type='index' AND name='idx_detalle_pedido_prod';

-- Índice parcial: solo indexa empleados con salario alto (más pequeño)
DROP INDEX IF EXISTS idx_emp_salario_alto;
CREATE INDEX idx_emp_salario_alto ON empleados(salario) WHERE salario > 8000;

SELECT name FROM sqlite_master
WHERE type='index' AND name='idx_emp_salario_alto';

💡 No indexes "por las dudas". Cada índice ocupa espacio y ralentiza las escrituras. Mide primero con EXPLAIN QUERY PLAN (Lección 43): si ves un SCAN costoso sobre una columna que filtras a menudo, ahí sí conviene un índice.

Cheatsheet

Cláusula / Función	Qué hace
CREATE INDEX i ON t(col)	Índice B-tree sobre una columna
CREATE INDEX i ON t(a, b)	Índice compuesto (orden de columnas importa)
CREATE UNIQUE INDEX i ON t(col)	Índice + restricción de unicidad
CREATE INDEX i ON t(col) WHERE cond	Índice parcial (solo filas que cumplen cond)
DROP INDEX IF EXISTS i	Elimina el índice (patrón idempotente)
SELECT name FROM sqlite_master WHERE type='index'	Lista los índices

---

← Vistas: CREATE VIEWTransacciones: BEGIN, COMMIT, ROLLBACK y SAVEPOINT →

Ver todas las lecciones de Aprende SQL →