Busca Inteligente no Banco de Dados: Além do SQL LIKE

Lede: Todo mundo já escreveu WHERE nome LIKE '%ana%' e viu "Vanessa" aparecer no resultado. Esse operador erra a resposta e custa O(n) por consulta. Este artigo mostra como o Full-Text Search conserta os dois problemas de uma vez — índice invertido, stemming nativo de português e respostas em sub-milissegundos — e as pegadinhas que fazem uma busca "indexada" continuar devolvendo lixo.

🎯 O que você leva daqui:

• Por que LIKE erra e é lento, com as duas causas separadamente

• Como funciona um índice invertido (e por que isso muda tudo)

• FTS no MySQL: MATCH() AGAINST() e os 6 gotchas que derrubam buscas

• FTS no PostgreSQL: tsvector, GIN, e o stemming português testado na prática

• A tabela de decisão: quando usar FTS, quando usar pg_trgm, quando LIKE ainda é ok

---

1. O que realmente quebra com o LIKE

1.1 Falsos positivos: o LIKE não entende português

O LIKE faz comparação de sequências de bytes. Não existe tokenizer, não existe dicionário, não existe noção de limite de palavra. O que ele sabe é: "essa sequência aparece em algum lugar desse texto?".

Busca	O que volta além do esperado	Por que falha
%Maria%	Mariana	"Maria" é prefixo literal de "Mariana" — nenhuma fronteira de palavra é avaliada
capa%	capacete	Prefixo literal casa com uma palavra maior
%anel%	panela de pressão, jogo de panelas	A sequência "anel" aparece dentro da palavra "panela"

1.2 Tirar o coringa da frente não resolve

A correção intuitiva é restringir ao início do texto:

-- 'anel%' NÃO devolve "panela" (não começa com "anel")
-- MAS continua errado:
SELECT * FROM produtos WHERE nome LIKE 'Maria%';
-- → devolve "Mariana", "Marialva", "Marina"...
SELECT * FROM produtos WHERE nome LIKE 'capa%';
-- → devolve "capacete"

E o problema se agrava: busca composta e fora de ordem não funciona. O usuário digita "anel prata" ou "panela pressão" e o LIKE não devolve nada, porque exige que os termos apareçam na mesma ordem e exatamente como estão cadastrados.

⚠️ LIKE 'termo%' só troca um problema (performance) por outro (falsos positivos). E ainda quebra a busca multi-termo. Não é uma solução parcial — é outra armadilha.

1.3 O custo: Seq Scan multiplicado pela concorrência

%termo% com wildcard à frente é inoperante para índices B-Tree: o índice é ordenado pelo início da string, e o padrão começa no meio. O SGBD desiste do índice e faz varredura completa.

• MySQL: Table Scan, laço iterativo sobre cada linha
• PostgreSQL: Seq Scan, idem

Em uma tabela de 10.000 linhas, uma consulta exige 10.000 verificações. E o custo total cresce de forma multiplicativa com a concorrência:

Total de leituras = Consultas simultâneas × Linhas na tabela

   1 usuário  →  10.000 leituras
  10 usuários  → 100.000 leituras
 100 usuários  →   1.000.000 de varreduras em RAM e disco

📊 Precisão sobre a complexidade: o LIKE '%termo%' é linear O(n) por consulta, não exponencial. O que multiplica é a concorrência — e é por isso que ele derruba infraestrutura em horário de pico, não em teste.

---

2. Como o Full-Text Search funciona

FTS inverte a lógica: em vez de varrer as linhas procurando o texto, ele pré-processa o texto e guarda um mapa termo → linhas. A consulta vira uma consulta no índice.

graph TD
    A["Texto bruto"] --> B["1. Tokenização · decompõe em palavras"]
    B --> C["2. Stop words · remove de, para, com, o, a"]
    C --> D["3. Índice invertido · token → IDs das linhas"]
    D --> E["Busca = interseção · de posting lists"]

Tokenização — o texto é quebrado em palavras, descartando pontuação e caracteres especiais.

Stop words — conectivos de baixo valor semântico (de, para, com, o, a, que) são eliminados. Reduz o índice e remove ruído estatístico.

