How to Create Fulltext Index in MySQL: Syntax and Examples

To create a fulltext index in MySQL, use the CREATE FULLTEXT INDEX statement on one or more text columns of a table. This index helps speed up text searches using the MATCH() and AGAINST() functions.

Syntax

The basic syntax to create a fulltext index is:

• CREATE FULLTEXT INDEX index_name: Names the fulltext index.
• ON table_name (column1, column2, ...): Specifies the table and one or more text columns to index.

This index allows efficient full-text searching on the specified columns.

sql

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

Example

This example creates a fulltext index on the title and body columns of a posts table. It then shows how to search using the fulltext index.

sql

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  body TEXT
) ENGINE=InnoDB;

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

INSERT INTO posts (title, body) VALUES
('MySQL Tutorial', 'Learn how to use MySQL fulltext indexes'),
('Database Tips', 'Fulltext indexes improve search speed');

SELECT * FROM posts
WHERE MATCH(title, body) AGAINST('fulltext' IN NATURAL LANGUAGE MODE);

Output

id | title          | body
---|----------------|---------------------------------------------
1  | MySQL Tutorial | Learn how to use MySQL fulltext indexes
2  | Database Tips  | Fulltext indexes improve search speed

Common Pitfalls

Common mistakes when creating fulltext indexes include:

• Trying to create a fulltext index on non-text columns like INT or DATE.
• Using fulltext indexes on tables with storage engines that do not support them (e.g., InnoDB supports fulltext indexes only from MySQL 5.6+).
• Not using the MATCH() and AGAINST() syntax for searching, which is required to leverage fulltext indexes.

Example of a wrong and right way:

-- Wrong: Creating fulltext index on an INT column
CREATE FULLTEXT INDEX ft_wrong ON posts (id);

-- Right: Creating fulltext index on text columns
CREATE FULLTEXT INDEX ft_right ON posts (title, body);

Quick Reference

Command	Description
CREATE FULLTEXT INDEX index_name ON table_name (columns);	Creates a fulltext index on specified columns.
MATCH(column1, column2) AGAINST('search_term')	Performs a fulltext search using the index.
DROP INDEX index_name ON table_name;	Removes a fulltext index.
SHOW INDEX FROM table_name;	Lists indexes on a table.

Key Takeaways

Use CREATE FULLTEXT INDEX on text columns to enable fast full-text searches.

Fulltext indexes work with MATCH() AGAINST() syntax for searching.

Only text-based columns like VARCHAR, TEXT can have fulltext indexes.

Ensure your storage engine supports fulltext indexes (InnoDB 5.6+ or MyISAM).

Avoid indexing non-text columns and always test your search queries.