Wyszukiwanie pełnotestowe w relacyjnych bazach danych

Przygotowanie środowiska

Zanim zaczniemy się przyglądać różnicom w wyszukiwaniu za pomocą klauzuli LIKE oraz wyszukiwaniu pełnotekstowemu. Konieczne jest przygotowanie środowiska na którym będziemy mogli pracować.

W tym celu założyłem tabelę, która będzie przechowywała listę produktów.

CREATE TABLE `products` (
    `id` INT NOT NULL AUTO_INCREMENT, 
    `title` VARCHAR(255) NOT NULL,
    `producer_name` VARCHAR(255) NOT NULL,
    `description` TEXT NOT NULL,
    PRIMARY KEY (`id`)
);

Zanim zaczniemy wyszukiwać potrzebujemy rekordów po których mogli byśmy szukać. Poniżej znajdziesz zapytanie dodające nieduży zbiór produktów. Tak mała liczba ma jedynie na celu pokazanie jak działa wyszukiwanie, gdyby tych produktów było kilka tysięcy ciężko by było wychwycić różnice.

INSERT INTO `products` (`id`, `title`, `producer_name`, `description`) VALUES
(2, 'Apple iPhone XS 4 GB / 64 GB złoty', 'Apple', 'Oryginalny Smartfon Apple iPhone XS 64GB Dostępne wersje kolorystyczne:  Gold (Złoty) Space Grey (Gwiezdna Szarość) Silver (Srebrny) Prosimy o wskazanie wybranego koloru w komentarzu do zamówienia. W przypadku braku informacji zostanie wysłany losowy kolor telefonu.  Zestaw zawiera: Oryginalny telefon marki Apple iPhone XS 64GB Gwarancja na produkt Ładowarka producenta TelForceOne Kabel Lightning producenta TelForceOne Kluczyk do tacki na kartę SIM Pudełko ekologiczne z naszym logiem W zestawie z telefonem otrzymujesz w pełni oryginalne i certyfikowane akcesoria, które gwarantują w pełni bezpieczne użytkowanie telefonu.  W prezencie otrzymujesz: Ładowarka producenta TelForceOne Kabel Lightning producenta TelForceOne Darmowe wsparcie techniczne po zakupie Idealnie zabezpieczona paczka do transportu Możliwość darmowego montażu szkła (wyłącznie po zakupie szkła hartowanego)'),
(3, 'Apple iPhone 6 1 GB / 16 GB szary', 'Apple', 'Apple iPhone 6 iPhone 6 imponuje nie tylko rozmiarem — jest lepszy pod każdym względem. Większy, a wyraźnie smuklejszy. Potężniejszy, ale niesłychanie energooszczędny. A jego gładka metalowa powierzchnia niezauważalnie łączy się z nowym wyświetlaczem Retina HD. To jednolita, harmonijna konstrukcja, w której sprzęt doskonale współpracuje z oprogramowaniem, dając początek nowej generacji iPhone\'ów — lepszej w każdym calu.'),
(4, 'Apple iPhone 8 2 GB / 64 GB złoty', 'Apple', 'Oryginalny smartfon - Apple iPhone 8 64GB iPhone 8 ma całkowicie nową szklaną obudowę. Udoskonalony najpopularniejszy aparat na świecie. Najpotężniejszy i najinteligentniejszy procesor w historii tych smartfonów. Ładowanie bezprzewodowe łatwe jak nigdy. I nowe, dotąd niemożliwe zastosowania rozszerzonej rzeczywistości. Oto iPhone nowej generacji.  Parametry podstawowe: Producent: Apple Model: iPhone 8 Procesor: Apple A11 64-bit Ekran: Retina 4,7\", IPS Kamera: iSight 12 Mpix 2 GB pamięci RAM 64GB pamięci wbudowanej Wbudowany czytnik linii papilarnych Touch ID System operacyjny iOS 3D Touch Fotografie typu \"Live\" LTE, 4G, NFC Wodoszczelny Ładowanie bezprzewodowe'),
(5, 'Apple iPhone 12 4 GB / 64 GB czerwony', 'Apple', 'Oryginalny Apple iPhone 12 64GB Telefon Nowy  Dostępny w kolorach:  Red (czerwony) Blue (niebieski) White (biały) Green (miętowy) Prosimy po zakupie koniecznie poinformuj nas jaki kolor wybierasz (w wiadomości do sprzedającego) - to znacznie przyspieszy realizację zamówienia.  Na życzenie klienta oferujemy możliwość wykonania zdjęć każdego egzemplarza telefonu oraz sprawdzenie miejsca produkcji po numerze IMEI.  Wraz z zakupem na naszej aukcji oferujemy Państwu darmowe wsparcie techniczne naszych doświadczonych doradców oraz profesjonalną obsługę serwisową.  W skład zestawu wchodzi: Oryginalne pudełko nowy oryginalny kabel USB lightning kluczyk do tacki na kartę sim Na naszych aukcjach posiadamy akcesoria takie jak słuchawki bezprzewodowe, szkła na ekran, etui , ładowarki indukcyjne czy uchwyty samochodowe, na które oferujemy atrakcyjne rabaty przy zakupach w zestawie.'),
(6, 'Samsung Galaxy S10 8 GB / 128 GB czarny', 'Samsung', 'Smartfon - Samsung Galaxy S10 128GB Dostępne wersje kolorystyczne:  Prism Black (czarny) Prism White (biały) Prism Red (czerwony) Prism Silver (wielokolorowy) PAMIĘTAJ! KONIECZNIE POINFORMUJ NAS O SWOIM WYBRANYM KOLORZE.  Prosimy o przekazanie nam informacji o wybranym kolorze telefonu w wiadomości dla sprzedającego. W przypadku braku takiej informacji, kolor telefonu do wysyłki zostaje wybierany losowo. GRATISY DO ZAMÓWIENIA:  Ładowarka sieciowa oraz kabel usb najwyższej jakości, Darmowe wsparcie techniczne po zakupie, Profesjonalna obsługa serwisowa w naszych serwisach, Perfekcyjnie zabezpieczona paczka do transportu.'),
(7, 'Samsung Galaxy S9+ 6/64 GB W RÓŻNE KOLORY!', 'Samsung', 'Samsung Galaxy S9+ 6/64 GB W RÓŻNE KOLORY! TELEFON BEZ SIMLOCKA, DZIAŁA W KAŻDEJ SIECI. NIE MA ŻADNYCH BLOKAD.  WERSJA EUROPEJSKA oznacza to że telefon można aktualizować i posiada pełny język polski.  TELEFON WYPRZEDAŻOWY. W zestawie znajduje się telefon z foliami na ekranie i obudowie + zastępcze (pudełko, ładowarkę i kabel do ładowania).  Posiada folie na ekranie i obudowanie  Pełne polskie Menu- DYSTRYBUCJA EUROPEJSKA - Z PEŁNĄ AKTUALIZACJĄ  Akcesoria z Certyfikatem CE  Wersja Europejska z możliwością pełnej aktualizacji'),
(8, 'Biały Smartfon SAMSUNG Galaxy A12 4-64GB', 'Samsung', 'Smartfon SAMSUNG Galaxy A12 4/64GB 6.5\" Biały SM-A127  Lampa LED: Tak Wersja systemu: Android 10 Pamięć RAM: 4 GB Pamięć wbudowana [GB]: 64 Dual SIM: Tak Pojemność akumulatora [mAh]: 5000 Komunikacja: Wi-Fi, NFC, Bluetooth 5.0, USB C Procesor: Mediatek MT6765, Ośmiordzeniowy Wyświetlacz: 6.5\", 1600 x 720px, TFT Aparat: Tylny 48 Mpx + 5 Mpx + 2 x 2 Mpx, Przedni 8 Mpx Indeks: 429077  Większy ekran, to więcej miejsca do zabawy Nowy smartfon Galaxy A12 został wyposażony w duży wyświetlacz pozwalający łatwiej przeglądać treści. Rozszerz swój widok na 6,5-calowy* wyświetlacz Infinity-V i zobacz, czego Ci brakowało. Dzięki technologii HD + to, co oglądasz - jest wyraźne jak nigdy.'),
(9, 'SMARTFON SAMSUNG GALAXY S10e 128GB / KOLORY', 'Samsung', 'Oryginalny Smartfon Samsung Galaxy S10e 128Gb  ZESTAW PREMIUM | SAMSUNG GALAXY S10E 128GB ZESTAW ZAWIERA:  Oryginalny telefon marki Samsung Galaxy S10e 128Gb Nową ładowarkę zamienną Czarne pudełko zamienne EKO Perfekcyjnie zabezpieczoną paczkę na czas transportu Darmowe wsparcie techniczne po zakupie Profesjonalną obsługę serwisową (współpracujemy tylko z autoryzowanymi serwisami) DOSTĘPNE WARIANTY KOLORYSTYCZNE:  BLACK (czarny) YELLOW (żółty) SILVER (srebrny) BLUE (niebieski) Poinformuj nas w wiadomości do sprzedającego który kolor wybierasz (w wiadomości dla sprzedającego).'),
(10, 'Xiaomi Redmi Note 10s', 'Xiaomi', 'Nowy Xiaomi Redmi Note 10s  6/64GB Dostępny w kolorach:  Ocean Blue  Dane techniczne: Pamięć wbudowana 64GB Pamięć Ram 6GB Aparat przód 13 mpx Aparat tył 64 mpx Bateria li-po 5000 mAh szybkie ładowanie Quick charge 3.0 Wyświetlacz Super Amoled Zegar procesora: 2.05 GHz Liczba rdzeni: 8 Informacje na temat oprogramowania brak blokady SIMLOCK polskie menu polski język system operacyjny Android'),
(11, 'Smartfon Xiaomi Redmi Note 10 Pro 6/128GB szary', 'Xiaomi', 'Redmi Note 10 Pro  6/128GB Onyx Grey Topowy Redmi Note wraca po raz dziesiąty! Imponujący, czterosensorowy aparat 108MP uchwyci więcej niż dotychczas. Starannie wykonana, odporna na zachlapania obudowa, bardzo pojemna bateria 5020mAh i szybkie ładowanie 33W.  Redmi Note 10 Pro 108 MP Podróżnik 120Hz AMOLED · szybkie ładowanie 33W Przełamuj granicę z serią Redmi Note 10 Od Antarktydy po kosmos, seria Redmi Note podbiła świat. Postawą jest ciągłe rzucanie wyzwań i przekraczanie oczekiwań. Teraz Twoja kolej! Rzuć wyzwanie swoim granicom i dowiedz się, do czego jesteś zdolny. Zmierz się z granicami dzięki 108 MP Flagowy system obrazowania, super wyraźny, super ekscytujący  108 MP Kamera główna 8 MP Aparat ultraszerokokątny 5 MP Kamera makro 2 MP Czujnik głębi');

