How to perform full-text search in MySQL 8

Introduction

Searching for text within a database is a common requirement for many applications. In scenarios where you need to search through a large amount of textual data, a simple query with LIKE can be inefficient and slow. MySQL 8 provides a powerful feature known as Full-Text Search (FTS) to address this challenge, allowing for a faster and more natural way of searching through text columns.

This tutorial will guide you through the fundamentals of implementing full-text search in MySQL 8, including basic to advanced examples and how to handle search outputs. We’ll cover creating full-text indexes, using natural language mode, boolean mode, and advanced features like word stemming and stop words.

Prerequisites

• MySQL 8 server installation
• Basic knowledge of SQL
• A database and table with textual data to search

Enabling Full-Text Search

To start using full-text search, you must first create a full-text index on the column you wish to search. This is done using the CREATE FULLTEXT INDEX statement.

CREATE FULLTEXT INDEX ft_index_name ON table_name (column_name);

For example, if you want to enable full-text search on the description column of a products table:

CREATE FULLTEXT INDEX idx_description ON products (description);

Basic Full-Text Search

After creating the index, you can start searching using the MATCH() ... AGAINST() syntax.

SELECT * FROM products WHERE MATCH(description) AGAINST('keyword');

The basic form of the match against syntax uses natural language mode. For example, to find products related to ‘coffee’:

SELECT * FROM products WHERE MATCH(description) AGAINST('coffee');

Natural Language Mode vs Boolean Mode

MySQL full-text search has two main modes: Natural Language Mode (default) and Boolean Mode. You can specify the mode in the query:

SELECT * FROM products WHERE MATCH(description) AGAINST('coffee' IN NATURAL LANGUAGE MODE);
SELECT * FROM products WHERE MATCH(description) AGAINST('coffee' IN BOOLEAN MODE);

In Boolean Mode, you can use operators to fine-tune your search:

• + signifies that the word must be present.
• - signifies that the word should not be present.
• * at the end of a word is a wildcard.
• …

An example query using Boolean Mode can be:

SELECT * FROM products WHERE MATCH(description) AGAINST('+coffee -decaf' IN BOOLEAN MODE);

Relevance Scores

Full-text searches can also provide a relevance score that indicates how closely a record matches the search terms:

SELECT *, MATCH(description) AGAINST('coffee') AS relevance FROM products ORDER BY relevance DESC;

Stopwords and Fine-tuning

MySQL excludes certain common words, known as stopwords, from full-text search indexes by default. You can customize this list if needed:

You can also adjust the server variable ft_min_word_len to change the minimum length of words included in a full-text search.

Advanced Searching

For more advanced full-text searches, you might consider using the WITH QUERY EXPANSION mode, which broadens the search with the most relevant documents.

SELECT * FROM products WHERE MATCH(description) AGAINST('coffee' WITH QUERY EXPANSION);

Handling Different Character Sets and Collations

Full-text searches should also be cognizant of the database character set and collation settings. These settings can impact search results. For instance:

SELECT * FROM products WHERE MATCH(description) AGAINST('café' COLLATE utf8mb4_general_ci);

Conclusion

In this tutorial, we covered how to use full-text search in MySQL 8. We went through creating a full-text index, executing searches in various modes, and handling relevance scores. By applying the examples provided, you can effectively incorporate full-text search into your applications for improved searching capabilities.