Full-Text Search dengan FTS5

Mencari artikel yang membahas “database” dengan LIKE '%database%' berhasil — sampai tabel memuat ribuan dokumen dan query mulai memindai semuanya dari awal, atau sampai kita butuh hal-hal yang tidak bisa diberikan LIKE: mencocokkan kata utuh saja, mengurutkan hasil dari yang paling relevan, dan menampilkan cuplikan di sekitar kata yang ditemukan. Modul FTS5 (tersedia sejak SQLite 3.9.0) menjawab semuanya dengan membangun inverted index — struktur data yang memetakan setiap kata ke daftar dokumen yang memuatnya, teknik yang sama dipakai mesin pencari.

Data Contoh #

Tabel FTS5 dibuat dengan CREATE VIRTUAL TABLE. Opsi tokenize='unicode61' memilih tokenizer standar yang membagi teks berdasarkan huruf dan angka — cukup untuk teks Indonesia yang memakai alfabet Latin:

CREATE VIRTUAL TABLE artikel_fts USING fts5(judul, isi, tokenize='unicode61');

INSERT INTO artikel_fts (judul, isi) VALUES
  ('Belajar SQLite', 'SQLite adalah database tertanam di dalam aplikasi tanpa server terpisah.'),
  ('Optimasi Query Database', 'Gunakan index agar query tidak memindai seluruh tabel baris demi baris.'),
  ('Backup Database SQLite', 'Cadangkan berkas database dengan backup online dari dalam aplikasi.'),
  ('Kopi Nusantara', 'Kopi Gayo dari dataran tinggi Aceh punya cita rasa khas nusantara.');

Pencarian Kata Utuh dengan MATCH #

Operator MATCH membandingkan kata, bukan potongan teks. Sertakan ORDER BY rank agar hasil terurut dari yang paling relevan:

SELECT judul FROM artikel_fts WHERE artikel_fts MATCH 'database' ORDER BY rank;

Output yang Dihasilkan:

Backup Database SQLite
Belajar SQLite
Optimasi Query Database

Perbedaan mendasar dengan LIKE tampak pada pencarian kata data. LIKE menemukan dua artikel karena substring data bersembunyi di dalam kata Database — padahal tidak ada satu pun artikel yang memuat kata data sebagai kata utuh. Query pertama di bawah hanya memeriksa kolom judul, sedangkan MATCH menelusuri seluruh kolom terindeks (judul dan isi) sekaligus. Tanda bintang pada 'data*' mengaktifkan pencarian prefiks sehingga kata database ikut tertangkap — begitu pula kata dataran pada artikel Kopi Nusantara, karena prefiksnya juga diawali data:

SELECT judul FROM artikel_fts WHERE judul LIKE '%data%' ORDER BY judul;
SELECT COUNT(*) FROM artikel_fts WHERE artikel_fts MATCH 'data';
SELECT judul FROM artikel_fts WHERE artikel_fts MATCH 'data*' ORDER BY judul;

Output yang Dihasilkan:

Backup Database SQLite
Optimasi Query Database
0
Backup Database SQLite
Belajar SQLite
Kopi Nusantara
Optimasi Query Database

Mengurutkan Relevansi dengan bm25 #

Kolom rank dihitung dari fungsi bm25(), implementasi algoritma ranking klasik di dunia information retrieval. Skornya selalu negatif — semakin negatif, semakin relevan:

SELECT judul, bm25(artikel_fts) AS skor
FROM artikel_fts WHERE artikel_fts MATCH 'database'
ORDER BY skor, judul;

Output yang Dihasilkan:

Backup Database SQLite|-1.3981308411215e-06
Belajar SQLite|-1.02465753424658e-06
Optimasi Query Database|-9.61439588688946e-07

Skor pada korpus mini kita bernilai sangat kecil karena database muncul di hampir semua dokumen; pada korpus besar yang kata-katanya lebih tersebar, selisih antar dokumen jauh lebih terlihat. Yang penting adalah urutannya konsisten dan bisa kita andalkan di ORDER BY rank.

Menampilkan Cuplikan dengan snippet dan highlight #

snippet() memotong teks di sekitar kata yang cocok dan menandainya dengan pembuka serta penutup pilihan kita. Argumen kedua adalah nomor kolom (0 = judul, 1 = isi), diikuti penanda, teks elipsis, dan jumlah token maksimum:

