Índices — Compuesto, Parcial, de Expresión y UNIQUE

El dataset de este artículo — la tabla perf_sales

Un índice funciona como el índice al final de un libro: te permite encontrar rápidamente filas con un valor de columna específico.

Este artículo recorre la creación de índices con CREATE INDEX, la construcción de índices compuestos, parciales, de expresión y UNIQUE, y el uso de EXPLAIN QUERY PLAN para confirmar cómo cambia la estrategia de búsqueda.

Antes de meterte de lleno, revisa las definiciones de columna, una muestra de los datos y el número de filas de perf_sales. (Ejecuta las consultas correctamente para revelar la explicación.)

① Usa PRAGMA table_info(perf_sales); para ver los nombres de columna, los tipos y la clave primaria.

② Usa SELECT COUNT(*) FROM perf_sales; para confirmar el total de filas.

③ Previsualiza las 5 primeras filas con SELECT * FROM perf_sales LIMIT 5;. Como perf_sales tiene 50 000 filas, incluye siempre LIMIT.

CREATE INDEX y EXPLAIN QUERY PLAN — convierte un barrido completo en una búsqueda por índice

Crea un índice con CREATE INDEX index_name ON table(column);.

Cuando usas una columna indexada en una condición de igualdad o rango en WHERE, el optimizador puede elegir una búsqueda por índice en lugar de un barrido completo y saltar directamente a las filas coincidentes.

Revisa la estrategia de búsqueda con EXPLAIN QUERY PLAN.

Antepón EXPLAIN QUERY PLAN a tu consulta y verás el plan de cómo se encontrarán las filas en lugar de los resultados reales.

Sin un índice verás SCAN perf_sales (también llamado FULL SCAN — examina cada fila de la tabla), y una vez que un índice entra en acción verás algo como SEARCH perf_sales USING INDEX ... (también llamado INDEX SCAN — acota la búsqueda con el índice).

La redacción exacta del plan varía según el entorno, así que este artículo trata una ejecución exitosa como la respuesta correcta y lee el significado del plan en la prosa y las explicaciones.

-- Plan sin índice (ejemplo de solo lectura aparte)
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales WHERE region = 'East';
--> SCAN perf_sales (examina cada fila)

-- Crea un índice y mira el mismo plan
DROP INDEX IF EXISTS ix_region;
CREATE INDEX ix_region ON perf_sales(region);
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales WHERE region = 'East';
--> SEARCH perf_sales USING INDEX ix_region (region=?)
-- Nota: ejecuta DROP -> CREATE -> EXPLAIN de principio a fin en la misma ejecución

Imagina el requisito: "Buscamos ventas por emp_id = 7 a menudo, así que queremos pasar de un barrido completo a una búsqueda por índice". Vas a comparar en tres pasos los tiempos de ejecución y los planes antes y después de añadir el índice: ① mide el plan SCAN sin índice (esta consola), ② construye el índice (siguiente consola), ③ vuelve a medir con el índice activo (última consola). La consola de creación del índice es un coste único, así que déjala fuera de la comparación pura de velocidad.

① En esta consola, ejecuta SELECT sale_id, amount FROM perf_sales WHERE emp_id = 7; y el EXPLAIN QUERY PLAN correspondiente. Confirma que el plan muestra SCAN perf_sales y anota el tiempo de ejecución t1 que aparece al pie de la consola.

② En esta consola, ejecuta DROP INDEX IF EXISTS ix; para limpiar cualquier índice anterior y luego CREATE INDEX ix ON perf_sales(emp_id); para construir el índice sobre la columna emp_id. Ten en cuenta que el tiempo total de esta consola es el coste único de construir el índice, no el coste de una búsqueda, así que queda fuera de la comparación pura de velocidad de búsqueda entre t1 y t3.

③ En esta consola, ejecuta el mismo SELECT sale_id, amount FROM perf_sales WHERE emp_id = 7; y el EXPLAIN QUERY PLAN correspondiente que en el paso ①. Confirma que ahora el plan muestra SEARCH perf_sales USING INDEX ix y que el tiempo de ejecución t3 es más corto que el t1 de la primera consola.

