Knowledgebase

Introduction

A MySQL full-text search index allows you to find matches for a given keyword against character-based columns. These types of indices are optimized for regular text columns and are much faster compared to wildcards searches. If you have a database application where end-users frequently search for words that don't perfectly match your records, consider implementing MySQL full-text search indices. For instance, assume you have a products table with thousands of records where a single item is saved as a WI-FI ROUTER WITH SIM SLOT. If a new staff member who is not conversant with your catalog tries to look up the item with a keyword like SIM CARD WIFI ROUTER, MySQL might return an empty set even if you use the wildcard ('%..%') search against the product_name column.

To overcome the above challenge, use MySQL full-text search index on your InnoDB and MyISAM tables for VARCHAR, CHAR, and TEXT fields. Under the hood, this feature splits the value of text-based columns into single words and creates an index based on them to make the searches faster. You can implement this technology to create a search engine for your online shopping carts, blogs, or HTML documents.

In this guide, you'll set up a test database and a table. You'll then populate the table with some records and run different types of full-text searches to see how the technology works.

Prerequisites

Before you begin, make sure you've got the following:

• An Ubuntu 20.04 server.
• A sudo user.
• A LAMP Stack.

Create a sample_db Database

SSH to your server and run the command below to log in to MySQL as root.

$ sudo mysql -u root -p

Enter the root password of your MySQL server and press Enter to continue. Once the mysql> command prompt appears, set up a sample_db database.

mysql> CREATE DATABASE sample_db;

Tell the MySQL server to use the sample_db database for subsequent statements.

mysql> USE sample_db;

Next, create a products table based on the InnoDB engine and define the product_name as a FULLTEXT index.

mysql> CREATE TABLE products (
       product_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       product_name VARCHAR(50),
       retail_price DOUBLE,
       FULLTEXT(product_name)
       ) ENGINE = InnoDB;

Insert the following records into the products table by running the commands below one by one.

mysql> INSERT INTO products (product_name, retail_price) VALUES ('LEMON STRAINER WITH 500ML BOTTLE', '3.45');
mysql> INSERT INTO products (product_name, retail_price) VALUES ('HARDENED PLASTIC SMART HAIR TRIMMER', '76.35');
mysql> INSERT INTO products (product_name, retail_price) VALUES ('SIMCARD ENABLED 4G WIFI ROUTER', '99.95');
mysql> INSERT INTO products (product_name, retail_price) VALUES ('GAMING MOUSE WITH SMART LED', '45.55');
mysql> INSERT INTO products (product_name, retail_price) VALUES ('JUICE - 500ML LEMON', '2.45');
mysql> INSERT INTO products (product_name, retail_price) VALUES ('5G SMART PHONE WITH NANO SIM', '369.75');
mysql> INSERT INTO products (product_name, retail_price) VALUES ('LED SMART PLASTIC WATCH', '89.85');
mysql> INSERT INTO products (product_name, retail_price) VALUES ('WI-FI ROUTER WITH SIM SLOT', '75.95');
mysql> INSERT INTO products (product_name, retail_price) VALUES ('LED TORCH - HARDENED PLASTIC', '25.62');
mysql> INSERT INTO products (product_name, retail_price) VALUES ('NANO SIM CARD CUTTER', '5.65');
mysql> INSERT INTO products (product_name, retail_price) VALUES ('DY MODEL 2T AIR CONTROL VALVE', '5.65');

Confirm that the data is in place by running a SELECT statement against the products table.

mysql> SELECT
       product_id,
       product_name,
       retail_price
       FROM products ;

Ensure you get a list of all products as shown below.

+------------+-------------------------------------+--------------+
| product_id | product_name                        | retail_price |
+------------+-------------------------------------+--------------+
|          1 | LEMON STRAINER WITH 500ML BOTTLE    |         3.45 |
|          2 | HARDENED PLASTIC SMART HAIR TRIMMER |        76.35 |
|          3 | SIMCARD ENABLED 4G WIFI ROUTER      |        99.95 |
|          4 | GAMING MOUSE WITH SMART LED         |        45.55 |
|          5 | JUICE - 500ML LEMON                 |         2.45 |
|          6 | 5G SMART PHONE WITH NANO SIM        |       369.75 |
|          7 | LED SMART PLASTIC WATCH             |        89.85 |
|          8 | WI-FI ROUTER WITH SIM SLOT          |        75.95 |
|          9 | LED TORCH - HARDENED PLASTIC        |        25.62 |
|         10 | NANO SIM CARD CUTTER                |         5.65 |
|         11 | DY MODEL 2T AIR CONTROL VALVE       |         5.65 |
+------------+-------------------------------------+--------------+
11 rows in set (0.00 sec)

Test Full-Text Searches

Next, you're going to run some keyword searches against the table. Please note, there are three types of Full-Text searches.

• Natural language full-text searches: Interprets text as free text in natural human language.
• Boolean full-text searches: For advanced queries based on complex Boolean operators such as +.
• Query expansion full-text searches: Based on compound and extended searches.

Run a Natural Language Full-Text Search

