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:

• Lucene
• Sfinge
• Elasticsearch : server basato su Lucene
• Solr : server basato su Lucene

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

column LIKE '%word%'

oppure

column REGEXP '.*word.*'

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:

MATCH(column) AGAINST(word)

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ì:

SELECT * FROM table
WHERE MATCH(column) AGAINST('word');

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):

SELECT *
FROM table
WHERE MATCH(column) AGAINST('word')
ORDER BY MATCH(column) AGAINST('word') DESC;

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:

Esempi

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

MATCH(column) AGAINST('+lorem ipsum' IN BOOLEAN MODE)

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

MATCH(column) AGAINST('+lorem ipsum -dolor' IN BOOLEAN MODE)

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

MATCH(column) AGAINST('(+lorem +ipsum) ("lorem ipsum")' IN BOOLEAN MODE)

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 .

MATCH(column) AGAINST('>lorem <ips*' IN BOOLEAN MODE)

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_len e ft_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:

SELECT id, title, image, text
FROM blog
WHERE
    publish_date > '2013-06-19 00:00:00' AND
    MATCH(title, text) AGAINST('lorem' IN BOOLEAN MODE)
ORDER BY MATCH(title, text) AGAINST('lorem' IN BOOLEAN MODE)
LIMIT 0, 10;

SELECT id, title, text
FROM pages
WHERE MATCH(title, text) AGAINST('lorem' IN BOOLEAN MODE)
ORDER BY MATCH(title, text) AGAINST('lorem' IN BOOLEAN MODE)
LIMIT 10, 0;

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:

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:

SELECT *, SUM(MATCH(testo) AGAINST('lorem' IN BOOLEAN MODE)) as score
FROM search_index
WHERE MATCH(testo) AGAINST('lorem' IN BOOLEAN MODE)
GROUP BY tabella, tabella_id
ORDER BY score DESC
LIMIT 0, 10;

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

$results = /* result set ritornato da una query che interroga la tabella indice search_index */;
$components = array();
foreach($results as $result) {
    $tabella = $result['tabella'];
    $tabella_id = $result['tabella_id'];

    $components[$tabella][] = $components[$tabella_id];
}
/*
$components conterrà:
array(
    'blog' => array(1, 2, 5, 8, 12, 13, 17),
    'page' => array(1, 3, 4)
);
*/

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.

// functions to return detailed information for both blog &
// page components, weeding out results that are no longer valid
function blog($ids) {
    return mysqli_query('
        SELECT id, title, image, text
        FROM blog
        WHERE
            publish_date > '2013-06-19 00:00:00' AND
            id IN ('. implode( ',', $ids ) .')
    ');
}
function page($ids) {
    return mysqli_query('
        SELECT id, title, image
        FROM page
        WHERE id IN ('. implode( ',', $ids ) .')
    ');
}

// passa gli ID raggruppati per componente alle funzioni di callback
// riempie $verified con i risultati di ricerca effettivamente verificati
$verified = array();
foreach($components as $component => $ids) {
    $componentResults = call_user_func($component, $ids);
    $verified = array_merge($verified, $componentResults);
}

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ù:

SELECT *, SUM(MATCH(text) AGAINST('lorem' IN BOOLEAN MODE)) as score
FROM search_index
WHERE MATCH(text) AGAINST('lorem' IN BOOLEAN MODE)
GROUP BY component, component_id
ORDER BY score DESC
LIMIT 10, 3;

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