Full-Text Search di SQLite: Virtual Table FTS5 dan MATCH

LIKE Tidak Bisa Diandalkan untuk Skala Besar

Kalau kamu pernah mencari teks di SQLite, kemungkinan kamu memakai LIKE '%word%'. Cara itu berhasil pada tabel kecil dan berantakan pada tabel besar. Tidak ada index yang bisa membantu: SQLite harus memindai setiap baris, mengubahnya ke huruf kecil, lalu memeriksa substring-nya. Batas kata, peringkat, query banyak kata, dan pencocokan prefix semuanya harus kamu implementasikan sendiri.

FTS5 adalah jawaban bawaannya. FTS5 adalah jenis virtual table yang memelihara inverted index atas kolom-kolom teksmu, memahami bahasa query kecil, dan memberi peringkat hasil dengan BM25. FTS5 sudah disertakan di SQLite secara bawaan, tanpa ekstensi yang perlu dipasang.

Membuat Tabel FTS5

Kamu membuat tabel FTS5 dengan CREATE VIRTUAL TABLE ... USING fts5(...), sambil menuliskan kolom teks yang ingin di-index:

CREATE VIRTUAL TABLE posts USING fts5(title, body);

INSERT INTO posts (title, body) VALUES
  ('SQLite full-text search', 'FTS5 is fast and built in.'),
  ('Indexes in SQLite', 'B-trees power most lookups in SQLite.'),
  ('Why use SQLite', 'It runs in-process and stores data in one file.');

SELECT title FROM posts WHERE posts MATCH 'sqlite';

Ada tiga hal yang layak diperhatikan. Kolomnya tidak punya tipe, karena FTS5 memperlakukan semuanya sebagai teks. Operator MATCH dipakai terhadap nama tabel (posts MATCH ...), bukan terhadap kolom. Dan query-nya tidak membedakan huruf besar kecil serta sudah dipecah menjadi token, jadi 'sqlite' menemukan SQLite di baris mana pun.

Bahasa Query MATCH

MATCH menerima lebih dari satu kata. String query-nya punya tata bahasa kecil sendiri:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Full-text search', 'FTS5 supports phrase and prefix queries.'),
  ('Indexes', 'A B-tree index speeds up equality lookups.'),
  ('Triggers', 'Use triggers to keep FTS in sync with the source table.');

SELECT title FROM posts WHERE posts MATCH 'fts5 AND prefix';
SELECT title FROM posts WHERE posts MATCH '"keep fts"';
SELECT title FROM posts WHERE posts MATCH 'trig*';
SELECT title FROM posts WHERE posts MATCH 'index NOT trigger';

Apa yang dilakukan masing-masing:

• 'fts5 AND prefix': kedua token harus muncul (dalam urutan apa pun, di mana pun dalam baris).
• '"keep fts"': frasa persis, dengan urutan itu.
• 'trig*': prefix search, cocok dengan trigger, triggers, trigonometry...
• 'index NOT trigger': mengandung index, tidak mengandung trigger.

Kamu juga bisa menargetkan satu kolom dengan column:term, misalnya 'title:sqlite'. Tata bahasa lengkapnya mencakup tanda kurung untuk pengelompokan dan OR untuk alternatif, bentuk yang sama seperti yang kamu harapkan dari mesin pencari.

Peringkat dengan BM25

Secara bawaan, FTS5 menyertakan kolom tersembunyi rank pada setiap baris. Kolom itu berisi skor relevansi BM25: makin rendah angkanya, makin cocok hasilnya. Urutkan berdasarkan kolom itu untuk mendapatkan hasil paling relevan lebih dulu:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite tutorial', 'Learn SQLite from scratch.'),
  ('SQLite vs Postgres', 'Comparing SQLite to Postgres for small apps.'),
  ('SQLite SQLite SQLite', 'A post that mentions SQLite many times.');

SELECT title, rank
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY rank;

Ingin memberi bobot lebih pada kolom tertentu? Panggil bm25() dengan bobot, satu untuk setiap kolom sesuai urutan deklarasinya:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite indexes', 'A short note about B-trees.'),
  ('Postgres notes', 'Includes a paragraph about sqlite for comparison.');

