MySql Ricerca full-text

October 20th 2009 by Paolo Bindi in: MySql | Tags: MySql

La ricerca full-text possiede numerosi vantaggi: per prima cosa e' migliore a livello di prestazioni (consente ricerche piu' veloci), e poi consente ricerche molto precise ordinando anche i risultati in base al grado di attinenza con la ricerca, in parole povere pesa i risultati.
Per comprendere meglio l'utilita'  della ricerca full-text proviamo a pensare a Google. Quando si effettua una ricerca con Google i risultati vengono mostrati in base al grado di attinenza con la parola cercata. Questo e' proprio quello che la ricerca full-text permette di fare.

La ricerca full-text si basa su indici, il che significa che la ricerca viene svolta duplicemente: innanzitutto MySQL ricerca il termine nell'indice e poi esegue la ricerca vera e propria.
E' stato verificato che su un database di 100MB una ricerca di tipo full-text si dimostra circa 8/10 volte piu' veloce di una ricerca libero (con "LIKE").

Premessa

Bisogna precisare fin dall'inizio che la ricerca full-text e' possibile solo su campi di testo, quindi CHAR, VARCHAR, TEXT ecc... Inoltre e' possibile solo a condizione che sia stato creato un full-text index. Per aggiungere l'indice ad una tabella esistente si puo' fare:

ALTER TABLE nomeTabella ADD FULLTEXT(primoCampo, secondoCampo, terzoCampo, ...);

Se invece si deve creare una nuova tabella l'indice si puo' creare al momento della definizione della tabella:

CREATE TABLE nomeTabella (
      primoCampo INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      secondoCampo VARCHAR(200),
      terzoCampo TEXT,
      FULLTEXT (secondoCampo, terzoCampo)
);

Ovviamente dentro FULLTEXT() bisogna inserire tutti i campi su cui creare l'indice full-text separati da una virgola ",".

Le query di ricerca

Se ad esempio vogliamo cercare la parola "milano" nei campi "indirizzo", "nome", "descrizione" della tabella "locali" del nostro database, potremmo usare la seguente query:

SELECT * FROM locali WHERE MATCH(indirizzo, nome, descrizione) AGAINST('milano')

La sequenza di comandi MATCH() ... AGAINST() restituisce il valore di attinenza del risultato con il testo cercato (numero in floating-point compreso tra 0 e 10). Quindi, considerando che la clausola WHERE per essere verificata deve avere valore booleano "true", se il risultato in questione ha attinenza 0 su 10, ovvero non ha nessuna attinenza, viene scartato. Vengono quindi selezionati solo quei risultati con attinenza maggiore di 0. Questa query tuttavia non ha grande utilita' , in quanto non ordina i risultati secondo la loro attinenza, ne tantomeno restituisce il valore di essa.
Modifichiamo la query in modo che ordini i risultati per attinenza e restituisca il valore della stessa riga per riga:

SELECT *, 
       MATCH(indirizzo, nome, descrizione) AGAINST('milano') AS attinenza 
  FROM locali 
 WHERE MATCH(indirizzo, nome, descrizione) AGAINST('milano') 
 ORDER BY attinenza DESC

In questo modo vengono restituiti tutti i campi e in piu' anche il valore dell'attinenza del risultato rispetto alla parola "milano", e le righe vengono ordinate da quella con piu' attinenza a quella che ne ha meno.

Possiamo anche fare la ricerca di tutti quei risultati che hanno parole che iniziano o terminano per "milano". Cio' si ottiene con il carattere jolly "*", che indica qualsiasi lettera. Quindi:

AGAINST("milano*") // cerca tutti i record con parole che iniziano per "milano"
AGAINST("*milano") // cerca tutti i record con parole che terminano per "milano"

Ricerca in boolean mode

E' possibile combinare possibile combinare alla ricerca full-text il boolean mode: in parole povere e' possibile effettuare ricerche con operatori booleani come si puo' fare con i motori di ricerca.
Facciamo un esempio pratico: se volessimo fare una ricerca come la precedente, cercando per tutti i risultati in cui compaia la parola "milano" ma contemporaneamente non compaia la parola "roma", potremmo usare una query di questo tipo:

SELECT *, 
       MATCH(indirizzo, nome, descrizione) AGAINST('+milano -roma' IN BOOLEAN MODE) AS attinenza 
  FROM locali 
 WHERE MATCH(indirizzo, nome, descrizione) AGAINST('+milano -roma' IN BOOLEAN MODE) 
 ORDER BY attinenza DESC

Come si puo' notare e' stata aggiunta la clausola "IN BOOLEAN MODE" all'interno di AGAINST e sono stati aggiunti anche gli operatori booleani + e - all'interno del testo da cercare.
Vediamo quali sono alcuni dei casi possibili per il testo di ricerca:

* 'milano roma': deve essere presente uno dei due termini
* '+milano +roma': devono essere presenti entrambi i termini
* '+milano roma': deve essere presente "milano" ed eventualmente "roma".
* '+milano -roma': deve essere presente "milano" ma non "roma"
* '+milano +(venezia)': devono essere presenti o "milano" e "roma" o "milano" e "venezia", ma i records con "milano" e "venezia" hanno rilevanza maggiore. ("<" indica minore rilevanza, ">" indica maggiore rilevanza)
* '"milano roma"': deve essere presente l'esatta sequenza "milano roma".

Nel caso che riscontrate qualche errore o se volete contattarmi per qualche consiglio o suggerimento, utilizzate la form della sezione contatti