Full-Text Index #

-- Query pencarian yang terlihat wajar
SELECT id, title, content FROM articles WHERE content LIKE '%database%';

-- EXPLAIN-nya:
-- +------+------+------+----------+-------------+
-- | type | key  | rows | filtered | Extra       |
-- +------+------+------+----------+-------------+
-- | ALL  | NULL | 850000 | 11.11 | Using where |
-- +------+------+------+----------+-------------+
-- type = ALL → full table scan
-- rows = 850.000 → setiap baris harus dibaca

-- Kenapa index tidak bisa membantu?
-- B-Tree index disortir berdasarkan nilai awal string.
-- Index bisa membantu WHERE content LIKE 'database%' (awalan pasti)
-- Tapi TIDAK BISA membantu WHERE content LIKE '%database%'
-- karena database tidak tahu di mana dalam string kata itu muncul.

-- Pada tabel baru
CREATE TABLE articles (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    title      VARCHAR(500) NOT NULL,
    content    MEDIUMTEXT NOT NULL,
    status     VARCHAR(20) NOT NULL DEFAULT 'draft',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_articles_status (status),
    FULLTEXT INDEX ft_articles_search (title, content)  -- FTI mencakup dua kolom
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Menambahkan FTI ke tabel yang sudah ada
ALTER TABLE articles
ADD FULLTEXT INDEX ft_articles_search (title, content);

-- FTI bisa mencakup beberapa kolom — query MATCH harus menyebut semua kolom
-- yang ada di index, tidak bisa sebagian saja

-- NATURAL LANGUAGE MODE (default): mencari kata, mengurutkan berdasarkan relevansi
SELECT id, title,
       MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE)
  AND status = 'published'
ORDER BY score DESC
LIMIT 20;
-- Cocok untuk: search engine umum, "cari artikel yang paling relevan"
-- Kelemahan: kata yang terlalu umum (muncul di > 50% baris) diabaikan otomatis

-- BOOLEAN MODE: kontrol eksplisit dengan operator
SELECT id, title
FROM articles
WHERE MATCH(title, content) AGAINST(
    '+database +optimization -beginner'
    IN BOOLEAN MODE
)
  AND status = 'published'
LIMIT 20;
-- + berarti WAJIB ada kata ini
-- - berarti HARUS TIDAK ada kata ini
-- Tanpa prefix: optional (meningkatkan relevansi jika ada)
-- "frasa" (dalam tanda kutip): harus muncul sebagai frasa berurutan
-- Cocok untuk: filter yang presisi, search dengan exclude

-- Contoh operator BOOLEAN MODE yang lengkap:
SELECT id, title FROM articles
WHERE MATCH(title, content) AGAINST(
    '+postgresql +"query optimization" -beginner tutorial*'
    IN BOOLEAN MODE
);
-- postgresql   : wajib ada
-- "query optimization" : frasa ini harus muncul berurutan
-- -beginner    : jangan tampilkan artikel untuk pemula
-- tutorial*    : wildcard — cocok dengan "tutorial", "tutorials", dll.

-- KETERBATASAN 1: Minimum word length (default: 3 karakter untuk InnoDB)
-- Kata pendek tidak diindex!
SELECT @@innodb_ft_min_token_size;  -- default: 3
-- "go", "db", "id" tidak akan diindex atau bisa dicari

-- Ubah jika perlu mendukung kata pendek:
SET GLOBAL innodb_ft_min_token_size = 2;
-- Setelah mengubah: REBUILD semua fulltext index!
-- ALTER TABLE articles DROP INDEX ft_articles_search;
-- ALTER TABLE articles ADD FULLTEXT INDEX ft_articles_search (title, content);

-- KETERBATASAN 2: Stopwords — kata yang selalu diabaikan
-- MySQL punya daftar stopwords bawaan: "the", "a", "is", "in", "at", dll.
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
-- "about", "are", "as", "at", "be", "by", "com", "for", "from", ...

-- Konsekuensi: pencarian "how to use database" hanya mencari "database"
-- "how", "to", "use" adalah stopwords!

