Índices de varias columnas

En resumen: Un índice compuesto es un índice sobre varias columnas que ayuda cuando la consulta usa sus columnas de izquierda a derecha.

Problema

Los últimos diez

La página «Vistos recientemente» muestra las últimas 10 visitas del cliente, empezando por la más nueva. El índice sobre customer_id ya está creado. Mira el plan:

SQLbase de datos: tienda

EXPLAIN QUERY PLAN
SELECT seen_at, product_id FROM visits
WHERE customer_id = 1234
ORDER BY seen_at DESC LIMIT 10;

EXPLAIN QUERY PLAN SELECT seen_at, product_id FROM visits WHERE customer_id = 1234 ORDER BY seen_at DESC LIMIT 10;

▶ EjecutarRestaurar la consultaBase nueva

El índice encuentra las 60 visitas, pero no las da ordenadas por fecha. La base de datos las ordena por su cuenta (USE TEMP B-TREE). Para un cliente con miles de visitas esta es la parte más lenta, y solo hacen falta 10.

La idea

Dos columnas en un índice

Un índice puede abarcar varias columnas: CREATE INDEX nombre ON tabla(columna1, columna2). Es un índice compuesto. Está ordenado como una guía telefónica: primero por apellido y, con el mismo apellido, por nombre.

El índice sobre (customer_id, seen_at) está ordenado primero por cliente, y las visitas de un cliente están ordenadas por fecha. La base de datos encuentra al cliente y lee sus fechas desde el final hacia atrás. No hace falta ordenar:

SQLbase de datos: tienda

EXPLAIN QUERY PLAN
SELECT seen_at, product_id FROM visits
WHERE customer_id = 1234
ORDER BY seen_at DESC LIMIT 10;

EXPLAIN QUERY PLAN SELECT seen_at, product_id FROM visits WHERE customer_id = 1234 ORDER BY seen_at DESC LIMIT 10;

▶ EjecutarRestaurar la consultaBase nueva

El mismo índice ayuda también cuando la condición afecta a las dos columnas. La base de datos va al cliente y busca en su parte a partir de la fecha:

SQLbase de datos: tienda

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM visits
WHERE customer_id = 1234 AND seen_at >= '2026-01-01';

EXPLAIN QUERY PLAN SELECT COUNT(*) FROM visits WHERE customer_id = 1234 AND seen_at >= '2026-01-01';

▶ EjecutarRestaurar la consultaBase nueva

Y con la primera columna sola, como si fuera un índice solo sobre customer_id:

SQLbase de datos: tienda

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM visits WHERE customer_id = 1234;

EXPLAIN QUERY PLAN SELECT COUNT(*) FROM visits WHERE customer_id = 1234;

▶ EjecutarRestaurar la consultaBase nueva

Pruébalo

Visitas en un periodo

Atención al cliente pregunta a menudo cuántas veces ha mirado un cliente productos a partir de una fecha. Crea un índice solo sobre estas dos columnas que haga de la consulta un SEARCH tanto por el cliente como por la fecha.

SQLbase de datos: tienda

SELECT COUNT(*) FROM visits
WHERE customer_id = 1234 AND seen_at >= '2026-01-01';

SELECT COUNT(*) FROM visits WHERE customer_id = 1234 AND seen_at >= '2026-01-01';

Comprobar▶ Ejecutar

Míralo

De izquierda a derecha

Con el mismo índice, el informe «por día» pregunta solo por la fecha:

SQLbase de datos: tienda

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM visits WHERE seen_at = '2026-03-01';

EXPLAIN QUERY PLAN SELECT COUNT(*) FROM visits WHERE seen_at = '2026-03-01';

▶ EjecutarRestaurar la consultaBase nueva

El plan es un SCAN. Dice USING COVERING INDEX, porque COUNT(*) se calcula a partir del índice, pero la base de datos sigue leyendo las 300 000 entradas que contiene. Ejecuta la consulta y mira el tiempo:

SQLbase de datos: tienda

SELECT COUNT(*) FROM visits WHERE seen_at = '2026-03-01';

SELECT COUNT(*) FROM visits WHERE seen_at = '2026-03-01';

▶ EjecutarRestaurar la consultaBase nueva

En una guía telefónica no puedes encontrar a todas las personas llamadas «Maria» sin leer el libro entero. Los nombres solo están ordenados dentro de cada apellido.

Esta es la regla de izquierda a derecha: un índice sobre (a, b) ayuda con condiciones sobre a, sobre a y b, y con ORDER BY b cuando a viene dada con =. Con una condición solo sobre b no ayuda. Si no estás seguro, compruébalo con EXPLAIN QUERY PLAN.

