How to Enable Full-text Search on MySQL Database

Full-text search (FTS) is an intelligent technique used to find and return better and relevant results from a database.

Search engines widely use FTS, but you can bring the same power to your MySQL database. This works better as compared to the popular slow wildcard lookups that utilize the similar statement.

FTS has greater advantages as compared to traditional database searching methods. First, it can stem words (reducing inflected words to their original based). For instance, if you search for the word ‘ran,’ you can get results for ‘run’ or ‘running.’

FTS is also popularly used to display weighted results. That is, once you run a query on a database, you can order the results in order of relevance, and this will display better results to the end user.

In this guide, we will show you how to enable full-text searches on your MySQL database. For the sake of simplicity, we will be using a hypothetical school database with a student table having two fields (student_name and student_id)

Prerequisites

Step 1:
Login to Your MySQL Server

There are different ways for logging to your MySQL/MariaDB server. If you are running Linux, the basic command is:

$ mysql -u root -p

You can replace ‘root’ with your username. Also the -p switch will prompt you to enter a password. If the credentials are correct, the MySQL prompt will be displayed.

mysql>

Step 2:
Select the Database That You Want To Use

To select a database, run the command below:

mysql> use dbname

For example, to use our ‘school’ database, we will run the command below:

mysql> use school

Step 3:
Enable Full-Text Search on The Target Column

First, let make sure that our table has some values. We can run the command below to verify this:

mysql> select * from students;

Output

+------------+--------------+
| student_id | student_name |
+------------+--------------+
|       1041 | John Doe     |
|       1042 | John Roe     |
|       1043 | Richard Roe  |
+------------+--------------+
3 rows in set (0.00 sec)

Full-text search only works with CHAR, VARCHAR, or TEXT data types. In this case, our ‘student_name’ column is set to use VARCHAR.

To allow full-text search on the column, we are going to run the SQL command below:

mysql> Alter table students ADD FULLTEXT (student_name);
Query OK, 0 rows affected, 1 warning (0.12 sec)

Step 4:
Testing If FTS Is Working

To test if MySQL FTS is working, we are going to run the command below against our database table:

mysql>Select student_id, student_name, match(student_name) AGAINST (‘John Roe’) as Relevance from students where match(student_name) AGAINST (‘John Roe’);

We have included a new column ‘Relevance’ so that we can be able to see the weight given to each record that matches our search term ‘John Roe’.

Output

mysql> Select student_id, student_name, match(student_name) AGAINST ('John Roe') as Relevance from students where match(student_name) AGAINST ('John Roe');
+------------+--------------+----------------------+
| student_id | student_name | Relevance            |
+------------+--------------+----------------------+
|       1042 | John Roe     | 0.062016263604164124 |
|       1041 | John Doe     | 0.031008131802082062 |
|       1043 | Richard Roe  | 0.031008131802082062 |
+------------+--------------+----------------------+
3 rows in set (0.00 sec)

As you can see in the above example, All 3 students were returned when we run the query, however, the first record with student name ‘John Roe’ is given more weight because it exactly matched our search terms.

This confirms that MySQL FTS is working as expected. Although this is a basic example using a few records, MySQL FTS can improve performance on a database with millions of records.

Conclusion

In this guide, we have discussed the importance of enabling full-text search on your MySQL database. We have also demonstrated the principle using some test data from a student’s table. With FTS enabled, you will be able to serve better and relevant search results on your applications.

Check out these top 3 MySQL hosting services:

HostArmada
£1.96 /mo
Starting price
Visit HostArmada
Rating based on expert review
  • User Friendly
    4.5
  • Support
    4.5
  • Features
    4.5
  • Reliability
    4.5
  • Pricing
    4.0
IONOS
£0.79 /mo
Starting price
Visit IONOS
Rating based on expert review
  • User Friendly
    4.5
  • Support
    4.0
  • Features
    4.5
  • Reliability
    4.5
  • Pricing
    4.3
Ultahost
£2.29 /mo
Starting price
Visit Ultahost
Rating based on expert review
  • User Friendly
    4.3
  • Support
    4.8
  • Features
    4.5
  • Reliability
    4.0
  • Pricing
    4.8

How to Install PhpMyAdmin on a Ubuntu 18.04 VPS or Dedicated Server

This article will describe the process of installing phpMyAdmin on an Ubuntu 18.
3 min read
Eliran Ouzan
Eliran Ouzan
Web Designer & Hosting Expert

How to Backup Your MySQL Database on an Ubuntu 18.04 VPS or Dedicated Server

This article shows you how to back up a MySQL database on a Linux Ubuntu 18.04 s
3 min read
Eliran Ouzan
Eliran Ouzan
Web Designer & Hosting Expert

How to Move Your WordPress Site from Your Local Web Server to Your Live Site

You can always accelerate the development process by developing your WordPress s
4 min read
Max Ostryzhko
Max Ostryzhko
Senior Web Developer, HostAdvice CTO

How to Disable MySQL 5 "Strict Mode" on an Ubuntu 18.04 VPS or Dedicated Server

How to Disable MySQL Strict Mode on MySQL 5.6 Running on an Ubuntu 18.04 VPS.
2 min read
Idan Cohen
Idan Cohen
Marketing Expert
HostAdvice.com provides professional web hosting reviews fully independent of any other entity. Our reviews are unbiased, honest, and apply the same evaluation standards to all those reviewed. While monetary compensation is received from a few of the companies listed on this site, compensation of services and products have no influence on the direction or conclusions of our reviews. Nor does the compensation influence our rankings for certain host companies. This compensation covers account purchasing costs, testing costs and royalties paid to reviewers.
Click to go to the top of the page
Go To Top