Full-Text Search — Database

Apa itu Full-Text Search? Full-text search (FTS) memungkinkan pencarian teks yang lebih cerdas daripada LIKE. FTS memahami kata, stemming, dan ranking…

Full-text search (FTS) adalah cara mencari kata di dalam teks panjang memakai index kata, bukan dengan mencocokkan potongan huruf baris demi baris. Bayangin indeks di halaman belakang buku resep: "susu: halaman 3, 7, 12". Kamu langsung membuka tiga halaman itu, gak perlu membaca bukunya dari halaman pertama. LIKE '%susu%' itu membaca bukunya dari depan sampai habis. Full-text search memakai indeks di belakangnya.

🧪 Tebakan cepat: pembeli mengetik "susu" di kotak pencarian menu Kedai Senja, dan aplikasinya memakai LIKE '%susu%'. Roti Bakar ikut muncul, padahal gak pakai susu sama sekali. Kenapa? Tebak dulu, jawabannya ada di bagian "LIKE mencocokkan huruf, bukan kata".

Menu yang kita cari

Kedai Senja sekarang punya 6 menu, masing-masing dengan deskripsi:

id name description
1 Kopi Susu Espresso dan susu segar, manisnya pas.
2 Kopi Susu Aren Kopi susu dengan gula aren. Susu segar, aren asli.
3 Teh Tarik Teh hitam ditarik dengan susu kental manis.
4 Roti Bakar Roti tebal disusun tiga lapis, cokelat dan keju.
5 Es Kopi Hitam Kopi tanpa gula, tanpa susu.
6 Matcha Latte Matcha Jepang dengan susu oat.

LIKE mencocokkan huruf, bukan kata

SELECT id, name FROM products
WHERE name LIKE '%susu%' OR description LIKE '%susu%';

Hasilnya 6 baris, semua menu. Roti Bakar ikut karena deskripsinya berisi "disusun". LIKE '%susu%' artinya "ada huruf s, u, s, u berurutan di mana saja", termasuk di tengah kata lain. Itu jawaban tebakannya.

Masalah kedua ada di rencana query-nya. EXPLAIN QUERY PLAN di depan query tadi menjawab SCAN products: SQLite membaca semua baris satu per satu. Index biasa (B-tree) diurutkan dari huruf awal, dan % di depan berarti huruf awalnya bebas, jadi index gak bisa dipakai. Dengan 6 baris gak terasa. Dengan 600.000 deskripsi, dibaca semua setiap kali pembeli mengetik, terasa sekali.

Masalah ketiga: LIKE '%kopi aren%' hasilnya 0 baris, padahal ada Kopi Susu Aren. LIKE mencari dua kata itu persis bersebelahan.

Index kata (inverted index)

Full-text search memecah teks jadi kata-kata (disebut token), lalu membuat inverted index: daftar semua kata, dan untuk tiap kata, baris mana saja yang memuatnya. Persis indeks di belakang buku. SQLite di browser ini punya modul FTS4:

CREATE VIRTUAL TABLE products_fts USING fts4(content="products", name, description);
INSERT INTO products_fts(products_fts) VALUES ('rebuild');

SELECT rowid, name FROM products_fts WHERE products_fts MATCH 'susu';

Hasilnya 5 baris: 1, 2, 3, 5 dan 6. Roti Bakar hilang, karena "disusun" adalah kata tersendiri. Isi index-nya bisa diintip lewat tabel fts4aux:

term documents occurrences
aren 1 3
disusun 1 1
kopi 3 5
susu 5 8

MATCH 'kopi aren' artinya dua kata itu wajib ada, di mana pun letaknya, jadi Kopi Susu Aren ketemu. Rencana query-nya sekarang SCAN products_fts VIRTUAL TABLE INDEX 4:. Di tabel virtual, INDEX 4 adalah kode modul FTS untuk pencarian lewat index kata; membaca semua baris akan tertulis INDEX 0.

Satu hal gak berubah: Es Kopi Hitam tetap muncul untuk "susu", karena deskripsinya "tanpa susu". FTS mencocokkan kata, bukan maksud.

Ranking: yang paling cocok di atas

