-- At table creation time
CREATE TABLE articles (
  id      INT          NOT NULL AUTO_INCREMENT,
  title   VARCHAR(255) NOT NULL,
  body    TEXT         NOT NULL,
  created DATETIME     DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FULLTEXT INDEX ft_article_content (title, body)
);

-- On an existing table
ALTER TABLE articles ADD FULLTEXT INDEX ft_article_content (title, body);

-- Using CREATE INDEX syntax
CREATE FULLTEXT INDEX ft_article_content ON articles (title, body);

-- Remove a FULLTEXT index
ALTER TABLE articles DROP INDEX ft_article_content;

-- Basic full-text search (natural language mode, default)
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('mysql database');

-- MATCH() returns a relevance score — use it for ranking
SELECT id, title,
  ROUND(MATCH(title, body) AGAINST('mysql database'), 4) AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST('mysql database')
ORDER BY relevance DESC
LIMIT 10;

-- Must contain 'mysql', must not contain 'oracle'
SELECT title FROM articles
WHERE MATCH(title, body) AGAINST('+mysql -oracle' IN BOOLEAN MODE);

-- Must contain 'mysql' AND 'index'
SELECT title FROM articles
WHERE MATCH(title, body) AGAINST('+mysql +index' IN BOOLEAN MODE);

-- Exact phrase match — words must be adjacent
SELECT title FROM articles
WHERE MATCH(title, body) AGAINST('"primary key"' IN BOOLEAN MODE);

-- Wildcard: find 'data', 'database', 'datatype', etc.
SELECT title FROM articles
WHERE MATCH(title, body) AGAINST('data*' IN BOOLEAN MODE);

