Full-text indexes in MySQL

Introduction

Full-text indexes help you quickly find words or phrases inside large text columns, like searching for keywords in articles or product descriptions.

You want to search for specific words inside a large text column, like searching blog posts for a topic.

You need to find records that contain certain keywords in product descriptions.

You want to build a simple search feature for a website that looks inside text fields.

You want to speed up text searches compared to using LIKE with wildcards.

You want to rank search results by how relevant they are to the search words.

Syntax

MySQL

CREATE FULLTEXT INDEX index_name ON table_name (column1, column2, ...);

Full-text indexes work best on CHAR, VARCHAR, or TEXT columns.

In MySQL, full-text indexes are supported on MyISAM and InnoDB tables.

Examples

Creates a full-text index named 'ft_index' on the 'title' and 'body' columns of the 'articles' table.

MySQL

CREATE FULLTEXT INDEX ft_index ON articles (title, body);

Adds a full-text index on the 'description' column of the 'products' table.

MySQL

ALTER TABLE products ADD FULLTEXT INDEX ft_desc (description);

Sample Program

This example creates a 'books' table with a full-text index on 'title' and 'description'. It inserts three books and then searches for books related to 'SQL'.

MySQL

CREATE TABLE books (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100),
  description TEXT,
  FULLTEXT (title, description)
) ENGINE=InnoDB;

INSERT INTO books (title, description) VALUES
('Learn SQL', 'A book about learning SQL databases'),
('Cooking Tips', 'Tips and tricks for cooking delicious meals'),
('SQL Performance', 'How to optimize SQL queries for speed');

SELECT id, title FROM books
WHERE MATCH(title, description) AGAINST('SQL');

Important Notes

Full-text search is case-insensitive by default.

Stopwords (common words like 'the', 'and') may be ignored in searches.

Minimum word length for indexing depends on MySQL settings (default is 3 or 4 characters).

Summary

Full-text indexes speed up searching for words inside text columns.

They are useful for building search features in databases.

Use MATCH() AGAINST() to perform full-text searches on indexed columns.