MySQL FULLTEXT Searching

MySQL uses a general index for the columns of the table to make the searching faster, and this type of searching is called the BTREE index. Another type of index is the FULLTEXT index, which is different from the BTREE index. FULLTEXT index is used to search a particular keyword in the text rather than the specific value in the indexed columns. The BTREE index and FULLTEXT index can be applied to the same column of the table.

Features of FULLTEXT Index

It can be applied to the CHAR, VARCHAR, and TEXT fields of the table.
It supports InnoDB and MyISAM storage engines.
It is used with MATCH() and AGAINST() clauses only

Types of FULLTEXT Search Modes

Three types of searching can be accomplished using the FULLTEXT index, which is explained below:

1. Boolean Mode
This type of searching is used for the queries that contain Boolean operators, such as less than (‘<‘) and more than (‘>’) operator, plus sign (‘+’), minus sign (‘-‘), double quotes (“”), wildcard operator (‘*), etc.

2. Natural Language Mode
The default search type interprets the search string as a literal phase.

3. Query Expansion Mode
The search is done two times in this searching type. The second search is applied to the result of the first search to find out the most relevant searching output.

Prerequisites

Create a table with data in a MySQL database to check the use of FULLTEXT search in MySQL. Open the terminal and connect to the MySQL server by executing the following command:

$ sudo mysql -u root

Run the following command to create a database named test_db:

CREATE DATABASE test_db;

Run the following command to select the database:

USE test_db;

Run the following query to create a table named customers with five fields:

CREATE TABLE customers(
id INT NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
email VARCHAR(50),
address TEXT,
contact_no VARCHAR(15));

Run the following INSERT query to insert four records into the customers table.

INSERT INTO `customers` (`id`, `name`, `email`, `address`, `contact_no`) VALUES
('4001', 'Laboni Sarkar', '[email protected]', '34, Dhanmondi 9/A, Dhaka.', '01844767234'),
('4002', 'Tahsin Ahmed', '[email protected]', '123/1, Jigatola, Dhaka.', '01599348742'),
('4003', 'Hasina Sarkar', '[email protected]', '280, Shantibagh, Dhaka.', '01600934874'),
('4004', 'Mehrab Ali', '[email protected]', '78, Jigatola, Dhaka.', '01727863459');

Run the following command to add the FULLTEXT index for the address field of the customers table:

Alter table customers ADD FULLTEXT (address);

Run the following command to check the list of the defined indexes of the customers table:

SHOW INDEX FROM customers;

Output
The following output shows that two indexes exist for the customers table. One is the BTREE index that has been defined by default for the primary field, and another is the FULLTEXT index that has been defined later for the address field:

Uses of FULLTEXT Searching

The search in the table using different modes of the FULLTEXT index has been shown in this tutorial.

FULLTEXT Search in Boolean Mode

The following SELECT query will search the word ‘Dhanmondi’ in the address field of the customers table in Boolean mode. The customers table contains just one record matching this criterion:

SELECT * FROM customers WHERE MATCH(address) AGAINST('Dhanmondi' IN BOOLEAN MODE);

Output
The following output will appear after executing the previous query:

Run the following SELECT query to search both words, ‘Dhaka’ and ‘Jigatola’ in the address field of the customers table by using the FULLTEXT index in Boolean mode. There are two rows in the customers table that contain both words, ‘Dhaka’ and ‘Jigatola’:

SELECT * FROM customers
WHERE MATCH(address) AGAINST('Dhaka +Jigatola' IN BOOLEAN MODE);

Output
The following output will appear after executing the previous query. The output shows that the address field contains ‘Dhaka’ and ‘Jigatola’:

Run the following SELECT query to search those rows of the customers table that contain the word ‘Dhaka’ but does not contain the word ‘Jigatola’ in the address field of the customers table using FULLTEXT index in Boolean mode. Two rows exist in the customers table that match the criteria:

SELECT * FROM customers WHERE MATCH(address) AGAINST('Dhaka -Jigatola' IN BOOLEAN MODE);

Output
The following output will appear after executing the previous query. The output shows the address field contains ‘Dhaka’ but not ‘Jigatola’:

FULLTEXT Search in Natural Language Mode

Run the following command to add the FULLTEXT index for the name field of the customers table:

Alter table customers ADD FULLTEXT (name);

The following SELECT query will search the word ‘sankar’ in the name field of the customers table in Natural Language mode. The customers table contains two records matching this criterion:

SELECT * FROM customers
WHERE MATCH(name) AGAINST('sarkar' IN NATURAL LANGUAGE MODE);

Output
The following output will appear after executing the previous query. The rows that contain the ‘sarkar’ value in the name field have been shown in the output:

FULLTEXT Search in Query Expansion Mode

The following SELECT query will search the word ‘laboni’ in the name field of the customers table in Query Expansion mode. The customers table contains two records matching with this criterion:

SELECT * FROM customers
WHERE MATCH(name) AGAINST("laboni" WITH QUERY EXPANSION);

Output
The following output will appear after executing the previous query. In the output, the name field of the first row contains the word ‘Laboni’ with another word, ‘Sarkar’, and the name field of the second row contains the matching word, ‘Sarkar’.

Drop FULLTEXT Index

Run the following command to drop the FULLTEXT index of the name field:

ALTER TABLE customers DROP INDEX name;

Run the following command to check the current index list of the customers table after removing the FULLTEXT index from the name field:

SHOW INDEX FROM customers;

Output
The following output will appear after executing the previous query. The output shows that the customers table contains one BTREE index for the id field and one FULLTEXT for the address field:

Conclusion

The different ways of searching content in a table using the FULLTEXT index have been shown in this tutorial to help the new MySQL users learn the uses of FULLTEXT searching in MySQL. In addition, the full features for the FULLTEXT index were discussed. We hope you found this article helpful. Check out other Linux Hint articles for more tips and tutorials.