Membuat Full-Text Index

Query MATCH ... AGAINST dari chapter sebelumnya hanya bekerja bila index bertipe FULLTEXT sudah ada di kolom yang tepat. Tanpa index itu, server bukan menjadi lambat — ia justru menolak query sama sekali dengan ERROR 1191. Pada chapter ini kita praktik membuat index full-text dengan tiga cara: mendeklarasikannya langsung di dalam CREATE TABLE, menambahkannya belakangan lewat CREATE FULLTEXT INDEX setelah data dimuat, dan memakai ALTER TABLE. Kita juga memverifikasi index lewat SHOW INDEX, membuktikan dua wajah galat ERROR 1191, lalu menutup dengan perbedaan InnoDB dan MyISAM dalam urusan token minimum serta perawatan index yang berjalan otomatis.

Menyiapkan Tabel Tanpa Index #

Kita mulai dari skenario yang paling jujur: tabel sudah berisi data, tetapi belum ada satu pun index full-text. Database latihan tetap belajar_fts dengan tabel artikel yang sama — sepuluh artikel tentang teknologi, kopi, dan masakan — kali ini dibuat polos tanpa klausa FULLTEXT:

DROP DATABASE IF EXISTS belajar_fts;
CREATE DATABASE belajar_fts;
USE belajar_fts;

Output yang Dihasilkan:

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

CREATE TABLE artikel (
  id INT AUTO_INCREMENT PRIMARY KEY,
  judul VARCHAR(100) NOT NULL,
  isi TEXT NOT NULL
) ENGINE=InnoDB;

INSERT INTO artikel (judul, isi) VALUES
  ('Belajar Database Relasional', 'Database relasional menyimpan data dalam tabel yang saling terhubung. MySQL adalah database relasional populer untuk aplikasi web. Indeks mempercepat pencarian data pada kolom tertentu.'),
  ('Panduan Menyeduh Espresso', 'Espresso adalah basis minuman kopi modern. Seduh espresso memakai mesin kopi bertekanan tinggi dan biji kopi gelap. Grinder yang baik menghasilkan rasa espresso seimbang.'),
  ('Memasak Rendang Daging', 'Rendang daging adalah masakan khas Padang. Memasak rendang membutuhkan santan, cabai, dan rempah. Proses memasak rendang memakan waktu beberapa jam hingga kuah mengering.'),
  ('Cloud Computing untuk Pemula', 'Cloud computing menyediakan server dan penyimpanan lewat internet. Perusahaan menyewa cloud agar tidak membeli server fisik. Layanan cloud populer contohnya penyimpanan berkas dan basis data terkelola.'),
  ('Asal Biji Kopi Gayo', 'Kopi Gayo tumbuh di dataran tinggi Aceh. Petani kopi Gayo memanen biji secara manual. Citarasa kopi Gayo khas dengan aroma rempah dan keasaman seimbang.'),
  ('Nasi Goreng Kampung', 'Nasi goreng kampung adalah masakan rumahan favorit. Bumbu nasi goreng terdiri dari bawang, cabai, dan kecap. Memasak nasi goreng butuh api besar agar wangi.'),
  ('JavaScript di Browser', 'JavaScript membuat halaman web menjadi interaktif. Browser menjalankan JavaScript pada sisi klien. Framework JavaScript mempercepat pembuatan antarmuka aplikasi modern.'),
  ('Cold Brew vs Kopi Panas', 'Cold brew diseduh dengan air dingin selama belasan jam. Rasa cold brew lebih halus dibanding kopi panas. Penikmat kopi sering menyimpan cold brew di kulkas.'),
  ('Tempe Sumber Protein', 'Tempe adalah makanan fermentasi kedelai khas Indonesia. Tempe mengandung protein tinggi dan harganya murah. Olahan tempe contohnya tempe goreng, tempe bacem, dan tempe crispy.'),
  ('Keamanan Aplikasi Web', 'Keamanan aplikasi web wajib dijaga sejak awal pengembangan. Pengembang memvalidasi input pengguna dan menyimpan sandi terenkripsi. Serangan umum pada aplikasi web contohnya SQL injection.');

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

Urutan ini sengaja: memuat data lebih dulu lalu membuat index belakangan. Saat INSERT dijalankan, MySQL cukup menulis baris tanpa pekerjaan ekstra; saat index dibuat setelahnya, pembangunan index terbalik dikerjakan sekali jalan untuk seluruh data. Untuk data besar, pola ini jauh lebih cepat daripada membangun index sambil-sambil pada setiap INSERT.

ERROR 1191: Mencari Tanpa Index #

Sekarang kita coba query pencarian yang sama seperti chapter sebelumnya:

-- @error
SELECT id, judul FROM artikel WHERE MATCH(judul, isi) AGAINST ('kopi');

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list
Bye