Z tak przygotowanym środowiskiem możemy iść dalej.

Dlaczego LIKE nie wystarczy

Na pewno kiedyś lub obecnie Twoje wyszukiwarka wykorzystywała klauzulę LIKE. Jest to bardzo fajne rozwiązanie, łatwo zrozumieć jak taka wyszukiwarka działa, a jej napisanie zajmuje chwilę. Niestety jak każde rozwiązanie i to posiada pewne wady. Poniżej znajdziesz listę wad, które z mojego punktu widzenia po pewnym czasie wykluczają ten sposób wyszukiwania.

Podwód 1 - wyszukiwanie złożone

Wyszukiwanie oparte o LIKE nie nadaje się do złożonego wyszukiwania. Już mówię o co mi dokładnie chodzi. W uproszczeniu możemy powiedzieć, że LIKE bierze tekst z danego pola i sprawdza czy fraza którą szukamy pasuje do jakiegoś fragmentu tekstu. O ile otoczyliśmy frazę znakiem procenta. Załóżmy, że użytkownik wpisał w pole wyszukiwania frazę s9. To w praktyce oznacza, że nasza aplikacja stworzy zapytanie SQL, które będzie wyglądało następująco:

SELECT * FROM `products` WHERE `title` LIKE '%s9%'

Zapytanie to przełoży się na wyszukiwanie wykonane przez bazę danych, które moglibyśmy zwizualizować w poniższy sposób.