Run a natural-language full-text mode search by implementing the MySQL MATCH(...) and AGAINST(...) functions.

mysql> SELECT
       product_id,
       product_name,
       retail_price
       FROM products
       WHERE MATCH(product_name)
       AGAINST('WIFI ROUTER' IN NATURAL LANGUAGE MODE);

Confirm the below output. As you can see from the results below, the full-text search is very powerful since even if you specified the word 'WIFI MySQL was able to retrieve a record having the word WI-FI.

+------------+--------------------------------+--------------+
| product_id | product_name                   | retail_price |
+------------+--------------------------------+--------------+
|          3 | SIMCARD ENABLED 4G WIFI ROUTER |        99.95 |
|          8 | WI-FI ROUTER WITH SIM SLOT     |        75.95 |
+------------+--------------------------------+--------------+
2 rows in set (0.00 sec)

Run a Full-Text Search in Boolean Mode

In Boolean Full-Text mode, you can put certain characters in the beginning or at the end of a keyword when running a search. Use the + and the - operators to indicate the words you want to include or exclude in the results.

For instance, run the query below to retrieve all records that match the keyword LED and contains the word plastic.

mysql> SELECT
       product_name,
       retail_price
       FROM products
       WHERE MATCH(product_name)
       AGAINST('LED +PLASTIC' IN BOOLEAN MODE);

Output

+-------------------------------------+--------------+
| product_name                        | retail_price |
+-------------------------------------+--------------+
| LED SMART PLASTIC WATCH             |        89.85 |
| LED TORCH - HARDENED PLASTIC        |        25.62 |
| HARDENED PLASTIC SMART HAIR TRIMMER |        76.35 |
+-------------------------------------+--------------+
3 rows in set (0.00 sec)

Use a leading minus - operator to return results that match the keyword LED but do not contain the word plastic.

mysql> SELECT
       product_name,
       retail_price
       FROM products
       WHERE MATCH(product_name)
       AGAINST('LED -PLASTIC' IN BOOLEAN MODE);

Output

+-----------------------------+--------------+
| product_name                | retail_price |
+-----------------------------+--------------+
| GAMING MOUSE WITH SMART LED |        45.55 |
+-----------------------------+--------------+
1 row in set (0.00 sec)

Still, in BOOLEAN MODE, you can enclose your keyword(s) in double-quotes to return only the rows that literally match the words as you've typed them in the same order. For instance, to look for the exact row that contains the phrase SMART PHONE, type the command below.

mysql> SELECT
       product_name,
       retail_price
       FROM products
       WHERE MATCH(product_name)
       AGAINST('"SMART PHONE"' IN BOOLEAN MODE);

Output

+------------------------------+--------------+
| product_name                 | retail_price |
+------------------------------+--------------+
| 5G SMART PHONE WITH NANO SIM |       369.75 |
+------------------------------+--------------+
1 row in set (0.00 sec)

Run a Full-Text Search with Query Expansion

You can use a full-text search with a QUERY EXPANSION statement only if the search phrase is too short. This adds relevant rows to the results.

By specifying the QUERY EXPANSION statement, you're instructing MySQL to widen the search by running it twice. In the first round, MySQL runs a search against the given keyword such as WIFI ROUTER, and once it retrieves high relevant rows, it conducts a second search based on the values of the top-ranked rows returned during the first search.

To put this into perspective, first, run the query with the keyword WIFI ROUTER in NATURAL LANGUAGE MODE

mysql> SELECT
       product_name,
       retail_price
       FROM products
       WHERE MATCH(product_name)
       AGAINST('WIFI ROUTER' IN NATURAL LANGUAGE MODE);

The above NATURAL LANGUAGE MODE query only returns two rows as you can see from the output below.

+--------------------------------+--------------+
| product_name                   | retail_price |
+--------------------------------+--------------+
| SIMCARD ENABLED 4G WIFI ROUTER |        99.95 |
| WI-FI ROUTER WITH SIM SLOT     |        75.95 |
+--------------------------------+--------------+
2 rows in set (0.00 sec)

Next, run the WIFI ROUTER query again but this time around, include the QUERY EXPANSION statement modifier.

mysql> SELECT
       product_name,
       retail_price
       FROM products
       WHERE MATCH(product_name)
       AGAINST('WIFI ROUTER' WITH QUERY EXPANSION);

As you can confirm from the output below, four records have been returned.

+--------------------------------+--------------+
| product_name                   | retail_price |
+--------------------------------+--------------+
| SIMCARD ENABLED 4G WIFI ROUTER |        99.95 |
| WI-FI ROUTER WITH SIM SLOT     |        75.95 |
| 5G SMART PHONE WITH NANO SIM   |       369.75 |
| NANO SIM CARD CUTTER           |         5.65 |
+--------------------------------+--------------+
4 rows in set (0.00 sec)

If you examine the above search results carefully, you'll see that the third and fourth records (5G SMART PHONE WITH NANO SIM and NANO SIM CARD CUTTER) were returned based on the results of the first (SIMCARD ENABLED 4G WIFI ROUTER) and the second record (WI-FI ROUTER WITH SIM SLOT).