Índice invertido — a peça central. Em vez do B-Tree mapear linha → atributos, ele mapeia termo → lista de IDs:

Token / Lexema    Posting list (IDs das linhas)
─────────────────────────────────────────────
anel              ► [1, 2]
capacete          ► [8, 9, 10]
panela            ► [3, 5]
prata             ► [3, 5]

É o índice remissivo do final de um livro técnico: você não folheia as páginas, você vai direto ao termo e lê a lista de páginas onde ele aparece.

A consulta composta vira uma interseção de conjuntos

Para "panela de pressão":

1. O token de é removido como stop word
2. Restam dois tokens válidos: panela e pressão
3. O motor busca as posting lists de cada um
4. Interseção matemática

Resultado = IDs(panela) ∩ IDs(pressão)
         = {3, 5} ∩ {3, 5}
         = {3, 5}

Nenhuma linha da tabela foi lida na fase de filtragem.

---

3. FTS no MySQL: MATCH() AGAINST()

O MySQL suporta FTS nativo em tabelas InnoDB (e MyISAM), indexando colunas CHAR, VARCHAR, TEXT e variantes.

3.1 Criando o índice

-- Uma coluna
ALTER TABLE Artigos
  ADD FULLTEXT INDEX ft_conteudo (Conteudo);

-- Índice composto: nome + descrição no mesmo índice
ALTER TABLE Produtos
  ADD FULLTEXT INDEX idx_busca (Nome, Descricao);

3.2 Natural Language Mode (padrão)

O usuário digita texto livre. O banco ignora stop words e calcula uma pontuação de relevância.

SELECT
    Id,
    Titulo,
    MATCH(Conteudo) AGAINST('SQL full-text' IN NATURAL LANGUAGE MODE) AS Relevancia
FROM Artigos
WHERE MATCH(Conteudo) AGAINST('SQL full-text' IN NATURAL LANGUAGE MODE)
ORDER BY Relevancia DESC;

3.3 Boolean Mode

Operadores explícitos, sem ranking de relevância:

-- +SQL -NoSQL  → deve conter "SQL" e NÃO conter "NoSQL"
SELECT Id, Titulo
FROM Artigos
WHERE MATCH(Conteudo) AGAINST('+SQL -NoSQL' IN BOOLEAN MODE);

-- pro* → casa com "produto", "programa", "processo"...
SELECT Id, Titulo
FROM Artigos
WHERE MATCH(Conteudo) AGAINST('+SQL pro*' IN BOOLEAN MODE);

Operador	Significado	Exemplo
+termo	Obrigatório	+SQL
-termo	Excluído	-NoSQL
termo*	Curinga de prefixo	pro*
"frase exata"	Correspondência de frase	"full text search"

3.4 Os 6 gotchas que derrubam buscas no MySQL

Este é o trecho que mais aparece em código quebrado em produção.

1. A regra dos 50% — a mais traiçoeira de todas.

No Natural Language Mode, uma palavra presente em 50% ou mais das linhas é tratada como stop word e removida do resultado. Se você tem 10.000 artigos e 6.000 citam "PostgreSQL", buscar por "PostgreSQL" não devolve nada — com os dados todos no banco.

A saída é o BOOLEAN MODE, que não aplica esse limiar:

-- Devolve vazio se "PostgreSQL" está em mais da metade das linhas
WHERE MATCH(Conteudo) AGAINST('PostgreSQL' IN NATURAL LANGUAGE MODE);

-- Retorna corretamente
WHERE MATCH(Conteudo) AGAINST('+PostgreSQL' IN BOOLEAN MODE);

2. Termos curtos são descartados na indexação

innodb_ft_min_token_size define o tamanho mínimo de token indexado. Padrão do InnoDB: 3. Então "TI", "RH" e "P1" nunca entram no índice — não adianta mudar a query.

[mysqld]
innodb_ft_min_token_size = 2

🔧 Mudar essa variável não reconstrói o índice existente. Depois de alterar my.cnf é preciso reiniciar o servidor e rodar OPTIMIZE TABLE Artigos;. Sem os dois passos, nada muda.

