Creare un motore di ricerca FULLTEXT

Gli argomenti di questa pagina

  • Crezione di un motore di ricerca FULLTEXT: introduzione
  • Modifiche da apportare alle tabelle per ricerche FULLTEXT
  • Aggiungere indici FULLTEXT ad una tabella esistente
  • Creare una nuova tabella con gli indici FULLTEXT
  • Duplicare tabella e dati per gli indici FULLTEXT
  • Le Query per creare i FULLTEXT index nella nuova tabella
  • La situazione finale della tabella che verrà usata per ricerche FULLTEXT
  • Modificatori di ricerca FULLTEXT
  • Vedere configurazione FULLTEXT con SHOW VARIABLES
  • Alcune restrizioni nelle ricerche FULLTEXT
  • Ricerca FULLTEXT di parole più brevi di 4 caratteri
  • Numero di record minimo e presenza dell'oggetto di ricerca in più del 50% dei record
  • Confronto metodo LIKE e metodo FULLTEXT
  • Possibile scegliere metodo LIKE o metodo FULLTEXT

Crezione di un motore di ricerca FULLTEXT: introduzione

Finora le ricerche sono state eseguite nel modo classico usando le clausole e le funzioni più comuni in MYSQL, tra le quali la keyword LIKE.
Ora cerchaimo di usare un metodo più avanzato che MYSQL ci mette a disposizione. Si tratta di un motore di ricerca avanzato che usa gli indici FULLTEXT di MySQL.

La ricerca FULLTEXT consente di eseguire delle Query FULLTEXT sui caratteri dei dati memorizzati in tabelle MYSQL.
Questo tipo di ricerca è utile quando occorre cercare parole o frasi in più campi. Con la maniera classica sarebbe molto difficile, se non, a volte, impossibile, effettuare questo tipo di ricerca. Ma usando il motore di ricerca FULLTEXT è possibile cercare parole o frasi senza l'utilizzo di operazioni di pattern-matching.
Ma per far questo è necessario preparare opportunamente le tabelle su cui si intende lavorare.

Modifiche da apportare alle tabelle per ricerche FULLTEXT

Tuttavia occorre subito dire che per usare ricerche FULLTEXT ci sono alcune restrizioni o regole da seguire ed è quindi necessario preparare il Database.

  • La tabella non può essere creata con il motore InnoDB ma deve essere assolutamente di tipo MyISAM che sono le uniche tabelle che supportano le ricerche FULLTEXT.
  • Non è possibile effettuare ricerche FULLTEXT su campi di tipo BLOB, i quali dovranno essere convertiti in campi di testo, quindi CHAR, VARCHAR, TEXT.
  • Nella tabella che verrà usata per ricerche FULLTEXT è necessario definire degli indici FULLTEXT.
  • La tabella usata per ricerche FUULTEXT dovrà contenere almeno 3 record perchè le ricerche abbiano l'effetto desiderato altrimenti si rischia di avere risultati inattesi.
  • Le stringhe da cercare con le query dovranno avere più di 3 caratteri.

E' possibile definire gli indici FULLTEXT

  • se la tabella è già esistente, utilizzando ALTER TABLE ed indicando le colonne desiderate nella clausola ADD FULLTEXT.
  • nell'istruzione CREATE TABLE per crearne una nuova già con gli indici

Aggiungere indici FULLTEXT ad una tabella esistente

ALTER TABLE table_name ADD FULLTEXT(field1, field2);

Dopo aver apportato questa modifica alla nostra tabella nel pannello di controllo di MYSQL avremo questa situazione:

field1 FULLTEXT Nessuno field1
      field2

Se l'operazione avviene con due distinti comandi come nel seguente esempio

ALTER TABLE table_name ADD FULLTEXT(field1);
ALTER TABLE table_name ADD FULLTEXT(field2);

Con queste istruzioni avremo questa situazione

field1 FULLTEXT Nessuno field1
field2 FULLTEXT Nessuno field2

Creare una nuova tabella con gli indici FULLTEXT

Se non si dispone di una tabella idonea a questo studio è possibile creare la nuova tabella con questa Query. In questa Query ci sono anche le istruzioni per creare gli indici FULLTEXT:
FULLTEXT KEY title (title,body)

1
2
3
4
5
6
7
8
9
10
11
<?php
$Query 
"CREATE TABLE IF NOT EXISTS sql_articoli (
  id int(11) NOT NULL auto_increment,
  title varchar(200) default NULL,
  body text,
  PRIMARY KEY  (id),
  FULLTEXT KEY title (title,body)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; 
"
;
?>

Duplicare tabella e dati per gli indici FULLTEXT

Se abbiamo una tabella ma, per fare delle semplici prove, non vogliamo lavorare con quella per timore di perdere i dati o, peggio, di renderla inutilizzabile, possiamo crearne un duplicato.
Questo passaggio è già spiegato in Gestione DB => Duplicare tabella e dati, quindi non mi dilungo nei particolari.

