A ferramenta FullText Search é uma joia rara quando se pretende realizar buscas bem específicas em bases grandes.
Os resultados de buscas LIKE ‘% %’ são lentas e no caso de procuras mais específicas, ficam pobres seus resultados.
Índices FullText são funcionais em tabelas MyIsam mas não InnoDB até a versão 5.6 do MySQL. Na versão 5.6, foi inserida pesquisa FullText em tabela InnoDB. Mas como fazer estas buscas caso meu MySQL seja inferior e não tenha como fazer upgrade?
Existe uma forma paleativa para fazermos estas pesquisas em tabelas InnoDB caso sua versão seja anterior a 5.6. Vamos lá…
Inicialmente, vamos ao cenário:
Base em InnoDB, com série de dados que compõe uma bíblia. O que nos interessa, neste caso, é a tabela que contém os versículos.
CREATE TABLE IF NOT EXISTS `versiculos` (
`id` int(10) unsigned NOT NULL auto_increment,
`livro` tinyint(3) unsigned NOT NULL,
`capitulo` tinyint(3) unsigned NOT NULL,
`versiculo` tinyint(3) unsigned NOT NULL,
`texto` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
Aqui se tentar criar um índice FullText, dará erro, pois a tabela InnoDB nesta versão (abaixo da 5.6) não aceita.
Vamos popular a base…
INSERT INTO `versiculos` VALUES
(1, 1, 1, 1, 'No princípio criou Deus os céus e a terra.'),
(2, 1, 1, 2, 'A terra era sem forma e vazia; e havia trevas sobre a face do abismo, mas o Espírito de Deus pairava sobre a face das águas.'),
(3, 1, 1, 3, 'Disse Deus: haja luz. E houve luz.'),
(4, 1, 1, 4, 'Viu Deus que a luz era boa; e fez separação entre a luz e as trevas.'),
(5, 1, 1, 5, 'E Deus chamou à luz dia, e às trevas noite. E foi a tarde e a manhã, o dia primeiro.'),
(6, 1, 1, 6, 'E disse Deus: haja um firmamento no meio das águas, e haja separação entre águas e águas.'),
(7, 1, 1, 7, 'Fez, pois, Deus o firmamento, e separou as águas que estavam debaixo do firmamento das que estavam por cima do firmamento. E assim foi.'),
(8, 1, 1, 8, 'Chamou Deus ao firmamento céu. E foi a tarde e a manhã, o dia segundo.'),
(9, 1, 1, 9, 'E disse Deus: Ajuntem-se num só lugar as águas que estão debaixo do céu, e apareça o elemento seco. E assim foi.'),
(10, 1, 1, 10, 'Chamou Deus ao elemento seco terra, e ao ajuntamento das águas mares. E viu Deus que isso era bom.');
Agora com estrutura e tabela populada, vamos à mágica…
Já que fica inviável a construção do índice, vamos criar uma tabela espelho em MyISAM e acrescentar uns gatilhos…
CREATE TABLE IF NOT EXISTS `versiculos2` (
`id` int(10) unsigned NOT NULL,
`texto` text NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `texto` (`texto`)
) ENGINE=MyISAM;
Algumas recomendações… Esta tabela espelho, precisa ter somente o objeto de pesquisa e o id da tabela.
Todo o cruzamento será unindo as duas tabelas, e a mágica está aí.
Agora vamos popular a tabela espelho…
INSERT INTO versiculos2 VALUES (SELECT id, texto FROM VERSICULOS);
Aqui pode ter uma pergunta… E se mudarmos dados da tabela “versiculo” (InnoDB) ?
Bom, pra evitar isso, vamos fazer umas triggers.
Trigger de Inserção
delimiter $$
CREATE TRIGGER adiciona_versiculos
AFTER INSERT ON versiculos
FOR EACH ROW
BEGIN
INSERT INTO versiculos2 SET versiculos2.id=new.id, versiculos2.texto=new.texto;
END$$
delimiter ;
Trigger de Deleção
delimiter $$
CREATE TRIGGER deleta_versiculos
AFTER DELETE ON versiculos
FOR EACH ROW
BEGIN
DELETE FROM versiculos2 WHERE versiculos2.id=old.id;
END$$
delimiter ;
Trigger de Atualização
delimiter $$
CREATE TRIGGER atualiza_versiculos
AFTER UPDATE ON versiculos
FOR EACH ROW
BEGIN
UPDATE versiculos2 SET versiculos2.texto=new.texto WHERE versiculos2.id=old.id;
END$$
delimiter ;
Com isso, qualquer mudança na tabela “versiculo” (InnoDB), será replicada para a tabela “versiculo2” (MyISAM) e os textos sempre estarão sincronizados…
Agora, vamos às pesquisas…
Para localizar um texto, vamos realizar a pesquisa cruzando as duas bases…
SELECT
*
FROM
versiculos,versiculos2
WHERE
versiculos.id=versiculos2.id
AND MATCH(versiculos2.texto) AGAINST ('Deus');
Não entrarei em detalhes referentes a pesquisa em si, mas passei a técnica da criação.
Resolução própria… Enjoy…