innodb_ft_max_token_size (padrão 84) funciona igual no outro extremo. MyISAM usa ft_min_word_len (padrão 4) e ft_max_word_len.

3. Stop words: são 3 variáveis, não uma

Aqui é comum errar o nome. Para InnoDB o controle é feito por:

• innodb_ft_enable_stopword — booleano que liga/desliga o mecanismo (padrão ON)
• innodb_ft_server_stopword_table — aponta para a tabela de stop words do servidor
• innodb_ft_user_stopword_table — stop words customizadas por tabela (o que você realmente quer)

As três são lidas na hora em que o índice é criado. Alterar qualquer uma exige reconstruir o índice:

-- 1. cria a tabela de stop words
CREATE TABLE app_stopwords (value VARCHAR(30)) ENGINE=InnoDB;
INSERT INTO app_stopwords VALUES ('de'), ('para'), ('com');

SET GLOBAL innodb_ft_user_stopword_table = 'seu_schema/app_stopwords';

-- 2. reconstrói o índice
OPTIMIZE TABLE Artigos;

4. As colunas do MATCH() têm que bater com o índice

O InnoDB exige um FULLTEXT index em todas as colunas da expressão MATCH(). Com o índice composto (Nome, Descricao):

-- ✅ funciona
MATCH(Nome, Descricao) AGAINST('sql')

-- ❌ ERRO: Can't find FULLTEXT index matching the column list
MATCH(Nome) AGAINST('sql')

5. EXPLAIN ANALYZE executa a query de verdade

Essa pegadinha vale para o MySQL e o PostgreSQL: o ANALYZE não é simulação, ele roda a query completa. Em UPDATE/DELETE/INSERT isso significa escrita real dentro de uma transação que você provavelmente não ia abrir.

BEGIN;
EXPLAIN ANALYZE UPDATE ...;   -- se a query escreve, você escreveu
ROLLBACK;

6. O parser ngram — o botão de escape para busca por fragmento

O parser padrão só enxerga palavras inteiras. Para busca no meio da palavra, existe o ngram:

CREATE FULLTEXT INDEX ft_ngram ON Artigos (Titulo, Conteudo) WITH PARSER ngram;
-- ngram_token_size: padrão 2, read-only, ajuste no startup (mysqld --ngram_token_size=3)

Ele resolve indexabilidade de fragmentos, mas não resolve semântica — MATCH(...AGAINST('oard')) casa "Keyboard" e também qualquer outra palavra que contenha "oard". É a ferramenta certa para CJK e busca por prefixo real, não um substituto do FTS semântico.

---

4. FTS no PostgreSQL: tsvector, tsquery e o poder do português

O PostgreSQL tem a implementação de FTS mais madura entre os bancos relacionais: dicionários para vários idiomas, stemming, sinônimos configuráveis e ranking por cobertura (para highlight).

4.1 Os três elementos fundamentais

Elemento	O que faz
to_tsvector(config, texto)	Converte o documento em tsvector: lista ordenada de lexemas + suas posições
to_tsquery / plainto_tsquery / phraseto_tsquery / websearch_to_tsquery	Converte a entrada do usuário em uma consulta lógica tsquery
operador @@	Avalia se o vetor atende ao tsquery → true / false

SELECT to_tsvector('portuguese', 'O PostgreSQL é um banco excelente')
       @@ to_tsquery('portuguese', 'banco & postgresql');
-- true

4.2 Coluna gerada STORED + índice GIN — e a pegadinha do IMMUTABLE

Para escalar em tabelas grandes, o PostgreSQL usa o índice GIN (Generalized Inverted Index), que mapeia lexemas → IDs de linha.

Duas abordagens possíveis:

-- (A) Índice de expressão — funciona, mas é frágil
CREATE INDEX idx_ft ON Artigos USING GIN (to_tsvector('portuguese', Conteudo));

-- (B) Coluna gerada persistida + GIN na coluna física — recomendado
ALTER TABLE Artigos
  ADD COLUMN BuscaLexematizada tsvector
  GENERATED ALWAYS AS (
    to_tsvector('portuguese', COALESCE(Titulo, '') || ' ' || COALESCE(Conteudo, ''))
  ) STORED;

