Come costruire un motore di ricerca basato su MySQL

Nei siti web content-heavy (con molti contenuti), è importante dare la possibilità di poter effettuare ricerche in grado di aiutare gli utenti a trovare esattamente ciò che stanno cercando. La soluzione più ovvia è la ricerca diretta all’interno delle tabelle del database MySQL. Tuttavia, l’implementazione di una ricerca MySQL generica non è affatto banale. Ecco come evitare insidie ​​e creare un motore di ricerca robusto basato su MySQL.

MySQL è un database relazionale, non un motore di ricerca. Sebbene fornisca alcuni strumenti per cercare all’interno delle tabelle, è meglio integrare un vero motore di ricerca se stai cercando una soluzione a tutti gli effetti. Alcuni dei più popolari motori di ricerca (open source) sono:

Le opzioni di cui sopra sono sicuramente di gran lunga superiori, tuttavia potrebbe avere senso costruire un motore di ricerca basato solo su MySQL, magari per evitare l’installazione e la manutenzione di software aggiuntivo o perchè il nostro hosting ha risorse limitate.

Ricerca full-text

Come si fa a cercare il testo in MySQL? Le soluzioni più semplici sono

oppure

ma offrono funzionalità limitate, poichè oltre a non fornire molte opzioni, non utilizzano in modo accurato gli indici e di conseguenza non saranno ottimizzate una volta che il set di dati crescerà.

Per potenziare la ricerca, innazitutto bisogna aggiungere un indice FULLTEXT alla colonna che è interessata dalla ricerca. Tali indici possono essere creati solo su campi di testo VARCHAR, TEXT, CHAR. Dopo aver creato l’indice, si costruisce la query usando le istruzioni:

La peculiarità di queste ricerche è che non si limitano a rintracciare le occorrenze di una parola o di una frase come farebbe l’operatore LIKE ma restituiscono dei risultati in cui il campo o i campi sottoposti alla ricerca mostrano un’attinenza particolare con il pattern indicato. Nella sua forma più semplice, una ricerca full-text potrebbe apparire così:

Nella clausola MATCH possono essere inclusi più campi, ma è fondamentale che tutti facciano parte dell’indice full-text creato. È altrettanto essenziale che, se l’indice include più campi, tutti vengano nominati nella clausola MATCH. La clausula MATCH restituisce un valore numerico in virgola mobile (punteggio), quindi è possibile ordinare i risultati in base alla pertinenza (il secondo MATCH usato nell’ORDER BY non causerà un ulteriore sovraccarico):

Modalità booleana

Per default, MATCH cercherà con l’impostazione IN NATURAL LANGUAGE MODE, dove ogni termine contenuto dentro la clausola AGAINST verrà controllato in modo uniforme sulla colonna definita in MATCH, come tipicamente avviene in un linguaggio naturale.

È possibile ottenere ricerche più avanzate tramite l’impostazione IN BOOLEAN MODE, che consente di escludere una determinata parola o di non pesare tutte le parole allo stesso modo. Un elenco completo degli operatori disponibili:

