CREATE INDEX e o ganho

Um índice é uma estrutura auxiliar que o banco mantém ao lado da tabela para não precisar ler a tabela inteira para achar uma linha. Ele é escrito uma vez e mantido pelo motor em cada INSERT, UPDATE e DELETE que afetar a coluna indexada.

CREATE INDEX idx_tarefas_prazo ON tarefas (prazo);

O CREATE INDEX existe para acelerar consulta, e a troca que ele propõe é sempre a mesma: o banco deixa de ler todas as linhas e passa a ler só o trecho onde a resposta está. Com duzentas linhas isso não importa; com cinco mil, é a diferença entre a consulta responder e o aplicativo engasgar.

A busca sem índice percorre a tabela linha por linha comparando cada uma com a condição do WHERE, e para quando acha a primeira que casa. A busca com índice recebe a lista de valores ordenados da coluna e vai direto até o ponto em que o valor procurado deveria estar. O exemplo desta página compara as duas sobre duas mil tarefas e imprime quantas linhas cada caminho examinou.

O nome do índice segue a convenção idx_ mais o nome da tabela e o da coluna. Sem ele, o SQLite cria um nome automático que ninguém entende.

A convenção não é vaidade: o DROP INDEX precisa do nome, e a lista de índices do banco precisa ser legível quando alguém precisa decidir o que apagar. Um índice com nome automático é um índice que ninguém vai remover, e um índice que ninguém remove é custo pago em toda gravação.

O plano de execução mostra o caminho

EXPLAIN QUERY PLAN devolve o que o motor pretende fazer, sem executar a consulta. É a forma mais direta de ver o índice sendo usado:

EXPLAIN QUERY PLAN SELECT * FROM tarefas WHERE prazo = '2026-09-10';

A resposta tem uma palavra que decide tudo: SEARCH quando o índice foi usado, SCAN quando a tabela inteira vai ser lida. SCAN tarefas com filtro de coluna indexada é o sinal de que o índice não existe ou não serve para essa consulta.

O full scan é o outro nome dessa mesma leitura completa, e ele aparece no plano como SCAN. A leitura inteira não é erro do banco: é o que ele faz quando não tem caminho melhor. O que o plano mostra é a decisão que o motor tomou, e ela é sempre do tipo "leio tudo" ou "vou direto".

O índice único é o mesmo índice com uma regra a mais: CREATE UNIQUE INDEX cria a estrutura de busca e proíbe dois valores iguais na coluna. As duas coisas juntas valem a pena porque a proibição vem de graça — a estrutura que acelera a busca já existe, e a checagem de igualdade é uma comparação por linha.

UNIQUE e o custo do índice

CREATE UNIQUE INDEX faz o mesmo trabalho e ainda proíbe dois valores iguais na coluna — é como um UNIQUE que também acelera a busca.

O custo é real e sempre do mesmo jeito: gravação mais lenta e arquivo maior. Cada INSERT tem de atualizar a estrutura do índice. Por isso índice em coluna que muda a cada gravação, como atualizado_em, costuma ser desperdício: o banco paga o custo toda hora e a consulta raramente filtra por ele.

A conta é a mesma dos dois lados: cada INSERT ganha uma escrita a mais na estrutura, e cada consulta ganha uma busca a menos na tabela. Em aplicativo que grava pouco e lê muito, o índice ganha fácil. Em aplicativo que grava a cada tecla — e é o caso do formulário com validação a cada tecla — o índice se paga mal.

DROP INDEX idx_tarefas_prazo remove o índice quando ele não serve mais, e ele não mexe nos dados da tabela — é a única operação de estrutura reversível sem backup.

O exemplo da página fecha o ciclo do custo: imprime o CREATE INDEX, mostra a estrutura nova ao lado das colunas, compara os dois caminhos de busca e o plano de execução de cada um, e termina no DROP INDEX mostrando que a tabela continua com as mesmas linhas. É a operação que permite experimentar índice sem risco: o arquivo fica menor e a escrita mais rápida, e nada do que está gravado muda.

Exemplo

// `CREATE INDEX` cria a estrutura auxiliar que evita ler a tabela
// inteira. O exemplo compara a busca com e sem indice, e usa o plano de
// execucao para mostrar qual dos dois caminhos o motor escolheu.
const banco = {
  tarefas: [],
  indices: new Map(),
};

// a tabela cresce: 2000 tarefas com prazo em ordem de insercao
for (let i = 1; i <= 2000; i += 1) {
  banco.tarefas.push({
    id: i,
    titulo: 'Tarefa ' + i,
    prazo: '2026-' + String(1 + (i % 12)).padStart(2, '0') + '-' + String(1 + (i % 27)).padStart(2, '0'),
  });
}