Pierwszy procent zastąpił fragment Samsung Galaxy, następnie znajdujemy s9, a wszystko po naszej frazie, czyli + 6/64GB W RÓŻNE KOLORY! zostaje zastąpione przez drugi znak procenta.

Mam nadzieję, że rozumiecie, jak działa to wyszukiwanie. Teraz rozszerzmy frazę, którą chce wyszukać użytkownik. Nasz nowa fraza to samsung s9 co przełoży się na zmianę zapytania.

SELECT * FROM `products` WHERE `title` LIKE '%samsung s9%'

W tym przypadku nie otrzymamy żadnego wyniku. Co jest zrozumiałe, ponieważ wyszukiwana jest fraza samsung s9, której nie znajdziemy w naszym tytule. Jednak użytkownika miał inną intencję, chciał znaleźć telefon samsung s9 i nie bardzo go interesuje, jak my wyszukujemy. Możemy za pomocą kodu zmodyfikować oryginalną frazę użytkownika i zastąpić spacje znakiem % (procenta).

SELECT * FROM `products` WHERE `title` LIKE '%samsung%s9%'

Takie zapytanie zwróci nam jakiś wynik wyszukiwania. Niestety wpisanie tej frazy w odwrotnej kolejności, czyli s9 samsung uniemożliwi znalezienie jakiegokolwiek wyniku.