La idea

Lo que cuesta un índice

Los índices no son gratis. Cada fila nueva tiene que escribirse en su lugar en todos los índices de la tabla. Los dos campos de abajo añaden 100 000 visitas cada uno. El primero no tiene índices, el segundo tiene cuatro. Ejecútalos y compara los tiempos:

SQLbase de datos: tienda

INSERT INTO visits (customer_id, product_id, seen_at)
SELECT customer_id, product_id, seen_at FROM visits WHERE id <= 100000;
SELECT COUNT(*) FROM visits;

INSERT INTO visits (customer_id, product_id, seen_at) SELECT customer_id, product_id, seen_at FROM visits WHERE id <= 100000; SELECT COUNT(*) FROM visits;

▶ EjecutarRestaurar la consultaBase nueva

SQLbase de datos: tienda

INSERT INTO visits (customer_id, product_id, seen_at)
SELECT customer_id, product_id, seen_at FROM visits WHERE id <= 100000;
SELECT COUNT(*) FROM visits;

INSERT INTO visits (customer_id, product_id, seen_at) SELECT customer_id, product_id, seen_at FROM visits WHERE id <= 100000; SELECT COUNT(*) FROM visits;

▶ EjecutarRestaurar la consultaBase nueva

Lo mismo vale para un UPDATE de una columna indexada y para DELETE.

Los índices también ocupan espacio. SQLite guarda la base de datos en páginas de 4 096 bytes, y pragma_page_count() dice cuántas páginas hay. Aquí está la base de datos sin índices y con los cuatro:

SQLbase de datos: tienda

SELECT page_count FROM pragma_page_count();

SELECT page_count FROM pragma_page_count();

▶ EjecutarRestaurar la consultaBase nueva

SQLbase de datos: tienda

SELECT page_count FROM pragma_page_count();

SELECT page_count FROM pragma_page_count();

▶ EjecutarRestaurar la consultaBase nueva

Con los cuatro índices la base de datos es más de tres veces mayor. Por eso añades índices para las consultas que de verdad tienes, no para cada columna «por si acaso».

Más precisamente, para curiosos

El segundo de los cuatro índices, (customer_id, seen_at), hace redundante al primero, (customer_id): por la regla de izquierda a derecha también cumple su función. Pares así son frecuentes y rara vez se notan. PostgreSQL lleva la cuenta de cuántas veces se ha usado cada índice (pg_stat_user_indexes), de modo que se pueden encontrar los que no se usan.

Ejercicio

Dos tareas

1. La base de datos tiene un índice sobre (customer_id) y uno sobre (customer_id, seen_at). Elimina el redundante.

Elimina el índice cuya función cumple el otro:

SQLbase de datos: tienda

SELECT name FROM sqlite_master WHERE type = 'index';

SELECT name FROM sqlite_master WHERE type = 'index';

Comprobar▶ Ejecutar

2. El índice sobre (customer_id, seen_at) está creado. Muestra las fechas de las últimas 3 visitas del cliente 1234, empezando por la más nueva.

Las últimas 3 fechas de visita del cliente 1234:

SQLbase de datos: tienda

SELECT seen_at FROM visits
WHERE customer_id = 1234;

SELECT seen_at FROM visits WHERE customer_id = 1234;

Comprobar▶ Ejecutar

Test

Ponte a prueba

1. Hay un índice sobre (customer_id, seen_at). ¿Qué condición NO puede usarlo para una búsqueda?

WHERE customer_id = 7WHERE seen_at = '2026-03-01'WHERE customer_id = 7 AND seen_at >= '2026-01-01'

2. Hay un índice sobre (customer_id, seen_at). ¿Necesitas además un índice solo sobre customer_id?

Sí, si no la búsqueda por cliente es lentaSolo si la tabla es grandeNo: una búsqueda por la primera columna usa el índice de las dos

3. ¿Por qué frenan los índices el INSERT?

Porque la base de datos comprueba su sintaxisNo lo frenanPorque cada fila nueva se escribe en su lugar en todos los índices de la tabla

4. ¿Cuándo es buena idea un índice sobre una columna?

Solo para la clave primariaSiempre, para todas las columnasCuando las consultas buscan, unen u ordenan a menudo por ella

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: Funciones de agregación: COUNT, SUM, AVG · INSERT: insertar filas · Índices · EXPLAIN: el plan de la consulta
• Continúa en: Consultas lentas
• Ejercicios sobre el tema: Índices y velocidad