Fulltext Search - Buscando string incompletas

>
Até pensei em usar um LIKE, mas essa clausula é bem lenta ne?! não posso comprometer a base.

Tenho cerca de 200 registros no banco.

200 mesmo ? não são 200.000 ? se for 200 , não vejo problemas em usar um LIKE, dependendo do que você peneirar na sua busca, até com 200.000 não daria muito problema nem seria muito lento, agora se eu não me engano, no arquivo de configurações do mysql, o full text index é limitado para buscas das quais contém 1 ou menos palavras, ou seja ... nunca vai casar.

Confesso que não entendo alguns dos comandos utilizados, mas também não é segredo que não sou o cara do SQL (esse é o Andrey :P)

Mas dando uma olhada no Oráculo Jr. seu problema não estaria porque seu AGAINST não define, pelo menos pelo apresentado, bordas de delimitação associadas ao $q?

[EDIT]

Não tenho certeza se o \b convencional seria o mais correto. Se não o for, fica essa segunda dica a respeito de [[:<:]] e [[:>:]] como delimitadores.

Cara 200 registros é uma merreca, não vejo porque NÃO utilizar o LIKE.

Desculpa a demora, é que o LIKE não resolve o problema como um todo, e neste caso se há uma forma correta de usar o MATCH, é bem melhor usar ele.

São sim, cerca de apenas 200 registros.

O próximo passo desta busca, seria trabalhar com similaridades.

Pensei em gravar uma nova coluna com o soundex de cada palavra, e então bater o search com o against neste soundex.

exatamente o q você quer fazer?

só procurar por vir, loja virtual e loja vir?

parece bem simples...

poderia postar a query sem o $q ?

ou seja com o valor de $q literal?

oi @giesta, o objetivo final é buscar similaridades tb.

Podemos esquecer o "loja vir", e o "vir".

Quero chegar em:

"loaa virtoal"... coisas assim..

ou

cloud -> cloudi, clod...

mas não sei como trabalhar com o SOUNDEX() corretamente.

Eu preciso mesmo gravar uma coluna com o soundex de cada palavra ? ou há como fazer o MySQL fazer isso ? e depois procurar ?

O mysql calcular o soundex do campo ? se você tem o campo com a palavra que será aplicado o SOUNDEX, facilita, ou apenas reserve um campo para achar essas similaridades fácil ...

mysql> create schema will ;
Query OK, 1 row affected (0.02 sec)

mysql> use will ;
Database changed

mysql> create table words (
 -> word text not null
 -> ) ;

mysql> insert into words values ( 'cloud' ) , ( 'loja virtual' ) ;
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from words where word sounds like 'cloudi' ;
+-------+
| word |
+-------+
| cloud |
+-------+
1 row in set (0.00 sec)

mysql> select * from words where word sounds like 'clod' ;
+-------+
| word |
+-------+
| cloud |
+-------+
1 row in set (0.00 sec)

mysql> select * from words where soundex( word ) = soundex( 'cloudi' ) ;
+-------+
| word |
+-------+
| cloud |
+-------+
1 row in set (0.01 sec)

Nesse campo reservado, você poderia já inserir o valor do soundex nele, e depois apenas fazer uma busca equacionada (=) com o calculo do soundex da palavra pesquisada (daria na mesma), só evitaria o calculo on the fly do soundex na consulta.

• Obs:, não usei o full text index no exemplo acima, isso fica por sua conta, o exemplo acima só foi pra demonstrar o uso do soundex direto na sql.

é que a coluna contem:

"um texto qualquer sobre cloud, tipo esse"

e a pesquisa seria:

"cloudi"

e teria que retornar. Expliquei melhor agora ?

Bom, na tabela words, eu tenho

+-----------------------------------------------------------------+----+
| word | id |
+-----------------------------------------------------------------+----+
| eu aqui falando sobre cloud na locaweb | 4 |
| fizemos uma palestra sobre loja virtual em algum canto do mundo | 5 |
| um texto grande aqui nessa coluna sera procurado | 6 |
+-----------------------------------------------------------------+----+

Um código que fiz para criar uma tabela temporária com o soundex de cada palavra em words.

delimiter //

create function split(x text, p int) returns text
return replace(substring(substring_index(x, ' ', p), length(substring_index(x, ' ', p -1)) +1), ' ', '');

delimiter //
create procedure soundexSearch(in search varchar(90))
begin

 declare done boolean default false;
 declare c int(11);
 declare k int(11);
 declare wordID int(11);
 declare s text;
 declare cur cursor for select word, id from words;
 declare continue handler for not found set done = true;

 drop temporary table if exists rows;
 create temporary table if not exists rows(
 sound varchar(255) not null,
 wid int(11) not null
 );
 open cur ;
 cl: loop
 fetch cur into s, wordID;
 set k = 0;
 if done then
 leave cl;
 end if;
 select sum(length(s) - length(replace(s, ' ', '')) + 1) into c;
 l: loop
 set k = k + 1;
 if k = c then
 leave l;
 end if;
 insert into rows(sound, wid) values (soundex(split(s, k)), wordID);
 end loop l;
 end loop;
 close cur ;
 select words.* from rows inner join words on rows.wid = words.id where sound = soundex(search) group by wid;

Vai retornar:

+---------+--------+
| E000 | 4 |
| A200 | 4 |
| F453 | 4 |
| S160 | 4 |
| C430 | 4 |
| N000 | 4 |
| F252 | 5 |
| U500 | 5 |
| P4236 | 5 |
| S160 | 5 |
| L200 | 5 |
| V634 | 5 |
| E500 | 5 |
| A425 | 5 |
| C530 | 5 |
| D000 | 5 |
| U500 | 6 |
| T230 | 6 |
| G653 | 6 |
| A200 | 6 |
| N200 | 6 |
| C450 | 6 |
| S600 | 6 |
+---------+--------+

Agora no select lá em baixo da procedure, você faz o calculo do soundex do valor de 'search' e procura por ele

select * from rows where soundex(search) = soundex;

Você vai ter o wordID que é o id do texto que essa palavra tá na tabela words, daí você pode depois selecionar a linha que a palavra casa mais vezes e retornar, porque pode acontecer de retornar mais de 1 linha com diferentes ID's.

E ah, já ia me esquecendo:

call soundexSearch('sua busca aqui ...');

 

Pra chamar é assim, não tem como acoplar procedure na select, por isso o select com os dados da tabela é feito dentro da procedure.

O SQL para pegar o texto na tabela words fica mais ou menos assim

select words.* from rows inner join words on rows.wid = words.id where sound = soundex(search) group by wid;

Trocando na procedure ... testei aqui:

mysql> select * from words ;
+-----------------------------------------------------------------+----+
| word | id |
+-----------------------------------------------------------------+----+
| eu aqui falando sobre cloud na locaweb | 4 |
| fizemos uma palestra sobre loja virtual em algum canto do mundo | 5 |
| um texto grande aqui nessa coluna sera procurado | 6 |
+-----------------------------------------------------------------+----+
3 rows in set (0.00 sec)

mysql> call soundexSearch('cloudi');
+----------------------------------------+----+
| word | id |
+----------------------------------------+----+
| eu aqui falando sobre cloud na locaweb | 4 |
+----------------------------------------+----+
1 row in set (0.46 sec)
Query OK, 0 rows affected (0.47 sec)

mysql> call soundexSearch('grandi');
+--------------------------------------------------+----+
| word | id |
+--------------------------------------------------+----+
| um texto grande aqui nessa coluna sera procurado | 6 |
+--------------------------------------------------+----+
1 row in set (0.49 sec)

É mais ou menos isso que você queria ?!