CREATE INDEX idx_ft_artigos ON Artigos USING GIN (BuscaLexematizada);

💡 Por que a opção (A) às vezes "ignora o índice"? Não é o Query Planner sendo burro — é volatilidade de função. to_tsvector('portuguese', col) com o dicionário explícito é IMMUTABLE e pode ser indexada. Já to_tsvector(col) sem dicionário é STABLE (depende de default_text_search_config) e o PostgreSQL recusa indexar: "functions in index expression must be marked IMMUTABLE".
Ou seja: sempre passe o dicionário como literal. Além de indexável, isso evita um table_rewrite completo ao adicionar a coluna.

Requisito: colunas geradas STORED existem a partir do PostgreSQL 12.

4.3 Stemming: o que realmente acontece

Aqui é onde a busca em português ganha de verdade. Stemming é a redução de uma palavra à sua raiz morfológica, de modo que variações gramaticais colidam no mesmo lexema.

🧪 As saídas abaixo foram verificadas contra o algoritmo Snowball — o mesmo que o PostgreSQL usa. Não são estimativas. Compare com o exemplo que costuma circular em tutoriais, que erra o lexema.

SELECT to_tsvector('portuguese', 'programador programando programação programadores');
-- 'program':1,2,3,4

SELECT to_tsvector('portuguese', 'anel de prata e anel prateado');
-- 'anel':1,5 'prat':3,6

Repare que o lexema real é program, não "programa", e é prat, não "pret". O comportamento — as quatro flexões colapsarem num único termo — está correto; o que circulava errado era o rótulo do lexema. E é por isso que buscar "anel prateado" encontra o registro cadastrado como "anel de prata": ambos viram anel + prat.

O mesmo mecanismo resolve os falsos positivos do LIKE****:

SELECT to_tsvector('portuguese', 'Maria')     -- 'mar':1
SELECT to_tsvector('portuguese', 'Mariana')   -- 'marian':1
SELECT to_tsvector('portuguese', 'capa')      -- 'cap':1
SELECT to_tsvector('portuguese', 'capacete')  -- 'capacet':1

Maria ≠ Mariana. capa ≠ capacete. A busca por nome próprio finalmente funciona.

🚧 O stemming não é magia — e vale testar o SEU vocabulário. O stemmer português lida bem com a maioria dos plurais, mas não com todos:
caderno/cadernos → ambos cadern ✅ · capa/capas → ambos cap ✅ · mas

anel → anel e anéis → ané ❌

Palavras cujo plural troca o acento caem em lexemas diferentes. Se seu domínio tem esses casos, o conserto é um dicionário de sinônimos ou um dicionário snowball customizado — não gambiarra no código da aplicação.

4.4 Escolhendo a função de consulta certa

Função	Entrada do usuário	Quando usar
to_tsquery	Operadores já escritos	Query montada pela própria aplicação. Exige sintaxe válida — erro de parse quebra a consulta
plainto_tsquery	Texto livre	Palavras soltas viram AND. Nunca quebra com input do usuário
phraseto_tsquery	Frase	Exige os termos consecutivos e na ordem
websearch_to_tsquery	Sintaxe de busca web	⭐ O melhor para formulário: aceita "frase", or, -exclusão e nunca quebra

-- Entrada crua do usuário, sem risco de parse error
SELECT Id, Titulo
FROM Artigos
WHERE BuscaLexematizada @@ websearch_to_tsquery('portuguese', $1);

-- Busca composta com exclusão
WHERE BuscaLexematizada @@ websearch_to_tsquery('portuguese', 'anel prateado -ouro');

4.5 Ranking e highlight

SELECT
    Id,
    Titulo,
    ts_rank(BuscaLexematizada, q) AS rank,
    ts_headline('portuguese', Conteudo, q,
                'StartSel=<mark>, StopSel=</mark>, MaxFragments=2') AS trecho
FROM Artigos, websearch_to_tsquery('portuguese', 'índice invertido') AS q
WHERE BuscaLexematizada @@ q
ORDER BY rank DESC;

• ts_rank — relevância simples
• ts_rank_cd — cover density: considera a proximidade dos termos, melhorando o ranking quando várias palavras aparecem juntas
• ts_headline — recorta o trecho relevante com marcação, direto para o frontend

