Ricerche FULL TEXT con MySQL

Uno dei vantaggi nell’utilizzo dei database per l’allocazione dei dati è quello di poter effettuare delle ricerche all’interno delle informazioni memorizzate. Nel caso particolare delle ricerche testuali, MySQL mette a disposizione un’avanzata metodologia di ricerca chiamata FULL TEXT che permette di ottenere risultati estremamente precisi e raffinati sulla base della pertinenza tra chiave di ricerca e contenuti.

Ricerche FULL TEXT: cosa sono e come funzionano

La prima domanda di chi si affaccia per la prima volta alle ricerche FULL TEXT con MySQL riguarda la differenza con l’operatore LIKE. Vediamo quindi come funzionano le ricerche FULL TEXT facendo un primo e veloce paragone con questa sintassi ben più conosciuta.

L’operatore LIKE, utilizzatissimo per le interrogazioni di ricerca testuale, si limita esclusivamente a reperire all’interno dei dati allocati la stringa passata come parametro di input; la metodologia FULL TEXT invece, effettua un confronto tra i diversi contenuti restituendo prioritariamente quello dotato dello SCORE (livello di pertinenza) più elevato.

Si tratta, in pratica, di una metodica di ricerca più raffinata e in grado di restituire risultati con una migliore pertinenza. Questo tipo di ricerca è molto simile a quella utilizzata da motori di ricerca per valutare la pertinenza di una chiave di ricerca all’interno del contenuto di una pagina web.

La possibilità di effettuare ricerche FULL TEXT in un database MySQL è stata introdotta per la prima volta con la versione 3.23.23. del DBMS, ma soltanto a partire dalla 4.1 questa opzione è stata implementata del tutto e resa utilizzabile senza particolari interventi di amministrazione.

Alla base dei comportamenti di MySQL nelle ricerche FULL TEXT vi sono 5 variabili (personalizzabili attraverso il file di configurazione del DBMS):

ft_boolean_syntax: operatore per le ricerche di tipo logico/booleano.
ft_max_word_len: dimensione massima delle singole stringhe indicizzate (valore predefinito a 254 char).
ft_min_word_len: dimensione minima delle singole stringhe indicizzate (valore predefinito 4 char).
ft_max_word_len_for_sort: dimensione massima delle stringhe indicizzabili nelle operazioni di ricostruzione dell’indice.
ft_stopword_file: riferimento al file che contiene i nomi e i tipi di stringhe che non devono essere sottoposti ad indicizzazione.

Potremo modificare il file contenente le stop words (di default ft_static.c) per definire la nostra lista di stringhe escluse dall’indicizzazione:

#ifdef COMPILE_STOPWORDS_IN
 "parola",
 "altra parola",
#endif

E’ bene ricordare che gli interventi sulle variabili possono comportare la necessità di effettuare la ricostruzione degli indici FULL TEXT: a questo scopo sarà possibile eliminare l’indice per poi ricrearlo con il comando REPAIR TABLE seguito dal nome della tabella interessata.

Creazione di un indice FULL TEXT in una tabella MySQL

Le ricerche FULL TEXT sono disponibili in MySQL sia per le tabelle MyIsam che INNODB. Mediante la creazione di indici, i dati vengono suddivisi in record composti da campi a lunghezza fissa; ogni record è immagazzinato in sequenza e ai record si riferisce un set di tabelle hash (denominate appunto indici) destinate a contenere i puntatori ai record; in questo modo ogni record verà estratto senza dover cercare in tutto l’insieme di dati.

Esistono due procedure per creare un indice FULLTEXT: la prima è quella di definire l’indice al momento della creazione della tabella; la seconda è quella di alterare una tabella già creata inserendo il nuovo indice in un secondo momento.

Vediamo il primo caso, creeremo la classica tabella per un news manager completa di titolo, descrizione, autore e data delle varie news:

CREATE TABLE news(
id INT NOT NULL AUTO_INCREMENT,
autore VARCHAR(100) NULL,
titolo VARCHAR(250) NULL,
descrizione TEXT NULL,
data DATE NOT NULL,
PRIMARY KEY(id),
FULLTEXT INDEX news_full(titolo, descrizione)
);

