Full-Text Search no MySQL

A última versão da engine InnoDB do MySQL permite que trabalhemos de forma bastante eficiente com consultas full text search. Esta novidade foi liberada recentemente pela Oracle na versão 5.6 do novo MySQL. Com isso, agora é possível inserir índices FULLTEXT em tabelas InnoDB para representar conteúdo baseado em texto e, dessa forma, agilizar o desempenho de buscas baseadas em palavras ou frases feitas no banco de dados.

Esta novidade traz uma série de novas possibilidades de uso de tabelas InnoDB em aplicações baseadas em bancos transacionais. Neste contexto, este artigo apresentará algumas das principais características da Full-Text Search no MySQL, apresentando alguns exemplos de uso e discutindo ao final algumas limitações desta funcionalidade na versão atual do MySQL.

Assim como a maioria das facilidades de busca, o InnoDB Full-Text Search é projetado como um índice invertido no qual a string de entrada é dividida em palavras individuais e estas palavras são então armazenadas em uma ou mais tabelas auxiliares. Para cada palavra, uma lista de Document IDs em conjunto com informação sobre o posicionamento das palavras são armazenados. Nós chamamos esta lista contendo IDs + Posicionamento de uma “ilist”. Assim, na tabela de índice, duas importantes colunas são: palavra e ilist. Além disso, o InnoDB FTS suporta busca por proximidade, que era uma característica não presente no Full-Text Search das tabelas MyISAM.

O InnoDB FTS representa cada índice de busca através de um conjunto de seis tabelas. As palavras são divididas entre estas seis tabelas baseadas em seu primeiro caractere. Atualmente, o critério de partição foi definido baseado no conjunto de caracteres presente no latin.

O uso do particionamento ajuda a paralelizar as operações envolvidas nas buscas FTS. Atualmente, o InnoDB apenas paraleliza operações CREATE INDEX, entretanto, a arquitetura atual permitirá que outras operações sejam paralelizadas no futuro.

Vamos agora iniciar um exemplo para entendermos melhor como tudo isso funciona em conjunto.

No MySQL 5.6 você pode examinar metadados do InnoDB (ou seja, as tabelas de sistema do InnoDB) através de tabelas específicas no banco de dados INFORMATION_SCHEMA. Vamos ver agora rapidamente como estas tabelas auxiliares são criadas durante a criação de índices FTS. Como estas tabelas são específicas do InnoDB, você não as encontrará diretamente nos metadados do MySQL. Vamos ver então como acessá-las.

Inicialmente iremos definir a tabela “quotes” que será utilizada em nossos exemplos. Observe a Listagem 1. Das linhas 1 a 7 estamos basicamente criando nossa tabela e indicando que a engine utilizada será a InnoDB. Depois disto, na linha 9 criamos um índice FTS na tabela que acabamos de criar. Por fim, na linha 13, fazemos uma consulta nas tabelas de sistema para observarmos o que ocorreu.

Listagem 1. Criando a tabela quotes

  1 create table quotes
  2  (    id int unsigned auto_increment primary key
  3    , author varchar(64)    
  4    , quote varchar(400)
  5    , source varchar(64)
  6  ) engine=innodb;
  7 
  8 -- Create the fulltext index
  9 create fulltext index idx on quotes(quote);
  10 
  11 -- Now check tables in the InnoDB SYS_TABLES:
  12  
  13 mysql> SELECT table_id, name FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES;

O resultado da consulta apresentada na linha 13 da Listagem 1 é apresentado na Listagem 2.

Listagem 2. Resultado da consulta na tabela de sistema

  table_id     name
  ...         ...
  548         test/FTS_0000000000000223_0000000000000257_INDEX_1
  549         test/FTS_0000000000000223_0000000000000257_INDEX_2
  550         test/FTS_0000000000000223_0000000000000257_INDEX_3
  547         test/quotes
  ...         ...
  19 rows in set (0.01 sec)  ...