MYSQL Fulltext Search

Posted Tue, 12/15/2009 - 21:06 by X-C0d3C

Untuk sebuah website yang memiliki banyak data, menyediakan fasilitas search merupakan hal yang tepat untuk mempermudah user mencari dengan "keyword" yang spesifik. Untuk membuat search engine ini, ada beberapa metode yang bisa digunakan, dan pada pembahasan kali ini, kita akan fokuskan kepada Full-text search pada MySQL.

Sebelum memulai, ada beberapa hal yang perlu diketahui, sbb:

Sebuah full-text index pada Mysql adalah index dengan tipe FULLTEXT.
Full-text index hanya dapat digunakan pada table MyISAM, dan dapat di buat untuk kolom dengan tipe CHAR, VARCHAR, atau TEXT.
Full-text index dapat dibuat ketika kita membuat table "CREATE TABLE", atau dibuat kemudian menggunakan statement "ALTER TABLE" atau "CREATE INDEX"
Untuk table dengan data yang besar, sebaiknya insert semua data terlebih dahulu pada table tanpa "FULLTEXT" index, kemudian baru buat "FULLTEXT" index setelah itu.

Sebelum kita masuk pada pembahasan mengenai cara pembuatan FULLTEXT search, terlebih dahulu perlu diketahui bahwa FULLTEXT search mempunyai 3 tipe yaitu "Boolean" , "Natural Languange" dan "Query Expansion".

Untuk memulai, kita perlu membuat sebuah table yang akan kita gunakan terus menerus dalam pembahasan ini.

#buat table dengan nama `article`
CREATE TABLE IF NOT EXISTS `article` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`title` varchar(150) NOT NULL,
`body` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Setelah table di buat, kita masukan data dengan statement "INSERT INTO"

#insert data ke table `article`

INSERT INTO `article` (`id`, `title`, `body`) VALUES
(1, 'PHP & Mysql : Tutorial Dasar', 'Pada tutorial kali ini, kita akan mencoba melakuka...'),
(2, 'MySQL - Pengenalan', 'Tutorial MySQL di situs ini memang buat kamu-kamu ...'),
(3, 'Mysql', 'Tutorial penggunaan database masa kini...'),
(4, 'Mysql & Oracle Database', 'Database masa kini...'),
(5, 'Implementasi Oracle', 'Bagaimana menggunakan oracle...'),
(6, 'SqlLite Database', 'Penggunaan SqlLite dan cpanel...'),
(7, 'let it be', 'by The Beatles...');

Sebenarnya, pencarian dapat menggunakan tipe yang konvensional seperti:

SELECT * FROM article WHERE title like'%mysql%';

Tetapi pencarian kata/keyword menggunakan "LIKE" sangatlah tidak baik untuk data yang besar. Untuk itulah fulltext search dapat penjadi pilihan dalam melakukan fungsinya sebagai search feature dari MYSQL.

Seperti yang telah dijelaskan di point atas, untuk menjalankan FULLTEXT Search diperlukan index dengan tipe FULLTEXT pada field field yang ingin kita search. Di sini, saya akan menambahkan 2 FULLTEXT indexing pada 2 field yaitu `title` dan `body`.

ALTER TABLE article ADD FULLTEXT(title);
ALTER TABLE article ADD FULLTEXT(body, title);

Penggunaan Fulltext Search

1. Implementasi tipe "Natural Language"
Secara default, penggunaan Fulltext Search masuk kedalam tipe ini.

mysql> SELECT * FROM article WHERE MATCH (title) AGAINST ('tutorial');
+---+----------------------------------+-------------------------------------------------------------+
| id | title | body |
+---+----------------------------------+-------------------------------------------------------------+
| 1 | PHP & Mysql : Tutorial Dasar | Pada tutorial kali ini, kita akan mencoba melakuka... |
+---+----------------------------------+-------------------------------------------------------------+
1 row in set

Query di atas akan selalu mengembalikan result id dimana pada field `title` mengandung kata 'tutorial'.

Contoh lain penggunaan (1):

mysql> SELECT * FROM article WHERE MATCH (title) AGAINST ('tutor');
Empty set

Kenapa Empty set? karena tutor tidak sama dengan tutorial.

Contoh lain penggunaan (2):

mysql> SELECT * FROM article WHERE MATCH (title, body) AGAINST ('tutorial');
+----+---------------------------------------+----------------------------------------------------------------------------+
| id | title                       | body                                                 |
+----+---------------------------------------+----------------------------------------------------------------------------+
| 1 | PHP & Mysql : Tutorial Dasar | Pada tutorial kali ini, kita akan mencoba melakuka...      |
| 3 | Mysql                          | Tutorial penggunaan database masa kini...            |
| 2 | MySQL - Pengenalan           | Tutorial MySQL di situs ini memang buat kamu-kamu ... |
+----+---------------------------------------+----------------------------------------------------------------------------+
3 rows in set

