Mengaplikasikan Full Text Search dengan MySQL di Ubuntu 16.04

Pendahuluan

Full-text search (FTS) adalah sebuah teknik yang digunakan oleh search engines untuk mencari sesuatu di dalam sebuah database. Teknik ini juga dapat dipakai untuk melakukan pecarian disitus-situs jual beli, berita, dll.

Lebih jelasnya, FTS akan mencari dokumen yang didalamnya terdapat data tekstual yang tidak sama persis dengan data yang dicari. Artinya saat seorang user melakukan pencarian untuk "cats and dogs", aplikasi yang menggunakan teknik FTS dapat memberikan hasil yang mengandung kedua kata (hanya "cats" atau hanya "dogs"), mengandung kata dalam urutan yang berbeda ("dogs and cats"), atau memiliki variasi kata ("cat" atau "dog"). Hasil ini memberikan aplikasi kemampuan untuk menebak apa kira-kira data yang diinginkan oleh pengguna sehingga dapat memberikan data yang lebih relevan lebih cepat.

< a href="https://www.codepolitan.com/membuat-database-dan-tabel-di-mysql-5884222be38fa">Membuat Database dan Tabel di MySQL

Database Managemen System (DBMS) seperti MySQL biasanya memiliki kemampuan untuk melakukan pencarian sebagian teks menggunakan LIKE. Namun, perintah ini cenderung memberikan hasil yang kurang bagus untuk data yang besar. Selain itu, ia juga memiliki batasan untuk input yang dapat diberikan user sehingga suatu query tidak dapat memberikan hasil yang semestinya meskipun ada dokumen dengan informasi yang relevan.

Dengan FTS, kita dapat membuat sebuah mesin pencari teks canggih tanpa menambah dependensi baru. Dalam tutorial ini, kita akan menggunakan MySQL 5.6 untuk melaukan query sebuah database menggunakan full-text search berdasarkan relevansinya dengan kata kunci lalu menampilkan hanya hasil yang paling cocok.

Prasyarat

Untuk mengikuti tutorial ini kita membutuhkan:

• Server Ubuntu 16.04 yang sudah siap dengan mengikuti tutorial ini.
• MySQL 5.6 atau lebih tinggi dengan mengikuti tutorial How To Install MySQL on Ubuntu 16.04 .

Langkah 1 — Membuat Test Data

Untuk mencoba melakukan full-text search, kita akan memerlukan beberapa data. Dilangkah yang pertama ini, kita akan membuat sebuah database bernama testdb dengan sebuah tabel bernama news, yang akan kita isi dengan beberapa data contoh untuk merepresentasikan artikel dari suatu situs berita.

Catatan:

Jika sudah memiliki tabel dengan data sendiri, pembaca bisa langsung ke langkah 2 dan melakukan perubahan seperlunya.

Pertama, akses konsol MySQL. Kita akan dimintai password root yang kita atur saat memasang MySQL.

mysql -u root -p

Setelah terhubung, prompt akan berubah menjadi mysql>.

Selanjutnya, buat sebuah database bernama testdb. Database ini akan memiliki beberapa data contoh.

CREATE DATABASE testdb;

Selanjutnya kita akan menggunakan database testdb ini.

USE testdb;

Selanjutnya, buat sebuah tabel di database bernama news dengan kolom untuk mengisi data artikel situs berita.

CREATE TABLE news (
   id INT NOT NULL AUTO_INCREMENT,
   title TEXT NOT NULL,
   content TEXT NOT NULL,
   author TEXT NOT NULL,

   PRIMARY KEY (id)
);

Mari kita bahas apa yang dilakukan perintah di atas:

• CREATE TABLE adalah perintah SQL untuk membuat tabel, mirip dengan sistem database lain.
• news adalah nama tabel.
• title, content dan author adalah kolom tanpa ada batasan panjang data.
• NOT NULL adalah deklarasi untuk menandakan kolom-kolom tersebut tidak bisa memiliki nilai null (meskipun boleh memiliki string kosong).
• id adalah indeks utama tabel dengan atribut khusus yaitu AUTO_INCREMENT, yang secara otomatis akan mengisi kolom ID.

Sekarang tambahkan beberapa data ke tabel.

INSERT INTO news (id, title, content, author) VALUES 
    (1, 'Pacific Northwest high-speed rail line', 'Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.', 'Greg'),
    (2, 'Hitting the beach was voted the best part of life in the region', 'Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.', 'Ethan'),
    (3, 'Machine Learning from scratch', 'Bare bones implementations of some of the foundational models and algorithms.', 'Jo');

Mari kita bahas apa yang dilakukan perintah di atas:

• INSERT memasukkan data.
• INTO menentukan ditabel apa data akan dimasukkan. Dalam kasus ini adalah tabel news
• (id, title, content, author) VALUES menentukan kolom apa yang akan diisi dengan masing-masing data.
• Tiga baris berikutnya adalah tiga baris data yang akan dimasukkan ke dalam tabel dan ke dalam kolom dengan urutan yang sesuai.