Server menolak dengan ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list. Pesan ini berisi dua syarat sekaligus: harus ada index bertipe FULLTEXT, dan daftar kolom pada MATCH harus cocok dengan kolom yang didefinisikan index tersebut. Pada tabel InnoDB, tidak ada jalur turun seperti “pencarian lambat tanpa index” — query full-text tanpa index yang cocok memang ditolak keras. Satu-satunya jalan adalah membuat index-nya, dan itulah langkah berikutnya.

Menambah Index dengan CREATE FULLTEXT INDEX #

Cara pertama menambahkan index pada tabel yang sudah ada adalah statement CREATE FULLTEXT INDEX:

CREATE FULLTEXT INDEX idx_ft_artikel ON artikel (judul, isi);

Output yang Dihasilkan:

Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 1

Statement di atas membuat index bernama idx_ft_artikel pada pasangan kolom judul, isi — satu index full-text multi kolom. Multi kolom penting: artinya token judul dan token isi digabung menjadi satu dokumen per baris, sehingga kata “kopi” yang muncul di judul maupun di isi keduanya terindeks dan sama-sama berkontribusi pada skor. Outputnya mencantumkan 1 warning — kalau kita jalankan SHOW WARNINGS; setelahnya, pesannya berbunyi InnoDB rebuilding table to add column FTS_DOC_ID: InnoDB diam-diam menambahkan kolom tersembunyi FTS_DOC_ID ke tabel sebagai penanda internal dokumen untuk index terbaliknya, dan penambahan itulah yang dilaporkan sebagai warning, bukan sebagai galat. Setelah statement berhasil, query yang barusan ditolak kini berjalan:

SELECT id, judul FROM artikel WHERE MATCH(judul, isi) AGAINST ('kopi') ORDER BY id;

Output yang Dihasilkan:

+----+---------------------------+
| id | judul                     |
+----+---------------------------+
|  2 | Panduan Menyeduh Espresso |
|  5 | Asal Biji Kopi Gayo       |
|  8 | Cold Brew vs Kopi Panas   |
+----+---------------------------+
3 rows in set (0.00 sec)

Cara kedua yang setara adalah ALTER TABLE artikel ADD FULLTEXT idx_ft_artikel (judul, isi); — hasil akhirnya identik, hanya bentuk statement yang berbeda. Pilih salah satu sesuai selera tim; CREATE FULLTEXT INDEX lebih eksplisit tentang niatnya, sedangkan ALTER TABLE menampung index biasa sekaligus index full-text dalam satu statement besar.

Memverifikasi Index dengan SHOW INDEX #

Index yang sudah dibuat bisa kita periksa tanpa membuka ulang definisi tabelnya:

SHOW INDEX FROM artikel;

Output yang Dihasilkan:

+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| artikel |          0 | PRIMARY        |            1 | id          | A         |          10 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| artikel |          1 | idx_ft_artikel |            1 | judul       | NULL      |          10 |     NULL |   NULL |      | FULLTEXT   |         |               | YES     | NULL       |
| artikel |          1 | idx_ft_artikel |            2 | isi         | NULL      |          10 |     NULL |   NULL |      | FULLTEXT   |         |               | YES     | NULL       |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

Perhatikan dua baris milik idx_ft_artikel: satu untuk kolom judul (Seq_in_index 1) dan satu untuk isi (Seq_in_index 2), keduanya bertanda Index_type = FULLTEXT dengan Collation = NULL — ciri khas index full-text yang tidak mengurutkan nilai, melainkan memetakan kata. Baris PRIMARY di atasnya adalah index B-tree biasa milik primary key; perbedaan Index_type inilah yang membedakan keduanya. Ekspresi SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'artikel' memberi data yang sama dalam bentuk tabel sistem bila kita ingin memprosesnya lewat query.

FULLTEXT Langsung di CREATE TABLE #

Cara ketiga mendeklarasikan index sejak tabel lahir — seperti yang kita pakai di chapter konsep. Pola lengkapnya:

CREATE TABLE catatan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  judul VARCHAR(100) NOT NULL,
  isi TEXT NOT NULL,
  FULLTEXT idx_ft_catatan (judul, isi)
) ENGINE=InnoDB;

INSERT INTO catatan (judul, isi) VALUES
  ('Uji Kopi Harian', 'Catatan uji rasa kopi hari ini dengan mesin espresso kecil di kantor.'),
  ('Uji Rasa Kopi Gayo', 'Catatan uji rasa biji kopi Gayo hasil roasting sedang.');

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Baris FULLTEXT idx_ft_catatan (judul, isi) berdiri sejajar dengan definisi kolom dan index lain di dalam CREATE TABLE. Kata kunci FULLTEXT boleh diikuti nama index — kita menamainya idx_ft_catatan — dan daftar kolom dalam kurung. Index full-text hanya boleh dibuat pada kolom bertipe CHAR, VARCHAR, atau TEXT; kolom numerik atau tanggal ditolak server. Sekali lagi perhatikan bahwa nama index dan daftar kolomnya tidak berhubungan dengan nama index B-tree biasa — dua dunia index boleh berdampingan pada tabel yang sama.