Índices compuestos — indexa varias columnas juntas

Escribir varias columnas entre paréntesis — CREATE INDEX name ON table(col1, col2); — produce un índice compuesto (también llamado índice de varias columnas).

Acelera las consultas que usan las condiciones a partir de la columna inicial, como WHERE col1 = ... AND col2 >= ....

El orden de las columnas importa: un índice compuesto no ayuda a las consultas que no usan su columna inicial en la cláusula WHERE.

Si tu patrón de acceso es fijo — por ejemplo "igualdad sobre emp_id y rango sobre sale_date" — construir un índice compuesto en ese orden permite que un único índice cubra ambas condiciones.

-- Índice compuesto (ejemplo de solo lectura aparte)
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, sale_date);

EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales
WHERE region = 'East' AND sale_date >= '2024-01-01';
--> SEARCH perf_sales USING INDEX ix_demo (region=? AND sale_date>?)
-- Acota por region (igualdad) y luego acota sale_date por rango

Imagina el requisito: "A menudo elegimos un comercial con emp_id y un periodo con sale_date, así que queremos un solo índice que cubra ambas condiciones".

① Empieza con DROP INDEX IF EXISTS ix;.

② Crea un índice compuesto llamado ix sobre perf_sales listando emp_id y sale_date en ese orden.

③ Luego ejecuta EXPLAIN QUERY PLAN sobre una consulta que filtre por emp_id = 7 y sale_date >= '2024-01-01' y seleccione sale_id y amount, y confirma que se usa el índice compuesto.

Índices parciales e índices de expresión

Un índice parcial se construye con CREATE INDEX name ON table(column) WHERE condition; e indexa solo un subconjunto de las filas de la tabla.

Si buscas con frecuencia filas con un valor específico como status = 'pending', indexar solo esas filas mantiene el índice pequeño y enfocado a las consultas que realmente ejecutas.

En perf_sales, solo 5000 filas (10% del total) tienen status = 'pending', así que un índice parcial con WHERE status = 'pending' encaja de forma natural.

Un índice de expresión se construye con CREATE INDEX name ON table(expression); e indexa el resultado de una expresión calculada en lugar de una columna en bruto.

Úsalo para consultas que aplican la misma expresión en el lado de búsqueda, como WHERE amount / 100 = ....

Un índice normal sobre una columna normalmente no ayuda cuando aplicas una función o aritmética sobre esa columna, pero si indexas la propia expresión, las búsquedas que usan la expresión coincidente pueden usarlo.

-- Índice parcial (ejemplo de solo lectura aparte)
DROP INDEX IF EXISTS ix_part;
CREATE INDEX ix_part ON perf_sales(sale_date) WHERE status = 'refunded';
EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales
WHERE status = 'refunded' AND sale_date >= '2024-01-01';
--> SEARCH perf_sales USING ... INDEX ix_part (sale_date>?)

-- Índice de expresión
DROP INDEX IF EXISTS ix_expr;
CREATE INDEX ix_expr ON perf_sales(qty * amount);
EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales WHERE qty * amount = 100000;
--> SEARCH perf_sales USING ... INDEX ix_expr (<expr>=?)

Imagina el requisito: "A menudo buscamos ventas pendientes (status = 'pending') por rango de fechas, así que queremos un índice pequeño que cubra solo esas filas".

① Empieza con DROP INDEX IF EXISTS ix;.

② Crea un índice parcial llamado ix sobre perf_sales(sale_date) que apunte solo a las filas donde status = 'pending'.

③ Ejecuta EXPLAIN QUERY PLAN sobre una consulta que filtre por status = 'pending' AND sale_date >= '2024-01-01' y confirma que se usa el índice parcial (SEARCH ... USING INDEX ix).

④ Luego ejecuta EXPLAIN QUERY PLAN sobre una consulta que use la misma columna pero con status = 'paid' (por ejemplo, status = 'paid' AND sale_date >= '2024-01-01') y confirma que el índice parcial no se usa (SCAN perf_sales) porque la condición no coincide con el predicado del índice.