Setiap baris akan memiliki id unik yang akan didapatkan secara otomatis ke indeks. Indeks database adalah struktur data yang akan emningkatkan performa saat mengambil data. Indeks disimpan ditempat yang berbeda dari data utama.

Sekarang setelah kita memiliki beberapa data, kita bisa mulai melakukan query untuk mengambil data menggunakan FTS

Langkah 2 — Membuat FTS Index dan Menggunakan FTS Function

Mari kita membuat sebuah indeks bagi kolom denagn jenis data text yang kita miliki sehingga bisa menggunakan FTS.

Untuk melakukan hal tersebut kita akan menggunakan perintah khusus MySQL bernama FULLTEXT. Perintah ini memberitahu MySQL untuk menentukan kolom mana yang ingin dicari menggunakan FTS ke indeks internalnya.

ALTER TABLE news ADD FULLTEXT (title, content, author);

Perintah di atas akan mengombinasikan kolom-kolom yang ditentukan dan "mensanitasinya" (sanitizing, menghilangkan tanda baca dan membuat semua teks menjadi lowercase). Setelah indeks dibuat, ia akan mengupdate dirinys sendiri setiap ada query SQL yang mengubah tabel utama.

Selanjutnya, coba melakukan full-text search untuk kata kunci "Seatle beach" menggunakan fungsi MATCH() AGAINST().

SELECT * FROM news WHERE MATCH (title,content,author) AGAINST ('Seattle beach' IN NATURAL LANGUAGE MODE)\G

Bagian MATCH() menentukan kumpulan kolom mana yang diindeks menggunakan FTS dan harus sama dengan kumpulan kolom saat membuat indeks sebelumnya. Bagian AGAINST() menentukan kata kuncinya.

IN NATURAL LANGUAGE MODE artinya kata kunci yang diberikan datang langsung dari user tanpa ada pemrosessan sebelumnya. MySQL secara otomatis akan mengasumsikan natural language mode jadi kita tidak harus menentukannya secara manual.

Catatan: Teknik FTS lain selain natural language mode adalah word stemming yang akan membuat indeks menyisihkan imbuhan dan hanya menyimpan kata dasar. Contoh, kata "fits" dan "fitted" akan memberikan hasil sama dengan word stemming. Sayangnya, MySQL belum mendukung word stemming. Stemming ada di MySQL worklog, tapi belum ada waktu pasti kapan ia akan diimplementasi dan dirilis. Jika masing ingin menggunakan word stemming, ada sebuah library bernama Snowball.

< a href="https://www.codepolitan.com/aplikasi-crud-java-sederhana-menggunakan-database-mysql">Aplikasi CRUD Java Sederhana Menggunakan Database MySQL

Tanda \G diakhir query di atas membuat setiap kolom dari hasil yang didapatkan dicetak di baris baru. Opsi ini membuat pesan keluaran yang cukup panjang menjadi lebih mudah di baca. Berikut hasil dari perintah di atas:

*************************** 1. row ***************************
     id: 1
  title: Pacific Northwest high-speed rail line
content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
 author: Greg
*************************** 2. row ***************************
     id: 2
  title: Hitting the beach was voted the best part of life in the region
content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
 author: Ethan
2 rows in set (0.00 sec)

Dikedua hasil di atas tidak ada "Seattle beach", tapi karena kita menggunakan full-text search, kita tetap mendapatkan dua hasil: row pertama hanya memiliki kata "Seattle", dan row kedua hanya memiliki kata "beach". Coba untuk menggunakan kata kunci lain untuk mendapatkan hasil yang berbeda.

Sekarang karena menggunakan fungsi FTS di query SQL untuk mencari hasil yang relevan, kita dapat membuat hasilnya menjadi lebih relevan lagi.

Now that you can use FTS functions in SQL queries to find the rows relevant to a search input, you can make those results more relevant.

Langkah 3 — Mendapatkan Hasil FTS yang Lebih Relevan

Ada dua teknik yang dapat membantu kita membuat hasil FTS menjadi lebih relevan. Yang pertama adalah mem-filter skor relevansi (relevance score) hasilnya, dan yang kedua menggunakan IN BOOLEAN untuk memberi pengecualian untuk kata tertentu dari hasil sehingga bisa memberikan batasan yang lebih ketat.

Menggunakan Relevance Score

Relevance score adalah nilai yang menentukan seberapa baik kesesuaian antara hasil dengan kata kunci dimana nilai 0 menandakan bahwa tidak ada relevansi sama sekali. Relevance score menggunakan beberapa faktor diantaranya mencari tahu seberapa sering kata kunci ditemukan di sebuah dokumen dan berapa banyak dokumen yang terdapat kata kunci tersebut. Dokumentasi full-text search MySQL membahas rumus matematika di belakang angka yang dihasilkan.

Mari kita cari tahu relevance score untuk tiap baris dengan query "traveling to parks".

SELECT id, MATCH (title,content,author) AGAINST ('traveling to parks') as score FROM news;

Bagian as score dari perintah di atas memberi nama kolom kedua dari hasil sebagai score.

Hasilnya adalah sebagai berikut:

