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
Or if the
news_articles table already exists, we can use
ALTER_TABLE to add the
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.
The syntax for using the MATCH function follows as below:
MATCH (column_1, column_2, ..., column_n)
AGAINST (expression <search_modifier>)
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:
IN NATURAL LANGUAGE MODE
IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
IN BOOLEAN MODE
The default search value is
IN NATURAL LANGUAGE MODE .
Using the MATCH Function
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
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.
MATCH(title, body) AGAINST ('health care' IN NATURAL LANGUAGE MODE) AS Relevance,
WHERE MATCH (title, body)
AGAINST ('health care' IN NATURAL LANGUAGE MODE)
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: