MySQL Full-text Index

Summary: in this tutorial, you’ll learn about the MySQL full-text index and how to use it to perform efficient text searches.

Introduction to the MySQL full-text index

MySQL full-text index is a kind of index that allows you to perform efficient text searches. The full-text index has the type of FULLTEXT.

MySQL supports full-text indexes for InnoDB or MyISAM tables with CHAR, VARCHAR, and TEXT columns.

Please note that support for the full-text index in MySQL is available only for InnoDB tables starting from version 5.6.

MySQL allows you to create a FULLTEXT index in the CREATE TABLE statement at the time of table creation or add it to the table later using ALTER TABLE or CREATE FULLTEXT INDEX.

MySQL provides full-text search functions, such as MATCH() and AGAINST(), which you can use to search for columns included in the full-text indexes.

Creating a MySQL full-text index

The following illustrates how to create a full-text index for one or more columns of a table:

CREATE TABLE tbl_name(
   column1 constaint, 
   column2 constaint, 
   ...,
   FULLTEXT(column1, column2)
);Code language: SQL (Structured Query Language) (sql)

In this syntax, you place a list of comma-separated column names in the parentheses after the FULLTEXT keyword.

For example, the following creates a new table called posts that has a full-text index which includes the title and body columns:

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  body TEXT,

  FULLTEXT KEY (title, body)
);Code language: SQL (Structured Query Language) (sql)

Adding a full-text index to an existing table using ALTER TABLE statement

MySQL allows you to add a full-text index to an existing table using the ALTER TABLE statement:

ALTER TABLE tbl_name
ADD FULLTEXT(column1, column2,…);Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, specify the name of the table that you want to create the index after the ALTER TABLE keywords.
Second, use the ADD FULLTEXT clause to define the FULLTEXT index for one or more columns of the table.

The following example creates the posts table and adds a full-text index using the ALTER TABLE statement:

First, create the posts table:

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  body TEXT
);Code language: SQL (Structured Query Language) (sql)

Note that you must drop the posts table if you have created it before using the DROP TABLE statement before running the CREATE TABLE statement above.

Second, add a full-text index to the posts table:

ALTER TABLE posts
ADD FULLTEXT(title, body);

Adding a full-text index to a table using the CREATE INDEX statement

It is possible to use the CREATE FULLTEXT INDEX statement to define a full-text index on columns of a table as follows:

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

For example, the following uses the CREATE FULLTEXT INDEX statement to create a full-text index for the title and body column of the posts table:

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

In this statement, the name of the full-text index is idx_fts.

Removing a full-text index

To remove a FULLTEXT index, you use the ALTER TABLE ... DROP INDEX statement:

ALTER TABLE tbl_name
DROP INDEX index_name;

For example, the following statement deletes the idx_fts index from the posts table:

ALTER TABLE posts
DROP INDEX idx_fts;

A complete MySQL full-text index example

First, create a posts table with a full-text index that includes the title and body columns:

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  body TEXT,
  FULLTEXT KEY (body, title)
);Code language: PHP (php)

Second, insert some rows into the posts table:

INSERT INTO posts (title, body) VALUES
('Introduction to MySQL', 'MySQL is an open-source relational database management system.'),
('MySQL Data Types', 'MySQL supports various data types, including INT, VARCHAR, and TEXT.'),
('MySQL Indexing', 'Proper indexing is crucial for optimizing MySQL query performance.'),
('MySQL Joins', 'Learn about different types of joins in MySQL, such as INNER JOIN and LEFT JOIN.'),
('MySQL Security Best Practices', 'Secure your MySQL database by following recommended security practices.'),
('MySQL Backup and Restore', 'Regularly backup your MySQL database to prevent data loss.'),
('MySQL Stored Procedures', 'Create reusable SQL code with MySQL stored procedures.'),
('MySQL Transactions', 'Ensure data consistency and integrity using MySQL transactions.'),
('MySQL Performance Tuning', 'Optimize the performance of your MySQL database for better efficiency.'),
('MySQL Tools and Utilities', 'Explore tools like MySQL Workbench for managing and interacting with MySQL databases.');
Code language: JavaScript (javascript)

Third, perform a full-text search using the MATCH() and AGAINST() functions to find the posts that contain the word database:

SELECT 
  id, 
  title, 
  body 
FROM 
  posts 
WHERE 
  MATCH (title, body) AGAINST (
    'database' IN NATURAL LANGUAGE MODE
  );
Code language: JavaScript (javascript)

Output:

+----+-------------------------------+-------------------------------------------------------------------------+
| id | title                         | body                                                                    |
+----+-------------------------------+-------------------------------------------------------------------------+
|  1 | Introduction to MySQL         | MySQL is an open-source relational database management system.          |
|  5 | MySQL Security Best Practices | Secure your MySQL database by following recommended security practices. |
|  6 | MySQL Backup and Restore      | Regularly backup your MySQL database to prevent data loss.              |
|  9 | MySQL Performance Tuning      | Optimize the performance of your MySQL database for better efficiency.  |
+----+-------------------------------+-------------------------------------------------------------------------+
4 rows in set (0.01 sec)Code language: JavaScript (javascript)

Summary

Use FULLTEXT to define a full-text index that includes one or more columns in the CREATE TABLE statement.
Use ALTER TABLE ... ADD FULLTEXT statement to add a FULLTEXT index to an existing table.
Use CREATE FULLTEXT INDEX statement to create an index for one or more columns of an existing table.
Use ALTER TABLE ... DROP INDEX statement to remove an existing FULLTEXT index.

Was this tutorial helpful?