How to create a FULLTEXT index in MySQL for search optimization

Create a FULLTEXT index in MySQL to enable efficient keyword-based searches on text columns. This guide applies to MySQL 8.0 and 8.4, which use InnoDB by default. You will configure the server to support full-text search, create the index, and verify that queries return accurate results.

Prerequisites

• MySQL server version 8.0.22 or later running on Linux or Windows.
• A database user with ALTER and CREATE privileges.
• An existing table with a TEXT, MEDIUMTEXT, or LONGTEXT column.
• Ensure the ft_min_word_len variable is set correctly in my.cnf if you need to index short words.

Step 1: Configure the full-text search variables

MySQL uses specific variables to control how full-text indexes behave. By default, words shorter than four characters are ignored. You must adjust ft_min_word_len to include shorter keywords if your data requires it. Open the my.cnf file located in /etc/my.cnf or /etc/mysql/my.cnf and add the following line under the [mysqld] section:

[mysqld]
ft_min_word_len = 2

Restart the MySQL service to apply the changes. On Ubuntu or Debian systems, run:

sudo systemctl restart mysql

On CentOS or RHEL systems, run:

sudo systemctl restart mysqld

Verify the new setting is active by running:

SHOW VARIABLES LIKE 'ft_min_word_len';

You should see the output:

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| ft_min_word_len | 2     |
+-----------------+-------+

Step 2: Create a FULLTEXT index on a table

Create a FULLTEXT index on a specific column or multiple columns. The syntax differs slightly between InnoDB and MyISAM, but modern MySQL uses InnoDB. Use the ALTER TABLE statement to add the index. Replace your_table and your_column with your actual names:

ALTER TABLE your_table
  ADD FULLTEXT INDEX idx_search (your_column);

If you want to search across multiple columns, list them inside the parentheses:

ALTER TABLE your_table
  ADD FULLTEXT INDEX idx_search (column1, column2, column3);

MySQL automatically rebuilds the index if the table is not locked during the operation. If the table is large, the process may take several minutes. Monitor the progress by checking the information_schema.innodb_trx table if necessary.

Step 3: Create a FULLTEXT index with a custom tokenizer

By default, MySQL uses the stopword list to ignore common words like "the" or "and". To create an index that includes these words, use the WITH PARSER ngram or WITH PARSER stopword clause. For standard search, the default tokenizer is sufficient. To use the ngram parser for phrase matching, run:

ALTER TABLE your_table
  ADD FULLTEXT INDEX idx_search (your_column)
  WITH PARSER ngram;

This parser allows you to search for exact phrases. Note that ngram is only available in MySQL 8.0.19 and later. If you need to ignore stopwords, use the default parser:

ALTER TABLE your_table
  ADD FULLTEXT INDEX idx_search (your_column)
  WITH PARSER stopword;

Step 4: Query the FULLTEXT index

Run a SELECT statement using the IN operator to search for keywords. The query returns rows where the keywords appear in the indexed column. Use the RELEVANCY_SCORE function to sort results by relevance. Replace your_table and your_column with your actual names:

SELECT your_column, RELEVANCY_SCORE(your_column, 'keyword1 keyword2') AS score
FROM your_table
WHERE MATCH(your_column) AGAINST('keyword1 keyword2' IN BOOLEAN MODE)
ORDER BY score DESC;

The IN BOOLEAN MODE clause allows you to use plus signs to force inclusion of words and minus signs to exclude them. For example, +important -bad forces "important" and excludes "bad".

Verify the installation

Confirm that the FULLTEXT index exists and is being used by the query optimizer. Run the following command to list all FULLTEXT indexes in the database:

SHOW INDEX FROM your_table WHERE Index_type = 'FULLTEXT';

You should see output similar to:

+------------+--------------+----------+--------------+-------------+----------------+---------+
| Table      | Part_name    | Key_name | Column_name  | Collation   | Cardinal_index | Index_type |
+------------+--------------+----------+--------------+-------------+----------------+---------+
| your_table | idx_search   | idx_search | your_column | NULL        | NULL           | FULLTEXT |
+------------+--------------+----------+--------------+-------------+----------------+---------+

Run an EXPLAIN query to ensure the optimizer uses the FULLTEXT index:

EXPLAIN SELECT your_column
FROM your_table
WHERE MATCH(your_column) AGAINST('keyword1' IN BOOLEAN MODE);

The type column in the output should show fulltext. If it shows ALL, the index is not being used. Check the key column to confirm the index name.

Troubleshooting

If the query returns no results, check the ft_min_word_len setting. Words shorter than the threshold are ignored. Increase the threshold or use WITH PARSER ngram to include short words. If the index is not being used, ensure the query does not contain functions or expressions on the indexed column. For example, avoid WHERE YEAR(your_column) = 2023 when searching for text. Use IN BOOLEAN MODE to force the optimizer to use the index. If the index is corrupted, drop it and recreate it. Run:

ALTER TABLE your_table DROP INDEX idx_search;
ALTER TABLE your_table ADD FULLTEXT INDEX idx_search (your_column);

Reindexing a large table can take time. Schedule this operation during low-traffic periods. If the server is still slow, check the innodb_buffer_pool_size setting to ensure enough memory is available for caching indexes.