-- Nonaktifkan stopword untuk konteks teknikal:
SET GLOBAL innodb_ft_enable_stopword = 0;
-- Atau gunakan stopword list kustom

-- KETERBATASAN 3: Kata yang muncul di > 50% baris diabaikan di NATURAL LANGUAGE
-- Jika 60% artikel mengandung kata "mysql", kata itu tidak berguna untuk relevance
-- Gunakan BOOLEAN MODE untuk override perilaku ini

-- KETERBATASAN 4: Minimal 3 baris data
-- FTI tidak akan mengembalikan hasil jika tabel punya < 3 baris
-- Ini sering menyebabkan kebingungan di environment test dengan data minimal

-- Pendekatan 1: Full-Text Index menggunakan GIN (Generalized Inverted Index)
-- GIN cocok untuk dokumen — query cepat tapi update lebih lambat
CREATE INDEX idx_articles_fts
ON articles USING GIN(to_tsvector('english', title || ' ' || content));

-- Pendekatan 2: GIST index — update lebih cepat tapi query sedikit lebih lambat
CREATE INDEX idx_articles_fts_gist
ON articles USING GIST(to_tsvector('english', title || ' ' || content));

-- Query dengan to_tsvector + to_tsquery
SELECT id, title,
       ts_rank(
           to_tsvector('english', title || ' ' || content),
           to_tsquery('english', 'database & optimization')
       ) AS rank
FROM articles
WHERE to_tsvector('english', title || ' ' || content)
      @@ to_tsquery('english', 'database & optimization')
  AND status = 'published'
ORDER BY rank DESC
LIMIT 20;

-- Tambahkan kolom tsvector yang diupdate otomatis
ALTER TABLE articles
ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(content, '')), 'B')
    ) STORED;
-- setweight 'A' untuk title (bobot lebih tinggi)
-- setweight 'B' untuk content (bobot lebih rendah)

-- Buat GIN index di kolom generated
CREATE INDEX idx_articles_search_vector
ON articles USING GIN(search_vector);

-- Query sekarang jauh lebih bersih dan cepat
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database & optimization') query
WHERE search_vector @@ query
  AND status = 'published'
ORDER BY rank DESC
LIMIT 20;
-- search_vector sudah tersimpan → tidak perlu hitung to_tsvector setiap query

-- to_tsquery: kata dipisah dengan operator logika
to_tsquery('english', 'database & optimization')  -- AND: kedua kata harus ada
to_tsquery('english', 'database | mysql')          -- OR: salah satu atau keduanya
to_tsquery('english', 'database & !beginner')      -- NOT: database tapi bukan beginner
to_tsquery('english', 'optim:*')                   -- prefix: optimasi, optimistic, dll

-- plainto_tsquery: input bebas, tidak perlu operator eksplisit
plainto_tsquery('english', 'database optimization tutorial')
-- otomatis diinterpretasikan sebagai AND

-- phraseto_tsquery: pencarian frasa berurutan
phraseto_tsquery('english', 'query optimization')
-- mencari "query" diikuti langsung "optimization"

-- websearch_to_tsquery: sintaks mirip Google Search
websearch_to_tsquery('english', 'database optimization -beginner "index scan"')
-- Lebih user-friendly untuk input dari search box

-- ANTI-PATTERN: FTI tanpa filter tambahan
SELECT id, title, content
FROM articles
WHERE MATCH(title, content) AGAINST('database' IN NATURAL LANGUAGE MODE);
-- Mungkin mengembalikan 100.000 baris untuk kata yang umum
-- Scoring dan ranking 100.000 baris sangat mahal

-- BENAR: FTI dikombinasikan dengan filter B-Tree
SELECT id, title,
       MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE status = 'published'          -- filter B-Tree: index di status
  AND category_id IN (1, 5, 12)     -- filter B-Tree: index di category_id
  AND created_at >= '2025-01-01'    -- filter B-Tree: index di created_at
  AND MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 20;
-- Database pertama filter dengan B-Tree (mungkin tinggal 5.000 artikel yang relevan)
-- Baru FTI dijalankan pada 5.000 artikel itu, bukan seluruh tabel