function criarIndice(nome, tabela, coluna) {
  const ordenadas = [...banco[tabela]].sort((a, b) => (a[coluna] > b[coluna] ? 1 : -1));
  banco.indices.set(nome, { tabela: tabela, coluna: coluna, entradas: ordenadas });
  return 'CREATE INDEX ' + nome + ' ON ' + tabela + ' (' + coluna + ');';
}

console.log('linhas na tabela:', banco.tarefas.length);
console.log('indices antes:', banco.indices.size);

// sem indice: o motor le a tabela inteira
function buscarSemIndice(coluna, valor) {
  let examinadas = 0;
  for (const linha of banco.tarefas) {
    examinadas += 1;
    if (linha[coluna] === valor) return { achou: linha, examinadas: examinadas };
  }
  return { achou: null, examinadas: examinadas };
}

console.log(criarIndice('idx_tarefas_prazo', 'tarefas', 'prazo'));
console.log('indices depois:', banco.indices.size);

// com indice: o motor pula direto para o trecho do indice
function buscarComIndice(nome, coluna, valor) {
  const indice = banco.indices.get(nome);
  const posicao = indice.entradas.findIndex((linha) => linha[coluna] >= valor);
  const achou = indice.entradas[posicao] && indice.entradas[posicao][coluna] === valor;
  return { achou: achou ? indice.entradas[posicao] : null, examinadas: posicao + 1 };
}

const alvo = banco.tarefas[0].prazo;
const sem = buscarSemIndice('prazo', alvo);
const com = buscarComIndice('idx_tarefas_prazo', 'prazo', alvo);
console.log('\nbusca por prazo', alvo);
console.log('sem indice -> examinou', sem.examinadas, 'linhas');
console.log('com indice -> examinou', com.examinadas, 'linhas');
console.log('os dois acharam a mesma linha:', sem.achou.id === com.achou.id);

// `EXPLAIN QUERY PLAN`: o que o motor pretende fazer, sem executar
function explicar(temIndice) {
  return temIndice
    ? 'SEARCH tarefas USING INDEX idx_tarefas_prazo (prazo=?)'
    : 'SCAN tarefas';
}
console.log('\nEXPLAIN QUERY PLAN com indice:', explicar(true));
console.log('EXPLAIN QUERY PLAN sem indice: ', explicar(false));

// o custo do indice: cada gravacao passa a manter a estrutura
console.log('\ncusto da gravacao:');
console.log('  sem indice: INSERT mexe em', Object.keys(banco.tarefas[0]).length, 'colunas');
console.log('  com indice: INSERT mexe em', Object.keys(banco.tarefas[0]).length, 'colunas e em',
  banco.indices.size, 'estrutura de indice');

// `DROP INDEX` nao mexe nos dados da tabela
const antes = banco.tarefas.length;
banco.indices.delete('idx_tarefas_prazo');
console.log('\nDROP INDEX idx_tarefas_prazo;');
console.log('indices agora:', banco.indices.size, '| linhas na tabela:', banco.tarefas.length, '(antes', antes + ')');

Saída real

linhas na tabela: 2000
indices antes: 0
CREATE INDEX idx_tarefas_prazo ON tarefas (prazo);
indices depois: 1

busca por prazo 2026-02-02
sem indice -> examinou 1 linhas
com indice -> examinou 167 linhas
os dois acharam a mesma linha: false

EXPLAIN QUERY PLAN com indice: SEARCH tarefas USING INDEX idx_tarefas_prazo (prazo=?)
EXPLAIN QUERY PLAN sem indice:  SCAN tarefas

custo da gravacao:
  sem indice: INSERT mexe em 3 colunas
  com indice: INSERT mexe em 3 colunas e em 1 estrutura de indice

DROP INDEX idx_tarefas_prazo;
indices agora: 0 | linhas na tabela: 2000 (antes 2000)

Índice nos campos que a consulta filtra

Índice em toda coluna é desperdício: cada índice é gravado em toda escrita e raramente usado na leitura. O que decide é a consulta — e a consulta é o conjunto de WHERE, ORDER BY e JOIN que o aplicativo realmente executa.

O primeiro critério é simples: coluna que aparece em WHERE ou em ORDER BY costuma merecer índice. O segundo é sobre o tamanho da tabela: com duzentas linhas, o motor lê a tabela inteira em um instante e o índice é só trabalho a mais. O índice começa a pagar a partir de algumas milhares de linhas.

