SQL FULLTEXT

What is SQL FULLTEXT?

FULLTEXT creates an index that supports fast natural-language search on CHAR, VARCHAR, and TEXT columns.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL FULLTEXT: Supports: MySQL 5.6+, MariaDB 10.0+ Partial equivalents: PostgreSQL (tsvector), SQL Server (CONTAINS), Oracle Text Not supported: SQLite, standard ANSI SQL

SQL FULLTEXT Full Explanation

FULLTEXT is a MySQL index type and table constraint that enables full-text search capabilities. When you add a FULLTEXT index to one or more text columns, MySQL tokenizes and stores each word in an internal inverted index. Queries that use MATCH ... AGAINST can then locate rows that contain or closely match search terms, returning relevance scores ordered by how well each row matches. FULLTEXT works in natural language, boolean, or query-expansion modes. It only supports InnoDB and MyISAM tables, requires the columns to use a supported character set, and ignores words shorter than the minimum length (default 3) or present in the stopword list. Although similar to PostgreSQL's full-text search, FULLTEXT is proprietary to MySQL and has its own syntax and limitations.

SQL FULLTEXT Syntax

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

-- or add later
ALTER TABLE articles ADD FULLTEXT idx_ft_title_body (title, body);

-- standalone form
CREATE FULLTEXT INDEX idx_ft_body ON articles(body);

SQL FULLTEXT Parameters

index_name (Identifier) - Optional name for the FULLTEXT index
column_list (List) - One or more CHAR, VARCHAR, or TEXT columns to index
WITH PARSER parser_name (Identifier) - Optional parser plugin for custom tokenization

Example Queries Using SQL FULLTEXT

-- Create FULLTEXT index
CREATE FULLTEXT INDEX idx_ft_title_body ON articles(title, body);

-- Natural language search
SELECT id, MATCH(title, body) AGAINST ('database indexing') AS score
FROM articles
WHERE MATCH(title, body) AGAINST ('database indexing');

-- Boolean mode search
SELECT id FROM articles
WHERE MATCH(title, body) AGAINST ('+sql -oracle' IN BOOLEAN MODE);

-- Query expansion mode
SELECT id FROM articles
WHERE MATCH(title, body) AGAINST ('analytics' WITH QUERY EXPANSION);

Expected Output Using SQL FULLTEXT

FULLTEXT index creation succeeds and MySQL returns OK
Subsequent MATCH
AGAINST queries return rows ranked by relevance
Boolean mode yields rows that satisfy required and excluded terms

Use Cases with SQL FULLTEXT

Speed up keyword search in blogs, knowledge bases, or product catalogs
Implement advanced filtering where LIKE would be too slow
Score and rank documents by relevance for search result pages

Common Mistakes with SQL FULLTEXT

Creating FULLTEXT on unsupported data types such as BLOB
Expecting it to work on InnoDB tables in versions prior to MySQL 5.6
Forgetting to use MATCH ... AGAINST when querying, leading to full table scans
Assuming it is case sensitive (search is case insensitive by default)
Not adjusting ft_min_word_len or stopword list for short or domain-specific terms

First Introduced In

MySQL 3.23

Frequently Asked Questions

What character sets are supported?

UTF8, UTF8MB4, and other multibyte character sets are fully supported as long as the server has the necessary parser.

How can I include short words like 'AI' in searches?

Decrease the system variable ft_min_word_len (or innodb_ft_min_token_size for InnoDB) and rebuild the index so that two-letter words are indexed.

Does FULLTEXT slow down writes?

Yes. Each insert, update, or delete on indexed columns triggers index maintenance. For heavy write workloads, consider batching inserts or using delayed indexing.

How is relevance score calculated?

MySQL uses term frequency-inverse document frequency (TF-IDF) under the hood, then normalizes the value so the highest matching row gets the largest score.