Full-Text Search and JSON

Why Full-Text

LIKE with percent wildcards cannot use indexes. Full-text indexes tokenize text and answer MATCH ... AGAINST queries with relevance ranking instead.

Natural Language Search

CREATE FULLTEXT INDEX idx_articles_body ON articles(body);
SELECT title FROM articles
WHERE MATCH(body) AGAINST('database performance');
SELECT title, MATCH(body) AGAINST('database performance') AS score
FROM articles ORDER BY score DESC;

Boolean Mode

SELECT title FROM articles
WHERE MATCH(body) AGAINST('+database -nosql' IN BOOLEAN MODE);

JSON Columns

CREATE TABLE products (
  id INT PRIMARY KEY,
  attrs JSON
);
SELECT id, JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.brand')) AS brand
FROM products;

Key Points

• Full-text search beats LIKE for text columns.
• BOOLEAN MODE adds and removes terms.
• JSON columns store flexible documents.
• JSON_EXTRACT and JSON_UNQUOTE read values.