Questa che segue è la Query per creare il duplicato di una tabella.
Dopo l'istruzione CREATE TABLE c'è il nome della nuova tabella che si intende creare, dopo l'operatore LIKE c'è il nome della tabella di origine.
Questa operazione copia la sola struttura della tabella di origine.

1
2
3
4
<?php
$Query 
"CREATE TABLE sql_articoli LIKE articoli
"
;
?>

Dopo aver creato la tabella su cui intendiamo lavorare il modo più semplice è quello di prelevare i dati dalla tabella di origine o, in alternativa, inserire i dati manualmente. Se si opta per la prima soluzione, si potrebbe mandare in esecuzione la seguente Query che preleva i dati da una tabella di origine e li inserisce nella nuova tabella.

1
2
3
4
5
6
<?php
$Query 
"INSERT INTO sql_articoli(
SELECT *
FROM articoli )
"
?>

Le Query per creare i FULLTEXT index nella nuova tabella

Se la nuova tabella non è ancora provvista di indici FULLTEXT è possibile seguire la sintassi usata nelle due Query che seguono per aggiungere gli indici ad una tabella già esistente.

1
2
3
4
5
<?php
$Query 
"ALTER TABLE sql_articoli 
ADD FULLTEXT (title, body)
"
;
?>

La situazione finale della tabella che verrà usata per ricerche FULLTEXT

Qui sotto alcune viste della struttura della tabella ottenuta:
la prima vista è quella classica che mostra i campi ed i loro dettagli
la seconda vista mostra solo i campi indice e le loro caratteristiche
la terza vista mostra le statistiche che riguardano la tabella

DESCRIBE sql_articoli
Field Type Null Key Default Extra
id int NO PRI auto_increment
title varchar(200) YES MUL
body text YES
I campi della tabella :
id, title, body

SHOW INDEX FROM sql_articoli
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
sql_articoli 0 PRIMARY 1 id A 30 BTREE YES
sql_articoli 1 title 1 title 1 YES FULLTEXT YES
sql_articoli 1 title 2 body 1 YES FULLTEXT YES


SHOW TABLE STATUS LIKE 'sql_articoli'
8 informazioni per tabelle trovate
Name Engine Collation Rows Auto_increment Data_length Create_time Update_time
sql_articoli MyISAM utf8_general_ci 30 31 8372 2021-08-12 20:24:23 2021-08-12 20:24:23

Modificatori di ricerca FULLTEXT

I tipi di ricerche più usati sono quelli che usano i seguenti MODIFICATORI:

  • IN NATURAL LANGUAGE MODE: interpreta la stringa di ricerca come una frase in linguaggio naturale umano (una frase in testo libero). Non ci sono operatori speciali. Viene applicato l'elenco stopword (parole non valide). Inoltre, parole che sono presenti nel 50% o più delle righe vengono considerati comuni e non vengono restituite.
    Le ricerche FULLTEXT di default sono ricerche IN NATURAL LANGUAGE MODE sia che il modificatore IN NATURAL LANGUAGE MODE viene espresso esplicitamente o se viene omesso.
    Per ulteriori informazioni, vedere "NATURAL LANGUAGE in ricerche FULLTEXT".
  • IN BOOLEAN MODE: interpreta la stringa di ricerca utilizzando le regole di un linguaggio di query speciale. La stringa contiene le parole da cercare. Si possono usare gli operatori che specificano i requisiti in modo tale che una parola deve essere presente o assente nei record di corrispondenza, o che dovrebbe essere ponderata una rilevanza superiore o inferiore rispetto al solito. Parole comuni come "some" o "then" sono considerate stopword e non vengono cercate se presente nella stringa di ricerca. Il modificatore IN BOOLEAN MODE specifica una ricerca booleana.
    Per ulteriori informazioni, vedere "IN BOOLEAN MODE per ricerche FULLTEXT".
  • WITH QUERY EXPANSION: è una variante di una ricerca IN NATURAL LANGUAGE MODE. La stringa di ricerca viene utilizzata per eseguire una ricerca IN NATURAL LANGUAGE MODE. Poi parole dalle righe più rilevanti restituiti dalla ricerca vengono aggiunti alla stringa di ricerca e la ricerca è fatto di nuovo. La query restituisce le righe dalla seconda ricerca. Il modificatore IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION o WITH QUERY EXPANSION specifica una espansione della query di ricerca. In sostanza, l'espansione della query esegue una ricerca a testo integrale e quindi utilizza i primi risultati (configurato da ft_query_expansion_limit) per cercare di nuovo e comporre il set di risultati.
    Per ulteriori informazioni, vedere "WITH QUERY EXPANSION per Ricerche FULLTEXT".

