Ricerca Full-Text in un database MySQL

Come la maggior parte dei programmatori in Php sanno, è stato verificato, che su un database di 100MB, una ricerca di tipo full-text si dimostra circa 8/10 volte più veloce di una ricerca libera (con “LIKE“).

Perchè usare una ricerca full-text?

La ricerca full-text possiede numerosi vantaggi: per prima cosa è migliore a livello di prestazioni infatti consente ricerche più veloci, e molto precise ordinando anche i risultati in base al grado di attinenza con la ricerca, in parole povere oltre a cercare i risultati, li valuta. 

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 è proprio quello che la ricerca full-text permette di fare.

Bisogna precisare fin dall’inizio che la ricerca full-text è possibile solo su campi di testo, quindi CHAR, VARCHAR, TEXT ecc… Inoltre è possibile solo a condizione che sia stato creato un full-text index. L’indice full-text può essere creato al momento della creazione della tabella o anche su una tabella esistente. Per aggiungere l’indice ad una tabella esistente si può fare:

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

Se invece si deve creare una nuova tabella l’indice si può creare ugualmente aggiungendo FULLTEXT:

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

Come avrete capito dentro FULLTEXT() bisogna inserire tutti i campi su cui creare l’indice full-text separati da una virgola “,”.

Ecco una quary di esempio:

Vediamo come usare la ricerca full-text, se ad esempio vogliamo cercare la parola “torino” nei campi “indirizzo”, “nome”, “descrizione” della tabella “uteni” del nostro database:

SELECT * FROM utenti WHERE MATCH(indirizzo, nome, descrizione) AGAINST('torino')

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 utilità, in quanto non ordina i risultati secondo la loro attinenza, nè tantomeno restituisce il valore di essa.

Potremmo modificare 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 più anche il valore dell’attinenza del risultato rispetto alla parola “milano”, e le righe vengono ordinate da quella con più attinenza a quella che ne ha meno.

Vediamo ora le clausole possibili da inserire in AGAINST()

• ‘Julius Design’: deve essere presente uno dei due termini
• ‘+Julius +Design’: devono essere presenti entrambi i termini
• ‘+Julius Design’: deve essere presente “Julius” ed eventualmente “Design”
• ‘+Julius -Design’: deve essere presente “Julius” ma non “Design”
• ‘+Julius +(<Design >Flash)’: devono essere presenti o “Julius” e “Design” o “Julius” e “Flash”, ma i records con “Julius” e “Flash” hanno rilevanza maggiore.
  (”<” indica minore rilevanza, “>” indica maggiore rilevanza)
• ‘”Julius Design”‘: deve essere presente l’esatta sequenza “Julius Design”
• ‘”*Julius”‘: deve iniziare per “Julius”
• ‘”Julius*”‘: deve finire per “Julius”

Buona ricerca

Cinema, Database, Design, HTML, PHP, Sviluppo, Tutorial