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
- MySQL or MariaDB server (list of the best MySQL hosting services
- A username and password for logging on your database server
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:
- You can discover new info about Best website hosting by clicking this link.