-- Complex: must have 'mysql', optionally 'tutorial' or 'guide', never 'paid'
-- 'guide' ranks higher than 'tutorial'
SELECT title, MATCH(title, body) AGAINST('+mysql >guide tutorial -paid' IN BOOLEAN MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST('+mysql >guide tutorial -paid' IN BOOLEAN MODE)
ORDER BY score DESC;

-- Show relevance scores for debugging and tuning
SELECT
  id,
  title,
  ROUND(MATCH(title, body) AGAINST('mysql index performance'), 4) AS score
FROM articles
WHERE MATCH(title, body) AGAINST('mysql index performance')
ORDER BY score DESC;

-- Scores differ between natural language mode and boolean mode
SELECT
  title,
  MATCH(title, body) AGAINST('mysql' IN NATURAL LANGUAGE MODE) AS nl_score,
  MATCH(title, body) AGAINST('+mysql' IN BOOLEAN MODE)         AS bool_score
FROM articles
WHERE MATCH(title, body) AGAINST('mysql' IN NATURAL LANGUAGE MODE);

-- Explicit natural language mode
SELECT title,
  MATCH(title, body) AGAINST('mysql tutorial' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST('mysql tutorial' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 10;

-- Search for 'database' but also find articles about SQL, MySQL, schema
-- even if they do not contain the word 'database'
SELECT title
FROM articles
WHERE MATCH(title, body) AGAINST('database' WITH QUERY EXPANSION);

-- Useful for finding conceptually related articles a user might want

-- Blend relevance score with recency using a time-decay factor
SELECT
  id,
  title,
  created_at,
  MATCH(title, body) AGAINST('mysql performance' IN BOOLEAN MODE) AS relevance,
  -- Recency factor: 1.0 for today, ~0.5 for 30 days ago, decays exponentially
  EXP(-0.023 * DATEDIFF(NOW(), created_at))                       AS recency_factor,
  -- Combined score: adjust weights to tune the balance
  MATCH(title, body) AGAINST('mysql performance' IN BOOLEAN MODE)
    * EXP(-0.023 * DATEDIFF(NOW(), created_at))                   AS combined_score
FROM articles
WHERE MATCH(title, body) AGAINST('mysql performance' IN BOOLEAN MODE)
  AND published = 1
ORDER BY combined_score DESC
LIMIT 20;

# Check current minimum token size
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';
# Default: 3

# To index 2-character words (e.g. "go", "UK", "AI"):
# Add to my.cnf under [mysqld]:
# innodb_ft_min_token_size = 2

# View the default stop word list
SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;

# Use a custom stop word file
# innodb_ft_server_stopword_table = mydb/my_stopwords
# (table with a single VARCHAR column named 'value')

-- After changing innodb_ft_min_token_size, rebuild ALL FULLTEXT indexes
ALTER TABLE articles DROP INDEX ft_article_content;
ALTER TABLE articles ADD FULLTEXT INDEX ft_article_content (title, body);

-- Also run OPTIMIZE TABLE to flush the FTS cache
OPTIMIZE TABLE articles;

-- Each FULLTEXT index creates 6 auxiliary tables (FTS_*)
-- They are visible in information_schema
SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME LIKE 'FTS_%';
-- Example: FTS_000000000169_00000000016a_INDEX_1, etc.

-- InnoDB adds a hidden FTS_DOC_ID column (BIGINT UNSIGNED) to every table
-- with a FULLTEXT index for fast lookup
SHOW CREATE TABLE articlesG

-- InnoDB batches FULLTEXT index updates optimistically
-- Recent inserts/updates may not be immediately searchable
-- Force sync with OPTIMIZE TABLE
OPTIMIZE TABLE articles;

-- Check the size of FULLTEXT auxiliary tables
SELECT
  table_name,
  ROUND(data_length / 1024 / 1024, 2) AS data_mb,
  ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
  AND table_name LIKE 'FTS_%';

-- Check ngram token size (default: 2)
SHOW VARIABLES LIKE 'ngram_token_size';

-- Create a FULLTEXT index using the ngram parser
CREATE TABLE products_cjk (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  name_ja TEXT,
  FULLTEXT INDEX ft_name (name_ja) WITH PARSER ngram
);

-- Search CJK text with ngram
SELECT * FROM products_cjk
WHERE MATCH(name_ja) AGAINST('データベース' IN BOOLEAN MODE);

-- ngram also helps search short English words like "MySQL", "AI", "JS"
-- when innodb_ft_min_token_size > 2 would otherwise exclude them

-- Product table with FULLTEXT on name + description + tags
CREATE TABLE products (
  id          INT          NOT NULL AUTO_INCREMENT,
  name        VARCHAR(255) NOT NULL,
  description TEXT         NOT NULL,
  tags        VARCHAR(500),
  price       DECIMAL(10,2),
  published   TINYINT(1)   NOT NULL DEFAULT 0,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FULLTEXT INDEX ft_products (name, description, tags)
);

-- Search API: rank by relevance, blend with recency
-- ? placeholder will be bound by the application
SELECT
  id,
  name,
  price,
  LEFT(description, 150)                                       AS excerpt,
  MATCH(name, description, tags) AGAINST(? IN BOOLEAN MODE)    AS relevance
FROM products
WHERE published = 1
  AND MATCH(name, description, tags) AGAINST(? IN BOOLEAN MODE)
ORDER BY relevance DESC
LIMIT 20 OFFSET 0;

-- Boolean search: must contain 'laptop', optionally 'gaming', exclude 'refurbished'
SELECT name, price
FROM products
WHERE published = 1
  AND MATCH(name, description, tags)
      AGAINST('+laptop gaming -refurbished' IN BOOLEAN MODE)
ORDER BY
  MATCH(name, description, tags) AGAINST('+laptop gaming -refurbished' IN BOOLEAN MODE) DESC,
  price ASC;

-- View tokenized words in the FULLTEXT index
-- (Requires setting innodb_ft_aux_table first)
SET GLOBAL innodb_ft_aux_table = 'mydb/articles';

SELECT * FROM information_schema.INNODB_FT_INDEX_CACHE LIMIT 20;
-- Shows: word, doc_id, position, count

SELECT * FROM information_schema.INNODB_FT_INDEX_TABLE LIMIT 20;
-- Shows committed (persisted) index entries

-- Check FULLTEXT configuration
SHOW VARIABLES LIKE 'innodb_ft%';
-- innodb_ft_min_token_size    = 3
-- innodb_ft_max_token_size    = 84
-- innodb_ft_enable_stopword   = ON
-- innodb_ft_server_stopword_table = (empty = use default)

-- Option 1: ALTER TABLE (rebuilds table online in MySQL 5.6+ / InnoDB)
-- This acquires a metadata lock for the duration — may block writes briefly
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (title, body),
  ALGORITHM=INPLACE, LOCK=NONE;
-- ALGORITHM=INPLACE allows concurrent DML on MySQL 5.7+ (for FULLTEXT, LOCK=SHARED may be needed)

-- Option 2: CREATE INDEX (same as ALTER TABLE ADD FULLTEXT)
CREATE FULLTEXT INDEX ft_content ON articles (title, body);

-- Option 3: For very large tables, use pt-online-schema-change
-- pt-online-schema-change --alter "ADD FULLTEXT INDEX ft_content (title, body)" --   D=mydb,t=articles --execute

-- After adding the index, optimize to flush FTS cache to disk
OPTIMIZE TABLE articles;

-- Verify the index was created
SHOW INDEX FROM articles WHERE Index_type = 'FULLTEXT';