-- Di MySQL: cek apakah FTI atau B-Tree yang dipakai duluan
EXPLAIN SELECT id, title
FROM articles
WHERE status = 'published'
  AND MATCH(title, content) AGAINST('database' IN BOOLEAN MODE);

-- Output yang diharapkan:
-- +------+-------+--------------------------+-------+------+---------------------------+
-- | type | key   | key_len                  | rows  | Extra                       |
-- +------+-------+--------------------------+-------+------+---------------------------+
-- | fulltext | ft_articles_search | 0 | 1 | Using where; Ft_hints: no_ranking |
-- +------+-------+--------------------------+-------+------+---------------------------+

-- Jika MySQL memilih FTI saja dan mengabaikan index status:
-- Gunakan FORCE INDEX untuk memaksa urutan
SELECT id, title
FROM articles USE INDEX (idx_articles_status, ft_articles_search)
WHERE status = 'published'
  AND MATCH(title, content) AGAINST('database' IN BOOLEAN MODE);

-- MySQL: tampilkan score relevansi untuk debugging
SELECT
    id,
    title,
    MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE) AS relevance_score
FROM articles
WHERE MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE)
  AND status = 'published'
ORDER BY relevance_score DESC
LIMIT 10;

-- Score yang tinggi = lebih relevan
-- Jika semua score sangat rendah (< 0.1): kata mungkin terlalu umum atau tidak ada di index

-- PostgreSQL: tampilkan headline (snippet dengan highlight)
SELECT
    id,
    title,
    ts_rank(search_vector, query) AS rank,
    ts_headline('english', content, query,
        'MaxWords=50, MinWords=20, ShortWord=3, HighlightAll=FALSE'
    ) AS snippet
FROM articles, to_tsquery('english', 'database & optimization') query
WHERE search_vector @@ query
  AND status = 'published'
ORDER BY rank DESC
LIMIT 10;
-- ts_headline menghasilkan snippet konten dengan kata kunci di-highlight
-- Berguna untuk menampilkan preview hasil pencarian ke user

-- ✗ Anti-pattern 1: LIKE '%keyword%' untuk pencarian di tabel besar
SELECT * FROM articles WHERE content LIKE '%database%';
-- → Full table scan, index tidak berguna
-- ✓ Solusi: gunakan FULLTEXT INDEX dengan MATCH ... AGAINST

-- ✗ Anti-pattern 2: FTI tanpa filter B-Tree
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('database');
-- → Bisa mengembalikan ratusan ribu baris untuk kata umum
-- ✓ Solusi: selalu tambahkan WHERE status = ? atau filter lain sebelum FTI

-- ✗ Anti-pattern 3: ORDER BY MATCH ... AGAINST tanpa LIMIT
SELECT *, MATCH(title, content) AGAINST('database') AS score
FROM articles
WHERE MATCH(title, content) AGAINST('database')
ORDER BY score DESC;
-- → Hitung score untuk semua baris yang match, sort semua — sangat mahal
-- ✓ Solusi: selalu tambahkan LIMIT 20 atau pagination

-- ✗ Anti-pattern 4: FULLTEXT INDEX di kolom yang sering diupdate
-- Misalnya: index di kolom notes, last_comment, activity_log
-- → Setiap UPDATE ke kolom tersebut rebuild bagian inverted index
-- ✓ Solusi: FTI hanya untuk kolom yang jarang diubah (title, content utama)

-- ✗ Anti-pattern 5: mencari kata pendek tanpa mengubah min token size
-- MATCH(title) AGAINST('go' IN BOOLEAN MODE) → tidak mengembalikan hasil
-- karena "go" (2 karakter) di bawah minimum token size default (3)
-- ✓ Solusi: ubah innodb_ft_min_token_size = 2 dan rebuild index

-- ✗ Anti-pattern 6: tidak handle stopword di NATURAL LANGUAGE MODE
-- User mencari "how to use index" → hanya "index" yang dicari
-- "how", "to", "use" adalah stopwords → user bingung kenapa hasilnya sedikit
-- ✓ Solusi: gunakan BOOLEAN MODE untuk query yang butuh presisi,
--   atau nonaktifkan stopword list, atau gunakan search engine eksternal