paolo@bimodesign.com | +34 608 61 64 10

DB Relational

        

MySQL e la ricerca FullText

In questo articolo illustero' come MySql rende piu' semplice la gestione di un motore di ricerca, utilizzando gli indici FULLTEXT e le ricerche booleane ordinate per range di importanza. Per rendere la lettura piu' snella e chiara, l'articolo e' suddiviso in punti principali, i quali comprendono tutte le caratteristiche di questa metodologia, lasciando alla vostra curiosita' lo studio dell'algoritmo per il calcolo del range, per il quale indico solo il link alla documentazione ufficiale.

0) Premessa
La ricerca Fulltext si puo' applicare solo se la tabella e' stata creata come MyIsam, i campi interessati devono essere solo di tipo char, varchar e text, escludendo quindi i binary e chiaramente i numerici, ed inoltre va considerato che la modifica dei parametri, i cui dettagli vedremo nel punto 2, comportano il riavvio del db MySql e la ricompilazione degli indici, ovvero una istruzione del tipo

mysql> REPAIR TABLE tbl_name QUICK;

Per curiosita', Oracle prevede una funzionalita' simile, chiamata Oracle Text, la cui spiegazione potrete trovare qui

1) Perche' scegliere la ricerca fulltext e non like?
Come esempio base, possiamo immaginare di dover effettuare la ricerca della parola "stringa" all'interno di articoli di un blog, identificati nel db con due campi, titolo e testo.
La soluzione piu' semplice sarebbe quella di usare questa istruzione SQL-Standard like

SELECT * FROM blog 
WHERE testo LIKE 'ricerca' OR titulo LIKE 'ricerca' 

Questa istruzione, oltre a risultare piuttosto lenta (cfr. gestione degli indici con Like), non permette di gestire il caso in cui la ricerca sia formata da piu' parole, se non strettamente nel loro ordine. Chiaramente si potrebbe modificare opportunamente l'istruzione Like, ma sarebbe piuttosto complesso gestire tutti i possibili casi.
MySql ci viene in aiuto con, appunto, la ricerca fulltext.

2) Creare l'indice FullText
Dopo aver individuato i campi sui quali effettuare la ricerca, dobbiamo creare uno o piu' indici FULLTEXT su tali campi, nel seguente modo.

alter table blog add fulltext(testo);
alter table blog add fulltext(titolo,testo); 

in questo articolo utilizzeremo solo il secondo che consente (ed obbliga) la ricerca per entrambi i cambi. Nel caso fosse necessario stabilire ricerche mirate, come per il solo campo testo, occorre creare tanti indici fulltext quanti necessari.

3) La select di base, complessa ed ordinata per range
3a) select di base
La select di base sara' la seguente, dove occorre indicare l'indice che si vuole utilizzare e quindi la parola da cercare. In questo caso la parola e' una sola.

SELECT * FROM blog 
WHERE MATCH(titolo, testo) AGAINST ('ricerca') 

3b) Ordinare per range
Se vogliamo ordinare per rilevanza il risultato trovato, occorre ripetere la condizione in fase di estrazione dei dati, ovvero

SELECT *, MATCH(titolo, testo) AGAINST ('ricerca') AS rilevanza
FROM blog 
WHERE MATCH(titolo, testo) AGAINST ('ricerca') 
ORDER BY rilevanza DESC

Spiego nei dettagli cosa si intende per rilevanza di una ricerca, nel punto 6 di questo articolo.
3c) Ricerche piu' complesse
FULLTEXT considera qualsiasi seguenza di caratteri (lettere, numeri e _) come una parola. La seguenza puo' contenere apostrofi ('), ma non piu' di uno per ogni stringa di ricerca. Cio' significa che aaa'bbb verra' considerato come una parola, mentre aaa''bbb come due. Gli apostrofi ad inizio e fine della parola, vengono elimitati durante il parser FULLTEXT. Ad esempio, 'aaa'bbb' sara' parseado come aaa'bbb
Inoltre il parser FULLTEXT identifica dove iniziano e finiscono le parole, cercando alcuni delimitatori, come per esempio ' ' (spazio), , (virgola), e . (punto).
Come descritto in seguito nel punto 4), la ricerca viene limitata alla lunghezza di alcune parole. Ad esempio, per default, le parole piu' corte di 4 caratteri, vengono scartate dalla ricerca.
Come descritto nel punto 5), vengono scartate tutte le cosidette stopwords, ovvero quelle di uso comune, che per default sono quelle della lingua inglese, ma come vedremo si possono definirne di proprie.
Premesso tutto cio', se vogliamo comunque estrarre tutte le righe risultanti da una ricerca, dobbiamo usare la modalita' boolean che si applica in due casi

SELECT *, MATCH(titolo, testo) AGAINST ('ricerca') AS rilevanza
FROM blog 
WHERE MATCH(titolo, testo) AGAINST ('ricerca'  IN BOOLEAN MODE) 
ORDER BY rilevanza DESC

in questo caso pero' non verra' ordinato per importanza e vengono estratte tutte le occorrenze trovate, anche quelle che hanno un peso uguale a 0
Nella modalita' boolean si possono applicare i modificatori (+/-), come ad esempio, se vogliamo cercare un contenuto che presenti 'blog' ma non 'libro', scriveremo cosi'

SELECT * 
FROM blog 
WHERE MATCH(titolo, testo) AGAINST ('+blog -libro'  IN BOOLEAN MODE) 

Nota:e' consentito utilizzare la wildcard '*'.

SELECT * 
FROM blog 
WHERE MATCH(titolo, testo) AGAINST ('text*'  IN BOOLEAN MODE) 