Come si avrà modo di notare, l’indice FULLTEXT è stato posto soltanto per i campi relativi al titolo e alla descrizione delle news, plausibilmente i due campi interessati all’azione di ricerca (naturalmente nulla ci vieta di estendere l’indice anche ad altri campi, se per esempio il nostro database è destinato a conservare i record di un blog potremmo aver interesse a svolgere una ricerca anche tra i commenti degli utenti ai diversi articoli).

Se invece la nostra tabella dovesse essere preesistente e priva di indici FULLTEXT, allora potremo sempre aggiungere questi ultimi attraverso due semplici comandi SQL:

ALTER TABLE news
ADD FULLTEXT news_full(titolo, descrizione)

ALTER TABLE inizializza la procedura di alterazione della tabella in oggetto, mentre ADD FULLTEXT crea un indice FULL TEXT con il nome arbitrario indicato (nell’esempio "news_full") sui campi che gli vengono passati come parametri.

Operatori e ricerche FULL TEXT in MySQL

Esistono due metodi per operare una ricerca FULL TEXT, uno basato sulla ricerca semplice, l’altro basato sulla ricerca logico-booleana. In entrambi i casi la procedura di ricerca si basa su determinate istruzioni SQL:

MATCH (campi) AGAINST (chiave di ricerca)

Vediamo un esempio:

SELECT nome_campo, MATCH (campi indicizzati) 
AGAINST ('chiave di ricerca')
FROM nome_tabella

Una volta eseguita l’istruzione verrà restituito un recordset costruito sul principio della corrispondenza, quindi tutti i record verranno discriminati sulla base di un livello di corrispondenza detto SCORE espresso in valore decimale; più alto sarà lo SCORE maggiore sarà la pertinenza del risultato, i risultati ritenuti privi di corrispondenza verranno associati ad uno SCORE pari a zero.

L’istruzione presentata non permette però di filtrare i risultati ottenuti, infatti le ricerche FULL TEXT in realtà non ricercano stringhe ma pertinenze tra chiavi e contenuti memorizzati; da questo punto di vista sono quindi interessanti tutti i risultati anche quelli che non presentano corrispondenze, ciò che conta è la "classifica finale".

Saranno però sufficienti alcuni accorgimenti sintattici per ovviare alla problematica descritta e ottenere output filtrati da cui sono stati rimossi i record non rilevanti ai fini della ricerca:

SELECT nome_campo, MATCH (campi_indicizzati) 
AGAINST ('chiave di ricerca')
FROM nome_tabella
WHERE MATCH (campi_indicizzati) AGAINST ('chiave di ricerca')

In questo caso tutti i risultati pari a zero verranno classificati come FALSE in senso logico e non saranno presenti all’interno del risultato finale.

Per "raffinare" le nostre ricerche, abbiamo a disposizione una serie di operatori che potranno essere utilizzati per operare ulteriori filtraggi e restringere gli output soltanto ai record dotati di SCORE più elavati:

*: consente di omettere un certo numero di caratteri all’interno della chiave di ricerca (carattere jolly).
~: diminuisce lo SCORE di una determinata chiave senza però escluderla dalla ricerca (tilde).
” ”: consente l’introduzione di caratteri non alfanumerici.
(): definisce un’espressione.
&: congiunzione di tipo logico (AND).
|: operatore logico OR.
+: indica che una ricerca è valida solo se la chiave indicata è presente su tutti i record.
–: indica che una ricerca è valida solo se la chiave indicata non è presente su tutti i record.
< e >: diminuisce o aumenta il livello di corrispondenza di una determinata chiave.

Ricerche FULL TEXT e applicazioni pratiche

Per presentare un esmpio pratico sull’impiego delle ricerche FULL TEXT inseriremo del contenuto all’interno della nostra tabella "news":

INSERT INTO news(id,data,autore,titolo,descrizione) 
VALUES (
'',
'2006-06-21',
'Cicerone',
'Lorem ipsum' , 
'Nunc ultricies pretium orci. Integer tortor.
Nam varius quam. Curabitur quis felis nec 
mauris rhoncus fringilla. Fusce sed magna 
eu neque imperdiet gravida.'
);

Quello proposto è solo un esempio, naturalmente il lettore dovrà sulla base di questo aggiungere più contenuti alla tabella in modo da rendere possibili i confronti e la generazione dei livelli di corrispondenza.

