Índices compuestos en SQLite y orden de columnas

Un índice, varias columnas

Un índice compuesto en SQLite (también conocido como índice multicolumna) es un único índice construido sobre dos o más columnas. Para crearlo, basta con listar las columnas en el orden deseado:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    created_at TEXT NOT NULL,
    total REAL NOT NULL
);

CREATE INDEX idx_orders_customer_status
    ON orders(customer_id, status);

INSERT INTO orders(customer_id, status, created_at, total) VALUES
    (1, 'paid',    '2026-01-10', 49.00),
    (1, 'paid',    '2026-02-03', 12.50),
    (1, 'pending', '2026-02-14',  9.99),
    (2, 'paid',    '2026-01-22', 80.00),
    (2, 'refunded','2026-02-01', 25.00);

SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid';

El índice idx_orders_customer_status guarda las entradas ordenadas primero por customer_id y, dentro de cada cliente, por status. Ese orden lo explica todo: el resto de cosas sobre índices compuestos se derivan de ahí.

El modelo mental: una guía telefónica ordenada

Imagina una guía telefónica de las de antes. Las entradas están ordenadas por apellido y, dentro de cada apellido, por nombre. Eso es exactamente lo que parece un índice sobre (last_name, first_name).

Algunas búsquedas son baratas; otras, no tanto:

• "Encuentra a todos los Patel" — fácil, todos los Patel están juntos.
• "Encuentra a Priya Patel" — fácil, saltas a Patel y luego buscas Priya.
• "Encuentra a todas las Priyas" — lento, te toca recorrer todas las páginas. Las Priyas están repartidas entre todos los apellidos.

Un índice compuesto en SQLite funciona igual. La primera columna es la clave principal de ordenación; la segunda solo ordena entradas que comparten el mismo valor en la primera.

La regla del prefijo izquierdo

SQLite solo puede usar un índice compuesto cuando la cláusula WHERE filtra por un prefijo izquierdo de sus columnas. Para un índice sobre (a, b, c):

• Filtrar por a — usa el índice.
• Filtrar por a y b — usa el índice.
• Filtrar por a, b y c — usa el índice.
• Filtrar solo por b, solo por c, o por b y c — el índice no se usa.

Puedes comprobarlo directamente con EXPLAIN QUERY PLAN:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    kind TEXT NOT NULL,
    occurred_at TEXT NOT NULL
);

CREATE INDEX idx_events_user_kind_time
    ON events(user_id, kind, occurred_at);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42 AND kind = 'login';

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE kind = 'login';

El primer plan muestra SEARCH events USING INDEX idx_events_user_kind_time. El segundo termina cayendo en SCAN events — al filtrar solo por kind te saltas la columna inicial user_id, así que el índice no sirve para nada en esa consulta.

El orden de las columnas es una decisión de diseño

Como el prefijo izquierdo manda, el orden en el que listas las columnas dentro de CREATE INDEX es una decisión real, no una cuestión de estilo. Dos reglas prácticas:

1. Pon primero la columna por la que filtras más a menudo. Esa columna es la que habilita el índice para el mayor número de consultas posibles.
2. Las columnas de igualdad van antes que las de rango. SQLite puede entrar al índice usando = y después recorrer un rango contiguo con <, > o BETWEEN — pero solo sobre la última columna utilizada.

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    region TEXT NOT NULL,
    sold_at TEXT NOT NULL,
    amount REAL NOT NULL
);

-- Bien: igualdad en region primero, rango en sold_at después.
CREATE INDEX idx_sales_region_time ON sales(region, sold_at);

INSERT INTO sales(region, sold_at, amount) VALUES
    ('EU', '2026-01-05', 120),
    ('EU', '2026-02-11', 80),
    ('US', '2026-01-09', 200),
    ('US', '2026-02-20', 150);

EXPLAIN QUERY PLAN
SELECT * FROM sales
WHERE region = 'EU' AND sold_at >= '2026-02-01';

El plan muestra SEARCH sales USING INDEX idx_sales_region_time (region=? AND sold_at>?). SQLite salta directamente a region = 'EU' y desde ahí avanza por el rango de fechas. Si inviertes el orden y dejas (sold_at, region), esa misma consulta tiene que recorrer todas las filas del rango de fechas y volver a comprobar region en cada una.

Índice compuesto vs. varios índices de una sola columna en SQLite

Una duda muy habitual: ¿conviene crear un único índice sobre (a, b) o mejor dos índices separados, uno sobre a y otro sobre b?

CREATE TABLE tickets (
    id INTEGER PRIMARY KEY,
    project_id INTEGER NOT NULL,
    assignee_id INTEGER NOT NULL,
    state TEXT NOT NULL
);

-- Opción A: un índice compuesto.
CREATE INDEX idx_tickets_proj_state ON tickets(project_id, state);

-- Opción B (alternativa): dos índices de una sola columna.
-- CREATE INDEX idx_tickets_project ON tickets(project_id);
-- CREATE INDEX idx_tickets_state   ON tickets(state);

