Turbinando suas buscas internas

O problema

Quase sempre vemos um formulário de busca interna em sites que publicam conteúdos com frequencia. Este formulário, assim como a navegação por categorias ou por tags, ajuda muito os visitantes a encontrarem o que procuram. Mas desenvolvedores iniciantes - ou nem tão iniciantes assim - ainda implementam esse mecanismo de busca de uma forma "crua".

Geralmente é um simples formulário, com método GET, um campo de texto e um botão de OK. Até aí, sem problemas. No front end é isso mesmo. Mas no back end, geralmente a implementação que vemos é algo assim:


<?php
$terms = explode(' ', $_GET);
$query = "SELECT * FROM table WHERE conteudo LIKE ";

foreach ($terms as $term) {
$query .= "%" . $term . "% OR LIKE";
}
... // roda a query, pega resultados, mostra
?>


Pois bem, esta abordagem, apesar de funcional, não é elegante, nem tem a precisão desejável. Se você buscar por "um cone" pode encontrar "a coleção de ícones do Humberto". Isso sem falar da performance das queries, que é seriamente afetada quanto mais registros no banco e mais termos são procurados.

A solução? Um recurso que os principais RDBMS tem chamado Full-Text Search. Vou ensinar aqui como implementar e usar estes recursos na prática (no MySQL e no PostgreSQL).

MySQL

NOTA: o recurso de Full-Text Search só está disponível para tabelas MyISAM.
Vamos supor que você tenha uma tabela de posts ou artigos com a estrutura parecida com a seguinte:

id
titulo
intro
conteudo
autor
tags
publishdate

Em primeiro lugar vamos precisar adicionar um índice, que é composto pelos campos que desejamos que sejam buscados. Rode isso pelo seu programa de administração do MySQL (MySQL Admin, phpMyAdmin).

ALTER TABLE posts ADD FULLTEXT (titulo, intro, conteudo, tags);


Depois disso, basta reescrever a query no PHP:

SELECT * FROM posts
WHERE MATCH (titulo, intro, conteudo, tags)
AGAINST (". $_GET .");


A busca será mais rápida e os resultados com menos falsos positivos. Sem contar que o código fica mais limpo e elegante.

PostgreSQL

O PostgreSQL dá um pouco mais de trabalho para fazer a implementação.

Pelo seu programa de administração, rode as seguintes queries.

ALTER TABLE posts ADD COLUMN postsfts tsvector;

UPDATE posts SET postsfts =
setweight(to_tsvector('pg_catalog.portuguese', coalesce(titulo,'')), 'A') ||
setweight(to_tsvector('pg_catalog.portuguese', coalesce(intro,'')), 'B') ||
setweight(to_tsvector('pg_catalog.portuguese', coalesce(conteudo,'')), 'C');

CREATE INDEX postsfts_idx
ON posts
USING gin(postsfts);

CREATE TRIGGER postsfts_tg
BEFORE INSERT OR UPDATE ON posts
FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger('postsfts', 'pg_catalog.portuguese', 'titlulo', 'intro', 'conteudo');


Entendendo o que foi feito.
A primeira query adiciona um outro campo à tabela, onde o vetor de busca ficará armazenado (isso aumenta de forma significativa a performance).
A segunda query define quais campos farão parte do full-text search. Note que no PostgreSQL você pode definir o peso de cada campo - A, B, C ou D, sendo A o campo mais importante, D o menos importante. Ah, e claro definimos a linguagem para português.
Na terceira query, construímos um index sobre o campo que acabamos de criar.
Por último, criamos um trigger que vai atualizar o campo que criamos, sempre que o o conteúdo da tabela for atualizado.

Com isto feito, podemos transformar as queries no PHP para:

SELECT * FROM posts, to_tsquery(' . $_GET . ') query
WHERE query @@ postsfts;


No PostgreSQL o operador "@@" funciona como o MATCH AGAINST do MySQL. E precisamos converter o que vai ser buscado através da função to_tsquery().

Qual é o mais relevante?

Os recursos de full-text search permitem ir um pouco mais além e definir um ranking sobre quais registros são mais relevantes sobre os termos buscados. No PostgreSQL isso pode ser feito com uma query similar à seguinte:


SELECT *, ts_rank_cd(postsfts, query) AS rank
FROM posts, to_tsquery(' . $_GET . ') query
WHERE query @@ postsfts
ORDER BY rank DESC LIMIT 5;


Aqui temos a função ts_rank_cd que faz essa função do ranking (além de já termos setado os pesos dos campos antes). E vocês podem notar que estamos ordenando pelo ranking. Ou seja, essa query trás os 5 resultados mais relevantes.

Para obter o mesmo efeito no MySQL podemos fazer a query da seguinte forma:

SELECT *, MATCH (titulo, intro, conteudo, tags) AGAINST (". $_GET .") AS rank
FROM posts
WHERE MATCH (titulo, intro, conteudo, tags) AGAINST (". $_GET .")
ORDER BY rank DESC LIMIT 5;


Conclusão

Espero ter criado um guia rápido e prático para que vocês possam aplicar o full-text search em seus próximos trabalhos e assim obter melhores resultados. Vocês codificam mais rápido, o sistema ganha em performance, e os usuários agradecem os resultados mais precisos.

Abraços e keep readin'

----------- keepReading