Fulltext Search MariaDB plus Ekspansi Query LLM
Ganti LIKE dengan FULLTEXT index MariaDB plus ekspansi query via LLM: dua lapis pencarian yang tetap jalan walau index gagal atau LLM mati.
Search di aplikasi saya waktu itu pakai pola klasik: WHERE title LIKE '%kata%' OR content LIKE '%kata%'. Berjalan oke di awal. Tapi pas datanya mulai ratusan post, request search makin lambat. Yang lebih bikin geregetan: hasilnya nggak ada ranking sama sekali. Post yang relevan banget dan yang cuma kebetulan nyebut kata itu sekali, dapet urutan yang sama.
LIKE itu bukan mesin pencari. Dia cuma substring match tanpa sense of relevansi.
Jadi saya pasang FULLTEXT index di MariaDB, dan ternyata ada beberapa jebakan yang nggak saya duga sebelum mulai.
Bootstrap dari Startup, Bukan Manual
Saya nggak mau bikin migration script terpisah buat FULLTEXT index. Alasannya sederhana: saya udah punya lifespan hook di FastAPI buat setup lain, dan CREATE FULLTEXT INDEX IF NOT EXISTS itu idempoten, aman dijalankan berulang. Jadi saya taruh langsung di sana.
CREATE FULLTEXT INDEX IF NOT EXISTS ft_articles_search
ON articles_posts (title, excerpt, content)Satu baris. Tapi jebakannya ada di kolom.
Kolom yang didefinisikan di index harus persis sama dengan kolom yang dipakai di MATCH() saat query. Kalau index definisi (title, excerpt, content) tapi query pakai MATCH(title, content), MariaDB tetap jalan tapi hasilnya nggak benar, atau malah error tergantung versi. Ini bukan hal yang saya sadari dari dokumentasi awal; saya nemu sendiri pas query FULLTEXT jalan tapi skornya aneh.
Kalau bootstrap gagal, misalnya permission kurang di environment dev yang pakai SQLite, saya nggak mau aplikasi crash. Jadi ada flag _index_ready. Nampilnya False, dan pencarian otomatis fallback ke LIKE. Graceful off. User nggak pernah tau ada yang berubah.
Dua Jalur di Repository
repositories.py punya dua path search sekarang:
Path pertama, FULLTEXT: pakai MATCH(title, excerpt, content) AGAINST(:terms IN NATURAL LANGUAGE MODE) dan hasilnya di-order berdasarkan score. Ini yang jadi primary path kalau index ready. Natural language mode artinya MariaDB buang stopwords dan bikin scoring berdasarkan relevansi [1], bukan cuma frekuensi kemunculan. Contoh di dokumentasi resmi MariaDB [4] persis pakai pola ini: MATCH(title,body) AGAINST('...' IN NATURAL LANGUAGE MODE) AS score.
Path kedua, LIKE fallback: kalau _index_ready False, query pakai LIKE biasa dengan escaping persen dan underscore. Nggak ada ranking, tapi minimal pencarian tetap jalan. Solusi yang buruk secara performa, tapi lebih baik daripada search mati total.
Query Expansion via LLM sebagai Lapisan Kedua
FULLTEXT solve masalah ranking. Tapi tetap ada batasan: kalau user cari "cara deploy" tapi post-nya judulnya "Tutorial CI/CD Pipeline", FULLTEXT nggak otomatis nangkep karena kata-katanya beda.
Ini yang bikin saya tambahin query expansion pakai LLM.
Alurnya: user masukin query → query itu dikirim ke LLM → LLM kembaliin 3-8 kata kunci sinonim/tambahan → kata kunci itu di-OR-in ke MATCH. Jadi kalau user cari "cara deploy", LLM mungkin tambahin "ci cd", "pipeline", "production", "launching", dan semua itu masuk ke pencarian.
Detail implimentasinya di llm_client.py: saya pakai client OpenAI-compatible yang ngecek dulu apakah env base_url, api_key, dan model terisi. Kalau salah satu aja kosong, client langsung configured=False dan fitur query expansion mati tanpa error. Kalau configured tapi request gagal (timeout, API down, respons aneh), complete() catch semua exception, return None. Tidak pernah raise. 8 detik timeout.
Di expand_query(), prompt minta LLM return JSON array 3-8 kata kunci. Hasilnya di-strip dari backtick fence yang sering LLM tambahin, divalidasi harus list of string. Kalau gagal di step mana aja, return None. Fitur expansion diam-diam off, pencarian tetap jalan tanpa expansion.
Yang penting dicatat: query expansion ini bukan menggantikan FULLTEXT search. Dia cuma menambah breadth. Kalau LLM mati total, pencarian tetap jalan, cuma hasilnya lebih sempit karena cuma match exact terms dari user. Nggak ada fitur yang hilang secara visible ke user.
Kenapa Desainnya Begini
Dua hal yang saya pelajari dari proses ini.
Pertama, FULLTEXT index di MariaDB itu straightforward kalau kamu tahu kolomnya harus match. Tapi bootstrap dari runtime itu pilihan yang saya ambil supaya nggak perlu tambah migration script. Trade-off-nya: harus handle graceful fallback kalau index gagal dibuat. Kalau aplikasi saya butuh zero-downtime di production, mungkin saya bikin migration terpisah. Tapi untuk skala sekarang, approach dari startup hook cukup.
Kedua, LLM query expansion itu eksperiment yang hasilnya kualitatif belum saya ukur secara formal. Saya bisa bilang hasilnya "lebih relevan" dari LIKE, tapi saya belum bikin benchmark apalagi A/B test. Tulis ini sebagai catatan jujur, bukan klaim. Kalau kamu mau coba, mulai dari logging dulu: log query asli vs expanded query vs top 3 result, dan lihat sendiri apakah ada bedanya di use case kamu.
Sumber
Artikel lain yang nyambung: jebakan tipe DATETIME naive di MariaDB dan wiring LLM router di dalam container.