SELECT * FROM `products` WHERE `title` LIKE '%s9%samsung%'

Oczywiście na poziomie kodu jesteśmy w stanie wygenerować odpowiednie zapytanie. Tylko jak dużo haków będziemy musieli zastosować, żeby nasza wyszukiwarka działała dobrze ?

Podwód 2 - niska wydajność

Ten problem pojawia się dopiero przy większej ilości rekordów do przeszukania. Mając kilkaset rekordów pewnie nie zauważymy spadku wydajności przy wyszukiwaniu. Jednak w przypadku, gdy rekordów są setki tysięcy to zaczniemy szukać optymalizacji.

Największy problem wydajnościowy pojawia się, gdy nasze zapytanie wykorzystujące LIKE zaczyna się i kończy znakiem procenta.

SELECT * FROM `products` WHERE title LIKE '%szary%'

Podwód 3 - które wyniki wyszukiwania są lepsze

Kolejny problem, którego na początku możemy nie dostrzegać to ocena wyniku wyszukiwania. Mam tutaj na myśli, że LIKE nie zwraca nam informacji jak dobrze dany rekord pasuje do zapytania.

Weźmy jako przykład wyszukiwanie frazy iPhone 6

SELECT * FROM `products` WHERE title LIKE '%iphone%6%'

Wyniki wyszukiwania mają sens, silnik znalazł wszystkie rekordy, które w tytule mają słowo iphone oraz 6.

Tylko jedne wyniki są lepsze od innych. Nam jako ludziom jest dość łatwo to ocenić, silnikowi bazodanowemu bez indeksu wyszukiwania pełnotestowemu już niekoniecznie.

Powód 4 - brak odmian

Ostatnią, ale nie jedyną wadą wyszukiwania LIKE, jest brak odmian. Czyli ma znaczenie, w jakiej formie zostanie wpisane słowo podczas wyszukiwania. Wpisanie czerwony to nie to samo co wpisanie czerwona. Z tym wyszukiwanie LIKE sobie nie poradzi.

Wyszukiwanie pełnotekstowe

Skoro wiemy, że wyszukiwanie za pomocą LIKE ma ograniczenia, to czy relacyjne bazy danych są w stanie wyszukiwać lepiej ? Otóż są i odpowiedzią na tę potrzebę jest wyszukiwanie pełnotekstowe.

W odróżnieniu od wyszukiwania za pomocą klauzuli LIKE, wyszukiwanie pełnotekstowe rozbija tekst na słowa z których buduje indeks. Później dzięki indeksowi jest w stanie wyszukiwać znacznie lepiej i szybciej niż wyszukiwanie za pomocą LIKE.