Vedere configurazione FULLTEXT con SHOW VARIABLES

Tramite una opportuna interrogazione del DB possiamo ottenere delle preziose informazioni sull'ambiente e sulle variabili della ricerca FULLTEXT.
Con questa Query SHOW VARIABLES LIKE 'ft%'; otteniamo queste informazioni che chiariremo più avanti.

1
2
3
4
5
<?php
$Query 
"
SHOW VARIABLES LIKE 'ft%';
"
;
?>
5 record visualizzati
Variable_name Value
ft_boolean_syntax + -><()~*:""&|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (built-in)
ft_boolean_syntax: indica gli operatori che si possono usare associati al BOOLEAN MODE
ft_max_word_len: indica la lunghezza massima delle parole
ft_min_word_len: indica la lunghezza minima delle parole
ft_query_expansion_limit: Il numero massimo di combinazioni da eseguire nelle ricerche FULLTEXT usando WITH QUERY EXPANSION.
ft_stopword_file:La lista stopword viene caricato e cercata usando query per full-text utilizzando il character_set e la collation di caratteri utilizzati dal server. Possono verificarsi falsi risultati o riscontri se il file stopword o le colonne utilizzate per indicizzazione full-text o le ricerche hanno un set di caratteri diversi dal character_set utilizzato dal server.

Alcune restrizioni nelle ricerche FULLTEXT

Oltre ad alterare la tabella sulla quale dobbiamo lavorare, come abbiamo già visto, esistono alcune restrizioni nell'uso delle ricerche FULLTEXT. Molte volte è possibile aggirare l'ostacolo cambiando il modificatore da IN NATURAL LANGUAGE MODE a IN BOOLEAN MODE.

Ricerca FULLTEXT di parole più brevi di 4 caratteri

MySQL, per motivi di ottimizzazione, imposta la lunghezza minima delle parole su cui effettuare le ricerche FULLTEXT a quattro caratteri.
Per risolvere il problema, piuttosto che modificare le configurazioni di MySql come da più parti consigliato, è possibile aggiungere alla fine della parola un * (asterisco) ed aggiungere in AGAINST, dopo la stringa delimitata da apici, il modificatore IN BOOLEAN MODE.
In questo modo potrebbe succedere di trovare più record di quello che ci si aspetta, ma, secondo me, più è meglio di meno.

Numero di record minimo e presenza dell'oggetto di ricerca in più del 50% dei record

E' ovvio che una tabella che contenga un numero esiguo di record non merita tutte le attenzioni e studi in cui ci stiamo prodigando.
Detto ciò c'è da dire che, perchè la ricerca FULLTEXT venga eseguita correttamente, è necessario che il minimo numero di record presenti in tabella non sia inferiore a tre.
Su questo punto c'è poco da dire.

Cosa più importante da sottolineare è che il termine cercato non sia presente in più del 50% dei records.
Le ricerche FULLTEXT effettuate nella maniera più semplice eseguite col modificatore IN NATURAL LANGUAGE MODE in maniera esplicita o sottintesa, assegna un valore maggiore alle parole poco frequenti all'interno della tabella ed un valore inferiore alle parole più diffuse. Questo perché ritiene poco importanti le parole più comuni. Se una parola è presente nel 50% dei record allora questa parola è ritenuta insignificante e quindi non viene utilizzata nella ricerca dei record.
Con tabelle di grandissime dimensioni questo comportamento è ottimale perché evita che una ricerca restituisca milioni di risultati.
Con tabelle piccole invece si possono ottenere risultati anomali.
Le ricerche FULLTEXT eseguite col modificatore IN BOOLEAN MODE, nonostante impieghino un meccanismo simile per assegnare il punteggio alle parole, non hanno il vincolo del 50% pertanto restituiscono anche più della metà dei record se la ricerca lo richiede.

Confronto metodo LIKE e metodo FULLTEXT

Qui sotto vengono mostrate due Query

  • una col metodo classico utilizzando LIKE
  • una utilizzando il motore FULLTEXT

Si mota subito come i risultati ottenuti sono gli stessi e che la seconda Query è più snella e, se si ha a che fare con migliaia o milioni di record, si noterà che che la seconda Query è anche più veloce della prima Query.

