Prawdopodobnie wszyscy administratorzy MySQL widzieli w swoich slowlogach zapytania typu:

SELECT id, to, to, tamto,  opis FROM produkt WHERE opis LIKE '%znakomity%' AND opis LIKE  '%genialny%' ORDER BY id ASC;

Zapytania tego typu potrafią się wykonywać bardzo długo, szczególnie gdy zawartość bazy jest spora. Nie korzystają też z indeksów, co jest akurat oczywiste. Aby zadziałał indeks typu BTREE, z lewej strony nie może występować wildcard. Możemy szukać pól zaczynających się w jakiś sposób: WHERE opis LIKE ‚genialny%’, ale wyrazu w środku zdania przy pomocy indeksu nie znajdziemy. Co można zrobić, aby przyspieszyć działanie takich zapytań?

MySQL wraz z silnikiem MyISAM udostępnia mechanizm wyszukiwania pełnotekstowego. Jego działanie przeanalizujemy na danych z bazy `sakila`. Aby móc korzystać z wyszukiwania pełnotekstowego dana tabela musi działać na silniku MyISAM, na odpowiednią kolumnę musi tez zostać nałożony indeks pełnotekstowy. Nakładamy taki indeks na kolumnę `description` w tabeli `film_text`:

mysql> ALTER  TABLE film_text ADD FULLTEXT INDEX idx_description (description);
Query  OK, 1000 rows affected (0,07 sec)
Rekordów: 1000  Duplikatów: 0   Ostrzeżeń: 0

Przykładowe zapytanie może wyglądać na przykład tak:

mysql> SELECT * FROM film_text WHERE  MATCH (description) AGAINST ('redeem') AND MATCH (description) AGAINST  ('Australia');
+---------+------------------+---------------------------------------------------------------------------+
|  film_id | title            |  description                                                                |
+---------+------------------+---------------------------------------------------------------------------+
|      384 | GROSSE WONDERFUL | A Epic Drama of a Cat And a Explorer who must  Redeem a Moose in Australia |
|     430 | HOOK CHARIOTS    | A  Insightful Story of a Boy And a Dog who must Redeem a Boy in Australia  |
+---------+------------------+---------------------------------------------------------------------------+
2  rows in set (0,00 sec)

Jest to odpowiednik zapytania:

mysql>  SELECT * FROM film_text WHERE description LIKE '%redeem%' AND  description LIKE '%Australia%';
+---------+------------------+---------------------------------------------------------------------------+
|  film_id | title            |  description                                                                |
+---------+------------------+---------------------------------------------------------------------------+
|      384 | GROSSE WONDERFUL | A Epic Drama of a Cat And a Explorer who must  Redeem a Moose in Australia |
|     430 | HOOK CHARIOTS    | A  Insightful Story of a Boy And a Dog who must Redeem a Boy in Australia  |
+---------+------------------+---------------------------------------------------------------------------+
2  rows in set (0,00 sec)

Różnicą jest to, że pierwsze zapytanie wykorzystuje indeks:

mysql> EXPLAIN SELECT *  FROM film_text WHERE MATCH (description) AGAINST ('redeem') AND MATCH  (description) AGAINST ('Australia');
+----+-------------+-----------+----------+-----------------+-----------------+---------+------+------+-------------+
|  id | select_type | table     | type     | possible_keys   |  key             | key_len | ref  | rows | Extra       |
+----+-------------+-----------+----------+-----------------+-----------------+---------+------+------+-------------+
|   1 | SIMPLE      | film_text | fulltext | idx_description |  idx_description | 0       |      |    1 | Using where |
+----+-------------+-----------+----------+-----------------+-----------------+---------+------+------+-------------+
1  row in set (0,00 sec)

W dokumentacji MySQL znaleźć można sporo informacji na temat możliwości wyszukiwania pełnotekstowego wbudowanego w MyISAM. W szczególności, może ono być stosowane w różnych trybach – Natural Language (domyślnym) i Boolean. Pierwszy, naturalny tryb przeszukuje dokument pod kątem podanej frazy. Tryb Boolean umożliwia dodanie do wyszukania warunków AND, OR, NOT, wprowadza możliwość wyszukiwania całej frazy, stosowania wildcardów i tak dalej. Przykładowym zapytaniem z wykorzystaniem tego trybu będzie poniższy SELECT wyszukujący w kolumnie `description` rekordy, w których występuje słowo „Story”, występuje fraza „Redeem a Boy” a nie pojawia się słowo „Sumo”:

mysql> SELECT * FROM film_text WHERE MATCH  (description) AGAINST ('+Story -Sumo +"Redeem a Boy"' IN BOOLEAN MODE);
+---------+---------------+--------------------------------------------------------------------------+
|  film_id | title         |  description                                                               |
+---------+---------------+--------------------------------------------------------------------------+
|      430 | HOOK CHARIOTS | A Insightful Story of a Boy And a Dog who must  Redeem a Boy in Australia |
+---------+---------------+--------------------------------------------------------------------------+
1  row in set (0,01 sec)

Zaletą wyszukiwania pełnotekstowego jest to, że umożliwia skalowanie się bazy ponad wielkość kilkuset megabajtów. Przy takich ilościach danych wyszukiwanie przez WHERE … LIKE, które łączy się z pełnym skanem tabeli, jest praktycznie niemożliwe. Wadą jest z kolei to, że jeśli wielkość bazy przekroczy kilka gigabajtów, tego typu wyszukiwanie także przestaje być wydajne. Problemem też jest to, że wyszukiwanie pełnotekstowe nie jest dostępne w InnoDB. Dodatkowo, przy dużej ilości danych, precyzyjność zwracanych wyników odbiega od ideału. Jest to powodem, dla którego przy większych bazach danych polecałbym skorzystanie z wyszukiwarek pełnotekstowych jak np. Sphinx. Takie dedykowane narzędzia potrafią przerobić ogromną ilość danych w sposób szybszy i mniej obciążający serwer fizyczny niż może zrobić to sam MySQL. Można je także wykorzystać do odciążenia serwera MySQL na różne inne sposoby (stronnicowanie na przykład), ale to temat na inny post.