Di seguito riportiamo il codice necessario per operare all’interno dei nostri dati una semplice ricerca basata su una chiave:

SELECT `titolo`, MATCH (`titolo`,`descrizione`) 
AGAINST ('tortor') AS rank
FROM news ORDER BY `rank` DESC

Lo scopo dell’istruzione appena descritta, è in pratica quello di ricercare all’interno dei contenuti indicizzati la chiave "tortor" e di visualizzare in output i titoli e gli SCORE ottenuti. Da notare l’utilizzo del costrutto AS che ci permette di creare un output virtuale chimato "rank" in sostituzione del meno leggile "MATCH (`titolo`,`descrizione`) AGAINST (‘tortor’)" che poi non è altro se non l’elenco degli SCORE.

Passiamo ora ad un esempio pratico di ricerca FULL TEXT più raffinata in cui vengono filtrati i risultati:

SELECT titolo, MATCH (`titolo`,`descrizione`) 
AGAINST ('tortor') AS rank
FROM news
WHERE MATCH (`titolo`,`descrizione`) AGAINST ('tortor')
ORDER BY rank DESC

L’istruzione descritta sopra contiene due passaggi fondamentali: innanzitutto viene operata un SELECT dei record corrispondenti al campo "titolo" sulla base di un confronto (AGAINST) col parametro chiave di input; in secondo luogo si specifica che il confronto dovrà riguardare soltanto i record dei campi indicizzati restituisce un risultato diverso da FALSE. Per cui in output avremo una "classifica" contenente soltanto i record dotati di uno SCORE diverso da zero.

Per filtrare ulteriormente gli esiti delle nostre ricerche potremo anche utilizzare più parametri e operare confronti in senso booleano:

SELECT titolo, MATCH (`titolo`,`descrizione`) 
AGAINST ('tortor magna' IN BOOLEAN MODE) AS rank
FROM news 
WHERE MATCH (`titolo`,`descrizione`) 
AGAINST ('tortor magna' IN BOOLEAN MODE)
ORDER BY rank DESC

In questo caso, abbiamo deciso di operare una ricerca limitata ai record in cui vengono associate le stringhe "tortor" e "magna". Da notare l’utilizzo dell’istruzione IN BOOLEAN MODE con il quale indichiamo che la ricerca deve essere operata in termini logici (0/1).

Confronto tra LIKE e FULL TEXT: esempi di query

All’inizio di questa lezione sulle ricerche FULL TEXT abbiamo detto che l’operatore LIKE si limita a parsare determinati contenuti alla ricerca delle chiave che le viene passata come parametro; quindi se trova nei nostri record la stringa cercata semplicemente li restituisce in output.

Questo vuol dire che con LIKE non avremo la possibilità di ottenere ricerche basate su confronti se non attraverso un largo utilizzo di operatori come WHERE, AND e OR.

Per chiarire il concetto appena espresso mostreremo al lettore due esempi, uno basato su LIKE e l’altro sull’indicizzazione FULL TEXT; entrambi mostreranno istruzioni create per lo stesso scopo finale, la ricerca all’interno di un contenuto di due parole.

Ecco l’esempio basato su LIKE:

SELECT titolo, descrizione
FROM news
WHERE (titolo LIKE '%tortor%' OR titolo LIKE '%magna%' ) 
OR (descrizione LIKE '%tortor%' OR descrizione LIKE '%magna%')

Di seguito, presentiamo invece l’esempio basato sulla ricerca FULL TEXT:

SELECT titolo, descrizione
FROM news
WHERE MATCH (titolo, descrizione) AGAINST ('tortor magna')

Come si potrà facilmente notare, a livello sintattico la seconda istruzione è sicuramente più semplice da digitare, inoltre, quest’ultima può ulteriormente essere raffinata sulla base dei livelli di corrispondenza, cosa che non è invece prevista per LIKE.

Se proprio siamo alla ricerca di un difetto nella procedura di ricerca FULL TEXT potremo dire che quest’ultima, a differenza di LIKE, non è immediatamente disponibile; dato che si basa sulla ricerca di contenuti indicizzati necessita di termini di paragone per la generazione degli SCORE, avremo quindi la necessità di operare su un certo numero di record prima di poter operare delle ricerche.