4.6 Dicionários de sinônimos (stemming ≠ sinônimo)

O stemming unifica flexões, não sinônimos. E isso é proposital:

SELECT to_tsvector('portuguese', 'carro')      -- 'carr':1
SELECT to_tsvector('portuguese', 'veículo')    -- 'veícul':1
SELECT to_tsvector('portuguese', 'automóvel')  -- 'automóvel':1

Para unificar sinônimos você monta um dicionário synonym sobre um dicionário simple:

CREATE TEXT SEARCH DICTIONARIO meu_sync (
    TEMPLATE = simple,
    STOPWORDS = pg_catalog.portuguese,
    CONTENT = 'carro,automóvel,veículo'
);

4.7 pg_trgm — a saída para quando você PRECISA de substring

🧩 Essa é a peça que falta em quase todo tutorial de FTS. Se você tem WHERE titulo ILIKE '%termo%' em produção e não consegue remover, pg_trgm indexa essa coluna sem reescrever a aplicação.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Índice trigram sobre a coluna
CREATE INDEX idx_titulo_trgm ON Artigos USING GIN (Titulo gin_trgm_ops);

-- A MESMA query com ILIKE agora usa o índice
SELECT Id, Titulo FROM Artigos WHERE Titulo ILIKE '%anel%';

-- Ranking por similaridade
SELECT Titulo, similarity(Titulo, 'anel de prata') AS sim
FROM Artigos
WHERE Titulo % 'anel de prata'          -- operador % usa o threshold
ORDER BY sim DESC;

O que o pg_trgm resolve: o custo. %termo% deixa de ser Seq Scan e vira busca no índice.

O que ele NÃO resolve: a semântica. O similarity() tem threshold padrão 0.3 e ainda casa "panela" com "anel". É ferramenta de performance, não de precisão.

Limitações: trigramas exigem 3+ caracteres (busca de 2 letras não se beneficia) e o índice é bem maior que um GIN comum.

4.8 Stop words customizadas

CREATE EXTENSION IF NOT EXISTS pg_stopwords;   -- PostgreSQL 13+
CREATE TEXT SEARCH CONFIGURATION meu_pt (COPY = pg_catalog.portuguese);
ALTER TEXT SEARCH CONFIGURATION meu_pt
  ALTER MAPPING FOR hword, hword_part, word WITH simple, meu_stopwords;

---

5. Números: o que o EXPLAIN ANALYZE mostra

Base de teste: 10.000 linhas.

Motor / Técnica	Plano	Leitura
MySQL · LIKE '%SQL%'	Table Scan	custo ~1028 · 10.000 linhas
MySQL · MATCH() AGAINST()	Full-text index lookup	custo ~0,35 · 419 linhas
PostgreSQL · ILIKE '%sql%'	Seq Scan	~4,9 ms
PostgreSQL · to_tsvector() sem índice	Seq Scan + cálculo por linha	~139 ms ⚠️
PostgreSQL · coluna STORED + GIN	Bitmap Index Scan	0,3 – 0,8 ms ⚡

⚠️ Dois avisos sobre esta tabela. Primeiro: custo do MySQL e milissegundos do PostgreSQL não são comparáveis — são unidades diferentes dentro de cada motor. Segundo: a linha "FTS sem índice" é a mais perigosa do artigo. Usar to_tsvector() direto no WHERE é pior que ILIKE (139 ms contra 4,9 ms), porque o banco calcula o vetor de todas as linhas a cada consulta. É exatamente o motivo de existirem as colunas STORED.

-- PostgreSQL: EXPLAIN sem ANALYZE mostra só custo estimado
EXPLAIN SELECT Id, Titulo FROM Artigos WHERE Conteudo ILIKE '%sql%';
-- Seq Scan on artigos  (cost=0.00..270.00 rows=10000)

-- EXPLAIN ANALYZE executa a query e adiciona tempo real
EXPLAIN ANALYZE SELECT Id, Titulo FROM Artigos WHERE Conteudo ILIKE '%sql%';
-- Seq Scan on artigos  (cost=0.00..270.00 rows=10000)
--                        (actual time=0.011..4.892 rows=10000 loops=1)
-- Planning Time: 0.089 ms
-- Execution Time: 4.931 ms

