Introduzione al metodo di ricerca full-text in database MySQL
La differenza
Noi ci concentreremo appunto sull’ultimo metodo di ricerca. Le differenze tra i vari metodi sono numerose, ma per quale motivo usare una ricerca full-text?
La ricerca full-text possiede numerosi vantaggi: per prima cosa è migliore a livello di prestazioni (consente ricerche più 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’utilità 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 è 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. Per chiarire questo concetto provate a pensare di dover cercare in uno stradario di una qualche città “via Milano 54”. Una ricerca condotta in modo classico inizierebbe a sfogliare tutte le carte dello stradario analizzandole fino a trovare la via desiderata. Una ricerca full-text invece per prima cosa va a consultare l’indice. Qui trova la via desiderata corredata dalle informazioni per localizzarla sulle carte. Con tali informazioni estrae poi la carta esatta. Si capisce bene che in questo modo la ricerca è molto più veloce ed efficiente. E’ 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 libero (con “LIKE”).
Precisazioni iniziali
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:
| [sourcecode language=’php’] ALTER TABLE nomeTabella ADD FULLTEXT(primoCampo, secondoCampo, terzoCampo, …); [/sourcecode] |
Se invece si deve creare una nuova tabella l’indice si può creare ugualmente aggiungendo FULLTEXT:
| [sourcecode language=’php’] 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 “,”.
E’ doveroso ricordare inoltre che la ricerca full-text è possibile solo dalla versione 3.23.23 e successive di MySQL.
Query di ricerca
Vediamo come usare la ricerca full-text. Se ad esempio vogliamo cercare la parola “milano” nei campi “indirizzo”, “nome”, “descrizione” della tabella “locali” del nostro database, potremmo usare la seguente query:
| [sourcecode language=’php’] SELECT * FROM locali WHERE MATCH(indirizzo, nome, descrizione) AGAINST(‘milano’) [/sourcecode] |
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
[/sourcecode]
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.
Possiamo anche fare la ricerca di tutti quei risultati che hanno parole che iniziano o terminano per “milano”. Ciò 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”
[/sourcecode]
Ricerca di concetti: il boolean mode
A partire dalla versione 4.0.1 di MySQL e successive è possibile combinare alla ricerca full-text il boolean mode: in parole povere è possibile effettuare ricerche con operatori booleani come si può 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
[/sourcecode]
Come si può notare è 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 +(<roma >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”.
Via : codicefacile