Caught in a Web

Query koji smo predstavili gore ima jednu veliku manu - nije u mogućnosti da koristi indekse! Zahvaljujući činjenici da search pattern počinje sa džokerom '%', upit ne može koristiti indekse, čak ni onda kada su oni definisani za polja koje pretražujete! U praksi, ovo znači znatno sporije vreme izvršavanja upita (koji mora proći kroz sve slogove u tabeli jer ne koristi indekse), a ako na to dodate sve veći broj sadržaja na sajtu i nekoliko korisnika koji mogu istovremenu vršiti pretragu - imate ozbiljan problem. Srećom rešenje je tu na dohvat ruke i zove se Full-Text Search.

Full-Text Search omogućava vam efikasnije pretraživanje unutar text polja (CHAR, VARCHAR, TEXT), korišćenjem takozvanih Full-Text Indeksa.
Postoje tri vrste Full-Text Search-a:
- Natural language search
String koji tražite je podeljen u reči, tako da kao rezulutat dobijate one slogove koji sadrže date reči.
- Bolean mode seach
String koji tražite je podeljen u reči, ali svakoj reči dodaje se logički operator koji omogućava pretragu onih reči koje su prisutne ili pak onih koje ne postoje u traženom tekstu
- Expansion Search
Pretraga u dve faze. Prva faza je identična "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 "uobičajene reči", tj. one koje postoje u minimum 50% slogova. Takođe, ignorišu se takozvane "stopwords" reči kao što su "the", "and" i sl. kao i reči kraće od 4 karaktera. Spisak svih ovih reči možete naći u odgovarajućoj sekciji MySQL manuala uz napomenu da ih možete promeniti (recimo prilagođavanjem srpskom jeziku) tako što ćete promeniti putanju do fajla sa ignorisanim rečima. Upustvo za to možete naći ovde.

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

Za početak 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 ćemo kreirati odgovarajuću tabelu, importovati podatke i na kraju dodati text indekse. Startujte MySQL klijent iz istog direktorijuma gde ste snimili gornji fajl, a zatim otkucaje sledeće:

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 '\r\n';

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 značajno sporiji ako tabela već 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 određen relevantnošću (eng. relevance) pronađenih slogova. To možemo videti na sledećem 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 omogućava pretragu sličnu nekim pretraživačima na Internetu u smislu da je moguće zadati reči 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 mogućnosti BOOLEAN MODE -a posetite odgovarajuću 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 reč market običnim (natural) searchom. Kod drugog upita koristili smo "QUERY EXPANSION" koji prvobitni string spaja sa najrelevantnijim slogovima dobijenim kao rezultat prve faze (natural searcha) čime smo dobili još jedan slog. Više o Expansion Searchu možete pronaći u MySQL Manualu.

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