Pada query diatas, pencarian dilakukan pada 2 field. Yaitu `title` dan `body`. Dan mengembalikan 3 result

Contoh lain penggunaan (3):

SELECT *, MATCH (title, body) AGAINST ('tutorial') as Score FROM article WHERE MATCH (title, body) AGAINST ('tutorial');
+----+---------------------------------------+----------------------------------------------------------------------------+----------------------------+
| id | title                    | body                                                    | Score           |
+----+---------------------------------------+----------------------------------------------------------------------------+----------------------------+
| 1 | PHP & Mysql : Tutorial Dasar | Pada tutorial kali ini, kita akan mencoba melakuka...      | 0.40983861684799 |
| 3 | Mysql                          | Tutorial penggunaan database masa kini...          | 0.26911327242851 |
| 2 | MySQL - Pengenalan           | Tutorial MySQL di situs ini memang buat kamu-kamu ... | 0.22223679721355 |
+----+---------------------------------------+----------------------------------------------------------------------------+----------------------------+
3 rows in set

Makin besar Score makin relevant resultnya. Pada result di atas, bisa di ambil kesimpulan bahwa MySQL selalu sort by relevansi dari yang paling relevan ke yang kurang relevan.

Contoh lain penggunaan (4):

mysql> SELECT * FROM article WHERE MATCH (title) AGAINST ('mysql');
Empty set

Contoh query di atas tidak mengembalikan result. Kenapa? Padahal mysql juga terdapat pada field `title` maupun `body`? Ini dikarenakan MySql mengimplementasi "Stop Word" atau "Skip Word" untuk setiap keyword yang tampil pada 50% atau lebih dari total data.

2. Implementasi tipe Boolean

Tipe boolean mengijinkan kita untuk menambahkan operator operator pada keyword pencarian seperti + atau -.

Berikut List completenya:

Operator	Arti
	Secara default, ini operator yang di pakai yang mempuyai arti "atau" / "or"
+	Tanda plus berarti keyword tersebut harus terdapat pada semua result yang didapatkan.
-	Tanda minus berarti keyword tersebut tidak boleh ada pada result yang didapatkan.
< >	Keyword yang diberikan tanda > atau < akan mempengaruhi relevansi. Pemberian tanda > pada awal kata mengakibatkan nilai relevansi/score menjadi
( )	Untuk melakukan grouping kemudian bisa menambahkan operator lain untuk menambah tingkat relevansi/score.
~	Operator ini berfungsi sebagai operator negasi. Jadi ketika sebuat keyword di berikan tanda ini, mengakibatkan relevansi/score minus (bukan 0).
*	Merupakan wildcard operator.
“	Operator ini digunakan pada awal dan akhir keyword yang terdiri dari 2 atau lebih kata.

Untuk mengetahui lebih jelas mengenai operator operator ini, kita akan test dengan beberapa contoh:

Contoh Boolean (1)

Result harus mengandung kata "mysql" dan tidak boleh mengandung kata "database" pada field title dan body.

Contoh Boolean (2)

Penambahan asterik pada awal ataupun akhir keyword menjadikannya seperti "LIKE 'tutor%'"

Contoh Boolean (3)

Penggunaan seperti ini mengakibatkan data yang mengandung kata mysql dan tutorial akan lebih tinggi score relevansinya dibandingkan data yang mengandung mysql dan database. Dan semua result harus mengandung kata mysql+tutorial atau database.

Contoh Boolean (4)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"mysql tutorial"' IN BOOLEAN MODE);
+----+---------------------+------------------------------------------+
| id | title | body |
+----+---------------------+------------------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
+----+---------------------+------------------------------------------+
1 row in set

Result harus mengandung kata "mysql" yang kemudian diikuti oleh kata "tutorial"

3. Tipe Query Expansion

Query Expansion merupakan salah satu fitur yang bisa digunakan ketika pencarian pencarian hanya mengandung keyword yang sedikit. Ex: cuman satu kata.

Ketika user mencari dengan keyword "database", kemungkinan result yang diinginkan user adalah yang berkaitan dengan Mysql atau Oracle atau SqlLite dll. Dengan pengimplementasian query expansion, result yang dihasilakan diproses 2 kali dengan mempelajari result pencarian pertama kemudian digabungkan dengan result pencarian kedua dengan tambahan keyword yang paling relevant terhadap pencarian pertama.

Untuk lebih jelasnya, bisa kita coba dengan contoh.

Contoh penggunaan dengan tipe Natural Language

Disini kita lihat bahwa pada result pertama keyword "mysql" selalu muncul, maka dianggap keyword "mysql" merupakan keyword dengan relevansi tinggi pada pencarian "database" sehingga result juga memunculkan semua data yang mengandung kata "mysql"

Note:
Penggunaan tipe ini pada keyword yang panjang mengakibatkan penambahan result yang tidak relevan.

logcode

MYSQL Fulltext Search

Syndicate

Tags in Tags

Recent blog posts

Links