-- MySQL 8.0.18+: formato em árvore, com custo E tempo real
EXPLAIN ANALYZE
SELECT Id, Titulo FROM Artigos WHERE Conteudo LIKE '%SQL%';
-- -> Table scan on Artigos  (cost=1028.0 rows=10000)
--                          (actual time=0.01..38.4 rows=10000 loops=1)

---

6. Matriz de decisão

Recurso	SQL LIKE	MySQL FTS	PostgreSQL FTS
Evita varredura completa	Não	Sim (índice invertido)	Sim (índice invertido / GIN)
Comportamento com volume	Linear O(n) por consulta	Proporcional à posting list	Proporcional à posting list
Dicionários por idioma	Não	Não nativo (só stop words)	Nativo, ~20 idiomas
Stemming	Não	Não	Sim (Snowball por idioma)
Plural / singular	Não	Não	Sim — com exceções
Sinônimos configuráveis	Não	Não	Sim (dicionário synonym)
Ranking de relevância	Não	Sim (só em Natural Language)	Sim (ts_rank, ts_rank_cd)
Booleanos	Não	Sim (Boolean Mode)	Sim (&, `
Highlight com marcação	Não	Não	Sim (ts_headline)
Indexa substring (%termo%)	Não usa índice	Só com parser ngram	Sim, com pg_trgm

Quando usar o quê

FTS nativo no PostgreSQL — se o requisito é busca em português, tolerância a plural e flexão, ranking e highlight. É a opção mais madura das três.

FTS nativo no MySQL — se você já está em MySQL e o caso é busca por palavra inteira com booleanos. Simples e suficiente. Só trate a regra dos 50% e o tamanho mínimo de token.

pg_trgm no PostgreSQL — quando a busca precisa ser por fragmento de verdade (código, SKU parcial, nome parcial) e você não pode exigir correspondência de palavra inteira.

LIKE — só para uso administrativo, relatórios ad-hoc e tabelas pequenas com volume estático. Fora disso, é dívida técnica com I/O.

---

7. Checklist de migração

Do LIKE para FTS, sem quebrar nada no caminho:

• Mapear as queries de busca existentes e quais colunas elas atingem
• Testar o stemming no vocabulário real do domínio (SELECT to_tsvector('portuguese', ...)) — o par anel/anéis vai te morder
• Criar a coluna tsvector STORED com o dicionário como literal IMMUTABLE
• Criar o índice GIN
• Trocar ILIKE por websearch_to_tsquery (nunca to_tsquery com input cru)
• Adicionar ranking (ts_rank_cd) e highlight (ts_headline) na UI
• Substituir EXPLAIN ANALYZE por EXPLAIN no que roda em produção
• No MySQL: configurar innodb_ft_min_token_size, reiniciar e reconstruir o índice
• No MySQL: migrar buscas frequentes para BOOLEAN MODE para escapar da regra dos 50%
• Acompanhar com EXPLAIN (ANALYZE, BUFFERS) e comparar com o baseline do LIKE

---

Referências

• 📚 PostgreSQL 12.3 — Controlling Text Search — to_tsvector, tsquery e websearch_to_tsquery
• 📚 PostgreSQL 12.6 — Dictionaries — stop words, lexemas e dicionários synonym
• 📚 PostgreSQL 12.1 — Introduction to Full-Text Search — o pipeline de pré-processamento
• 📚 PostgreSQL — pg_trgm — busca por substring indexada
• 📚 PostgreSQL — Generated Columns — GENERATED ALWAYS AS ... STORED
• 📚 MySQL — Natural Language Full-Text Searches — regra dos 50% e stop words
• 📚 MySQL — Boolean Full-Text Searches — operadores e a isenção do limiar de 50%
• 📚 MySQL — Fine-Tuning Full-Text Search — innodb_ft_min_token_size e quando reconstruir o índice
• 📚 MySQL — ngram Full-Text Parser — busca por fragmento
• 🔧 Snowball — Stemmer Algorithms — os algoritmos de stemming por idioma usados pelo PostgreSQL