SQL: Introduction to Full Text Search Functions

In a previous post, I wrote about different ways to filter data in SQL. One of those was using the LIKE operator, which is useful when trying to find patterns within data tables. This method will simply look for the pattern within the specified columns. However, there is another function that is especially useful when searching through texts. In this post, we will go over the basics of Full Text Search Functions, and specifically the MATCH function from MySQL.

What Is Full Text?

The first question to consider when learning about Full Text Search Functions is: what do we mean by Full Text?

Full Text is a type of indexing. Only certain data types can be used in this type of index, and as one might expect, these are usually the string data types such as VARCHAR and TEXT. In MySQL, the only data types accepted for Full Text indexing are CHAR, VARCHAR, and TEXT.

To create an index using the Full Text type, it can be declared at the beginning when the table is first being created (using the CREATE TABLE statement), or it can be added using the CREATE INDEX statement or the ALTER TABLE statement, if the table already exists. When we consider uses for Full Text indexing, a common example is news articles. Let’s use this example to create a table with Full Text indexing:

CREATE TABLE news_articles (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    title VARCHAR(200) NOT NULL
    body TEXT NOT NULL
    FULLTEXT(title, body);

Or if the news_articles table already exists, we can use ALTER_TABLE to add the FULLTEXT index:

ALTER TABLE news_articles
ADD FULLTEXT(title, body);

Once specific columns have Full text indexing, it allows for FULL TEXT Search functions. Queries using these functions complete linguistic searches based on the language used. For MySQL, the main function used for these searches is the MATCH function.

Syntax

The syntax for using the MATCH function follows as below:

MATCH (column_1, column_2, ..., column_n) 
AGAINST (expression <search_modifier>)

The MATCH statement accepts a list of columns that have Full Text indexing that you want to search for matches within. The AGAINST statement takes in an expression or string that represents what you are searching for. The search_modifier represents the different search types:

1. IN NATURAL LANGUAGE MODE
2. IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
3. and IN BOOLEAN MODE

The default search value is IN NATURAL LANGUAGE MODE .

Using the MATCH Function

The MATCH function itself is usually used with the WHERE clause. Using the default search value IN NATURAL LANGUAGE MODE , the MATCH function will complete a natural language search for an expression or string against a collection of texts determined by the columns specified. For each row, the MATCH function will assign a relevance value, which is a measure of how similar to the string or expression a text is from that row.

For example, if we wanted to search for articles about health care:

SELECT title, body
FROM news_articles
WHERE MATCH (title, body)
AGAINST ('health care' IN NATURAL LANGUAGE MODE);

This query would return a table of articles with the rows with the highest relevance values being listed at the top and the rest in descending order.

We could also add the actual relevance score to the returned table and add a limit so we only identified the top scores.

SELECT
    MATCH(title, body) AGAINST ('health care' IN NATURAL LANGUAGE MODE) AS Relevance,
    title,
    body
FROM news_articles
WHERE MATCH (title, body)
AGAINST ('health care' IN NATURAL LANGUAGE MODE)
LIMIT 10;

This would add a column with relevance values for each row and show the top 10 most relevant articles.

Two important points to consider: one, as with the LIKE operator, the MATCH function is case insensitive and secondly, double quotes will mean the function will search for that exact string.

I hope this post helps build a foundation on how to use FULL TEXT Search Functions and adds another powerful tool to your SQL skillset. Thanks for reading!

For more documentation on FULL TEXT Search Functions:

MySQL :: MySQL 8.0 Reference Manual :: 12.10 Full-Text Search Functions