Índices no SQLite: CREATE INDEX e quando usar

O que é, na prática, um índice

Um índice no SQLite é uma estrutura de dados separada — uma B-tree ordenada — que permite ao SQLite localizar linhas pelo valor de uma coluna sem precisar varrer a tabela inteira. Sem índice, uma consulta como WHERE email = 'rosa@example.com' lê linha por linha e testa cada uma. Com um índice em email, o SQLite percorre a árvore em aproximadamente log(n) passos e vai direto ao registro.

Mas essa velocidade tem preço. O índice guarda uma cópia da coluna indexada mais um ponteiro de volta para a linha. Toda vez que você faz um INSERT, um UPDATE na coluna indexada ou um DELETE, o índice também precisa ser atualizado. O uso de disco cresce e a vazão de escrita cai um pouquinho. O acordo é esse: você paga nas escritas e economiza bem mais nas leituras.

Como criar um índice no SQLite

A sintaxe básica para create index no SQLite é:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    name TEXT,
    created_at TEXT
);

INSERT INTO users (email, name, created_at) VALUES
    ('rosa@example.com', 'Rosa', '2026-01-10'),
    ('boris@example.com', 'Boris', '2026-02-14'),
    ('ada@example.com', 'Ada', '2026-03-01');

CREATE INDEX idx_users_email ON users(email);

SELECT * FROM users WHERE email = 'boris@example.com';

Convenção de nomes: a maioria dos times adota idx_<tabela>_<coluna>, assim fica óbvio para que serve cada índice. Lembre que o nome precisa ser único no banco inteiro, e não só dentro da tabela — por isso o nome da tabela entra no padrão.

Para remover um índice:

DROP INDEX idx_users_email;

Índices são puro andaime de performance. Remover um nunca afeta seus dados — só muda a velocidade com que as consultas rodam.

Índice único no SQLite

Um unique index faz dois trabalhos ao mesmo tempo: acelera as buscas e garante que duas linhas nunca tenham o mesmo valor na coluna indexada.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_accounts_username ON accounts(username);

INSERT INTO accounts (username) VALUES ('rosa');
INSERT INTO accounts (username) VALUES ('boris');
INSERT INTO accounts (username) VALUES ('rosa');

A terceira inserção falha com UNIQUE constraint failed: accounts.username. O SQLite já cria índices únicos automaticamente para colunas PRIMARY KEY e UNIQUE — você verá esses índices com nomes do tipo sqlite_autoindex_<table>_<n>. Só faz sentido escrever CREATE UNIQUE INDEX na mão quando a restrição não foi declarada direto na tabela.

O que o planejador de consultas faz na prática

Criar um índice no SQLite não significa que ele vai ser usado. O query planner escolhe uma estratégia para cada consulta, e dá pra ver qual ele escolheu com EXPLAIN QUERY PLAN:

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

INSERT INTO orders (customer_id, total, status) VALUES
    (1, 99.0, 'shipped'), (2, 45.5, 'pending'),
    (1, 12.0, 'shipped'), (3, 200.0, 'pending');

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

Procure por SEARCH ... USING INDEX idx_orders_customer na saída — isso significa que o índice está sendo usado. Se aparecer SCAN orders, o planejador entendeu que um full scan na tabela seria mais barato (o que costuma fazer sentido em tabelas pequenas) ou o formato da sua consulta impediu o uso do índice. Tem um doc inteiro mais adiante só sobre como ler esses planos.

Quando o índice no SQLite não é usado

Os índices têm alguns pontos cegos bem conhecidos. Cada um dos casos abaixo faz o índice em email ser ignorado:

-- A função encapsula a coluna
SELECT * FROM users WHERE lower(email) = 'rosa@example.com';

-- Curinga no início do LIKE
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Incompatibilidade de tipos força uma conversão
SELECT * FROM users WHERE email = 12345;

A B-tree é ordenada pelo valor cru de email, então qualquer coisa que transforme a coluna em tempo de consulta força um full scan. Soluções possíveis: já armazene o dado normalizado (uma coluna email_lower), use um índice de expressão (CREATE INDEX idx ON users(lower(email))), ou recorra ao full-text search do SQLite para buscas por substring.

Covering index no SQLite

Quando o índice já contém todas as colunas que a consulta precisa, o SQLite consegue responder sem nem encostar na tabela — é o famoso covering index. O pulo do gato é incluir colunas extras na definição do índice:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    type TEXT,
    created_at TEXT
);

INSERT INTO events (user_id, type, created_at) VALUES
    (1, 'login', '2026-01-01'),
    (1, 'click', '2026-01-02'),
    (2, 'login', '2026-01-03');

CREATE INDEX idx_events_user_type ON events(user_id, type);

EXPLAIN QUERY PLAN
SELECT user_id, type FROM events WHERE user_id = 1;

Como as duas colunas que a query precisa já estão dentro do índice, o SQLite retorna USING COVERING INDEX. Não precisa nem buscar a linha na tabela. O covering index no SQLite é uma das otimizações com maior retorno em rotas de leitura críticas — o preço a pagar é um índice maior em disco. Índices com múltiplas colunas são um assunto à parte; o próximo doc trata disso com calma.

Listar índices no SQLite e inspecionar a estrutura

Existem duas formas de ver o que já existe:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT UNIQUE,
    name TEXT
);

CREATE INDEX idx_products_name ON products(name);

SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'index';

Isso retorna todos os índices do banco junto com a instrução CREATE que os criou. Se quiser ver só os de uma tabela, use PRAGMA index_list('products');. E para descobrir quais colunas cada índice cobre, vai de PRAGMA index_info('idx_products_name');. Qualquer coisa que comece com sqlite_autoindex_ foi criada automaticamente por causa de uma PRIMARY KEY ou de uma restrição UNIQUE — esses não dá para remover.

Quando não usar índice no SQLite

Tem situações em que criar um índice só piora as coisas:

• Tabelas pequenas. Algumas centenas de linhas são varridas em microssegundos. O otimizador provavelmente vai ignorar o índice de qualquer jeito, e você só adicionou custo extra nas escritas à toa.
• Colunas com muita escrita e pouca consulta. Toda escrita atualiza todos os índices. Indexar uma coluna pela qual você quase nunca filtra é puro desperdício.
• Colunas de baixa cardinalidade isoladas. Um índice numa coluna status com três valores possíveis não filtra quase nada. Ele até pode ajudar como segunda coluna de um índice composto, ou como índice parcial — mas sozinho, geralmente não compensa.
• Já tem cobertura. Se você já tem um índice em (a, b), não precisa de outro só em (a). O SQLite aproveita as colunas iniciais de um índice composto quando a query filtra apenas por a.

A resposta honesta para "será que devo criar esse índice?" quase sempre é: cria, roda EXPLAIN QUERY PLAN, mede com dados realistas e decide.

A seguir: índices compostos

Um índice de coluna única já resolve bastante coisa, mas na prática as queries costumam filtrar e ordenar por várias colunas ao mesmo tempo. É aí que entram os índices compostos — índices sobre (a, b, c) —, e a ordem das colunas importa muito mais do que a galera imagina. É o tema da próxima página.