The MySQL QUERY EXPANSION statement is very powerful because indeed, the 5G SMART PHONE WITH NANO SIM and NANO SIM CARD CUTTER are somehow related to your search term WIFI ROUTER.

Relevance Ranking

Optionally, you may retrieve a relevance ranking score when conducting full-text searches. Relevance is the measure of accuracy used by the MySQL server to sort returned rows based on their importance and this is highly determined by the total number of words that match a search term in a record as well as their order.

To see how MySQL allocates a relevance score, search the keyword LED SMART WATCH and include a relevance_score column using the SQL command below.

mysql> SELECT
       product_id,
       product_name,
       retail_price,
       MATCH(product_name) AGAINST ('LED SMART WATCH' IN NATURAL LANGUAGE MODE) AS relevance_score
       FROM products
       WHERE MATCH(product_name)
       AGAINST('LED SMART WATCH' IN NATURAL LANGUAGE MODE);

From the output below, you can see that the LED SMART PLASTIC WATCH has been allocated the highest score of ~1.60 since it contains all search keywords that you've defined almost in the same order.

+------------+-------------------------------------+--------------+---------------------+
| product_id | product_name                        | retail_price | relevance_score     |
+------------+-------------------------------------+--------------+---------------------+
|          7 | LED SMART PLASTIC WATCH             |        89.85 |  1.5959142446517944 |
|          4 | GAMING MOUSE WITH SMART LED         |        45.55 |  0.5114154815673828 |
|          9 | LED TORCH - HARDENED PLASTIC        |        25.62 |  0.3184022605419159 |
|          2 | HARDENED PLASTIC SMART HAIR TRIMMER |        76.35 | 0.19301322102546692 |
|          6 | 5G SMART PHONE WITH NANO SIM        |       369.75 | 0.19301322102546692 |
+------------+-------------------------------------+--------------+---------------------+
5 rows in set (0.00 sec)

Run Full-Text Search using Two-Character Word

By default, the MySQL server may not return results based on keyword terms that are shorter than 3 characters. For instance, try running the keyword DY against the products table in IN NATURAL LANGUAGE MODE.

mysql> SELECT
       product_name,
       retail_price
       FROM products
       WHERE MATCH(product_name)
       AGAINST('DY' IN NATURAL LANGUAGE MODE);

The following output confirms that an empty set has been returned for the keyword DY although you have a matching product named DY MODEL 2T AIR CONTROL VALVE

Empty set (0.00 sec)

In MySQL, the minimum size of the search term is controlled by the innodb_ft_min_token_size variable. Confirm if this is the current value by running the command below.

mysql> SHOW VARIABLES LIKE '%innodb_ft_min_token_size%';

You can see that the default value is 3 unless you had previously changed it in the MySQL settings.

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_ft_min_token_size | 3     |
+--------------------------+-------+
1 row in set (0.04 sec)

If you have two many two-lettered words, change the value of innodb_ft_min_token_size by editing the MySQL settings.

First, exit from the MySQL command-line interface.

mysql> QUIT;

Then, open the MySQL configuration file.

$ sudo nano  /etc/mysql/my.cnf

Add the following information into the bottom of the file.

[mysqld]
innodb_ft_min_token_size = 2

Save and close the configuration file when you're through with editing. Then, restart the MySQL server to reload the changes.

$ sudo systemctl restart mysql-server

Log in back to the MySQL server as root.

$ sudo mysql -u root -p

Key in the root password and press Enter to proceed. Then, switch back to the sample_db database.

mysql> USE sample_db;

First, retrieve the name of the full-text index from the information_schema.

mysql> SELECT
       TABLE_SCHEMA,
       TABLE_NAME,
       INDEX_NAME
       FROM information_schema.statistics
       WHERE index_type LIKE 'FULLTEXT%';

Output

+--------------+------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME   |
+--------------+------------+--------------+
| sample_db    | products   | product_name |
+--------------+------------+--------------+
1 row in set (0.01 sec)

Rebuild the product_name full-text index.

mysql> ALTER TABLE products DROP INDEX product_name;
mysql> CREATE FULLTEXT INDEX product_name ON products(product_name);

Make sure you get an output confirming that the indices were rebuilt succesfully.

...
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Try to run your two-lettered full-text search again.

mysql> SELECT
       product_name,
       retail_price
       FROM products
       WHERE MATCH(product_name)
       AGAINST('DY' IN NATURAL LANGUAGE MODE);

You should now see the output below that confirms that MySQL full-text search is now working on two-lettered words.

+-------------------------------+--------------+
| product_name                  | retail_price |
+-------------------------------+--------------+
| DY MODEL 2T AIR CONTROL VALVE |         5.65 |
+-------------------------------+--------------+
1 row in set (0.00 sec)

Conclusion

In this tutorial, you've created a database and learned how to perform full-text searches against a table. You can use the technology in this guide to implement an intuitive search engine feature for your website, products' catalog, or blog by including the MySQL code in your favorite scripting languages such as PHP, Python, or Node. js.