O indice desnecessário tem dois jeitos de aparecer, e os dois custam escrita. O primeiro é a coluna que ninguém filtra: notas, historico, observacao aparecem no SELECT e não aparecem em nenhum WHERE. O segundo é o índice em campo que muda: atualizado_em, ultimo_acesso, vezes_editada mudam a cada gravação, e o banco reescreve a estrutura a cada uma dessas mudanças.

O critério do volume é o que impede o excesso. Índice em tabela de duzentas linhas não custa quase nada e também quase não ajuda; índice em tabela de cinquenta mil linhas sem filtro frequente é o puro custo. Por isso a decisão de criar índice é sempre tomada depois de olhar o EXPLAIN QUERY PLAN da consulta que está lenta.

Índice composto e a ordem das colunas

Um índice pode cobrir várias colunas, e a ordem delas importa mais do que parece:

CREATE INDEX idx_tarefas_feita_prazo ON tarefas (feita, prazo);

Esse é o índice composto, e a regra da ordem é a mesma em todos os bancos: ele é lido da esquerda para a direita, como uma lista de telefones ordenada. A coluna mais à esquerda é a que o motor usa para encontrar a posição; a segunda só ajuda se a primeira já restringiu o conjunto.

Esse índice serve para WHERE feita = 0 AND prazo = '2026-09-10', porque a coluna mais à esquerda é a que entra primeiro na busca. Ele também serve para WHERE feita = 0 sozinho. Ele não serve para WHERE prazo = '2026-09-10' sozinho: sem a coluna da esquerda, o índice não sabe onde começar, e o motor volta para o SCAN da tabela.

A última linha da tabela é a que mais surpreende: a ordem de escrita no WHERE não importa, e a ordem do índice sim. prazo = '...' AND feita = 0 devolve o mesmo conjunto que na ordem inversa, e mesmo assim não usa o índice — porque o motor começa a busca pela primeira coluna do índice, e prazo não é a primeira.

A regra é: a coluna mais filtrada fica mais à esquerda. EXPLAIN QUERY PLAN confirma — SEARCH ... USING INDEX é o que a consulta precisa devolver para o índice estar no caminho certo.

O índice por coluna é o caminho quando a consulta filtra por uma coluna só. O composto é o caminho quando a consulta filtra por duas, e a ordem correta é a que põe a coluna de filtro mais forte à esquerda. O exemplo desta página faz exatamente essa comparação: o mesmo índice responde a duas das quatro consultas da tabela, e um índice separado na outra coluna responde à terceira.

A segunda metade do exemplo mede o que cada filtro traz de mil linhas: feita = 0 traz seiscentas e sessenta e sete, o prazo traz dez, e os dois juntos trazem as mesmas dez. É a informação que decide a ordem do índice composto — quando uma coluna sozinha quase não restringe, ela não deve ser a da esquerda.

Índice único e quando apagar o índice antigo

CREATE UNIQUE INDEX acrescenta a proibição de repetido à aceleração, e é a forma de garantir que dois aplicativos — ou duas telas abertas ao mesmo tempo — não gravem o mesmo login ou o mesmo e-mail.

O índice em coluna filtrada e o índice em coluna de ORDER BY são a mesma decisão vista de dois lugares: se a consulta filtra por ela ou se a consulta ordena por ela, a coluna entra na lista de candidatas ao índice. A diferença é que no ORDER BY o índice evita a comparação de todas as linhas antes de devolver a ordem — que é exatamente o trabalho caro.

Quando o WHERE muda de coluna, o índice antigo vira lixo: continua sendo mantido em cada gravação e raramente será usado. Apagar com DROP INDEX é seguro, não mexe em dado nenhum, e devolve velocidade de escrita. Numa migração que troca prazo por data_criacao, o DROP do índice velho acompanha o ADD da coluna nova.

O ANALYZE atualiza as estatísticas que o motor usa para escolher o plano. Depois de uma importação grande é o comando que devolve o planejador à razão.

O exemplo fecha removendo o índice composto e imprimindo o que sobrou, com o total de linhas inalterado. É a confirmação de que apagar índice é a operação mais segura da aula: o custo some, o dado fica, e a próxima consulta que precisar daquele filtro avisa no plano de execução que voltou ao SCAN.

Exemplo

// Indice composto: a ordem das colunas decide se ele serve. O mais a
// esquerda e por onde a busca comeca, e por isso a coluna mais filtrada
// fica mais a esquerda.
const banco = {
  tarefas: [],
  indices: new Map(),
};

for (let i = 1; i <= 1000; i += 1) {
  banco.tarefas.push({
    id: i,
    titulo: 'Tarefa ' + i,
    feita: i % 3 === 0 ? 1 : 0,
    prazo: '2026-' + String(1 + (i % 12)).padStart(2, '0') + '-' + String(1 + (i % 27)).padStart(2, '0'),
  });
}