SELECT judul, snippet(artikel_fts, 1, '[', ']', '...', 6)
FROM artikel_fts WHERE artikel_fts MATCH 'tertanam';

Output yang Dihasilkan:

Belajar SQLite|SQLite adalah database [tertanam] di dalam...

highlight() mempertahankan seluruh teks kolom dan hanya membungkus kata yang cocok:

SELECT highlight(artikel_fts, 0, '[', ']')
FROM artikel_fts WHERE artikel_fts MATCH 'backup';

Output yang Dihasilkan:

[Backup] Database SQLite

Operator Pencarian Lanjutan #

Kueri MATCH mendukung bahasa mini yang kaya. Filter kolom membatasi pencarian ke satu kolom dengan sintaks kolom:; frasa dikurung tanda kutip ganda; operator OR menjumlahkan hasil:

SELECT judul FROM artikel_fts WHERE artikel_fts MATCH 'judul: database' ORDER BY judul;
SELECT judul FROM artikel_fts WHERE artikel_fts MATCH '"backup online"';
SELECT judul FROM artikel_fts WHERE artikel_fts MATCH 'optimasi OR kopi' ORDER BY judul;

Output yang Dihasilkan:

Backup Database SQLite
Optimasi Query Database
Backup Database SQLite
Kopi Nusantara
Optimasi Query Database

Beberapa kata tanpa operator berarti AND — 'sqlite backup' hanya mengembalikan dokumen yang memuat keduanya. Operator NEAR(...) menuntut kedua frasa berdekatan: parameter kedua adalah jumlah maksimum token yang boleh berada di antara kedua frasa. Pada judul Backup Database SQLite, kata backup dan sqlite dipisahkan satu token (database) sehingga batas 1 masih lolos, sedangkan batas 0 yang menuntut keduanya berdampingan gagal:

SELECT judul FROM artikel_fts WHERE artikel_fts MATCH 'NEAR(sqlite backup, 1)';
SELECT COUNT(*) FROM artikel_fts WHERE artikel_fts MATCH 'NEAR(sqlite backup, 0)';

Output yang Dihasilkan:

Backup Database SQLite
0

Batas 0 gagal karena kata backup dan sqlite tidak berdampingan. Pencarian juga tidak peka kapital, termasuk untuk teks Indonesia:

SELECT judul FROM artikel_fts WHERE artikel_fts MATCH 'kopi';
SELECT judul FROM artikel_fts WHERE artikel_fts MATCH 'GAYO';

Output yang Dihasilkan:

Kopi Nusantara
Kopi Nusantara

Opsi content: Menautkan ke Tabel Biasa #

Contoh kita menyimpan data dua kali — sekali di FTS5, sekali di tempat lain. Opsi content= menghilangkan duplikasi: FTS5 hanya menyimpan index sementara teks asli tetap di tabel biasa. Perintah khusus 'rebuild' mengisi ulang index dari tabel sumber:

CREATE TABLE artikel (id INTEGER PRIMARY KEY, judul TEXT NOT NULL, isi TEXT NOT NULL);
INSERT INTO artikel (id, judul, isi) VALUES
  (1, 'Belajar SQLite', 'SQLite adalah database tertanam di dalam aplikasi.'),
  (2, 'Kopi Nusantara', 'Kopi Gayo dari dataran tinggi Aceh.');

CREATE VIRTUAL TABLE artikel_idx USING fts5(judul, isi, content='artikel', content_rowid='id');
INSERT INTO artikel_idx(artikel_idx) VALUES('rebuild');

SELECT a.id, a.judul
FROM artikel_idx JOIN artikel a ON a.id = artikel_idx.rowid
WHERE artikel_idx MATCH 'database';

Output yang Dihasilkan:

1|Belajar SQLite

Pola dengan content= menuntut disiplin: setiap INSERT/UPDATE/DELETE pada tabel sumber harus diikuti penulisan yang sama ke tabel FTS — biasanya diotomatisasi dengan trigger.

Kapan FTS5, Kapan LIKE #

Dengan FTS5, kotak pencarian milik toko kita bisa melayani “kopi aceh” dengan ranking dan cuplikan yang rapi. Section ini menuntaskan fitur lanjutan SQLite; perjalanan berikutnya berpindah ke fondasi keandalan database: transaksi, yang dibahas pada Section Transaksi dan Konsistensi.