Pencarian pada tabel baru ini langsung bisa dilakukan:

SELECT id, judul FROM catatan WHERE MATCH(judul, isi) AGAINST ('gayo') ORDER BY id;

Output yang Dihasilkan:

+----+--------------------+
| id | judul              |
+----+--------------------+
|  2 | Uji Rasa Kopi Gayo |
+----+--------------------+
1 row in set (0.00 sec)

Sekarang kita buktikan wajah kedua ERROR 1191 yang dijanjikan di awal chapter. Index idx_ft_catatan memang ada, tetapi daftar kolomnya (judul, isi) — sedangkan query berikut hanya menyebut satu kolom:

-- @error
SELECT id, judul FROM catatan WHERE MATCH(judul) AGAINST ('kopi');

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list
Bye

Galat yang sama muncul, dan sekarang kita pahami alasannya: MySQL mencari index FULLTEXT yang daftar kolomnya persis judul saja — tidak lebih, tidak kurang — dan tidak menemukannya. Daftar kolom pada MATCH harus sama persis dengan definisi salah satu index full-text tabel tersebut, urutan kolom pun harus sama. Kalau kita ingin mencari hanya pada judul, solusinya membuat index terpisah FULLTEXT (judul), lalu MATCH(judul) akan menemukannya.

InnoDB, MyISAM, dan Perawatan Index #

Dua storage engine mendukung full-text search: InnoDB dan MyISAM. Sejak MySQL 5.6, InnoDB mendukung FULLTEXT dan menjadi pilihan bawaan — seluruh contoh chapter ini berjalan di InnoDB tanpa konfigurasi tambahan. MyISAM adalah engine lama yang mendukung full-text lebih dulu; kecuali kita memelihara sistem warisan, tidak ada alasan memilih MyISAM demi full-text, apalagi MyISAM tidak mendukung transaction dan crash recovery sekelas InnoDB. Perbedaan kecil yang perlu diingat: panjang kata minimum diatur innodb_ft_min_token_size (bawaan 3) untuk InnoDB dan ft_min_word_len (bawaan 4) untuk MyISAM, plus daftar stopword yang mekanisme pengaturannya juga berbeda antar engine.

Perawatan index berjalan otomatis. Setiap INSERT, UPDATE, dan DELETE pada kolom yang berindex full-text membuat server menokenisasi teks dan memperbarui index terbaliknya — tanpa statement maintenance manual dari kita. Detail penting untuk tabel InnoDB: pembaruan index full-text diproses saat transaction di-commit, sehingga pencarian hanya melihat data yang sudah committed; perubahan di dalam transaction yang masih terbuka belum bisa dicari oleh sesi lain. Konsekuensinya, menulis banyak baris ke tabel berindex full-text sedikit lebih mahal daripada tabel polos — harga yang pantas untuk kecepatan pencarian.

Menghapus index full-text memakai statement yang sudah kita kenal: DROP INDEX idx_ft_artikel ON artikel; atau bentuk ALTER TABLE artikel DROP INDEX idx_ft_artikel;. Setelah index dihapus, query MATCH yang mengandalkan kolom-kolomnya kembali ditolak ERROR 1191 — arsitektur “index dulu, query kemudian” tidak bisa dinegosiasikan.

Membersihkan Database Latihan #

Rangkaian latihan selesai dan kedua tabel kita bawa turun bersama databasenya:

DROP DATABASE belajar_fts;

Output yang Dihasilkan:

Query OK, 2 rows affected (0.00 sec)

Referensi #

• Full-Text Search Functions — MySQL 8.4 Reference Manual
• CREATE INDEX Statement — MySQL 8.4 Reference Manual
• SHOW INDEX Statement — MySQL 8.4 Reference Manual
• InnoDB FULLTEXT Indexes — MySQL 8.4 Reference Manual

Index FULLTEXT kini bisa kita buat lewat tiga pintu — CREATE TABLE, CREATE FULLTEXT INDEX, dan ALTER TABLE — diverifikasi dengan SHOW INDEX, dan galat ERROR 1191 tidak lagi mengejutkan kita karena dua sebabnya sudah dibuktikan. Index hanyalah setengah cerita; cara menanyakan kata ke index itu adalah fungsi MATCH ... AGAINST beserta mode pencariannya. Kita mulai dari mode yang menjadi perilaku bawaan — natural language search — di chapter berikutnya.