4) Parametri di configurazione (e come cambiarli)
Per verificare quali siano i parametri di default del sistema, riferiti alla ricerca fulltext, eseguiamo questa istruzione

SQL> show variables like 'ft%';

ft_boolean_syntax + -><()~*:""&|
ft_max_word_len   84
ft_min_word_len   4
ft_query_expansion_limit 20
ft_stopword_file  (built-in)

La lunghezza minima e massima di una parola nella ricerca, sono identificate dai parametri ft_min_word_len y ft_max_word_len. Il valore minimo e' 4, mentre il massimo dipende dalla versione di MySQL utilizzata.
Per modificare la lunghezza minima di una parola, occorre modificare, nel file /etc/mysql/my.cnf, la sezione "[mysqld]" aggiungendo:

# Full Text Minimum Word Length
ft_min_word_len = 3

effettuate quindi le modifiche, come indicato nella premessa di questo articolo, occorre riavviare MySQL

shell> /etc/init.d/mysql restart
e ed eseguire il rebuilt di tutti gli indici di tipo FULLTEXT.
mysql> REPAIR TABLE tbl_name QUICK;


5) Modificare la lista delle stopwords
Per default il sistema presenta una lista di parole comuni in lingua inglese. Per cambiare la lista delle stopwords occorre cambiare il valore del parametro ft_stopword_file il quale dovra' contenere il path del file della nuova lista o un stringa nulla nel caso si voglia disattivare tale filtro. La lista delle stopwords e' libera, e quindi puo' essere un qualsiasi carattere non alfanumerico, ad eccezione dell'underscore (_) e l'apostrofo ('), che vengono trattati come parte di una parola.
Ad esempio possiamo creare un file di questo tipo, usando i comandi unix ;)

shell> echo "roma"    > /var/lib/mysql/stopwords.txt
shell> echo "italia" >> /var/lib/mysql/stopwords.txt
shell> echo "madrid" >> /var/lib/mysql/stopwords.txt

Quindi, modificare nel file /etc/mysql/my.cnf la sezione "[mysqld]" aggiungendo:

# List Stopword 
ft_stopword_file=/var/lib/mysql/stopwords.txt


6) Come MySql calcola il range
Il peso si calcola a partire di vari parametri, come il numero di parole trovate nel record, il numero di parole uniche nel record, il numero totale di parole nel set di record e il numero di record che contengono la parola cercata.
Ogni parola corretta viene pesata secondo un particolare algoritmo, per il quale rimando alla spiegazione presente in questo link alla documentazione ufficiale MySql
Puo' accadere quindi che una parola presente in molti record risulti con un peso minore (addirittura potrebbe essere 0), perche' ha un valore semantico minore in quel particolare set di dati. Al contrario, se una parola risulta essere "rara", ovvero con poche occorrenze, ricevera' un peso maggiore.
I pesi di tutte le parole indicate nella stringa di ricerca, si combinano per definire il peso del record estratto.
Questa tecnica fu adattata a grandi quantita' di dati e quindi per tabelle piu' piccole, la distribuzione delle parole non riflette automaticamente il loro valore semantica. Quindi questo modello, potrebbe produrre risultati "strani", per esempio se tutti i record presentano la parola "MySQL", una ricerca per tale parola non dara' risultati.
NOTE:
a) Problema se la stringa di ricerca e' formata da un solo valore
Sono stati rilevati errori nel caso in cui la stringa di ricerca sia formata sola parola. Per evitare questo inconveniente, occorre eseguire un controllo sul numero di parole presenti nella ricerca e nel caso che il risultato sia uguale a 1, eseguire l'istruzione like altrimenti MATCH(...) AGAINST(...).
In php il codice potrebbe essere il seguente

$lista_parole = explode(" ",$ricerca); 
$numero = count($lista_parole);
if ($numero==1){
   $select="SELECT * FROM blog WHERE titolo LIKE '%$ricerca%' OR testo LIKE '%$ricerca%'";
}elseif ($numero>1){ 
   $select="SELECT *, MATCH (titolo, testo) AGAINST ('$ricerca' ) AS rilevanza FROM blog WHERE MATCH (titolo, articolo) AGAINST('$ricerca') ORDER BY rilevanza DESC";
  }


b) Comportamento con ricerca con rilevanza oltre il 50%
In questo caso il risultato della ricerca sara' nullo e la parola cercata verra' considerata come una stopword. Questo comportamento e' comodo in caso di grandi quantita' di dati, in quanto e' preferibile che non vengano estratti troppi risultati per ricerche con parole troppo comuni all'interno del range di dati. E' comunque possibile che alcune parole abbiano un peso in un determinato set di dati piuttosto che in un altro, quindi occorre prevedere e valutare questa eventualita' in fase di progettazione del motore di ricerca
Come indicato nel punto 6), questa tecnica, puo' creare inconvenienti con piccole quantita' di dati che, soprattutto, durante la fase di test, determina risultati non sperati. In questa fase e' quindi preferibile inserire piu' record tali da gestire questa eventualita'.
Il limite del 50% e' stabilito dallo schema di pesi scelti.
Per disattivarlo, occorre cambiare questa linea

#define GWS_IN_USE GWS_PROB

#define GWS_IN_USE GWS_FREQ

In questo caso occorre riavviare solo il db MySql e non effetturare il Rebuild degli indici.

Considerazione importante
Modificare questo parametro compromette sensibilmente la capacita' di MySQL nel definire il peso di una ricerca e se veramente si ha la necessita' di cercare parole comuni, e' consigliabile usare il BOOLEAN MODE, che non presenta la limitazione del 50%