Turbinando suas buscas internas

Os bancos de dados geralmente tem um recurso embutido chamado Full-Text Search, muito útil para mecanismos de busca internos, e pouco explorado. Aprenda aqui como implementar no seu site.

Publicado em: 11/10/2010 Avaliação: 3.1 de 5.0 baseada em 613 votos.

otimização PHP MySQL PostgreSQL melhores práticas design de software

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 .");

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', 'titulo', '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.

daVe html deV

Artigos, tutoriais e ferramentas para desenvolvimento de websites