MySQL Full-Text Searches

Danas vam predstavljam treci, ujedno i poslednji zapis iz serije tutorijala o “Mogucnostima MySQL-a koje developeri retko koriste”.

U prethodna dva pricali smo transakcijama i referencijalnom integritetu – mogucnostima koje su dostupne samo u InnoDB i BDB tabelama. Danas vam predstavljam “Full-Text Search”, koji je za razliku od gore opisanih feature-a dostupan samo u MyISAM tabelama (što je MySQL-ov default).

Upravo ste kreirali još jedan database driven sajt (Forum, Blog, CMS, šta vec) i došao je red na pretragu. Bez mnogo razmišljanja, dolazite do uobicajenog rešenja:

select * from moja_tabela 
where textpolje like '%text_iz_search_polja%'

koje po svoj prilici završava posao. Malo varijacije na temu ako je potrebno ukljuciti više polja u pretragu, par logickih operatora koje dinamicki generišete u vašem scriptu i to je to? Hmmm ne baš.

Query koji smo predstavili gore ima jednu veliku manu – nije u mogucnosti da koristi indekse! Zahvaljujuci cinjenici da search pattern pocinje sa džokerom ‘%’, upit ne može koristiti indekse, cak ni onda kada su oni definisani za polja koje pretražujete! U praksi, ovo znaci znatno sporije vreme izvršavanja upita (koji mora proci kroz sve slogove u tabeli jer ne koristi indekse), a ako na to dodate sve veci broj sadržaja na sajtu i nekoliko korisnika koji mogu istovremenu vršiti pretragu – imate ozbiljan problem. Srecom rešenje je tu na dohvat ruke i zove se Full-Text Search.

Full-Text Search omogucava vam efikasnije pretraživanje unutar text polja (CHAR, VARCHAR, TEXT), korišcenjem takozvanih Full-Text Indeksa.

Postoje tri vrste Full-Text Search-a:
– Natural language search
String koji tražite je podeljen u reci, tako da kao rezulutat dobijate one slogove koji sadrže date reci.
– Bolean mode seach
String koji tražite je podeljen u reci, ali svakoj reci dodaje se logicki operator koji omogucava pretragu onih reci koje su prisutne ili pak onih koje ne postoje u traženom tekstu
– Expansion Search
Pretraga u dve faze. Prva faza je identicna “natural language search-u”. U drugoj fazi obavlja se pretraga tako što se prvobitni string spaja sa najrelevantnijim slogovima dobijenim kao rezultat prve faze.

Prilikom pretrage ignorišu se “uobicajene reci”, tj. one koje postoje u minimum 50% slogova. Takode, ignorišu se takozvane “stopwords” reci kao što su “the”, “and” i sl. kao i reci krace od 4 karaktera. Spisak svih ovih reci možete naci u odgovarajucoj sekciji MySQL manuala uz napomenu da ih možete promeniti (recimo prilagodavanjem srpskom jeziku) tako što cete promeniti putanju do fajla sa ignorisanim recima. Upustvo za to možete naci ovde.