+----+----------------------+
| id | score                |
+----+----------------------+
|  1 | 0.031008131802082062 |
|  2 |  0.25865283608436584 |
|  2 |  0                   |
+----+----------------------+
3 rows in set (0.00 sec)

Baris ketiga memiliki relevance score 0 karena tidak ada kata kunci yang muncul ditiap dokumen dalam database. Baris peratma memiliki kata "traveling", tapi tidak ada "to" atau "parks" dan memiliki relevance score yang cukup rendah yaitu 0.03. Baris kedua yang memiliki kesemua kata mendapatkan *relevance score * tertinggi yaitu 0.25.

Skor yang dihasilkan dapat dipakai untuk mengurutkan hasil query dan menampilkan yang paling relevan terlebih dahulu. Perintah berikut melakukan query yang sama tapi menambah dua hal:

• Ia akan menampilkan baris yang tidak memiliki relevance score 0 dengan perintah WHERE MATCH (title,content,author) AGAINST ('traveling to parks') > 0
• Lalu ia akan mengurutkan hasil berdasarkan relevance score dengan perintah ORDER BY score DESC

SELECT id, MATCH (title,content,author) AGAINST ('traveling to parks') as score FROM news WHERE MATCH (title,content,author) AGAINST ('traveling to parks') > 0 ORDER BY score DESC;

Hasilnya adalah sebagai berikut:

+----+----------------------+
| id | score                |
+----+----------------------+
|  2 |  0.25865283608436584 |
|  1 | 0.031008131802082062 |
+----+----------------------+
2 rows in set (0.01 sec)

Hasil paling relevan, baris kedua, sekarang ditampilkan pertama, diikuti baris pertama yang relevansinya lebih rendah.

Untuk mendapatkan hasil yang lebih akurat kita bisa mengubah nilai 0 menjadi 0.1 dan hanya baris kedua yang akan ditampilkan.

Menggunakan IN BOOLEAN

Dilangkah kedua, kita menggunakan mode IN NATURAL LANGUAGE saat menuliskan kata kuncinya. Salah satu mode lain adalah IN BOOLEAN yang memungkinkan kita mengecualikan kata tertentu dari hasil pencarian.

Untuk mengecualikan kata kunci dari sebuah query, gunakan operator minus dengan IN BOOLEAN. Perintah berikut akan memberikan hasil yang memiliki kata "travelling" tapi tidak memiliki kata "Seattle".

SELECT * FROM news WHERE MATCH (title,content,author) AGAINST ('traveling -Seattle' IN BOOLEAN MODE)\G

Hasilnya hanya akan menampilkan row 2:

*************************** 1. row ***************************
     id: 2
  title: Hitting the beach was voted the best part of life in the region
content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
 author: Ethan
1 row in set (0.01 sec)

Hal ini terjadi karena tanda minus akan membuat dokumen yang memiliki kata kunci tertentu dan nilai relevansi 0 untuk tidak ditampilkan.

Kita juga dapat menggunakan IN BOOLEAN MODE untuk menentukan jarak maksimum antara kata kunci. Misal, pada kalimat "cats and gos" memiliki jarak 3.

Perintah berikut akan mengembalikan hasil yang memiliki kata "traveling" dan "miles" namun dengan jarak maksimum 4 (ada dua kata diantara traveling dan miles/traveling (1), the (2), 140 (3), miles (4) sehingga jaraknya menjadi @4).

SELECT * FROM news WHERE MATCH (title,content,author) AGAINST ('"traveling miles" @4' IN BOOLEAN MODE)\G

Berikut hasilnya:

*************************** 1. row ***************************
     id: 1
  title: Pacific Northwest high-speed rail line
content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
 author: Greg
1 row in set (0.00 sec)

Jika @4 diganti menjadi @3 maka tidak ada hasil yang didapatkan.

Memberikan limit pada hasil pencarian berdasarkan jarak akan sangat membantu pencarian di dokumen yang sangat besar dengan perbendaharaan yang bermacam-macam. Semakin pendek jaraknya, semakin akurat pula hasil yang akan diberikan meskipun pengaturan jaraknya akan bergantung pada kumpulan dokumen yang kita gunakan. Contoh, pada kumpulan dokumen akademis, jarak 3 sudah cukup baik, tapi mencari posting forum mungkin akan menghasilkan data yang lebih baik dengan jarak 8 atau lebih tinggi tergantung seberapa luas hasil yang diinginkan.

< a href="https://www.codepolitan.com/membuat-pagination-ala-facebook-dengan-phpmysql-dan-jquery-581c109f5c55c-20915">Membuat pagination ala Facebook dengan PHP, MySQL dan jQuery

Penutup

Pada tutorial kali ini kita sudah mempelajari bagaimana menggunakan fitur FTS di MySQL. Kita membuat sebuah indeks saat membuat skema databasenya dan menggunakan operator khusus dalam melakukan pencarian.

Jika ingin menyelami kemampuan FTS lebih jauh lagi, silahkan baca dokumentasi resmi MySQL 5.6 tentang full-text search.

Diterjemahkan dari How To Improve Database Searches with Full-Text Search in MySQL 5.6 on Ubuntu 16.04 di bawah Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Gambar sampul: udemy