Are you looking for a guide on implementing the MySQL full text searching feature? Do you want to know how to accurately find documents in your database that match a user’s search query?
If you answered yes to any of these questions, this tutorial is for you! This guide will show you how to enable and configure the MySQL full-text searching feature to find contents in your database.
Read on to get started!
Prerequisites
This tutorial will be a hands-on demonstration. If you’d like to follow along, you must have the following requirements.
- You must have a MySQL server already installed and configured on a Linux computer. This tutorial uses a MySQL 8.0.28 on Ubuntu 20.04.
- You must have an empty database or create a new one if there is none. This database is where you’ll create the table and insert sample data for indexing. This tutorial will be using a database named
ata_db.
Creating the MySQL Full Text Searching Index
There are three methods to add a full-text search index to a table. Each method, while using different statements, achieves the same result.
Option 1: Using the CREATE TABLE Statement (New Table)
Typically, you would create a new table and already know which columns to add to the full-text index. Suppose you need to create a table containing article posts with columns such as:
id: The primary key that uniquely identifies each row in the table. The integer value in this column increments with each new row.title: This column stores the article titles.content: This column stores the content or body of the articles.author: This column stores the author’s name.
In this case, you’ll want to include the title, content, and author into the MySQL full text searching index when you create the table. Follow the below steps to create the new table.
1. First, log in to your server using your preferred SSH client.
2. Now, connect to the MySQL server. This example logs in to MySQL using the default MySQL root user.
sudo mysql
If you have a different MySQL user account, run the below command instead. Make sure to replace USERNAME with your MySQL user account.
mysql -u USERNAME -p3. Now, create a new table named ata_tb in the ata_db database by running the following statement:
The PRIMARY KEY(id) specifies that the table’s primary key will be the id column.
The FULLTEXT ata_post_index (title,content,author) creates a full-text search index called ata_post_index. The full-text index includes the title, content, and author columns.
-- Select the ata_db database
USE ata_db;
-- Create the ata_tb table with indexing
CREATE TABLE ata_tb (
id INT NOT NULL AUTO_INCREMENT,
title TEXT NOT NULL,
content TEXT NOT NULL,
author TEXT NOT NULL,
PRIMARY KEY (id),
FULLTEXT ata_post_index (title, content, author)
);The below screenshot shows the result of the successful table creation.
Option 2: Using the ALTER TABLE Statement (Existing Table)
What if you previously created a table and did not include any full-text search indexing? Or perhaps there’s an existing table, and you want to add a full-text search index? You can do so by running the ALTER TABLE statement.
MySQL only supports full-text indexing on tables whose storage engine is either InnoDB or MyISAM. InnoDB is the default MySQL storage engine.
The full syntax below shows how to alter a table to add the full-text index.
ALTER TABLE table_name ADD FULLTEXT index_name (column_name1, column_name2,…)
To add the full-text index to the ata_tb table, including the title, content, and author columns, execute the below statement. The resulting index name will be ata_post_index.
-- ALTER the ata_tb table to create a full-text search index
ALTER TABLE
ata_tb
ADD
FULLTEXT ata_post_index (title, content, author);
After running the statement, you will likely get a warning, as you can see below.
Should you be worried about this warning? To answer that, show the warning message on the screen like so.
SHOW WARNINGS;
The warning below with the message InnoDB rebuilding table to add column FTS_DOC_ID is normal when you first create the full-text index on a table. The storage engine is rebuilding the table with a new FTS_DOC_ID column for indexing. This warning is informational.
Option 3: Using the CREATE INDEX Statement (Existing Table)
Another way to create a full-text index on an already existing table is with the CREATE INDEX statement. The full statement syntax is shown below.
CREATE FULLTEXT INDEX index_name ON table_name (column_name1, column_name2, ... );
Following that syntax, run the statement below to create a full-text index called ata_post_index on the ata_tb table. This full-text index will include the title, content, and author columns.
-- Creating a full-text index on a table
CREATE FULLTEXT INDEX ata_post_index ON ata_tb(title, content, author);
-- Show the warning messages on the screen
SHOW WARNINGS;
If you create the full-text index for the first time in this table, you will get the same warning message that says InnoDB rebuilding table to add column FTS_DOC_ID, which is informational. This warning message does not require any action from you.
Inserting Sample Data into the Table
The new table is empty, and you must insert new rows of data for indexing. To insert new sample data into the table, run the below INSERT INTO statement. The below statement inserts three rows into the title, content, and author columns.
You don’t need to include the id column because MySQL inserts an autoincrementing integer value with new each row.
INSERT INTO
ata_tb (title, content, author)
VALUES
(
'Get Paid to Write',
'ATA Learning is always seeking instructors of all experience levels.
Regardless if you''re a junior admin or system architect,
you have something to share.',
'Adam'
),
(
'Automate your Java Builds',
'If you''re a developer, you''d typically prefer to automate
the tedious manual build deployment process.',
'Sagar'
),
(
'Tapping Cloudflare Load Balancing to Route Server Traffic',
'Are you tired of applications and system outages whenever
your site detects a spike in traffic?',
'Obare'
);
As you can see below, the MySQL statement inserted three rows into the table.
You now have a demo table with sample data for indexing.
Using MySQL Full-Text Searching
Now that you have created a full-text index in the table, you can start running full-text searches on your data. MySQL provides the MATCH() AGAINST() function explicitly for doing full-text searches.
MATCH()takes a list of columns as an argument to search for the keyword you specify.AGAINST()accepts the keyword to search and an optional search modifier.
By default, if you do not specify a modifier, the function uses the IN NATURAL LANGUAGE MODE modifier. This modifier interprets the keyword as a phrase in natural human language.
For example, run the below statement to search for the phrase ‘are you’. The
Run the command below to see all the rows where the columns contain the phrase Are you. The \\G modifier tells MySQL to break the output into multiple lines instead of one long line with the semi-colon (;) modifier.
SELECT
*
FROM
ata_tb
WHERE
MATCH (title, content, author) AGAINST ('Are you' IN NATURAL LANGUAGE MODE)\\G
As you can see in the output below, there is only one entry containing the phrase “Are you”, but you get three results since the “you're” string is also considered a match.
This behavior is helpful for typos when users search your site. When your users search for a term and don’t find what they are looking for, they will usually search for a similar term.
Returning Search Results by Order of Relevance
MySQL does not consider the relevance of each search result when returning them. You can make MySQL return results based on relevance by ranking them based on scores.
Rank, also known as relevance rank, is a score that MySQL gives to each row that matches the search condition. Scores are floating-point values, where 0 is the lowest relevance score.
Run the below command to see how MySQL ranks each row based on the query “Adam You”. The condition > 0 in the WHERE clause will filter out all rows with a score of 0 (no relevance).
The ORDER BY score DESC keyword sorts the results by relevance in descending order so that the most relevant results are on top.
SELECT
id,
MATCH (title, content, author) AGAINST ('Adam You') as score
FROM
ata_tb
WHERE
MATCH (title, content, author) AGAINST ('Adam You') > 0
ORDER BY
score DESC;
You will get an output similar to the one below. You can see in the output that the row with id 1 has the highest score in the set, which makes it the most relevant to the search. The reason is that this row has both search terms Adam and You in the content and author columns, respectively.
The row with id 2 is in the second position because it contains only one of the search terms, “You” in the content column.
The row with id 3 is the least relevant because it only contains the term You.
This behavior can be helpful when your users search for multiple terms and you want to prioritize results that contain all of the search terms.
Full-Text Searching with Query Expansion
Now that you know how to order the results by relevance, you may be wondering if you can make the results even more relevant. Yes, you can — with query expansion. With query expansion, you can tell MySQL to automatically add related terms to the search phrase that the user entered.
This technique will give you more relevant results because it will find entries that contain the related terms even if they don’t contain the exact search phrase. For example, the search “database” could be expanded to “MySQL”, “Oracle”, “RDBMS”, etc.
The trade-off is that you may get more results than you want, even results not relevant to the original search. But, you can always include the ORDER BY score DESC to rank the results by relevance and show only the most relevant ones.
The statement below shows the syntax that includes the WITH QUERY EXPANSION modifier.
SELECT * FROM ata_tb WHERE MATCH (column_name1,column_name2,column_name3) AGAINST ('The_search_phrase' WITH QUERY EXPANSION);
In this example, you’ll create another table in the same database, insert data, and perform full-text searches with query expansion.
1. Create a new table named articles that includes the title and id columns in the full-text search index.
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title, body)
);2. Now, insert some data into the articles table.
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial, we show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');3. Now, Run the below command to search for the term “database” in the title and body columns.
SELECT
*
FROM
articles
WHERE
MATCH (title, body) AGAINST ('database');As you can see below, the search two results; MySQL Tutorial and MySQL vs. YourSQL.
4. To further improve the relevance of the results, rerun the command above with the WITH QUERY EXPANSION modifier.
SELECT
*
FROM
articles
WHERE
MATCH (title, body) AGAINST ('database' WITH QUERY EXPANSION);This time, you have six results instead of the previous two. Why? MySQL conducts the search using two different search phrases. The first phrase only contains “database”. The second phrase is “database,” plus the most relevant documents from the first search.
In this case, one of these documents contains the word “database” and “MySQL”. MySQL uses this second phrase to retrieve more documents that contain the word “MySQL” that might be relevant to the search.
This behavior is useful when users make typographical errors or use different terms to mean the same thing.
Conclusion
In this guide, you have learned how to use the MySQL full-text searching function to find accurate results. You have also learned how to use the WITH QUERY EXPANSION modifier to retrieve more relevant results even if they don’t contain the exact search phrase.
At this point, you should have a good understanding of how to use MySQL full-text searching to your advantage. Perhaps add full-text search functionality to your JavaScript applications and return the most relevant results in Node.JS.