Tworzenie indeksu pełnotekstowego

Aktualnie tabela z listą produktów nie mam dodanego indeksu pełnotekstowego, dopiero muszę go dodać. Można to zrobić na kilka sposobów.

Zanim jednak przejdziemy do tworzenia indeksów. Należy znać o jedno ograniczenie. Zakładając indeks na kilku kolumnach nie oznacza to, że możemy szukać pełnotekstowo tylko w jednej z nich. Co w praktyce oznacza, że jeśli założę indeks na kolumnie tytuł i opis to muszę przeszukiwać obie kolumny. Gdybym chciał szukać tylko w jednej to muszę założyć dodatkowe indeksy na kolumny które chce przeszukiwać.

ALTER TABLE

Najprostszym sposobem jest wykorzystanie ALTER TABLE zwłaszcza, gdy tabela do której chcemy dodać indeks już istnieje. Prototyp zapytania jest bardzo prosty.

ALTER TABLE table_name ADD FULLTEXT(column_name_1, column_name_2,…)

Poszczególne elementy oznaczają:

• table_name, nazwa tabeli
• ADD FULLTEXT, dodaj indeks pełnotekstowy,
• column_name_1, column_name_2,…, lista kolumn, która ma być zaindeksowana,

Mając wiedzę jak powinno wyglądać zapytanie, możemy napisać własne.

ALTER TABLE `products` ADD FULLTEXT(`title`, `description`);

W powyższym zapytaniu widzimy, że do tabeli products został dodany indeks pełnotekstowy. Indeksy ten obejmuje dwie kolumny: title oraz description. Celowo pominąłem kolumnę producer_name, gdyż na jej przykładzie zobaczymy jaki błąd zostanie wygenerowany, przy próbie wyszukiwania pełnotestowego na kolumnie nie objętej indeksem.

Dodatkowo zakładam kolejny indeks na kolumnie title. Przyda się, aby porównać wyszukiwanie pełnotekstowe z klauzulą LIKE.

ALTER TABLE `products` ADD FULLTEXT(`title`);

CREATE FULLTEXT INDEX

Drugim sposobem na dodanie indeksu pełnotekstowego do tabeli, jest założenie indeksu CREATE FULLTEXT INDEX. Prototyp takiego zapytania wygląda następująco.

CREATE FULLTEXT INDEX index_name ON table_name(column_name_1, column_name_2,…)

I tu poszczególne elementy są niemal identyczne jak w przypadku prototypu ALTER TABLE.

• CREATE FULLTEXT INDEX załóż indeks pełnotekstowy,
• index_name, nazwa indeksu,
• table_name, nazwa tabeli,
• column_name_1, column_name_2,…, lista kolumn

Skoro wiemy do czego służą poszczególne składowe to możemy stworzyć własne zapytanie.

CREATE FULLTEXT INDEX search_full ON products(`title`, `description`)

Oczywiście my już mamy indeks założony przez ALTER TABLE‌ i nie powinniśmy zakładać kolejnego indeksu na te same kolumny. Powód jest prozaicznie prosty, indeksy zajmują miejsce na dysku, a indeksy pełnotekstowe bardzo szybko się rozrastają.

CREATE TABLE

Ostatnim sposobem na dodanie indeksu jest dodanie go w momencie tworzenia tabeli. Zdajemy sobie jednak sprawę, że taką możliwość mamy tylko w momencie tworzenia nowej tabeli. Więc poprzednie opcje będą zapewne bardziej przydatne.

CREATE TABLE `products` ( 
    `id` INT NOT NULL AUTO_INCREMENT , 
    `title` VARCHAR(255) NOT NULL , 
    `producer_name` VARCHAR(255) NOT NULL , 
    `description` TEXT NOT NULL , 
    PRIMARY KEY (`id`),
    FULLTEXT KEY (`title`, `description`)
);

Na samym końcu mamy zapis FULLTEXT KEY (title, description), który spowoduje założenie indeksu w momencie założenia tabeli.

Niezależnie który sposób wybierzesz, efekt będzie dokładnie ten sam. W tabeli products będziemy mogli wyszukiwać pełnotekstowo.

