maj 26 2008
Wyszukiwanie w bazie MySQL wykorzystujące indeksy FULLTEXT
Każdy, kto tworzy swój serwis w oparciu o bazę danych, prędzej czy później odczuje potrzebę stworzenia wyszukiwarki informacji zawartych w ów bazie. Pierwsza rzeczą po jaką sięga jest klauzula LIKE.
-
SELECT * FROM articles WHERE content LIKE "kopytko"
Niestety, takie wyszukiwanie jest powolne i nieefektywne. Nie dość, że zwraca o wiele za dużo niepotrzebnych wyników, to jeszcze w przypadku dużej bazy może wręcz ją zabić.
Jak zatem rozwiązać ten problem? Otóż na ratunek przychodzi specjalny rodzaj indeksów zwanych FULLTEXT. Mamy na przykład taką tabelę:
-
CREATE TABLE articles(
-
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
-
title VARCHAR( 100 ) ,
-
content TEXT
-
)
Polom, na których masz zamiar przeprowadzać wyszukiwanie nadajesz indeks FULLTEXT:
-
ALTER TABLE articles ADD FULLTEXT(title, content)
Zauważ, że nadałem indeks zarówno polu przechowującemu tytuł, jak i polu z treścią artykułu. Oczywiście możesz wybrać tylko jedno pole. Należy tylko uważać, żeby nasze pole nie miało typu BLOB. BLOB z założenia ma przechowywać dane binarne, a FULLTEXT nie został zaprojektowany do pracy na takich danych.
Nasza tabela jest już przystosowana, teraz czas sprawdzić jak wszystko działa w praktyce:
-
SELECT * FROM articles WHERE MATCH(title, content) AGAINST (‘kopytko’)
Taka wyszukiwarka jest o tyle inteligentna, że jeżeli wpiszemy bardzo popularne słowo, to nie otrzymamy wyniku. Na przykład, jeśli wpisaliśmy przyimek “na”, to zapewne MySQL nie zwróci nic. Otóż jeśli w bazie dane słowo występuje często, nie jest unikalne i siłą rzeczy zwracałoby wiele niereprezentatywnych wyników. W związku z czym silnik bazy je pomija.
Trafność i sortowanie
FULLTEXT sortując wyniki używa swoich punktów trafności. Oczywiście możemy im się przyjrzeć:
-
SELECT id, title, content, MATCH(title, content) AGAINST (‘kopytko’) AS scores
-
FROM articles
-
WHERE MATCH(title, content) AGAINST (‘kopytko’)
Pole scores będzie zawierało ów punkty. Zauważ, że rekordy są posortowane według scores malejąco, zatem według trafności. Te które najbardziej pasują kryteriom wyszukiwania będą wyżej.
Błędem jednak będzie pomnożenie punktów przez 100 i podanie ich jako trafności procentowej. Pole scores może zawierać liczby >1 , zatem w wielu przypadkach uzyskalibyśmy trafność większą niż 100% 
Dane pochodzące od użytkownika
Mało który użytkownik wpisuje w wyszukiwarkę jedno słowo. MySQL również jest na to przygotowane. Każde słowo zostanie wzięte pod uwagę, oddzielone od reszty i porównane z tym co jest w bazie. Jedyną rzeczą, którą powinno się zrobić to usunięcie znaków interpunkcyjnych (str_replace), które mogą przeszkodzić prawidłowemu rozdzieleniu. Chodzi chociażby o przecinki, kropki, wykrzykniki, itp.
Należy też pamiętać (jak zresztą w przypadku każdych danych pochodzących od użytkownika) o niebezpieczeństwie SQL Injection.
Nasze własne Google, czyli Boolean Mode
MySQL umożliwia również bardziej zaawansowane wyszukiwanie. Kiedy korzystamy z Google, używamy takich operatorów jak AND, OR, myślnik (tudzież minus). Nasza wyszukiwarka również może takie znaki rozpoznawać i je interpretować. Spójrzmy na przykład:
-
SELECT * FROM articles
-
WHERE MATCH(title, content) AGAINST (‘+kopytko -makowiec’ IN BOOLEAN MODE)
Powyższy przykład zwróci rekordy, w których wystąpiło słowo ‘kopytko’, ale jednocześnie nie znalazło się ‘makowiec’.
Uwaga! Jeśli przed słowem nie znalazł się żaden znak, zostanie ono potraktowane opcjonalnie (OR), ale jeśli wystąpi w danym rekordzie, rekord ten zostanie potraktowany wyżej w wynikach. To jest największa różnica w stosunku do wyszukiwarek internetowych. Jeśli wpiszemy w Google słowa oddzielone wyłącznie spacją, on potraktuje je jakby między nimi było AND. Oznacza to, że wszystkie muszą się znaleźć na wyszukanej stronie. W MySQL brak operatora oznacza OR. Możemy sobie jednak z tym poradzić wykorzystując wcześniej wspomnianą funkcję str_replace (choć bardziej przydatna w tym przypadku będzie raczej preg_replace). W ten sposób, jeśli użytkownik wpisze słowa oddzielone tylko spacją, możemy powstawiać wspomniane znaki plus. Niemniej jednak ja w swoich projektach pozostawiam tę sprawę “jak jest”. Trafność w obu przypadkach jest podobna.
A oto opisy innych operatorów:
| + | Słowo następujące musi się znaleźć w wynikach. |
| - | Słowo następujące nie może się znaleźć w wynikach. |
| ( ) | Nawiasy otaczające podwyrażenie nadają mu pierwszeństwo. |
| “ ” | Wyszukuje frazę z nawiasów dokładnie tak jak została zapisana. |
| ~ | Tylda jest rodzajem negacji, stawiana jest przed słowami niepożądanymi. Rekord, który zawiera takie słowo zostanie oceniony nisko, ale zostanie zwrócony (w przeciwieństwie do znaku minus). |
| < > | Znaki mniejszości i większości ustawione przed słowem zmieniają jego ważność, operator < zmniejsza ważność, a > zwiększa. |
| * | Stojący za częścią (tematem) wyrazu wyszuka go we wszystkich formach fleksyjnych, np. samoch* zwróci rekordy zawierające słowa samochód, samochodowy, samochody, samochodem, itd. Operator nie działa jeśli jest ustawiony przed wyrazem (na początku wyrazu). |
Jeśli chcemy, by użytkownik mógł wpisywać słowne operatory (AND, OR, NOT), możemy się wspomóc funkcją str_replace.
Wrzesień 24th, 2008 at 20:45
Fajnie opisane
Często korzystam z tego rozwiązania. Wiele osób zapomina o tym prostym rozwiązaniu pisząc serwisy pracujące pod dużym obciążeniem. Warto im o tym przypomnieć. 
Listopad 7th, 2008 at 22:22
Ciekawy i przydatny wpis. Krótko a treściwie
Kwiecień 11th, 2009 at 16:19
No bardzo, przyjaźnie opisane, zwłaszcza operatory.
Maj 13th, 2009 at 14:02
Bardzo fachowo napisane. Tego szukałem i to wykorzystam. Dobra robota
Luty 4th, 2010 at 19:43
Ok a jak zrobić żeby wyszukiwało jak użytkownik wpisze “plaszcz” zamiast “płaszcz”? Da się za pomocą MYSQL usunąć polskie znaki z rekordu zawierającego te słowa? Np. płaszcz, kurtka, szkoła – jeżeli wpisze “plaszcz” to mi tego rekordu nie znajdzie. Any idea?