Introdução ao MySQL Full Text Search
Hoje, exploraremos o Full Text Search. É muito provável que você já tenha enfrentado a necessidade de implementar uma busca em um sistema que desenvolveu. Geralmente, recorremos a uma cláusula SQL como “campo LIKE %busca%
” para esse fim. Embora esse método funcione bem para termos isolados e buscas simples, quando precisamos de uma abordagem mais flexível, ele pode não ser suficiente. Por exemplo, se procurarmos por “dias semana” e o campo contiver “dias da semana”, uma pesquisa com LIKE
não encontrará correspondências, assim como para termos invertidos, como “semana dias”. Para contornar isso, podemos empregar o Full Text Search.
O Full-Text Search é uma técnica que possibilita a busca em um documento mesmo quando não corresponde exatamente ao termo procurado, ignorando conectores como “e”, “ou”, pontuações, espaços em branco e termos invertidos, além de proporcionar a ordenação por relevância dos termos pesquisados. A maioria dos bancos de dados possui mecanismos para realizar essa busca avançada. No MySQL, por exemplo, são utilizados índices para esse processo, armazenando a coluna a ser pesquisada de maneira mais inteligente. Portanto, antes de iniciar a busca, é necessário criar um índice.
Criando um índice Full Text Search
É possível criar um índice FULLTEXT para uma coluna do tipo CHAR
, VARCHAR
e TEXT
durante a criação da tabela. Para isso, utilizamos a sintaxe FULLTEXT(coluna1 [, coluna2 ...])
ao criar o índice.
CREATE TABLE posts(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
body TEXT NOT NULL,
FULLTEXT (title),
FULLTEXT (body)
);
Também é possível criar um índice por meio da instrução ALTER TABLE
, conforme demonstrado a seguir:
ALTER TABLE posts ADD FULLTEXT(title);
ALTER TABLE posts ADD FULLTEXT(body);
Ou ainda, criando um índice por meio da instrução CREATE FULLTEXT INDEX
.
CREATE FULLTEXT INDEX index_fulltext ON posts(title);
Se não houver mais necessidade de um índice, é possível removê-lo utilizando a instrução DROP INDEX
.
ALTER TABLE posts DROP INDEX body;
Vale ressaltar que, se desejarmos conduzir buscas full-text em campos distintos, devemos criar índices separados. No entanto, se a intenção for realizar buscas em várias colunas simultaneamente, podemos criar um índice para ambas. Por exemplo: FULLTEXT(title, body)
.
Realizando Buscas
Antes de realizar alguma busca, vamos popular a tabela que foi criada com os índices.
-- insert.sql
-- Gerado por ChatGPT
INSERT INTO posts(title, body) VALUES
('Explorando as Maravilhas do Full Text Search em SQL Server', 'Este post apresenta casos práticos de busca de texto completo no SQL Server, revelando como otimizar consultas e aprimorar a experiência de pesquisa em bancos de dados.'),
('Full Text Search no MongoDB: Desvendando os Segredos da Busca Textual', 'Descubra como o MongoDB lida com buscas de texto completo. Este guia prático explora estratégias eficazes para implementar pesquisas avançadas em documentos JSON.'),
('PostgreSQL Full Text Search: Estratégias para Consultas Poderosas','Aprofunde-se no PostgreSQL Full Text Search. Este post fornece insights sobre configurações, operadores e táticas para melhorar a precisão das consultas de texto.'),
('MySQL: Estratégias de Otimização Avançada', 'Este post explora técnicas avançadas de otimização para o MySQL, fornecendo insights valiosos para melhorar o desempenho do seu banco de dados.'),
('Full Text Search no Elasticsearch: Potencializando a Busca em Tempo Real','Explore a magia do Elasticsearch para busca de texto completo em tempo real. Saiba como indexar e pesquisar grandes volumes de dados de maneira eficiente.'),
('Implementando Full Text Search no SQLite: Dicas e Truques','Descubra como incorporar eficientemente a busca de texto completo no SQLite. Este guia oferece estratégias e truques para otimizar consultas e melhorar a performance.'),
('MySQL Full Text Search: Estratégias Avançadas para Desempenho Otimizado','Aprenda técnicas avançadas para aprimorar a busca de texto completo no MySQL. Este post explora índices, configurações e otimizações para consultas mais rápidas.'),
('Full Text Search no Apache Solr: Potencializando Pesquisas em Grande Escala','Descubra como o Apache Solr revoluciona pesquisas em grande escala com busca de texto completo. Este guia prático oferece dicas valiosas para indexação eficiente.'),
('MySQL Avançado: Otimizando Consultas Complexas para Pesquisa Eficiente', 'Este guia avançado explora técnicas específicas para otimizar consultas complexas no MySQL, oferecendo soluções eficazes para aprimorar a pesquisa em cenários desafiadores.'),
('Desafios e Soluções: Full Text Search em Sistemas Distribuídos','Explore os desafios enfrentados ao implementar busca de texto completo em ambientes distribuídos. Este post oferece soluções para garantir resultados precisos e eficientes.'),
('Full Text Search no Neo4j: Transformando Relacionamentos em Conhecimento','Aprofunde-se no Full Text Search no Neo4j, explorando como essa poderosa base de dados de grafos lida com consultas de texto para revelar insights valiosos.'),
('GraphQL Full Text Search: Integrando Pesquisas Textuais em Sua API','Descubra como incorporar busca de texto completo em APIs GraphQL. Este post explora estratégias para integrar eficientemente capacidades de busca textual em suas consultas GraphQL.'),
('MySQL e Busca Textual: Estratégias Integradas para Otimização', 'Explore as melhores práticas para implementar a busca textual no MySQL, destacando estratégias integradas para otimização e eficiência nas consultas.');
Para efetuar uma pesquisa, temos à disposição dois modos: a busca em “linguagem natural” e a busca “Boolean“. Ambas fazem uso das funções MATCH
e AGAINST
.
MATCH (col1,col2,...) AGAINST (expr [search_modifier])
Busca NATURAL LANGUAGE
Uma pesquisa em linguagem natural interpreta o texto de busca como uma frase na linguagem humana comum. Não há operadores naturais, exceto as aspas duplas (“). A lista de palavras de parada (stopwords), que são palavras sem valor semântico e são ignoradas na pesquisa, é aplicada.
Para realizar a pesquisa, empregamos a função MATCH
com a coluna (ou colunas) na qual desejamos realizar a busca, junto com a função AGAINST
e os termos de pesquisa entre aspas. Em seguida, especificamos o modo de pesquisa (caso nenhum seja informado, o modo NATURAL LANGUAGE
é utilizado).
SELECT * FROM posts WHERE MATCH(title) AGAINST ("full-text dicas" IN NATURAL LANGUAGE MODE);
Ao efetuar a busca, será atribuído a cada registro um score de correspondência, que será utilizado para determinar a relevância do registro em relação a pesquisa. Ao utilizarmos a combinação MATCH...AGAINST
na cláusula WHERE, os resultados serão ordenados conforme o score. Podemos visualizar o score calculado incluindo a expressão MATCH...AGAINST
também na lista de campos.
SELECT
title,
MATCH(title) AGAINST ("busca Full-Text") as SCORE
FROM posts WHERE MATCH(title) AGAINST ("busca Full-Text");
É importante destacar que podemos fornecer apenas colunas que possuam um índice FULLTEXT à função MATCH
! Caso contrário ocorrerá um erro.
Busca BOOLEAN MODE
Outra abordagem em Full Text Search é a BOOLEAN, na qual certos caracteres têm significados especiais quando estão no início ou no final de uma palavra durante uma pesquisa. Por exemplo, os caracteres + e – no início de uma palavra indicam se a palavra deve estar presente ou ausente nos resultados da busca.
Para conduzir uma pesquisa no modo BOOLEAN, é necessário especificar IN BOOLEAN MODE
na função AGAINST
.
SELECT * FROM posts WHERE MATCH(body) AGAINST ("+guia -apache" IN BOOLEAN MODE);
Na consulta acima, estamos procurando registros que contenham a palavra “guia” e que não contenham a palavra “apache”.
Vamos examinar a lista de caracteres especiais.
Operador | Descrição |
+ | Inclui, a palavra deve estar presente. |
– | Exclui, a palavra não deve estar presente. |
> | Inclui, e aumenta o valor de relevância da palavra. |
< | Inclui, e diminue o valor de relevância da palavra. |
() | Agrupa palavra em subexpressões(permitindo elas serem incluidas, excluidas, rankeadas e assim por diante como um grupo). |
~ | Nega o valor de relevância da palavra. |
* | Coringa ao final da palavra. |
“” | Defina uma frase(ao contrário de uma lista de palavras individuais, a frase inteira deve coincidir para inclusão ou exclusão. |
Vamos ver alguns exemplos de usos dos operadores.
Para buscar linha que contenham uma das palavras “mysql” ou “busca“:
SELECT * FROM posts WHERE MATCH(title) AGAINST ('mysql busca' IN BOOLEAN MODE);
Para busca por uma linha que contenha as duas palavras: “mysql” e “busca“:
SELECT * FROM posts WHERE MATCH(title) AGAINST ('+mysql +busca' IN BOOLEAN MODE);
Para encontrar uma linha que contenha a palavra “mysql” e classificar os registros que contenham a palavra “busca” com maior relevância:
SELECT * FROM posts WHERE MATCH(title) AGAINST ('+mysql busca' IN BOOLEAN MODE);
Para buscar uma linha que contenha a palavra “busca” mas não a palavra “mysql“:
SELECT * FROM posts WHERE MATCH(title) AGAINST ('+busca -mysql' IN BOOLEAN MODE);
Para buscar uma linha que contenha a palavra “mysql” e classificá-la com menor relevância se tiver a palavra “busca“:
SELECT * FROM posts WHERE MATCH(title) AGAINST ('+mysql ~busca' IN BOOLEAN MODE);
Para procurar uma linha que contenha as palavras “mysql” e “busca“, ou “mysql” e “pesquisa” em qualquer ordem, garantindo que a linha que contém “mysql busca” seja classificada com maior relevância do que “mysql pesquisa“:
SELECT * FROM posts WHERE MATCH(title) AGAINST ('+mysql +(>busca <pesquisa)' IN BOOLEAN MODE);
Para buscar uma linha que contenha palavras iniciando com “my“, como “mysql” e “mydb“:
SELECT * FROM posts WHERE MATCH(title) AGAINST ('my*' IN BOOLEAN MODE);
Para pesquisar uma linha que contenha exatamente um texto, coloque-o entre aspas:
SELECT * FROM posts WHERE MATCH(body) AGAINST ('"busca de texto"' IN BOOLEAN MODE);
Query Expansion
Às vezes, o termo de busca é muito curto, e o usuário pode não ter uma ideia clara do que está procurando. Nestes casos, a Query Expansion pode ser uma abordagem interessante. Essa técnica opera em duas etapas: inicialmente, realiza uma busca convencional para encontrar correspondências normais e analisar os resultados mais relevantes. Em seguida, identifica palavras relacionadas para incluí-las na próxima busca, ampliando assim a abrangência da pesquisa.
Para realizar uma busca com Query Expansion adicionamos WITH QUERY EXPANSION
na função AGANIST
SELECT * FROM posts WHERE MATCH(body) AGAINST ('mysql' WITH QUERY EXPANSION);
Bom era isso, uma pequena introdução a Full Text Search no MySQL, para mais detalhes não deixe de conferir a doumentação. T++