Memakai Full-Text Search Di MySQL Server


Hari ini adalah hari pertama di tahun 2015. Sama seperti hari-hari sebelumnya, awan gelap dan angin kencang tidak kunjung hilang. Tidak ada yang lebih indah daripada mengawali hari pertama di tahun baru dengan segelas teh hangat sambil menulis blog. Pada artikel Memahami Eksekusi SQL di MySQL Server, saya menunjukkan bahwa query dengan pencarian floating seperti LIKE '%abc%' tidak dapat memanfaatkan index. Untuk mengatasi permasalahan tersebut, MySQL Server sejak versi 5.6.4 telah menambahkan dukungan Full-Text Index pada engine InnoDB. Full-Text Search memiliki kemampuan yang jauh lebih canggih dibandingkan dengan floating LIKE: ia akan mengurutkan record berdasarkan hasil yang paling relevan (misalnya record yang mengandung banyak jumlah kata yang dicari akan muncul di urutan awal).

Sebagai contoh, saya dapat menambahkan Full-Text Index pada kolom nama di tabel barang dengan memberikan SQL seperti berikut ini:

ALTER TABLE barang
ADD FULLTEXT INDEX idx_nama(nama ASC);

Setelah ini, saya dapat memberikan SQL yang menggunakan Full-Text Search pada kolom nama seperti:

SELECT nama FROM barang WHERE MATCH(nama) AGAINST('seal');

Query di atas akan mengembalikan seluruh nama produk yang mengandung kata 'seal'. Untuk menunjukkan bahwa index dipakai, saya dapat menggunakan EXPLAIN yang hasilnya terlihat seperti pada gambar berikut ini:

Full-Text Index Dipakai Pada Full-Text Search

Full-Text Index Dipakai Pada Full-Text Search

Agar lebih jelas, saya juga akan membandingkan perubahan kinerja yang dicapai. Saya akan mulai dengan memantau kinerja versi query yang memakai pencarian floating LIKE:

mysql> SET PROFILING=1;

mysql> SELECT SQL_NO_CACHE * FROM barang WHERE nama LIKE '%seal%';

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000108 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000715 |
| init                 | 0.000056 |
| System lock          | 0.000009 |
| optimizing           | 0.000014 |
| statistics           | 0.000037 |
| preparing            | 0.000022 |
| executing            | 0.000003 |
| Sending data         | 0.154063 |
| end                  | 0.000014 |
| query end            | 0.000010 |
| closing tables       | 0.000017 |
| freeing items        | 0.000131 |
| cleaning up          | 0.000031 |
+----------------------+----------+

mysql> SELECT * FROM barang WHERE nama LIKE '%seal%';

mysql> SHOW PROFILE FOR QUERY 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000069 |
| checking permissions | 0.000008 |
| Opening tables       | 0.000024 |
| init                 | 0.000032 |
| System lock          | 0.000010 |
| optimizing           | 0.000009 |
| statistics           | 0.000018 |
| preparing            | 0.000014 |
| executing            | 0.000004 |
| Sending data         | 0.009502 |
| end                  | 0.000013 |
| query end            | 0.000009 |
| closing tables       | 0.000014 |
| freeing items        | 0.000122 |
| cleaning up          | 0.000017 |
+----------------------+----------+

mysql> SELECT * FROM barang WHERE nama LIKE '%seal%';

mysql> SELECT * FROM barang WHERE nama LIKE '%seal%';

mysql> SELECT * FROM barang WHERE nama LIKE '%seal%';

mysql> SHOW PROFILES;
+----------+------------+------------------------------------------------------------+
| Query_ID | Duration   | Query                                                      |
+----------+------------+------------------------------------------------------------+
|        1 | 0.15523725 | SELECT SQL_NO_CACHE * FROM barang WHERE nama LIKE '%seal%' |
|        2 | 0.00986225 | SELECT SQL_NO_CACHE * FROM barang WHERE nama LIKE '%seal%' |
|        3 | 0.01032150 | SELECT SQL_NO_CACHE * FROM barang WHERE nama LIKE '%seal%' |
|        4 | 0.00977775 | SELECT SQL_NO_CACHE * FROM barang WHERE nama LIKE '%seal%' |
|        5 | 0.00983650 | SELECT SQL_NO_CACHE * FROM barang WHERE nama LIKE '%seal%' |
+----------+------------+------------------------------------------------------------+

Sekarang, saya akan membandingkannya dengan query yang memakai Full-Text Search:

mysql> SET PROFILING=1;

mysql> SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal');

mysql> SHOW PROFILE FOR QUERY 1;
+-------------------------+----------+
| Status                  | Duration |
+-------------------------+----------+
| starting                | 0.000115 |
| checking permissions    | 0.000008 |
| Opening tables          | 0.000708 |
| init                    | 0.000046 |
| System lock             | 0.000010 |
| optimizing              | 0.000013 |
| statistics              | 0.000037 |
| preparing               | 0.000013 |
| FULLTEXT initialization | 0.172336 |
| executing               | 0.000012 |
| Sending data            | 0.093124 |
| end                     | 0.000013 |
| query end               | 0.000013 |
| closing tables          | 0.000017 |
| freeing items           | 0.000150 |
| cleaning up             | 0.000022 |
+-------------------------+----------+

