Índice Composto no SQLite: Ordem das Colunas e Prefixo

Um índice, várias colunas

Um índice composto no SQLite — também conhecido como índice de múltiplas colunas — é um único índice construído sobre duas ou mais colunas. Para criar, basta listar as colunas na ordem desejada:

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';

O índice idx_orders_customer_status armazena as entradas ordenadas primeiro por customer_id e, dentro de cada cliente, por status. Essa ordenação é o ponto central — todo o comportamento de um índice composto decorre dela.

O modelo mental: uma lista telefônica ordenada

Imagine uma velha lista telefônica. Os registros estão ordenados por sobrenome e, dentro de cada sobrenome, pelo primeiro nome. É exatamente assim que se comporta um índice em (last_name, first_name).

Algumas buscas saem baratas, outras nem tanto:

• "Encontrar todo mundo com sobrenome Patel" — tranquilo, todos os Patel ficam juntos.
• "Encontrar Priya Patel" — tranquilo, vai direto em Patel e percorre até Priya.
• "Encontrar todo mundo chamado Priya" — lento, você precisa varrer cada página. As Priyas estão espalhadas por todos os sobrenomes.

Um índice composto no SQLite funciona igualzinho. A primeira coluna é a chave principal de ordenação; a segunda coluna só ordena entradas que compartilham o mesmo valor na primeira.

A regra do prefixo mais à esquerda

O SQLite só consegue usar um índice composto numa consulta quando o WHERE restringe um prefixo mais à esquerda das suas colunas. Para um índice em (a, b, c):

• Filtrando por a — usa o índice.
• Filtrando por a e b — usa o índice.
• Filtrando por a, b e c — usa o índice.
• Filtrando só por b, só por c, ou por b e c — o índice não é usado.

Dá para confirmar isso na prática com 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';

O primeiro plano mostra SEARCH events USING INDEX idx_events_user_kind_time. Já o segundo cai para SCAN events — filtrar só por kind pula a coluna user_id (que está na frente), então o índice não serve para nada nessa consulta.

A ordem das colunas no índice é uma decisão de projeto

Como o prefixo mais à esquerda é o que manda, a ordem em que você lista as colunas no CREATE INDEX é uma escolha de verdade, não algo estético. Duas regras práticas:

1. Coloque primeiro a coluna que você mais filtra. É ela que destrava o índice para o maior número de consultas.
2. Colunas de igualdade vêm antes das de intervalo. O SQLite consegue mergulhar direto no índice usando = e depois varrer uma faixa contígua com <, > ou BETWEEN — mas só na última coluna usada.

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

-- Bom: igualdade em region primeiro, intervalo em sold_at depois.
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';

O plano mostra SEARCH sales USING INDEX idx_sales_region_time (region=? AND sold_at>?). O SQLite vai direto em region = 'EU' e a partir daí percorre o intervalo de datas. Agora inverta a ordem das colunas para (sold_at, region): a mesma consulta passa a varrer todas as linhas dentro do intervalo de datas e revalidar region em cada uma delas.

Índice composto vs. vários índices de coluna única no SQLite

Uma dúvida bem comum: vale mais criar um índice em (a, b) ou dois índices separados, um em a e outro em b?

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

-- Opção A: um índice composto.
CREATE INDEX idx_tickets_proj_state ON tickets(project_id, state);

-- Opção B (alternativa): dois índices de coluna única.
-- 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 o filtro combinado, o índice composto é mais rápido — o SQLite vai direto às entradas (project_id, state) que batem com a busca. Com dois índices de coluna única, normalmente o SQLite escolhe um deles, usa-o para reduzir o conjunto de linhas e depois confere a outra coluna em cada linha restante. Em alguns casos ele consegue fazer a interseção dos dois índices, mas, quando as colunas são consultadas juntas, o índice composto resolve de forma muito mais limpa.

Se project_id e state também forem consultados separadamente, talvez valha a pena manter os dois — o composto para o filtro combinado e um índice de coluna única em state para as consultas que filtram só por essa coluna.

Covering index no SQLite

Quando um índice inclui todas as colunas que a consulta precisa — tanto as colunas do filtro quanto as colunas do SELECT — o SQLite consegue responder sem nem encostar na tabela. Isso é um covering index, e é o cenário mais rápido possível para uma 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, 'primeira'),
    (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;

O plano mostra USING COVERING INDEX idx_invoices_cover. A consulta lê issued_at e total direto do índice — notes e id não são necessários, então a tabela em si nunca é aberta. Acrescentar uma coluna a um índice composto só para cobrir uma query crítica vale a pena quando essa query roda o tempo todo.

Restrições UNIQUE compostas

Índices compostos também garantem unicidade na combinação de colunas. Isso é útil quando nenhuma coluna sozinha é única, mas a combinação delas precisa ser:

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');  -- falha: par repetido

O terceiro INSERT dispara UNIQUE constraint failed: enrollments.student_id, enrollments.course_id. O par já existe no índice, então o SQLite recusa a duplicata.

Armadilhas que vale a pena conhecer

• OR entre colunas que não são a primeira derruba o índice. Um WHERE a = 1 OR b = 2 sobre um índice (a, b) normalmente não consegue usar o índice — o SQLite precisa avaliar cada ramo separadamente.
• Funções em colunas indexadas desativam o índice. WHERE lower(email) = 'x' não usa um índice em email. A saída é indexar a expressão ou normalizar o dado já na inserção.
• Índice tem custo. Todo índice é atualizado a cada INSERT, UPDATE (das colunas indexadas) e DELETE. Três índices compostos numa tabela com muita escrita podem dominar o custo das gravações.
• Rode ANALYZE depois de criar índices. O planejador do SQLite usa as estatísticas coletadas pelo ANALYZE para escolher entre índices candidatos. Sem essas estatísticas, ele recorre a heurísticas que nem sempre acertam.

Um fluxo de trabalho prático

Quando você está afinando uma consulta lenta, o ciclo costuma ser este:

1. Rode EXPLAIN QUERY PLAN na consulta para ver o que o SQLite está fazendo hoje.
2. Se estiver fazendo scan, olhe a cláusula WHERE — qual é a coluna de igualdade? Qual é a de intervalo? Quais colunas estão no SELECT?
3. Monte um índice composto com a coluna de igualdade primeiro, a de intervalo depois, e acrescente as colunas selecionadas se um covering index ajudar.
4. Rode ANALYZE.
5. Rode EXPLAIN QUERY PLAN de novo. Confirme que o plano mudou e que o índice está sendo usado.
6. Meça o tempo da consulta antes e depois, com dados representativos.

Pular o passo 6 é por sua conta e risco. Um índice que parece certo no plano ainda pode ser mais lento na prática se a tabela for pequena ou se o planejador escolher outro caminho.

A seguir: índices parciais

Os índices compostos cobrem todas as linhas da tabela. Mas muitas vezes só um subconjunto pequeno de linhas importa — tickets abertos, jobs ainda não processados, registros não deletados. Um índice parcial permite indexar apenas essas linhas, com uma cláusula WHERE embutida no próprio índice. É o tema da próxima página.