Uma parte muito importante para quem desenvolve aplicações e sites são os mecanismos de busca internos, que permitem ao usuário localizar um conteúdo dentro da aplicação, e para nossos usuários, em sites, é igualmente importante uma busca que retorne não só o termo buscado, como também outros termos relevantes, e uma classificação pela relevância do termo localizando as ocorrências mais exatas, e é justamente isso que vamos abordar neste artigo.
Para realizar buscas, usando a linguagem natural (ou o texto, como é escrito e falado), os bancos de dados relacionais como MySQL, MariaDB, MS-SQL, Oracle, PostgreSQL, e uma infinidade enorme de outros usam um conjunto de 3 mecanismos, que são os Índices FullText, e as funções MATCH() e AGAINST() trabalhando em conjunto. Vamos falar agora sobre esses 3 grupos.
FULLTEXT INDEX – O MySQL, a partir da versão 3.25.25, provê o mecanismo de índices fulltext, efetuando buscas textuais com maior precisão. Este recurso é mais poderoso que o uso de LIKE pois, além de ordenar o resultado pela similaridade semântica, oferece mais opções para filtragem na consulta. Os tipos de dados suportados são VARCHAR, TEXT e CHAR, e para que as buscas retornem com maior precisão, é necessário que um índice FULLFEXT seja criado com todas as colunas que serão pesquisadas em conjunto, ou que vários índices sejam concatenados.
MATCH() – especifica a coluna, ou as colunas na qual você deseja pesquisar
AGAINST() – determina a expressão de pesquisa a ser usada, e o formato com que o banco vai gerar a RELEVÂNCIA
RELEVÂNCIA – É um número positivo de ponto flutuante que determina o quanto os termos pesquisados tem a ver com aquela linha retornada. Quando a relevância é zero, significa que não há semelhança. O MySQL calcula a relevância com base em vários fatores, incluindo o número de palavras no documento, o número de palavras únicas no documento, o número total de palavras na coleção e o número de documentos (linhas) que contêm uma palavra específica.
Criando o ÍNDICE FULLTEXT
Para criar o índice, nas tabelas caso ele não exista, você pode usar:
CREATE FULLTEXT INDEX <NOME_DO_INDICE> ON <TABELA> (<COLUNA1>,<COLUNA2>, ... , <COLUNA3>) ;
Você pode ter 1 único índice fulltext, com todos os campos que serão pesquisados em conjunto, ou pode ter cada coluna em um índice separado, caso queira fazer pesquisas somente em uma ou em outra coluna. Caso opte por um índice com várias colunas, o desempenho é otimizado para buscas neste grupo, caso queira cada um em separado, é otimizado para aquela situação.
Você pode ter vários índices com nomes diferentes, e conjuntos de campos diferentes, para otimizar suas buscas em várias situações diferentes, porém, vale lembrar que as operações de insert, update e delete ficam prejudicadas para tabelas com muitos índices, já que toda operação todos os índices precisam também ser atualizados.
Para que a consulta utilize o ÍNDICE CORRETO e seja otimizada, é importante que a clausula MATCH tenha exatamente os mesmos campos que estejam em um ou mais indices, adiante vamos ver os exemplos.
MATCH vs LIKE
O comando MATCH é mais veloz, tendo em vista a indexação de cada palavra do campo que faz parte do índice fulltext. A pesquisa fulltext foi criada com o objetivo de fornecer uma busca semântica em bases que contenham muito texto. Dessa forma, o MySQL desconsidera palavras com menos de quatro caracteres. Expressões como “de”, “que” e “ou” são excluídas automaticamente da pesquisa. Esta restrição é justificável na maioria dos casos, dada a baixa seletividade destas palavras em pesquisas textuais.
O MySQL possui uma lista de palavras desconsideradas na pesquisa, conhecidas como stopwords. A lista contém termos comuns em inglês, como “able”, “about”, “above”, “according”, entre outros. Só é possível personalizar as stopwords alterando o arquivo ft_static.c, disponível no diretório myisam, nos fontes do MySQL. Após alterar o arquivo, devemos recompilar o MySQL e reconstruir os índices fulltext. Na lista de solicitações para as novas versões do MySQL está a possibilidade de configurar as stopwords de uma forma mais simples.
A pesquisa fulltext pode operar também com parâmetros booleanos, aumentando significativamente o poder na construção de filtragens de texto. A pesquisa booleana tem como base a manipulação de strings de acordo com alguns operadores. Veja a lista dos operadores disponíveis:
- : a string deve estar presente em todos os registros retornados;
- : a string não deve estar presente nos registros retornados;
*: trabalha com parte da palavra a ser procurada;
“ ”: retorna a string entre aspas duplas exatamente da maneira como foi digitada;
( ): Agrupa palavras em sub-expressões;
< >: Muda a contribuição da string no cálculo da relevância. O operador < decrementa a relevância e o operador > aumenta a relevância;
~ : age como operador de negação. A contribuição de relevância da string se torna negativa.
A pesquisa booleana desconsidera o filtro de 4 letras mínimas e de 50% de ocorrência no resultado. Portanto, se você precisa de uma busca que não leve em consideração essas restrições, utilize o modo booleano.
ALGUMAS CONSIDERAÇÕES
Desconsidera palavras com menos de quatro caracteres, caso IN BOOLEAN MODE não seja utilizado;
Desconsidera palavras presentes em mais de 50% dos registros, caso IN BOOLEAN MODE não seja utilizado;
É indicada para tabelas textuais grandes, contendo centenas ou milhares de registros;
Palavras hifenizadas são tratadas em separado. Por exemplo, em azul-celeste, o mecanismo busca pela ocorrência de azul e/ou celeste;
Por default, os registros são retornados por ordem descendente de relevância;
Palavras da lista de stopwords são desconsideradas;
A pesquisa é case-insensitive (por exemplo, não há diferença entre “Server” ou “server”);
Uso de várias opções de operadores booleanos;
Melhor performance que o operador like;
A pesquisa em modo booleano também pode funcionar sem a existência de um índice fulltext. A performance, no entanto, será menor.
Algumas limitações do mecanismo de pesquisa:
Um dos recursos solicitados pelos usuários do MySQL é o aumento no poder de parametrização de pesquisas fulltext.
A criação deste tipo de índice causa uma queda no desempenho de operações INSERT e UPDATE. A perda é maior se comparada com o uso de índices comuns sobre colunas texto e mais explícita quando temos tabelas muito grandes.
O parâmetro utilizado em AGAINST( ) deve ser do tipo string;
Este recurso possui uma grande lista to-do na documentação do MySQL, com itens que deverão ser implementados nas próximas versões do banco. Entre eles, podemos destacar o uso de fulltext em tabelas MERGE, melhorias no algoritmo com o intuito de produzir pesquisas ainda mais rápidas, regionalização da lista de stopwords e mais flexibilidade para o administrador configurar o comportamento da pesquisa.
Exemplos de uso comuns
Para os exemplos, vamos usar a tabela contendo a BLIBLIA SAGRADA – TRADUÇÃO BRASILEIRA que você pode baixar e impostar no seu banco de dados, para entender bem como essas funções trabalham.
Pesquisa de texto básica em 1 ou vários campos.
SELECT * from TraducaoBrasileira WHERE MATCH (text) AGAINST ('Jesus')
vai retornar: (1092 registros no total, Consulta levou 0.0054 segundos.) sendo o versículo mais relevante Mateus 26:50
SELECT * from TraducaoBrasileira WHERE MATCH (book_name,text) AGAINST ('Mateus')
vai retornar: 1074 registros no total, Consulta levou 0.0038 segundos. Sendo o versículo mais relevante Mateus 9:9
PONTUAÇÃO DE RELEVÂNCIA
Retornar a pontuação de relevância, para poder-mos apresentar o quanto relevante os resultados são, basta incluir a expressão na lista de colunas, veja:
SELECT *, MATCH (book_name,text) AGAINST ('Mateus') as Relevancia from TraducaoBrasileira
WHERE MATCH (book_name,text) AGAINST ('Mateus')
Agora, a resposta inclui a coluna relevância, com uma pontuação de 0(não relevante) até 100(totalmente relevante). Para exemplificar, quanto mais palavras colocamos, mais refinado é nosso resultado, por exemplo usando o comando abaixo, temos:
SELECT *, MATCH (book_name,text) AGAINST ('Maria Madalena') as Relevancia from TraducaoBrasileira WHERE MATCH (book_name,text) AGAINST ('Maria Madalena')
46 no total, Consulta levou 0.0047 segundos. e os primeiros 5 registros sendo:
| id | book_name | chapter | verse | text | Relevancia | |
|---|---|---|---|---|---|---|
| 24185 | Mateus | 27 | 56 | entre elas se achavam Maria Madalena, Maria, mãe d… | 27.611881256103516 | |
| 24190 | Mateus | 27 | 61 | Achavam-se ali Maria Madalena e a outra Maria, sen… | 27.611881256103516 | |
| 24196 | Mateus | 28 | 1 | No fim do sábado, ao alvorecer do primeiro dia da … | 27.611881256103516 | |
| 24866 | Marcos | 15 | 40 | Estavam ali também algumas mulheres observando de … | 27.611881256103516 | |
| 24873 | Marcos | 15 | 47 | Maria Madalena e Maria, mãe de José, observaram on… | 27.611881256103516 |
Veja, agora as relevâncias são mais precisas.
SELECT *, MATCH (book_name,text) AGAINST ('"Maria Madalena" mãe') as Relevancia from TraducaoBrasileira WHERE MATCH (book_name,text) AGAINST ('"Maria Madalena" mãe')
Para exigir que uma expressão seja consultada, deve-se colocar a expressão entre aspas duplas.
SELECT *, MATCH (book_name,text) AGAINST ('"Maria Madalena" mãe') as Relevancia from TraducaoBrasileira WHERE MATCH (book_name,text) AGAINST ('"Maria Madalena" mãe') > 25
Podemos também passar um operador booleano, dessa maneira, poderíamos apresentar somente o que tem mais de 25% de relevância.
Calculando a relevância, sem Usa-la nos filtros
SELECT *, MATCH (book_name,text) AGAINST ('"Maria Madalena" mãe de jesus') as Relevancia from TraducaoBrasileira WHERE `book_name`='Mateus' ORDER BY `book_name`,`chapter`,`verse`
Neste exemplo, incluimos todos os versículos do livro de Mateus, e calculamos a relevância deles com “Maria Madalena” mãe de jesus – Veja que mesmo os com relevância 0 são apresentados, já que a relevância não é uma condição de pesquisa, e sim um resultado.
Modo Booleano
Para o modo boleano, devemos alterar a chamada de AGAINST() incluindo operadores para as palavras, veja o exemplo:
SELECT *, MATCH (book_name,text) AGAINST ('-Madalena Maria +"mãe de jesus"' in Boolean MODE) as Relevancia from TraducaoBrasileira WHERE MATCH (book_name,text) AGAINST ('-Madalena Maria +"mãe de jesus"' in Boolean MODE) ORDER BY `Relevancia` DESC
Damos mais importância a presença de mãe de jesus e maria do que a madalena.
Modo Query Expansion
SELECT *, MATCH (book_name,text) AGAINST ('-"Maria Madalena" +"mãe de jesus"') as Relevancia from TraducaoBrasileira WHERE MATCH (book_name,text) AGAINST ('-"Maria Madalena" +"mãe de jesus"') ORDER BY `Relevancia` DESC
Neste modo, mesmo versículos onde MARIA MADALENA não aparece, serão retornados, por exemplo, versiculos onde JESUS aparece, já que por exemplo a palavra JESUS aparece com muita frequência onde aparece a expressão “MARIA MADALENA”.
A expansão da consulta pode ampliar a pesquisa retornando linhas que, de outra forma, não seriam retornadas. Em particular, se uma linha não correspondente contiver palavras que também estão contidas em uma linha correspondente, essa linha não correspondente poderá se tornar uma linha correspondente. Em outras palavras, uma linha não correspondente ainda pode ser retornada, simplesmente porque compartilha outras palavras com uma linha correspondente.