mysql> SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal');

mysql> SHOW PROFILE FOR QUERY 2;
+-------------------------+----------+
| Status                  | Duration |
+-------------------------+----------+
| starting                | 0.000090 |
| checking permissions    | 0.000014 |
| Opening tables          | 0.000027 |
| init                    | 0.000031 |
| System lock             | 0.000012 |
| optimizing              | 0.000009 |
| statistics              | 0.000022 |
| preparing               | 0.000010 |
| FULLTEXT initialization | 0.000320 |
| executing               | 0.000006 |
| Sending data            | 0.001038 |
| end                     | 0.000009 |
| query end               | 0.000009 |
| closing tables          | 0.000013 |
| freeing items           | 0.000141 |
| cleaning up             | 0.000018 |
+-------------------------+----------+

mysql> SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal');

mysql> SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal');

mysql> SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal');

mysql> SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                               |
+----------+------------+---------------------------------------------------------------------+
|        1 | 0.26663350 | SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal') |
|        2 | 0.00176875 | SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal') |
|        3 | 0.00201450 | SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal') |
|        4 | 0.00165800 | SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal') |
|        5 | 0.00160050 | SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal') |
+----------+------------+---------------------------------------------------------------------+

Pada hasil percobaan sederhana tersebut, terlihat bahwa bila saya mengabaikan eksekusi pertama yang lambat, maka untuk setiap query berikutnya, versi yang memakai Full-Text Search rata-rata lebih cepat 82% dibanding versi yang memakai float LIKE.

Selain melakukan pencarian pada modus Natural Language, Full-Text Search juga menyediakan modus Boolean. Sebagai contoh, saya bisa mencari kolom nama yang mengandung kata 'Seal' dan 'Set' tetapi tidak mengandung kata 'Shock' dengan query seperti berikut ini:

SELECT 
    nama
FROM
    barang
WHERE
    MATCH (nama) AGAINST ('+Seal +Set -Shock' IN BOOLEAN MODE);

Pada saat melakukan pencarian pada modus boolean, saya dapat memakai operator seperti + untuk menyertakan sebuah kata, - untuk memastikan sebuah kata tidak muncul, ( dan ) untuk pengelompokkan, dan sebagainya. Saya juga dapat menggunakan operator > dan < untuk memberikan prioritas kata yang dicari sehingga mempengaruhi urutan record yang dikembalikan. Sebagai contoh, query berikut ini:

SELECT 
    nama
FROM
    barang
WHERE
    MATCH (nama) AGAINST ('+Seal >Water' IN BOOLEAN MODE);

akan menghasilkan record yang mengandung kata 'Water' pada urutan yang lebih awal.

Full-Text Search juga memiliki modus Query Expansion yang akan melakukan pencarian dua kali. Pencarian kedua akan menghasilkan record yang kira-kira berhubungan dengan hasil pencarian pertama. Modus pencarian ini biasanya menghasilkan banyak record yang tidak relevan, tetapi bisa berguna untuk pencarian fuzzy dimana pengguna tidak tahu persis apa yang hendak dicari. Sebagai contoh, berikut adalah SQL yang memakai Query Expansion:

SELECT 
    nama
FROM
    barang
WHERE
    MATCH (nama) AGAINST ('seal water' WITH QUERY EXPANSION);

Bila saya mengerjakan query di atas, saya tidak hanya memperoleh nama yang mengandung 'water seal', tetapi juga yang berkaitan dengannya seperti 'water pump'. Pada modus Natural Language dan Boolean, saya hanya memperoleh 1 record yang benar-benar mengandung nama 'water seal'. Akan tetapi pada modus Query Expansion, saya bisa memperoleh hingga 92 record.

Full-Text Search akan mengabaikan stopword (kata yang dianggap tidak penting) pada kolom yang dicari. Secara default, stopword bawaan MySQL Server adalah:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a     |
| about |
| an    |
| are   |
| as    |
| at    |
| be    |
| by    |
| com   |
| de    |
| en    |
| for   |
| from  |
| how   |
| i     |
| in    |
| is    |
| it    |
| la    |
| of    |
| on    |
| or    |
| that  |
| the   |
| this  |
| to    |
| was   |
| what  |
| when  |
| where |
| who   |
| will  |
| with  |
| und   |
| the   |
| www   |
+-------+

Terlihat bahwa stopword terdiri atas kata-kata dalam bahasa Inggris. Bila ingin memakai tabel lain sebagai daftar stopword, saya dapat mengubah nilai variabel innodb_ft_server_stopword_table untuk merujuk pada tabel lain tersebut.

Perihal Solid Snake
I'm nothing...

One Response to Memakai Full-Text Search Di MySQL Server

  1. Ping-balik: Melakukan Query Full-Text Search Dengan Hibernate Search | The Solid Snake

Apa komentar Anda?

Please log in using one of these methods to post your comment:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: