Skip to main content

Full Text Search

Normally, searching text with LIKE '%word%' is slow on large datasets because MySQL must scan every row.

Full-Text Search (FTS) solves this by creating a special index designed for text searching. It allows efficient searching for words and phrases in large text fields.

  • A Full-Text Index is an index type optimized for text-based columns (CHAR, VARCHAR, TEXT).
  • It supports:
    • Natural language search (search like Google).
    • Boolean search (with operators like +, -, *).
    • Query expansion (broaden results).

Creating a Full-Text Index

CREATE TABLE articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT(title, body)
);

Searching with Full-Text Index​

MySQL searches for relevant results based on word frequency (ignores very common words like "the", "is").

SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST ('database security');

Finds rows where database and security appear. Results are ranked by relevance (higher score = better match).

Allows use of operators for precise control.

SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST ('+mysql -oracle' IN BOOLEAN MODE);
  • +mysql → Must contain "mysql".
  • -oracle → Must not contain "oracle".

Returns only MySQL-related articles, excluding Oracle.

Other Boolean operators:

  • *→ wildcard (e.g., databas* matches "database", "databases").
  • "" → exact phrase search (e.g., "data security").

Query Expansion Mode​

Expands the search using related terms from top matching rows.

SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST ('encryption' WITH QUERY EXPANSION);

Finds not only "encryption" but also related terms found in matching articles (e.g., "security", "keys").