Wyszukiwanie pełnotekstowe w MySQL / MariaDB

Znamy ograniczenia wyszukiwania za pomocą LIKE, zobaczy czy wyszukiwanie pełnotekstowe jest lekarstwem na te problemy.

Wyszukiwanie pełnotekstowe dostarcza nam dwie metody wyszukiwania:

• Natural Language, wyszukiwanie naturalne oparte o wpisaną frazę,
• Boolean, wyszukiwanie bazujące na wyszukiwaniu naturalnym dostarczające dodatkowe modyfikatory umożliwiające wpływanie na wyniki wyszukiwania.

Niezależnie od metody z której skorzystamy, musimy poznać dwa elementy zapytania SQL, które są wykorzystywane przy wyszukiwaniu pełnotekstowym.

• MATCH, zawiera listę kolumn po których należy wyszukiwać,
• AGAINST, zawiera frazę którą chcemy znaleźć.

Natural Language Full-Text Searches

Zaczniemy od wyszukiwania naturalnego, gdzie właściwie jedyne co robimy to przekazujemy do zapytania frazę wpisaną przez użytkownika.

I zobaczmy jak będą wyglądały wyniki dla fraz które testowaliśmy z wykorzystaniem klauzuli LIKE.

SELECT * FROM products 
WHERE MATCH (`title`) AGAINST('s9' IN NATURAL LANGUAGE MODE)

Wynik wyszukiwania Cię zaskoczył ?? I słusznie, ale niestety nie ma co się dziwić, otóż domyślne ustawienia indeksowania pełnotestowego mają minimalne ograniczenie długości słowa. W przypadku Innodb są to słowa, których minimalna ilość znaków to 3. Oczywiście możemy to zmienić, zmieniając wartość parametru innodb_ft_min_token_size.

Znając ograniczenie zobaczmy czy otrzymamy jakikolwiek wynik dla frazy s10, która ma minimalną wymaganą długość.

SELECT * FROM products 
WHERE MATCH (`title`) AGAINST('s10' IN NATURAL LANGUAGE MODE)

Mamy jeden wynik ponieważ tylko jeden z produktów spełnił podany warunek. Ale zobaczmy, czy wyszukiwanie pełnotekstowe rzeczywiście rozwiązuje problemy z którymi nie radzi sobie LIKE.

Złożone wyszukiwanie

Pierwszym problemem było wyszukiwanie złożone, czyli brak możliwości obsłużenia przypadków gdzie kolejność słów była odwrócona.

Zacznijmy od frazy samsung s10 z którą LIKE byłby w stanie sobie poradzić.

SELECT * FROM products 
WHERE MATCH (`title`) AGAINST('samsung s10' IN NATURAL LANGUAGE MODE)

Wyszukiwarka pełnotestowa też sobie radzi, można by żec że gorzej bo zwróciła zbyt dużo wyników. Jednak o tym porozmawiamy za chwilę.

Pytanie czy wyszukiwarka poradzi siebie, gdy odwrócimy kolejność słów we frazie s10 samsung. Z tym już LIKE sobie by nie poradził.

SELECT * FROM products 
WHERE MATCH (`title`) AGAINST('s10 samsung' IN NATURAL LANGUAGE MODE)

Mamy 4 wyniki wyszukiwania, czyli 1:0 dla wyszukiwania pełnotekstowego.

Wydajność

Tu także wygrywa wyszukiwanie pełnotekstowe dzięki specjalnemu indeksowi, który jest tworzony. Dzięki rozbiciu tekstu na słowa ( tokeny ), wyszukiwarka jest dużo bardziej wydajna.

Określanie które wyniki wyszukiwania są lepsze

Jak można łatwo zauważyć przy wyszukaniu frazy samsung s10 otrzymaliśmy więcej wyników wyszukiwania niż bym się spodziewali. Powód jest banalny, wyszukiwarka oparta o naturalne wyszukiwanie szuka wystąpienia choćby jednego z podanych słów. Więc dla naszej frazy można by było to przetłumaczyć na polecenie szukaj samsung lub s10.