A sada da vidimo nekoliko primera. Koristicu se bazom najpoznatijih pogrešnih predvidanja, koju sam kreirao specijalno za ovu priliku. (inspiraciju sam pronašao na ES-u – http://www.elitesecurity.org/tema/156409-Pogresna-predvidjanja)

Za pocetak odradite copy/paste teksta koji sledi i snimite ga na vaš kompjuter kao fajl wrong_prediction.txt.

Albert Einstein, 1974|There is not the slightest indication that nuclear energy will ever be obtainable. It would mean that the atom would have to be shattered at will
Margaret Thatcher, 1974|It will be years - not in my time - before a woman will become a Prime Minister.
Alexander Graham Bell, c.1880.|One day there will be a telephone in every major city in the USA.
Popular Mechanics, 1949|Computers in the future may weigh no more than 1.5 tons.
Thomas Watson, IBM Computers,1943|I think there is a world market for as many as 5 computers.
Marshal Foch, France, 1912|Aircraft are interesting toys, but of no military value.

Zatim cemo kreirati odgovarajucu tabelu, importovati podatke i na kraju dodati text indekse. Startujte MySQL klijent iz istog direktorijuma gde ste snimili gornji fajl, a zatim otkucaje sledece:

create table wrong_predictions(
who varchar(100) not null,
prediction text not null
) engine = MyISAM;

load data local infile 'wrong_predictions.txt' into table wrong_predictions
fields terminated by '|' lines terminated by 'rn';

ALTER TABLE wrong_predictions
ADD FULLTEXT (who),
ADD FULLTEXT (prediction),
ADD FULLTEXT (who, prediction);

Kao što vidite, prvo smo importovali sve podatke u tabelu, a tek onda kreirali indekse. Razlog zašto smo to baš tako uradili su perfomanse – insert je uvek znacajno sporiji ako tabela vec ima kreirane indekse.

A sada nekoliko primera “Natural Search”-a:

mysql> SELECT * FROM wrong_predictions WHERE MATCH(who) AGAINST('Thatcher');
+-------------------------+---------------------------------------------------------------------------------+
| who                     | prediction                                                                      |
+-------------------------+---------------------------------------------------------------------------------+
| Margaret Thatcher, 1974 | It will be years - not in my time - before a womanill become a Prime Minister.  |
+-------------------------+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>  SELECT * FROM wrong_predictions WHERE MATCH(who,prediction) AGAINST('computers');
+-------------------------+-------------------------------------------------------------+
| who                     | prediction                                                  |
+-------------------------+-------------------------------------------------------------+
| Thomas Watson, IBM,1943 | I think there is a world market for as many as 5 computers. |
| Popular Mechanics, 1949 | Computers in the future may weigh no more than 1.5 tons.    |
+-------------------------+-------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

Kada koristimo MATCH u where klauzi, redosled kolona je odreden relevantnošcu (eng. relevance) pronadenih slogova. To možemo videti na sledecem primeru:

mysql> SELECT prediction, MATCH(prediction) AGAINST('computers') AS relevance FROM wrong_predictions;
+---------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| prediction                                                                                                                                        | relevance        |
+---------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| There is not the slightest indication that nuclear energy will ever be obtainable. It would mean that the atom would have to be shattered at will | 0                |
| It will be years - not in my time - before a woman will become a Prime Minister.                                                                  | 0                |
| One day there will be a telephone in every major city in the USA.                                                                                 | 0                |
| Computers in the future may weigh no more than 1.5 tons.                                                                                          | 0.66266459031789 |
| I think there is a world market for as many as 5 computers.                                                                                       | 0.67003110026735 |
| Aircraft are interesting toys, but of no military value.                                                                                          | 0                |
+---------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
6 rows in set (0.00 sec)

Boolean mode omogucava pretragu slicnu nekim pretraživacima na Internetu u smislu da je moguce zadati reci koje moraju postojati (+rec) kao i reci koje ne smeju postojati (-rec). Na primer:

mysql> SELECT * FROM wrong_predictions WHERE MATCH(who,prediction)
-> AGAINST('+computers -popular' IN BOOLEAN MODE);
+-------------------------+-------------------------------------------------------------+
| who                     | prediction                                                  |
+-------------------------+-------------------------------------------------------------+
| Thomas Watson, IBM,1943 | I think there is a world market for as many as 5 computers. |
+-------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

Pored toga, mogu se koristiti džokeri kao na primer:

mysql> SELECT * FROM wrong_predictions WHERE MATCH(who,prediction)
-> AGAINST('comp*' IN BOOLEAN MODE);
+-------------------------+-------------------------------------------------------------+
| who                     | prediction                                                  |
+-------------------------+-------------------------------------------------------------+
| Popular Mechanics, 1949 | Computers in the future may weigh no more than 1.5 tons.    |
| Thomas Watson, IBM,1943 | I think there is a world market for as many as 5 computers. |
+-------------------------+-------------------------------------------------------------+
2 rows in set (0.00 sec)

Za pun opis mogucnosti BOOLEAN MODE -a posetite odgovarajucu sekciju MySQL manuala.

I za kraj primer “Expansion Search”-a:

mysql> SELECT * FROM wrong_predictions WHERE MATCH(who,prediction)
-> AGAINST('market');
+-------------------------+-------------------------------------------------------------+
| who                     | prediction                                                  |
+-------------------------+-------------------------------------------------------------+
| Thomas Watson, IBM,1943 | I think there is a world market for as many as 5 computers. |
+-------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM wrong_predictions WHERE MATCH(who,prediction)
-> AGAINST('market' WITH QUERY EXPANSION);
+-------------------------+-------------------------------------------------------------+
| who                     | prediction                                                  |
+-------------------------+-------------------------------------------------------------+
| Thomas Watson, IBM,1943 | I think there is a world market for as many as 5 computers. |
| Popular Mechanics, 1949 | Computers in the future may weigh no more than 1.5 tons.    |
+-------------------------+-------------------------------------------------------------+
2 rows in set (0.00 sec)

Kod prvog upita tražili smo rec market obicnim (natural) searchom. Kod drugog upita koristili smo “QUERY EXPANSION” koji prvobitni string spaja sa najrelevantnijim slogovima dobijenim kao rezultat prve faze (natural searcha) cime smo dobili još jedan slog. Više o Expansion Searchu možete pronaci u MySQL Manualu.

Eto toliko o FullText Searchu. Nadam se da ce primeri koje sam kreirao biti od pomoci da vas zainteresuju za njegovo korišcenje. Kao i uvek, za više informacija možete konsultovati MySQL manual.