Imagina el requisito: "A veces buscamos por el valor de bucket de amount dividido entre 100 (amount / 100), así que queremos indexar ese resultado calculado". Las búsquedas que aplican aritmética a una columna no se benefician de un índice de columna normal, así que usa un índice de expresión.

① Empieza con DROP INDEX IF EXISTS ix;.

② Crea un índice llamado ix sobre la expresión amount / 100 sobre perf_sales.

③ Ejecuta EXPLAIN QUERY PLAN sobre una consulta que filtre por amount / 100 = 5000 y confirma que se usa el índice de expresión (SEARCH ... USING INDEX ix).

④ Luego ejecuta EXPLAIN QUERY PLAN sobre una consulta que filtre por amount = 500000 sin pasar por la expresión (un ejemplo que usa la misma columna amount pero con una forma distinta a la del índice) y confirma que el índice de expresión no se usa (SCAN perf_sales).

Índices UNIQUE — un índice que prohíbe duplicados

Un índice UNIQUE — construido con CREATE UNIQUE INDEX name ON table(column); — acelera las búsquedas y evita que se inserten valores duplicados en la columna.

Si intentas crear un índice UNIQUE sobre una columna que ya contiene duplicados, obtendrás un error UNIQUE constraint failed y el índice no se creará en absoluto.

La otra cara: si lo creas sobre una columna cuyos valores son únicos (una clave primaria o una clave de negocio única), obtienes prevención de duplicados más una búsqueda rápida por igualdad.

En perf_sales, product (200 valores distintos) tiene muchas repeticiones, así que un índice UNIQUE no es posible ahí. sale_id, sin embargo, es la clave primaria y es único, así que puedes crear uno.

El siguiente ejercicio crea un índice UNIQUE sobre sale_id, confirmando que una columna única acepta el índice y que las búsquedas por igualdad sobre esa columna lo usan.

-- Índice UNIQUE (ejemplo de solo lectura aparte)
DROP INDEX IF EXISTS ix_u_demo;
CREATE UNIQUE INDEX ix_u_demo ON perf_sales(sale_id);
EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales WHERE sale_id = 12345;

-- Intentar añadir UNIQUE sobre una columna con duplicados se rechaza
-- CREATE UNIQUE INDEX ix_bad ON perf_sales(product);
--> UNIQUE constraint failed: perf_sales.product
-- product tiene 200 valores distintos repetidos por 50 000 filas, así que no puede hacerse UNIQUE

Imagina el requisito: "sale_id es único por venta, así que queremos evitar duplicados y acelerar las búsquedas de una sola fila". Este es el último ejercicio del artículo.

① Empieza con DROP INDEX IF EXISTS ix;.

② Crea un índice UNIQUE llamado ix sobre la columna sale_id de perf_sales (funciona porque sale_id es único).

③ Luego ejecuta EXPLAIN QUERY PLAN sobre una consulta que filtre por sale_id = 12345 y seleccione sale_id y amount, y confirma que la búsqueda de una sola fila va a través del índice.

Tips — cuándo añadir un índice

Regla práctica: un índice tiende a superar a un SCAN cuando puedes acotar el resultado a aproximadamente el 15-20% de la tabla o menos. Por encima de ese umbral, el optimizador a menudo elige SCAN automáticamente de todos modos.

- Añade uno: búsquedas de igualdad o rango sobre una columna de una tabla grande, claves primarias / claves foráneas / columnas UNIQUE, claves de JOIN, columnas referenciadas por ORDER BY / GROUP BY

- Sáltatelo: tablas pequeñas con unos pocos miles de filas o menos, columnas de baja selectividad como gender, tablas con mucha escritura, LIKE '%foo%' (un comodín al principio no puede usar índice)

El flujo de trabajo base: revisa el plan SCAN / SEARCH con EXPLAIN QUERY PLAN y añade índices solo a las consultas lentas.