Pretraga MySQL baze podataka korištenjem Full-Text Search (FTS) tehnike

Za neke slučajeve, gdje nema previše podataka i gdje pretraga jako jednostavna, kao npr. pretraga po imenu i prezimenu ili par prvih karaktera određenog pojma, upotreba SQL LIKE operatora u kombinaciji sa wildcard karakterima (% i _) može biti zadovoljavajuće rješenje. Međutim, treba imati na umu da se radi o ograničenom i neefikasnom rješenju.

Ukoliko je potrebno pretragu riješiti na kvalitetnijem nivou, onda je rješenje Full-Text Search (FTS). Možete odabrati built-in rješenje u MySQL-u ili neke od još kvalitetnijih rješenja kao što su Sphinx Search (naš izbor), Apache Solr... 

Koje ćete rješenje odabrati, ovisi od zahtjeva projekta i dostupnih tehnologija. Ako se radi o manjim projektima koji će završiti na shared hostingu, onda je izbor dosta uži i obično MySQL-ov FTS, a sam naslov teksta govori o kojem pišemo u nastavku.

Full-text Search kao opcija je dostupan od MySQL verzije 3.23.23 i to prvobitno isključivo samo za MyISAM engine kao zadani DB engine u to vrijeme. Ukoliko ste trebali koristiti InnoDB, zbog recimo podrške za transakcije i strane ključeve (FK), onda niste mogli koristiti FTS, jer isti i nije bio podržan. Danas, sa obzirom da je InnoDB zadani engine, podrška za FTS je stigla u verziji MySQL 5.6. Poprilično sa zakašnjenjem ako pitate autora ovog teksta.

Glavni razlog da počnete koristiti MySQL FTS je brzina i mogućnost sortiranja rezultata pretrage po relevantnosti.

Brzina

Kao primjer, koristićemo pretragu tabele članaka, čija je struktura navedena u nastavku.

CREATE TABLE `articles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`text` text,
`date_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Potrebno je saznati broj tekstova koji sadrži riječ „prvak“.

SQL upit pretrage po title i text kolonama upotrebom LIKE operatora u kombinaciji sa % karakterom bi izgledao ovako:

SELECT COUNT(id) FROM articles WHERE title LIKE '%prvak%' OR text LIKE '%prvak%';

Na tabeli koja sadrži oko 220.000 redova, tj. tekstova vrijeme izvršavanja iznad navedenog upita (na development mašini) je preko 13 sekundi. Ako dodate index na navedene kolone, neće biti razlike, jer zbog wildcard karaktera na početku MySQL ne može da koristi index u ovom slučaju.

U nastavku ćemo vidjeti kako će se FTS ponašati sa istim zadatkom. Da bi koristili FTS, potrebno je dodati FULLTEXT index na title i text kolone.

ALTER TABLE `articles` ADD FULLTEXT `title_text` (`title`, `text`);

Treba imati na umu da dodavanje indexa na velikim tabelama može značajno potrajati.

U nastavku ćemo izvršiti SQL upit koji će koristiti maloprije kreirani FULLTEXT index i zapravo je bazični primjer Full-text pretrage.

SELECT COUNT(id) FROM articles WHERE MATCH (title,text) AGAINST ('*prvak*' IN BOOLEAN MODE);

Očekivano, vrijeme potrebno za izvršavanje je 0.112 sekundi. Ako uporedimo, to je oko 116 puta brže. Kažemo opet očekivano i treba imati na umu da se radi o poređenju različitih tehnika.

Relevantnost rezultata

Bez upotrebe FTS i za primjer strukture tabele koji koristimo, jedino je moguće da rezultate pretrage sortiramo po title, text ili date_time koloni, po ASC ili DESC redoslijedu. Za neke slučajeve je sasvim zadovoljavajuće da se rezultati pretrage sortiraju po recimo redoslijedu objave (npr. ako pretražujete sportski portal), ali ako se radi o stručnim tekstovima ili proizvodu, onda je neophodno korisnicima ponuditi relevantnije rezultate pretrage.

Korištenjem FTS u MySQL dostupna je ocjena relevantnosti, koja se upravo može koristiti za sortiranje. Spomenuta ocjena relevantnosti predstavlja pozitivne decimalne vrijednosti, gdje vrijednost 0 predstavlja rezultat pretrage koji uopće nije relevantan. Ocjena relevantnosti zasniva se na nizu faktora, uključujući i koliko često se taj pojam koji se pretražuje nalazi u određenom članku i koliko ostalih članaka sadrži taj pojam.

Da bi dobili rezultat 10 najrelevantnijih tekstova koji sadrže riječ prvak, izvršimo naredni SQL upit:

SELECT id, title, MATCH (title,text) AGAINST ('*prvak*' IN BOOLEAN MODE) as score FROM articles WHERE MATCH (title,text) AGAINST ('*prvak*' IN BOOLEAN MODE) ORDER BY score DESC LIMIT 10;

Primijetite MATCH (title,text) AGAINST ('*prvak*' IN BOOLEAN MODE) as score kao novu kolonu u rezultatu upita, te ORDER BY score, zapravo gdje se dešava sortiranje po relevantnosti. Vrijeme izvršavanja upita je 0.121 sekundi.

Ako vas zanima kako tačno MySQL izračuna ocjenu relevantnosti, poboljšate rezultate pretrage za vaš praktičan projekat, te da upoznate sve mogućnosti (ali i ograničenja) MySQL Full Text Search, svakako istražite detaljnije MySQL 5.6 dokumentaciju.