Operatore Descrizione
+ Indica che una parola DEVE essere presente nel testo.
Indica che una parola NON DEVE essere presente nel testo.
(nessun operatore) Per impostazione predefinita (quando né + né – viene specificato), la parola è facoltativa, ma le righe che lo contengono acquisiscono un punteggio maggiore.
@distanza  Verifica se due o più parole iniziano tutte a una distanza specificata l’una dall’altra, misurate in parole. Ad esempio se le parole word1, word2 e word3 dovrebbero apparire tutte all’interno di un intervallo di 8 parole possiamo scrivere MATCH (col1) AGAINST(‘”word1 word2 word3″ @8’ IN BOOLEAN MODE). Funziona solo su tabelle INNODB
> < Aumenta o diminuisce l’importanza di una parola nel punteggio di pertinenza.
() Raggruppa le parole in una sottoespressione.I gruppi con parentesi possono essere nidificati.
~ Una tilde principale agisce come un operatore di negazione, facendo sì che il contributo della parola alla pertinenza della riga sia negativo. Questo è utile per contrassegnare le parole ” rumore ” . Una riga contenente una parola di questo tipo è classificata più bassa rispetto ad altre, ma non è esclusa del tutto, come sarebbe con l’operatore -.
* Carattere jolly, dal significato classico
Una frase racchiusa tra virgolette doppie ( ") corrisponde solo alle righe che contengono letteralmente la frase, per come è stata digitata (solo parole, non punteggiatura).

Esempi

Le righe DEVONO contenere loremIpsum è facoltativo, ma se viene trovato anche ipsum, la riga “acquista” un punteggio più alto di altre righe che invece non contengono ipsum.

Le righe DEVONO contenere lorem, ma NON DEVE contenere dolorIpsum è facoltativo.

Le righe DEVONO contenere sia lorem che ipsum. Se la condizione viene soddisfatta esattamente nell’ordine in cui è stata definita, avrà un punteggio più alto rispetto a se entrambe le parole sono o non sono presenti nel testo

Le righe dovrebbero contenere lorem o qualsiasi cosa che inizi con ips . Le corripondenze con il lorem avranno punteggi più alti delle corripondenze  con parole che iniziano con ips .

Avvertenze

Alcune parole vengono semplicemente ignorate dalla ricerca full-text di MySQL, anche se esistono nelle righe, e la corrispondenza non produce alcun risultato. Per cominciare, ci sono le direttive di configurazione ft_min_word_lenft_max_word_len, che indicano la lunghezza minima e massima dovrebbe avere per essere indicizzata. Per impostazione predefinita, ft_min_word_len è 4, quindi non è possibile cercare parole con meno di 4 caratteri, a meno che non si modifichi questa direttiva di configurazione. Questa è un’impostazione a livello di server che interesserà tutti database e non a livello di singolo database.

Un altro motivo comune per cui le parole non vengono trovate è perché si trovano nell’elenco delle parole chiave di MySQL. Parole comuni come “the” o “and” non sono mai indicizzate, proprio come fa Google.

Ricerche complesse

La ricerca si fa ancora più complessa e articolata quando si tratta di fare una ricerca di dati distribuiti su più tabelle e colonne o quando il set di dati diventa molto grande. Effettuare una ricerca di dati sparsi tra tabelle e poi ordinarli in base al punteggio di pertinenza della ricerca, è abbastanza impossibile. Si potrebbe provare a generare più query per tabella, ad esempio:

Cattiva idea! Sebbene le query siano corrette e funzionanti, si sta lasciando l’onere di costruire il dataset al linguaggio PHP (o qualsiasi altro). L’esempio sopra funziona: trova i primi 10 risultati, su più tabelle: ma esegue due query che restituiscono i primi 10 risultati, scartando il resto.

Tuttavia, potrebbe accadere questo: se cerchi i risultati di ricerca da 1000 a 1009, tutte le query dovrebbero restituire i 1009 risultati migliori e il PHP deve unirli tutti insieme per arrivare a creare esattamente un unico set da 1000 a 1009. Ad un certo punto, se il set di dati diventa abbastanza grande, potresti avere problemi. Inoltre, la ricerca su più tabelle comporta l’esecuzione di più query che alla fine inizieranno a paralizzare il sistema.

Allora si potrebbe provare a utilizzare la clausula UNION in modo che sia MySQL a restituire un unico dataset di dati, anzichè lasciare quest’onere al linguaggio di programmazione. In questo modo MySql si potrebbe anche occupare di ordinare il set di risultati. Anche se questo potrebbe sembrare un miglioramento rispetto al primo esempio, alla fine si incontreranno gli stessi problemi… La soluzione consiste quindi in un raggruppamento dei dati in una tabella indice di ricerca.

Utilizzare un indice di ricerca

Se vogliamo che MySQL utilizzi efficacemente l’indice full-text, il consiglio è quello di raggruppare tutti i testi ricercabili in una tabella. Si può fare semplicemente duplicando i dati testuali in una tabella utilizzata come “indice” di ricerca, eliminando nel frattempo tutti i rumori irrilevanti (come i tag HTML o segni di punteggiatura) che potrebbero influenzare la ricerca: vogliamo solo il testo nudo e crudo. Manteniamo poi un riferimento alla fonte originale di tali dati (i campi tabella e tabella_id). Se si aggiorna un post su un blog, dovrebbe anche essere aggiornato il testo duplicato presente nella tabella indice.

Una tabella semplificata di esempio (che chiameremo search_index)  potrebbe essere implementata in questo modo, dove il campo testo ha un indice FULLTEXT:

tabella tabella_id testo
blog 1 Questo è il titolo del blog n. 1
blog 1 Questo è il testo del blog n. 1
blog 2 Questo è il titolo del blog n. 2
blog 2 Questo è il testo del post di blog n. 2
pagina 1 Questo è il titolo della pagina n
pagina 1 Questo è il testo della pagina # 1

La scansione e l’ordinamento di tutti i dati nel database diventa banale perchè la ricerca avviene solo in una tabella e si produce un punteggio in base alla pertinenza della ricerca sul campo testo:

Callback

E se la ricerca di post sul blog richiede che vi sia una condizione applicata su campi non testuali (tipo le date)? Fortunatamente, abbiamo il riferimento alla tabella e all’ID. Questo ci offre un’opportunità unica per verificare i dati. Diciamo che abbiamo appena eseguito la nostra query su search_index che ha restituito 7 post di blog e 3 pagine. Possiamo raggrupparli insieme

Raggruppandoli insieme, e usando techiche di callback, possiamo inviare richieste individuali ad alte prestazioni a entrambe le tabelle specifiche, che ora possono eliminare i risultati di ricerca che, dopo tutto, non dovrebbero essere inclusi. Si noti che nella condizione WHERE è presente una colonna di tipo data.

Otteniamo esattamente lo stesso risultato che avevamo originariamente. Lo abbiamo fatto in modo scalabile, con solo 3 query altamente performanti, tutte utilizzando un indice. Per recuperare 10 voci, il caso peggiore è che ci ritroviamo con 11 query diverse: una prima query che è l’interrogazione a search_index  che utilizza l’indice FULLTEXT e potenzialmente 10 query su 10 diverse tabelle per verificare i risultati, in cui la query utilizza la colonna della chiave primaria indicizzata.

Cosa succede realmente quando i callback mostrano risultati inferiori a 10, ad esempio 7? Abbastanza facile: rifacciamo esattamente lo stesso giro, partendo dall’offset 10, chiedendo 1 risultato di ricerca in più:

Quindi, verifichiamo di nuovo i risultati e ripetiamo fino a quando non sono stati “abbinati” tutti e 10 i risultati.


Comments are closed.

Utilizzando il sito, accetti l'utilizzo dei cookie da parte nostra. maggiori informazioni

Questo sito utilizza i cookie per fornire la migliore esperienza di navigazione possibile. Continuando a utilizzare questo sito senza modificare le impostazioni dei cookie o cliccando su "Accetta" permetti il loro utilizzo.

Chiudi