Metodo classico con LIKE
1
2
3
4
5
6
7
8
9
10
11
<?php
$Query 
"SELECT *
FROM sql_articoli
WHERE title LIKE '%substr%'
OR title LIKE '%funzione%'
OR title LIKE '%MySQL%'
OR body LIKE '%substr%'
OR body LIKE '%funzione%'
OR body LIKE '%MySQL%'
"
;
?>
Metodo con ricerca FULLTEXT
1
2
3
4
5
6
<?php
$Query 
"SELECT *
FROM sql_articoli
WHERE MATCH(title,body) AGAINST ('substr funzione MySQL') 
"
;
?>
19 record letti
id title body
1 La funzione PHP strpos La funzione strpos() trova e...
2 La funzione PHP substr_count La funzione substr_count() Conta il...
3 La funzione PHP substr Questa funzione restituisce parte di...
4 La funzione PHP time La funzione time() restituisce...
5 La funzione mktime La funzione PHP mktime() restituisce il...
6 Piccolo tutorial per opendir La funzione opendir() apre l'handle...
12 La funzione strlen() La funzione strlen() restituisce la...
13 La funzione count_chars() La funzione count_chars(), da non...
14 funzione SUBSTRING_INDEX() La funzione SUBSTRING_INDEX()...
15 Nidificazione della funzione... Se si vuole estrarre una delle parti...
16 funzione LOCATE() La funzione LOCATE() restituisce la...
17 Un esempio pratico su una vera tabella... ora vediamo di usare la funzione...
18 funzioni UPPER() e LOWER() Le due funzioni sono sinonimi delle...
19 funzione REPLACE() La funzione REPLACE() può essere...
20 Ricerca per numero telefonico Togliendo gli spazi ed altri eventuali...
22 istruzione SELECT L'istruzione SELECT, più di tutte le...
24 Le tabelle In ambito MYSQL le tabelle sono le...
25 Introduzione al DB Con MYSQL le pagine diventano ancora...
28 Le funzioni VAL.ERR e SE In queste formule usiamo ben tre...
19 record letti
id title body
1 La funzione PHP strpos La funzione strpos() trova e...
2 La funzione PHP substr_count La funzione substr_count() Conta il...
3 La funzione PHP substr Questa funzione restituisce parte di...
4 La funzione PHP time La funzione time() restituisce...
5 La funzione mktime La funzione PHP mktime() restituisce il...
6 Piccolo tutorial per opendir La funzione opendir() apre l'handle...
12 La funzione strlen() La funzione strlen() restituisce la...
13 La funzione count_chars() La funzione count_chars(), da non...
14 funzione SUBSTRING_INDEX() La funzione SUBSTRING_INDEX()...
15 Nidificazione della funzione... Se si vuole estrarre una delle parti...
16 funzione LOCATE() La funzione LOCATE() restituisce la...
17 Un esempio pratico su una vera tabella... ora vediamo di usare la funzione...
18 funzioni UPPER() e LOWER() Le due funzioni sono sinonimi delle...
19 funzione REPLACE() La funzione REPLACE() può essere...
20 Ricerca per numero telefonico Togliendo gli spazi ed altri eventuali...
22 istruzione SELECT L'istruzione SELECT, più di tutte le...
24 Le tabelle In ambito MYSQL le tabelle sono le...
25 Introduzione al DB Con MYSQL le pagine diventano ancora...
28 Le funzioni VAL.ERR e SE In queste formule usiamo ben tre...

Possibile scegliere metodo LIKE o metodo FULLTEXT

Metodo con ricerca FULLTEXT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?php
$Query 
""
$find 'substr funzione MySQL';
$parts explode(" ",$find);
$num_parts count($parts);
if (
$num_parts==1){
    
$Query="SELECT * 
    FROM sql_articoli 
    WHERE title LIKE '%"
.$find."%'
    OR body LIKE '%"
.$find."%'
    "
;
} elseif (
$num_parts>1){
    
$Query="SELECT *, MATCH (title, body) AGAINST ('".$find."' ) AS rilevanza 
    FROM sql_articoli 
    WHERE MATCH (title, body) 
    AGAINST('"
.$find."') 
    ORDER BY rilevanza DESC"
;
}
echo 
"La query selezionata<br />\n";
highlight_num($Query);
?>
La query selezionata
1
2
3
4
5
SELECT *, MATCH (title, body) AGAINST ('substr funzione MySQL' ) AS rilevanza 
    FROM sql_articoli 
    WHERE MATCH (title, body) 
    AGAINST('substr funzione MySQL') 
    ORDER BY rilevanza DESC
7 record letti
id title body rilevanza
3 La funzione PHP substr Questa funzione restituisce parte di... 2.811974287033081
22 istruzione SELECT L'istruzione SELECT, più di tutte le... 1.3879175186157227
25 Introduzione al DB Con MYSQL le pagine diventano ancora... 1.137705683708191
17 Un esempio pratico su una vera tabella... ora vediamo di usare la funzione... 0.9700345993041992
18 funzioni UPPER() e LOWER() Le due funzioni sono sinonimi delle... 0.9698547124862671
19 funzione REPLACE() La funzione REPLACE() può essere... 0.9194468259811401
24 Le tabelle In ambito MYSQL le tabelle sono le... 0.8876984715461731

 

 



settore tecnico il sito di lorettabweb il Forum di sostegno
il forum il forum il forum