Jednak to co jest ważne, to określanie poziomu zgodności znalezionego rekordu z wyszukiwaną frazą. Coś czego LIKE nie potrafił zrobić. My możemy zobaczyć ten poziom poprzez dodanie tej samej klauzuli do listy kolumn do wyświetlenia.

SELECT 
  `title`, 
  MATCH (`title`) AGAINST('s10 samsung' IN NATURAL LANGUAGE MODE) _score 
FROM 
  `products` 
WHERE 
  MATCH (`title`) AGAINST('s10 samsung' IN NATURAL LANGUAGE MODE)

Dzięki dodaniu do listy kolumn klauzuli MATCH (title) AGAINST('s10 samsung' IN NATURAL LANGUAGE MODE) dowiemy się, jak poszczególne rekordy zostały ocenione.

Na podstawie oceny rekordy zostały automatycznie posortowane i pierwszy rekord zawierający oba słowa jest na pierwszym miejscu. Więc idealnie, wyszukiwanie pełnotekstowe 3:0.

Odmiany słów

Odmiany językowe są czymś co jest dostępne, ale niestety natywnie wspierany jest język angielski i jeszcze nie widziałem wsparcia dla języka polskiego.

Boolean Full-Text Searches

Wyszukiwanie pełnotekstowe oparte o naturalne wyszukiwanie nie pozwala na dodawanie modyfikatorów, które mogą znacznie nam rozszerzyć możliwości.

Zacznijmy od naszego ostatniego wyszukiwania ze zmianą trybu.

SELECT 
  `title`, 
  MATCH (`title`) AGAINST('s10 samsung' IN BOOLEAN MODE) _score 
FROM 
  `products` 
WHERE 
  MATCH (`title`) AGAINST('s10 samsung' IN BOOLEAN MODE)

Skoro tryb ten bazuje na NATURAL LANGUAGE MODE to nie ma co się dziwić, że otrzymujemy praktycznie ten sam wynik. Jednak w przypadku BOOLEAN MODE możemy powiedzieć, że dane słowo jest wymagane lub ma zostać wykluczone.

Robimy to za pomocą operatorów:

• + słowo jest wymagane,
• - słowo ma zostać wykluczone,

Operator są bardzo naturalne i nawet osoby nie znające wyszukiwania pełnotekstowego będą w stanie zrozumieć co autor miał na myśli.

SELECT 
  `title`, 
  MATCH (`title`) AGAINST('+s10 +samsung' IN BOOLEAN MODE) _score 
FROM 
  `products` 
WHERE 
  MATCH (`title`) AGAINST('+s10 +samsung' IN BOOLEAN MODE)

W wyniku zapytania otrzymamy tylko jeden rekord. Oczywiście stosując operator odwrotny, czyli - (minus) możemy coś wykluczyć np. szukamy telefonów apple, ale nie chcemy koloru szarego.

SELECT 
  `title`, 
  MATCH (`title`) AGAINST('apple -szary' IN BOOLEAN MODE) _score 
FROM 
  `products` 
WHERE 
  MATCH (`title`) AGAINST('apple -szary' IN BOOLEAN MODE)

Wyszukiwanie frazy

Ciekawą opcją jest także możliwość wyszukania konkretnej frazy. Robimy to poprzez zapisanie frazy w cudzysłowiu, co także wydaje się dość naturalne.

Co ważne w tym przypadku nie obowiązuje ograniczenie długości słów. Więc mogę wyszukać frazę iphone xs i zostanie znaleziony odpowiedni rekord.

SELECT 
  `title`, 
  MATCH (`title`) AGAINST('"iphone xs"' IN BOOLEAN MODE) _score 
FROM 
  `products` 
WHERE 
  MATCH (`title`) AGAINST('"iphone xs"' IN BOOLEAN MODE)

Określanie ważności słów

Kto by nie chciał móc wpłynąć na wyniki wyszukiwania, poprzez podanie które słowa są ważniejsze od innych. I dzięki dwóm operatorom mamy taką możliwość.

• > zwiększa ważność słowa,
• < zmniejsza ważność słowa,

