Diseño de índices — orden de columnas en compuestos y condiciones que usan el índice

El dataset de este artículo — perf_sales (50 000 filas)

A partir de aquí, los artículos de tuning usan una tabla con muchas filas para observar cómo se comportan realmente los índices.

Este artículo cubre el orden de columnas de un índice compuesto (un índice construido sobre varias columnas en un orden específico), la regla del prefijo izquierdo y cómo escribir condiciones que pueden usar el índice (también llamadas SARGables).

Antes de la práctica, echa un vistazo rápido a las definiciones de columna y al tamaño y una muestra de los datos de perf_sales. (Ejecuta las consultas correctamente y se 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 número de filas.

③ Previsualiza las 5 primeras filas con SELECT emp_id, region, product, amount, sale_date, status FROM perf_sales LIMIT 5; (como la tabla tiene 50 000 filas, no extraigas nunca cada fila con SELECT * — usa agregación o LIMIT para echar un vistazo).

Orden de columnas en un índice compuesto — la regla del prefijo izquierdo

Un índice compuesto (como CREATE INDEX ix ON table(col_A, col_B), listando varias columnas en un orden fijo) guarda sus entradas exactamente en ese orden.

El índice se ordena como un diccionario: por col_A ascendente, y dentro del mismo col_A por col_B ascendente.

Debido a esto, si el índice ayuda depende de si tus condiciones cubren las columnas de forma continua desde la inicial.

Esto se llama la regla del prefijo izquierdo (un índice compuesto solo ayuda a condiciones especificadas de forma continua desde la columna inicial).

Un índice sobre (emp_id, sale_date) funciona para una condición sobre emp_id, o sobre emp_id y sale_date juntos, pero no para una condición sobre sale_date solo — falta el emp_id inicial, así que el índice no se usa.

-- Construye un índice compuesto sobre region y product, luego compara
-- los planes para una condición que incluye la region inicial
-- con una que usa product solo
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, product);

-- Usa la columna inicial region -> ix_demo entra en juego
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE region = 'East';

-- Se salta la region inicial y filtra solo por product -> ix_demo no entra en juego
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product = 'P050';

Construye un índice compuesto sobre emp_id y sale_date sobre perf_sales y confirma la regla del prefijo izquierdo leyendo el plan de ejecución. Recrea el índice y muestra los planes dentro de una sola ejecución de la consola para que los pasos sean autocontenidos. (Ejecuta las consultas correctamente y se revelará la explicación.)

① Empieza con DROP INDEX IF EXISTS para que el índice se pueda reconstruir incluso si queda uno con el mismo nombre.

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

③ Usa EXPLAIN QUERY PLAN para mostrar el plan de una consulta de agregación filtrada solo por emp_id.

④ Luego muestra el plan de una consulta de agregación filtrada solo por sale_date y compara cómo cambia el plan respecto a ③.

Lista las columnas en orden igualdad-luego-rango

Hay una guía para el orden de columnas en un índice compuesto.

Pon las condiciones de igualdad (un = o IN que fija una columna a un valor) al frente, y las condiciones de rango (>, >=, <, BETWEEN, etc., que cubren un tramo) al final.

Fijar la columna inicial con igualdad mantiene las entradas coincidentes en un bloque contiguo dentro del índice, así que puedes recortar ese bloque de una sola vez.

Una condición como WHERE emp_id = 7 AND sale_date >= '2024-01-01' es un ajuste natural para el orden (emp_id, sale_date).

-- Indexa region (igualdad) y amount (rango) juntos,
-- con la columna de igualdad primero y la columna de rango segunda
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, amount);

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales
WHERE region = 'West' AND amount >= 500000;

Imagina el requisito: "Queremos hacer un roll-up de ventas para un comercial específico desde una fecha dada en adelante." Las condiciones son igualdad sobre emp_id y un rango sobre sale_date. Construye un índice compuesto sobre estas dos columnas en orden igualdad-luego-rango y confirma con el plan de ejecución que se convierte en una búsqueda por índice.

① Ejecuta DROP INDEX IF EXISTS para eliminar cualquier índice anterior.

② Crea un índice compuesto con la columna de igualdad primero y la columna de rango segunda.

③ Usa EXPLAIN QUERY PLAN sobre una consulta de agregación que combine la igualdad sobre emp_id con el rango sobre sale_date vía AND, y confirma que se convierte en una búsqueda por índice.

Condiciones que pueden usar el índice — compara la columna tal cual, sin envolverla

Incluso con un índice en su sitio, la forma en que escribes la condición decide si se usa.

Una condición con una forma que permita al índice acotarla se llama una condición que puede usar el índice (en inglés esto también se conoce como SARGable).

La regla básica: compara la columna indexada tal cual, sin envolverla en ningún cálculo.

Algo como WHERE emp_id = 7, donde la columna se compara directamente por igualdad o rango, deja que el índice entre en juego.

Por otro lado, WHERE emp_id + 0 = 7 (la columna está dentro de una expresión) o WHERE substr(sale_date,1,4) = '2024' (la columna está envuelta en una función) rompe la correspondencia con el ordenamiento del índice y recae en un barrido completo de filas.

Mantén la columna en bruto en el lado izquierdo de la comparación, y si hace falta cualquier ajuste, aplícalo al valor del lado derecho.

-- Mismo significado, distintas formas -> distintos planes
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(amount);

-- Columna comparada tal cual -> el índice entra en juego, SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount >= 800000;

-- Columna dentro de una expresión -> el índice no entra en juego, recae en SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount + 0 >= 800000;

Observa cómo dos condiciones con el mismo significado — una comparando la columna tal cual y otra envolviéndola en una expresión — producen distintos planes de ejecución. Construye un índice sobre emp_id y compara los planes de las dos formas.

① Ejecuta DROP INDEX IF EXISTS para eliminar el índice anterior.

② Crea un índice de una sola columna sobre emp_id.

③ Muestra el plan para una consulta de agregación que compare emp_id tal cual con igualdad.

④ Luego muestra el plan para una consulta de agregación con el mismo significado pero con emp_id dentro de una expresión, y compara cómo cambia el plan respecto a ③.