Full-Text search MySQL ricerca con indici fulltext

by sergiorinaudo.com

Fulltext, che cos’è?

La funzionalità di ricerca FULLTEXT è un particolare tipo di ricerca del Database MySql effettuata usando la sintassi MATCH() … AGAINST(). All’interno di MATCH() andremo ad inserire una lista di campi separati da virgola, in cui andremo a carcare il contenuto di AGAINST().

Ecco subito un esempio pratico:

SELECT *, MATCH(titolo, sottotitolo, descrizione)
AGAINST('html') AS score FROM articoli
WHERE MATCH(titolo, sottotitolo, descrizione)
AGAINST('html') ORDER BY score DESC

Prima di poter utilizzare questa funzionalità però dovremo assicurarci che la tabella che contiene i campi in cui vogliamo andare ad effettuare la ricerca sia di tipo MyISAM. Se la tabella è di un’altro tipo come fare? Modifichiamo il tipo di tabella con questa query:

ALTER TABLE miaTabella  ENGINE = MYISAM

Inoltre, tutti i campi che inseriremo all’interno di MATCH() dovranno essere inseriti come indici FULLTEXT e dovranno essere di tipo CHAR, VARCHAR, or TEXT. Come fare? Con questa query:

ALTER TABLE miaTabella
ADD FULLTEXT(primoCampo, secondoCampo, ...);

Prima di effettuare qualsiasi modifica al vostro database il consiglio è sempre di creare una copia di backup .

Oltre alla stringa di ricerca, AGAINST può contenere al suo interno un modifier tra quelli che MySql rende disponibili:

search_modifier:
{
IN BOOLEAN MODE
| IN NATURAL LANGUAGE MODE
| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
| WITH QUERY EXPANSION
}

Notate che i modifiers IN NATURAL LANGUAGE MODE e IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION sono stati aggiunti nella versione MySQL 5.1.7 e saranno trattati in un prossimo articolo.

Ricerche Full-Text Boolean

MATCH(primoCampo, secondoCampo)
AGAINST('+parolaCheDeveEsserePresente
-parolaCheNonDeveEsserepresente' IN BOOLEAN MODE)

Per effettuare una ricerca in boolean mode bisogna aggiungere all’interno di AGAINST la parola chiave IN BOOLEAN MODE come mostrato nell’esempio.
La peculiarità del boolean mode è la possibilità di utilizzare i seguenti operatori per raffinare pe proprie ricerche:
+: La parola deve esistere in ogni riga.
–: La parola non dev’essere presente. Fate bene attenzione a questo segno. Una stringa di ricerca contenente solo parole precedute dal ‘-’ ritornerà sempre un risultato vuoto. Infatti non è valida la logica ‘ritorna tutte le righe tranne che quelle dove sono presenti i termini contrassegnati dal -’.
*: L’asterisco funge da carattere jolly. Anche l’uso di questo carattere ha bisogno di particolare attenzione soprattutto quando utilizzato con le stopwords ( vedi in fondo a questo articolo per la spiegazione sulle stopwords ). Infatti una query di ricerca ‘+word +stopword*’ ritornerà meno risultati che di una ricerca ‘+word +stopword’ in quanto la prima query richiederà la presenza di ‘stopword*’ mentre la seconda query sarà trasformata in ‘+word’.
( ): le parentesi servono per creare delle espressioni. Si possono includere parentesi all’interno di altre parentesi.
> < : maggiore e minore. Questi operatori servono per aumentare o diminuire il contributo alla rilevanza di una specifica parola. L’operatore > aumenta la rilevanza mentre < la diminuisce. Vedere gli esempi per una maggiore comprensione.
~ : il carattere tilde funge come operatore negativo, in quanto una parola preceduta da esso, se individuata nella riga, contribuirà ad abbassare la rilevanza. Da non confondersi con l’operatore -, in quanto tilde diminuisce la rilevanza ma non esclude la riga dal risultato.
“: doppio apice. Questo operatore serve ad eseguire la query cercando letteralmente quello che è contenuto al suo interno.

ATTENZIONE: la configurazione di default prevede che la ricerca funzioni solo per parole di un minimo di 4 caratteri, per modificare questo comportamento è necessario modificare la chiave di configurazione ‘ft_min_word_len’ in /etc/mysql/my.cf sotto la sezione [msqld] ed impostarla al valore desiderato ( 3 dovrebbe essere sufficiente ). Se la seguente modifica è effettuata successivamente all’inserimento dei dati nella tabella, sarà necessario ricostruire gli indici con la seguente query:

REPAIR TABLE nometabella QUICK;

Esempi

I seguenti esempi dovrebbero chiarire le spiegazioni del paragrafo precedente:

• ‘apple banana’
  Trova delle righe che contengano almeno una di queste parole.
• ‘+apple +juice’
  Trova righe che contengano entrambe le parole.
• ‘+apple macintosh’
  Trova righe che contengano la parola ‘apple’ la cui importanza sale se contengono anche ‘macintosh’.
• ‘+apple -macintosh’
  Trova righe che contengono ‘apple’ E che NON contengono ‘macintosh’.
• ‘+apple ~macintosh’
  Trova righe che contengono “apple”, e che in caso contengano la parola “macintosh”, diminuisce la loro rilevanza rispetto ai risultati che non la contengono. Questo è un metodo meno di impatto della ricerca ‘+apple -macintosh’, in quanto la presenza di “macintosh” fa in modo che la riga non sia affatto nel risultato.
• ‘+apple +(>turnover <strudel )’
  Trova delle righe che contengono “apple” e “turnover”, o “apple” e “strudel” (in qualsiasi ordine), ma da maggiore rilevanza a “apple turnover” piuttosto che “apple strudel”.
• ‘apple*’
  Trova righe che contengono parole come “apple”, “apples”, “applesauce”, or “applet”.
• ‘”some words”‘
  Trova righe che contengono l’esatta frase “some words” (ad esempio, la riga potrebbe contenere “some words of wisdom” ma non “some noise words”). Da tenere presente che il carattere doppio apice “”” che racchiude la frase sono degli operatori che delimitano la frase. Non sono apici che delimitano la frase nel risultato.

Stopwords