SELECT title, bm25(posts, 10.0, 1.0) AS score
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY score;

Post pertama menang karena sqlite muncul di title (berbobot 10×), bukan hanya di body (berbobot 1×). Pilih bobot yang sesuai dengan cara aplikasimu sebenarnya ingin memberi peringkat.

Menjaga Index Tetap Sinkron

Tabel FTS5 paling sederhana menyimpan salinan teksnya sendiri. Itu tidak masalah untuk data bergaya log yang hanya kamu tambahkan, tapi sebagian besar aplikasi sudah punya tabel asli dan ingin FTS mengikutinya. Pola yang rapi adalah tabel FTS external content ditambah tiga trigger.

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT,
    body TEXT
);

CREATE VIRTUAL TABLE articles_fts USING fts5(
    title, body,
    content='articles',
    content_rowid='id'
);

CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

INSERT INTO articles (title, body) VALUES
  ('SQLite triggers', 'How to use AFTER INSERT triggers.'),
  ('FTS5 patterns', 'External content keeps the data in one place.');

SELECT a.id, a.title
FROM articles a
JOIN articles_fts f ON f.rowid = a.id
WHERE articles_fts MATCH 'triggers';

content='articles' menyuruh FTS5 tidak menyimpan teksnya sendiri: ia akan mengambilnya dari tabel articles saat dibutuhkan. Trigger-trigger itu mencerminkan setiap penulisan ke index FTS. Sekarang articles adalah sumber kebenaran, dan articles_fts hanyalah struktur pencarian di sampingnya.

INSERT INTO articles_fts(articles_fts, ...) VALUES ('delete', ...) yang tampak aneh itu adalah sintaks perintah FTS5 untuk menyuruh index menghapus sebuah baris.

Snippet dan Highlight

Hasil pencarian biasanya butuh pratinjau dengan kata yang cocok ditonjolkan. FTS5 punya dua fungsi untuk itu:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Getting started', 'SQLite is a small, fast, embedded database engine that stores data in a single file on disk.');

SELECT
    highlight(posts, 0, '<b>', '</b>') AS title,
    snippet(posts, 1, '<mark>', '</mark>', '...', 10) AS preview
FROM posts
WHERE posts MATCH 'sqlite database';

• highlight(table, column_index, open, close) mengembalikan teks lengkap kolom dengan token yang cocok dibungkus penanda.
• snippet(table, column_index, open, close, ellipsis, token_count) mengembalikan potongan pendek yang berpusat pada kecocokan.

Indeks kolom dimulai dari nol sesuai urutan deklarasi. Inilah bahan dasar untuk perilaku "kata yang cocok berwarna kuning" yang dibutuhkan setiap UI pencarian.

Jebakan yang Perlu Diketahui

Beberapa hal yang sering menjebak orang:

• MATCH hanya bekerja pada tabel FTS. Kamu tidak bisa memakai MATCH pada kolom biasa. Kalau kamu butuh pencarian di tabel yang sudah ada, pakai pola external-content di atas.
• Jangan lupa mengurutkan berdasarkan rank. Tanpa itu, FTS5 mengembalikan baris sesuai urutan penyimpanan, yang tidak ada hubungannya dengan relevansi.
• Tokenizer itu penting. Tokenizer bawaan (unicode61) memecah teks di batas kata Unicode dan mengubahnya ke huruf kecil. Untuk stemming (run cocok dengan running), pakai tokenizer porter: USING fts5(body, tokenize='porter').
• FTS5 bukan engine yang toleran terhadap salah ketik. Ia melakukan pencocokan prefix, bukan fuzzy matching. Kalau kamu butuh perilaku "mungkin maksudmu...", itu lapisan di atas FTS5.
• Tabel contentless (content='') lebih kecil tapi kehilangan data. Kamu bisa mencari di dalamnya, tapi tidak bisa mengambil teks aslinya, hanya rowid-nya. Berguna kalau teksnya kamu simpan di tempat lain.

Berikutnya: Window Function

FTS5 menangani pencarian teks. Halaman berikutnya membahas bentuk query tingkat lanjut yang berbeda, yaitu window function, yang memungkinkanmu menghitung total berjalan, peringkat, dan analitik per kelompok tanpa meleburkan baris-barismu menjadi agregat.