Pencarian yang baik mengurutkan hasil dari yang paling relevan. Ini disebut ranking. Cara paling sederhana: hitung berapa kali kata yang dicari muncul. Di FTS4, offsets() memberi 4 angka untuk tiap kecocokan, jadi jumlah angkanya dibagi 4:

SELECT p.id, p.name,
  (length(offsets(products_fts)) - length(replace(offsets(products_fts), ' ', '')) + 1) / 4 AS hits
FROM products_fts
JOIN products p ON p.id = products_fts.rowid
WHERE products_fts MATCH 'susu'
ORDER BY hits DESC, p.id;
id name hits
2 Kopi Susu Aren 3
1 Kopi Susu 2
3 Teh Tarik 1
5 Es Kopi Hitam 1
6 Matcha Latte 1

LIKE gak bisa mengurutkan begini: barisnya cocok atau gak, itu saja. Database lain punya fungsi ranking siap pakai: ts_rank di PostgreSQL, dan skor dari MATCH ... AGAINST di MySQL.

Di PostgreSQL dan MySQL

PostgreSQL memakai dua tipe data: tsvector (teks yang sudah dipecah jadi kata, lengkap dengan posisinya) dan tsquery (kata kunci yang dicari). Contohnya, to_tsvector('simple', 'Kopi susu dengan gula aren') menghasilkan 'aren':5 'dengan':3 'gula':4 'kopi':1 'susu':2.

ALTER TABLE products ADD COLUMN search tsvector
  GENERATED ALWAYS AS (to_tsvector('simple', name || ' ' || description)) STORED;
CREATE INDEX products_search_idx ON products USING GIN (search);

SELECT name, ts_rank(search, query) AS rank
FROM products, to_tsquery('simple', 'kopi & aren') AS query
WHERE search @@ query
ORDER BY rank DESC;

MySQL memakai index FULLTEXT dan MATCH ... AGAINST. Mode bawaannya mencari salah satu kata lalu mengurutkan dari yang paling relevan. Kalau dua-duanya wajib, pakai AGAINST ('+kopi +aren' IN BOOLEAN MODE).

ALTER TABLE products ADD FULLTEXT INDEX products_ft (name, description);
SELECT name FROM products WHERE MATCH(name, description) AGAINST ('kopi aren');

Di Laravel, index-nya dibuat di migration dan dipakai dengan whereFullText(), yang diterjemahkan jadi MATCH ... AGAINST di MySQL dan to_tsvector(...) @@ plainto_tsquery(...) di PostgreSQL:

$table->fullText(['name', 'description']); // di migration

Product::whereFullText(['name', 'description'], 'kopi aren')->get();

FTS database atau search engine terpisah?

FTS bawaan database sudah cukup untuk banyak aplikasi. Search engine terpisah seperti Meilisearch, Algolia dan Elasticsearch dipakai saat butuh fitur lanjutan: typo tolerance (pembeli mengetik "kopi suus" tetap ketemu, padahal MATCH 'kopi suus' hasilnya 0 baris), faceting (filter lengkap dengan hitungannya, misalnya Kopi 3, Teh 1), dan instant search (hasil berubah setiap huruf diketik).

Skala Budget Pilihan
kecil apa pun PostgreSQL FTS, sudah ada di database-mu
sedang gratis Meilisearch, open source, di-host sendiri
sedang berbayar Algolia, layanan hosted, tinggal bayar
besar apa pun Elasticsearch, cluster banyak server untuk data sangat besar

⚠️ Jebakan yang sering ketemu

TL;DR: LIKE '%kata%' mencocokkan potongan huruf: "susu" ketemu di "disusun", "kopi aren" gak ketemu di "Kopi Susu Aren", gak ada ranking, dan rencana query-nya SCAN semua baris. Full-text search memecah teks jadi kata dan menyimpan inverted index (kata ke daftar baris), jadi pencariannya lewat index, cocok per kata, dan bisa diurutkan dari yang paling relevan (ranking). SQLite punya FTS4/FTS5 dengan MATCH, PostgreSQL punya tsvector, tsquery, @@ dan index GIN, MySQL punya index FULLTEXT dengan MATCH ... AGAINST, dan Laravel membungkusnya dengan whereFullText(). Butuh typo tolerance, faceting atau instant search? Pakai search engine terpisah: Meilisearch, Algolia atau Elasticsearch.

Yang akan kamu pelajari