Post da nerd, astenersi perditempo… :-P
[NERD MODE ON]
Alcuni giorni fa sul lavoro mi sono cimentato in un nuovo esperimento.
Tutto è partito dal problema di dover indicizzare dei contenuti, sui quali poi eseguira una ricerca “generica” in stile google (ovvero un unico campo che riceve un insieme di parole, che possono corrispondere a diversi campi del db).
Bene non voglio stare qui a spiegare nei dettagli la soluzione che abbiamo deciso di adottare, ma in sostanza mi sono ritrovato con un campo testuale con dentro tutte le parole che corrispondono ad una chiave.
Ok, e ora? L’idea iniziale è stata quella di eseguire una query con ” LIKE ‘%parola%’ ” in OR con altre LIKE simili, per ogni parola inserita dall’utente. Una volta implementata, tutto funzionava (avevo 3 record in quella tabella). Siccome per scrupolo avevo anche implementato un timer che misurasse il tempo di esecuzione, ho provato a fare un test pesante… Con python mi sono creato un programma per scrivere su file qualche centinaia di query di inserimento su quella tabella, lo eseguo e popolo il db. Ora ci sono 1000 record (non sono ancora abbastanza ma rende già l’idea), rilancio la ricerca e il timer segna 0,3 secondi. Buono no? sì ma sono 1000 record, se mai i record dovessero essere 100K o ancora di più? Mmm non mi soddisfa questa cosa. *Googleing*….*Googleing*….*Googleing* FULL TEXT INDEX
Cos’è? In MySQL è possibile definire su un campo di una tabella, un indice FULL TEXT, ovvero una struttura che indicizza in maniera ottimale tutte le parole presenti in uno o più campi. Ci sono alcune restrizioni però. Quella più importante è che la tabella non può essere creata con il motore InnoDB (il più comune perchè supporta query multiple in una singola transazione). Inoltre le stringhe da cercare con le query dovranno avere più di 3 caratteri (altrimenti bisogna andare a modificare le impostazioni di MySQL, cosa impossibile se non si ha accesso al server, e comunque relativamente complicata da fare).
Detto ciò i passi da fare sono i seguenti:
Definiamo la tabella e l’indice:
CREATE TABLE test (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
keywords TEXT,
[keywords2] VARCHAR(255),
FULLTEXT (keywords[,keywords2])
);
[Tra le parentesi quadre ho messo il secondo campo, spero si capisca che è opzionale…]
Fatto questo e dopo aver popolato la tabella, possiamo provare la nostra ricerca con la seguente query:
SELECT * FROM test WHERE MATCH(keywords) AGAINST('pippo')
In questo modo andremo a dire a MySQL di cercarci l’esatta parola ‘pippo’ all’interno del campo keywords.
Una cosa che non si trova facilmente nelle guide che ho seguito (ho fatto perciò riferimento alla documentazione ufficiale di MySQL) è che così facendo si effettua una ricerca in linguaggio naturale, che è ottimale per indicizzare frasi di senso (più o meno) compiuto. Il “difetto” se può definirsi così, è che con questo tipo di ricerca alcune parole non verranno trovate, infatti MySQL utilizza un array di stopwords che verranno saltate a piè pari dalla ricerca. Per effettuare una ricerca su tutte le parole e utilizzando operatori logici (ad esempio “+” significa OR mentre “-” esclude la parole che segue questo simbolo) bisogna effettuare una ricerca IN BOOLEAN MODE, più lenta, ma più efficace. La nostra query dovrà quindi essere modificata in:
SELECT * FROM test WHERE MATCH(keywords) AGAINST('*pippo*,*pluto*' IN BOOLEAN MODE)
In questo modo si effettua una ricerca all’interno del campo keywords, delle parole che contengono al loro interno “pippo” o “pluto”. Il carattere “*” funziona un po’ come il “%” nelle LIKE.
A questo punto manca un ultima chicca. La funzione MATCH (..) AGAINST(..) ritorna un valore che rappresenta la pertinenza delle parole cercate nel campo. Nel BOOLEAN MODE sarà il numero di parole trovate nel campo, mentre con la ricerca in linguaggio naturale ci sarà un algoritmo che calcola la pertinenza con delle regole strane ma che funzionano ;)
Quindi per sfruttare al massimo questo algoritmo bisognerà scrivere la query con:
SELECT *, MATCH(keywords) AGAINST('*pippo*,*pluto*' IN BOOLEAN MODE) as pertinenza FROM test WHERE MATCH(keywords) AGAINST('*pippo*,*pluto*' IN BOOLEAN MODE) ORDER BY pertinenza DESC
Così facendo ordiniamo i risultati dal più pertinente, a quello meno.
Risultato? sui miei 1000 records il tempo si è dimezzato, nel caso peggiore… Risultato accettabile no? :-)
Non vi resta che provare per credere, buon divertimento!
[NERD MODE OFF]
[ad#ad-1]