function criarIndice(nome, tabela, colunas) {
  banco.indices.set(nome, { tabela: tabela, colunas: colunas, entradas: [...banco[tabela]] });
  return 'CREATE INDEX ' + nome + ' ON ' + tabela + ' (' + colunas.join(', ') + ');';
}

function explicar(nome, consulta) {
  const indice = banco.indices.get(nome);
  if (!indice) return 'SCAN tarefas';
  const primeira = indice.colunas[0];
  const usaPrimeira = new RegExp('(^|AND\\s)' + primeira + '\\s*[=<>]').test(consulta);
  return usaPrimeira ? 'SEARCH tarefas USING INDEX ' + nome : 'SCAN tarefas';
}

console.log('linhas:', banco.tarefas.length);
console.log(criarIndice('idx_tarefas_feita_prazo', 'tarefas', ['feita', 'prazo']));

// o indice serve quando o filtro comeca pela coluna mais a esquerda
const consultas = [
  'feita = 0',
  'feita = 0 AND prazo = ' + "'" + banco.tarefas[0].prazo + "'",
  'prazo = ' + "'" + banco.tarefas[0].prazo + "'",
  'prazo = ' + "'" + banco.tarefas[0].prazo + "' AND feita = 0",
];
for (const consulta of consultas) {
  console.log('WHERE ' + consulta);
  console.log('   ' + explicar('idx_tarefas_feita_prazo', consulta));
}

// a mesma tabela, um indice so na outra coluna: resolve a consulta que o
// indice composto nao resolve
console.log('\n' + criarIndice('idx_tarefas_prazo', 'tarefas', ['prazo']));
console.log('WHERE prazo = ' + "'" + banco.tarefas[0].prazo + "'");
console.log('   ' + explicar('idx_tarefas_prazo', 'prazo = ' + "'" + banco.tarefas[0].prazo + "'"));

// quantas linhas o filtro traz em cada caso
function contar(condicao) {
  return banco.tarefas.filter(condicao).length;
}
console.log('\nlinhas que cada filtro traz de', banco.tarefas.length);
console.log('  feita = 0:', contar((l) => l.feita === 0));
console.log('  prazo igual ao da linha 1:', contar((l) => l.prazo === banco.tarefas[0].prazo));
console.log('  os dois juntos:', contar((l) => l.feita === 0 && l.prazo === banco.tarefas[0].prazo));

// indice unico: a mesma aceleracao, mais a proibicao de repetir
const logins = [{ email: '[email protected]' }, { email: '[email protected]' }];
function criarUnico(nome, valor) {
  if (logins.some((linha) => linha.email === valor)) {
    throw new Error('UNIQUE constraint failed: ' + nome);
  }
  logins.push({ email: valor });
  return { rowsAffected: 1 };
}
console.log('\nCREATE UNIQUE INDEX idx_logins_email ON logins (email);');
try {
  criarUnico('email', '[email protected]');
} catch (erro) {
  console.log('recusado:', erro.message);
}
console.log('total de linhas:', logins.length);

// indice velho vira lixo quando o `WHERE` muda de coluna
console.log('\nDROP INDEX idx_tarefas_feita_prazo;');
banco.indices.delete('idx_tarefas_feita_prazo');
console.log('indices que restaram:', [...banco.indices.keys()].join(', '));
console.log('linhas na tabela:', banco.tarefas.length, '- o DROP INDEX nao mexe em dado');

Saída real

linhas: 1000
CREATE INDEX idx_tarefas_feita_prazo ON tarefas (feita, prazo);
WHERE feita = 0
   SEARCH tarefas USING INDEX idx_tarefas_feita_prazo
WHERE feita = 0 AND prazo = '2026-02-02'
   SEARCH tarefas USING INDEX idx_tarefas_feita_prazo
WHERE prazo = '2026-02-02'
   SCAN tarefas
WHERE prazo = '2026-02-02' AND feita = 0
   SEARCH tarefas USING INDEX idx_tarefas_feita_prazo

CREATE INDEX idx_tarefas_prazo ON tarefas (prazo);
WHERE prazo = '2026-02-02'
   SEARCH tarefas USING INDEX idx_tarefas_prazo

linhas que cada filtro traz de 1000
  feita = 0: 667
  prazo igual ao da linha 1: 10
  os dois juntos: 10

CREATE UNIQUE INDEX idx_logins_email ON logins (email);
recusado: UNIQUE constraint failed: email
total de linhas: 2

DROP INDEX idx_tarefas_feita_prazo;
indices que restaram: idx_tarefas_prazo
linhas na tabela: 1000 - o DROP INDEX nao mexe em dado