EXPLAIN QUERY PLAN
SELECT * FROM tickets
WHERE project_id = 7 AND state = 'open';

Para el filtro combinado, el índice compuesto es más rápido: SQLite va directo a las entradas (project_id, state) que coinciden. Con dos índices de una sola columna, lo que suele hacer SQLite es elegir uno, reducir las filas con él y luego volver a comprobar la otra columna en cada fila que sobreviva. A veces puede intersectarlos, pero cuando consultas las columnas juntas, el índice compuesto es la solución más limpia.

Si también consultas project_id y state por separado, quizás te convenga tener los dos: el compuesto para el filtro combinado y un índice de una sola columna sobre state para las consultas que filtran solo por ahí.

Covering index en SQLite

Cuando un índice incluye todas las columnas que necesita una consulta —tanto las del filtro como las seleccionadas—, SQLite puede resolverla sin tocar la tabla siquiera. A eso se le llama covering index, y es lo más rápido que va a correr una consulta.

CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    issued_at TEXT NOT NULL,
    total REAL NOT NULL,
    notes TEXT
);

CREATE INDEX idx_invoices_cover
    ON invoices(customer_id, issued_at, total);

INSERT INTO invoices(customer_id, issued_at, total, notes) VALUES
    (1, '2026-01-12', 100, 'primera'),
    (1, '2026-02-02', 250, NULL),
    (2, '2026-01-30',  75, 'urgente');

EXPLAIN QUERY PLAN
SELECT issued_at, total FROM invoices WHERE customer_id = 1;

El plan muestra USING COVERING INDEX idx_invoices_cover. La consulta lee issued_at y total directamente desde el índice — notes e id no hacen falta, así que ni siquiera se abre la tabla. Sumar una columna extra a un índice compuesto solo para cubrir una consulta crítica vale la pena cuando esa consulta se ejecuta constantemente.

Restricciones UNIQUE compuestas

Los índices compuestos también sirven para imponer unicidad sobre combinaciones de columnas. Resultan útiles cuando ninguna columna es única por sí sola, pero la combinación sí debe serlo:

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id  INTEGER NOT NULL,
    enrolled_at TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_enrollments_pair
    ON enrollments(student_id, course_id);

INSERT INTO enrollments VALUES (1, 100, '2026-01-05');
INSERT INTO enrollments VALUES (1, 101, '2026-01-05');  -- ok, curso diferente
INSERT INTO enrollments VALUES (1, 100, '2026-02-01');  -- falla: el par se repite

El tercer INSERT lanza UNIQUE constraint failed: enrollments.student_id, enrollments.course_id. La pareja ya existe en el índice, así que SQLite rechaza el duplicado.

Detalles que conviene tener presentes

• Un OR entre columnas que no sean la primera anula el índice. Con WHERE a = 1 OR b = 2 sobre un índice (a, b), SQLite normalmente no puede aprovecharlo: tiene que evaluar cada rama por separado.
• Aplicar funciones sobre columnas indexadas inutiliza el índice. WHERE lower(email) = 'x' no usará un índice sobre email. La solución es indexar la expresión o normalizar los datos al insertarlos.
• Los índices no salen gratis. Cada índice se actualiza en cada INSERT, UPDATE (de columnas indexadas) y DELETE. Tener tres índices compuestos sobre una tabla con muchas escrituras puede disparar el coste de escritura.
• Ejecuta ANALYZE después de crear los índices. El planificador de SQLite se apoya en las estadísticas que recoge ANALYZE para decidir entre índices candidatos. Sin esas estadísticas, recurre a heurísticas que no siempre aciertan.

Un flujo de trabajo práctico

A la hora de afinar una consulta lenta, el ciclo suele ser este:

1. Lanza EXPLAIN QUERY PLAN sobre la consulta para ver qué está haciendo SQLite ahora mismo.
2. Si está escaneando, fíjate en el WHERE: ¿qué columna se compara por igualdad?, ¿cuál por rango?, ¿qué se selecciona?
3. Crea un índice compuesto ordenado primero por igualdad, después por rango, y añade al final las columnas seleccionadas si conviene cubrirlas (covering index).
4. Ejecuta ANALYZE.
5. Vuelve a lanzar EXPLAIN QUERY PLAN. Confirma que el plan ha cambiado y que se está usando el índice.
6. Mide los tiempos de la consulta antes y después con datos representativos.

Saltarte el paso 6 es jugar con fuego. Un índice que parece correcto en el plan puede acabar siendo más lento en la práctica si la tabla es pequeña o si el planificador decide otro camino.

A continuación: índices parciales

Los índices compuestos cubren todas las filas de la tabla. Pero muchas veces solo te interesa un subconjunto pequeño: tickets abiertos, trabajos sin procesar, registros no eliminados. Un índice parcial te permite indexar solo esas filas, con una cláusula WHERE integrada en el propio índice. De eso va la siguiente página.