Powiedzmy, że na nasze potrzeby będziemy bardziej promować telefony w kolorze złotym. W związku z czym, jeśli wykryjemy ten kolor we frazie użytkownika to dodajemy do niej > zwiększenie poziomu ważności.

SELECT 
  `title`, 
  MATCH (`title`) AGAINST('>złoty lub czarny' IN BOOLEAN MODE) _score 
FROM 
  `products` 
WHERE 
  MATCH (`title`) AGAINST('>złoty lub czarny' IN BOOLEAN MODE)

Analogicznie możemy w wynikach wyszukiwania zmniejszać poziom ważności jakiegoś słowa.

SELECT 
  `title`, 
  MATCH (`title`) AGAINST('<złoty lub czarny' IN BOOLEAN MODE) _score 
FROM 
  `products` 
WHERE 
  MATCH (`title`) AGAINST('<złoty lub czarny' IN BOOLEAN MODE)

I jak widzimy kolor złoty tym razem jest niżej w wynikach wyszukiwania. Dzięki dostępowi do tych operatorów mamy pewien wpływ na wyniki wyszukiwania. Brakuje mi tutaj tylko możliwości zdefiniowania poziomu ważności poza samym określeniem, że dane słowo jest ważniejsze od innego.

wildcard

Ostatnim operatorem o którym sobie powiemy jest wildcard, czyli magiczna gwiazdka zastępująca dowolny ciąg znaków. I tu pewnie was nie zaskoczę, bo dzięki temu operatorowi możemy wyszukiwać słowa na podstawie wpisanego fragmentu.

Jest to przydatne przy auto uzupełnianiu, gdzie nie mamy pewności czy wpisane przez użytkownika słowo jest kompletne.

SELECT 
  `title`, 
  MATCH (`title`) AGAINST('samsu*' IN BOOLEAN MODE) _score 
FROM 
  `products` 
WHERE 
  MATCH (`title`) AGAINST('samsu*' IN BOOLEAN MODE)

Wyniki zgodne z oczekiwaniami, zostały znalezione wszystkie rekordy ze słowem samsung.

Stopwords

Stopwords jest to lista tokenów, które nie mają być indeksowane. Wynika to z faktu, że same w sobie są bezużyteczne. Przynajmniej w tym kontekście, gdybym robili analizę tekstu to co innego, ale tu niepotrzebnie będą zwiększały wielkość indeksu.

Listę stopwords-ów w InnoDB możemy wyświetlić za pomocą poniższego zapytania.

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

Łatwo zauważyć, że wszystkie wartości pochodzą z języka angielskiego. Jednak nic nie stoi na przeszkodzie, aby taką listę zbudować dla języka polskiego. W tym celu będziemy potrzebowali tabeli z listą polskich stopwords-ów.

CREATE TABLE stopwords(`value` VARCHAR(255));

Dodajemy rekordy. Oczywiście należy znaleźć listę dla danego języka w internecie i ją dodać. Ja tutaj dodaję tylko jedną pozycję z języka Polskiego.

INSERT INTO stopwords SET value='i';

Ustawiamy naszą tabelę jako tą która ma być listą stopwords-ów.

SET GLOBAL innodb_ft_server_stopword_table = 'stopwords';

Po ustawieniu nowej tabeli konieczna jest przebudowa indeksu, w celu usunięcia zaindeksowanych stopwords-ów. Przebudowę indeksu wykonujemy poniższym poleceniem.

rebuild index

I od tego momentu mamy obsługę stopwords-ów dla danego języka.

Podsumowanie

Wpis ten przedstawia absolutne minimum wiedzy jaką każdy z nas powinien posiadać o wyszukiwaniu pełnotekstowym w relacyjnych bazach danych. Warto zaglądać do dokumentacji związanej z tym tematem. Co jakiś czas dochodzą kolejne rzeczy związane z wyszukiwaniem pełnotekstowym. A jeśli ten rodzaj wyszukiwania jest niewystarczający dla was, to zachęcam do zapoznania się z ElasticSearch-em, który jest silnikiem wyszukiwania pełnotekstowego dużo bardziej rozbudowanym od tych w relacyjnych bazach danych.