MySQL Full-Text Search Capabilities

Introduction

In this lab, you will explore MySQL's full-text search capabilities. You'll begin by creating a database and a table named articles with columns for id, title, and content, then populate it with sample data.

Next, you'll add a full-text index named article_index to the articles table, specifically on the title and content columns, using the ALTER TABLE statement. Finally, you'll verify the index creation using the SHOW INDEXES command, confirming that the Index_type is FULLTEXT. This setup prepares you for efficient text searches within your MySQL database.

Throughout this lab, you will work inside the MySQL shell. You only need to enter the MySQL shell at the beginning, and exit at the end. All SQL commands in the following steps should be executed within the same MySQL session.

Create Database and Table

In this step, you will create a MySQL database and a table to store articles. This table will be used to demonstrate full-text search capabilities.

First, open your terminal and connect to the MySQL server as the root user. You can do this using the following command:

sudo mysql -u root

This command connects to the MySQL server using the root user. Since you are using sudo, you will not be prompted for a password.

Once connected to the MySQL shell, you will see the mysql> prompt. Remain in the MySQL shell for all subsequent steps until the end of the lab.

Now, let's create a database named search_db. This database will hold our articles table. Execute the following SQL command:

CREATE DATABASE IF NOT EXISTS search_db;

The IF NOT EXISTS clause ensures that the database is only created if it doesn't already exist.

Next, switch to the newly created database using the USE command:

USE search_db;

You should see a message indicating that the database has changed.

Now, create the articles table. This table will have three columns: id, title, and content.

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT
);

Let's break down this command:

CREATE TABLE articles: This statement creates a new table named articles.
id INT AUTO_INCREMENT PRIMARY KEY: This defines an integer column named id that automatically increments for each new row and serves as the primary key for the table.
title VARCHAR(255) NOT NULL: This defines a string column named title with a maximum length of 255 characters. NOT NULL means this column cannot be empty.
content TEXT: This defines a text column named content which can store larger strings.

After executing this command, the articles table will be created within the search_db database.

You can verify the table creation by listing the tables in the current database:

SHOW TABLES;

You should see articles listed in the output.

+-----------------------+
| Tables_in_search_db   |
+-----------------------+
| articles              |
+-----------------------+
1 row in set (0.00 sec)

Now that you have created the database and table, you are ready to populate it with some sample data in the next step.

Insert Sample Data and Add Full-Text Index

In this step, you will insert sample data into the articles table and then add a full-text index to enable efficient text searching.

Make sure you are still in the MySQL shell and using the search_db database. If you are not, select the database:

USE search_db;

Now, let's insert some sample data into the articles table. We will add three rows with different titles and content:

INSERT INTO articles (title, content) VALUES
('MySQL Full-Text Search', 'This article explains how to use full-text search in MySQL.'),
('Indexing in MySQL', 'Learn about different types of indexes in MySQL, including full-text indexes.'),
('Optimizing MySQL Queries', 'Tips and tricks for optimizing your MySQL queries for better performance.');

This INSERT statement adds multiple rows to the articles table. Each set of parentheses after VALUES represents a new row, with the values corresponding to the title and content columns.

You can verify that the data has been inserted by selecting all rows from the table:

SELECT * FROM articles;

You should see the three inserted rows in the output:

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

Now that the table has data, let's add a full-text index. A full-text index allows MySQL to perform fast and relevant searches on text data. We will add a full-text index on both the title and content columns.

Use the ALTER TABLE statement to add the index:

ALTER TABLE articles ADD FULLTEXT INDEX article_index (title, content);

Let's break down this command:

ALTER TABLE articles: This indicates that we are modifying the articles table.
ADD FULLTEXT INDEX article_index: This adds a new index of type FULLTEXT and names it article_index.
(title, content): This specifies the columns that will be included in the full-text index.

After executing this command, MySQL will build the full-text index on the specified columns.

To verify that the full-text index has been created successfully, you can use the SHOW INDEXES command:

SHOW INDEXES FROM articles;

The output will show all indexes on the articles table. You should see an entry for article_index with FULLTEXT in the Index_type column.

+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| articles |          0 | PRIMARY        |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       |
| articles |          1 | article_index  |            1 | title       | NULL      |           3 |     NULL | NULL   | YES  | FULLTEXT   |         |               | YES     | NULL       |
| articles |          1 | article_index  |            2 | content     | NULL      |           3 |     NULL | NULL   | YES  | FULLTEXT   |         |               | YES     | NULL       |
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

You have now successfully inserted data and added a full-text index to your table. In the next step, you will learn how to use this index to perform full-text searches.

Perform Basic Full-Text Search

In this step, you will perform basic full-text searches on the articles table using the MATCH AGAINST clause. This is the fundamental way to utilize the full-text index you created.

Make sure you are still in the MySQL shell and using the search_db database. If you are not, select the database:

USE search_db;

The MATCH AGAINST clause is used within the WHERE clause of a SELECT statement. The basic syntax is:

SELECT column1, column2, ...
FROM table_name
WHERE MATCH (column_list) AGAINST ('search_term');

Here, column_list is a comma-separated list of the columns included in the full-text index (in our case, title and content), and 'search_term' is the word or phrase you want to search for.

Let's search for articles that contain the word "MySQL":

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('MySQL');

This query selects the id, title, and content columns from the articles table where the title or content columns match the term "MySQL".

You should see the following output:

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

All three articles are returned because they all contain the word "MySQL".

Full-text search also provides a relevance score, indicating how well each row matches the search term. You can include this score in your SELECT statement:

SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL') AS relevance FROM articles;

This query adds a column named relevance which shows the score for each row.

+----+--------------------------+---------------------------------------------------------------------+--------------------+
| id | title                    | content                                                             | relevance          |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         | 1.34832763671875   |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. | 0.5215404033660889 |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.5215404033660889 |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
3 rows in set (0.00 sec)

You can order the results by relevance to see the best matches first:

SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL') AS relevance FROM articles ORDER BY relevance DESC;

This query sorts the results based on the relevance score in descending order.

+----+--------------------------+---------------------------------------------------------------------+--------------------+
| id | title                    | content                                                             | relevance          |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         | 1.34832763671875   |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. | 0.5215404033660889 |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.5215404033660889 |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
3 rows in set (0.00 sec)

In this step, you learned how to perform basic full-text searches using MATCH AGAINST and how to retrieve and order results by relevance. In the next step, you will explore more advanced search options using boolean mode.

Use Boolean Mode for Advanced Search

In this step, you will use the boolean mode of the MATCH AGAINST clause to perform more precise and flexible full-text searches. Boolean mode allows you to use operators to control which words must or must not be present in the results.

Make sure you are still in the MySQL shell and using the search_db database. If you are not, select the database:

USE search_db;

To use boolean mode, you add the IN BOOLEAN MODE clause to the MATCH AGAINST statement. The syntax is:

SELECT column1, column2, ...
FROM table_name
WHERE MATCH (column_list) AGAINST ('search_term' IN BOOLEAN MODE);

Here are some common operators used in boolean mode:

+: Requires the word to be present in the result.
-: Excludes rows that contain the word.
"": Searches for an exact phrase.
*: Wildcard operator (at the end of a word).

Let's find articles that must contain the word "MySQL" and must not contain the word "optimizing".

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL -optimizing' IN BOOLEAN MODE);

This query uses the + operator to require "MySQL" and the - operator to exclude "optimizing".

You should see the following output:

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL      | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

The article about "Optimizing MySQL Queries" is excluded because it contains "optimizing".

Now, let's search for the exact phrase "full-text search":

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('"full-text search"' IN BOOLEAN MODE);

Using double quotes searches for the words as a contiguous phrase.

The output will be:

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL.         |
+----+------------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Only the first article is returned because it contains the exact phrase "full-text search".

Let's try using the wildcard operator *. Suppose you want to find articles containing words that start with "index".

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('index*' IN BOOLEAN MODE);

The index* term will match words like "index" and "indexing".

The output will be:

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  2 | Indexing in MySQL      | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

In this step, you learned how to use boolean mode with the MATCH AGAINST clause and various operators to perform more controlled and specific full-text searches. In the next step, you will practice combining these techniques.

Combine Search Techniques

In this step, you will combine the basic MATCH AGAINST search with boolean mode operators to perform more complex and refined full-text queries.

Make sure you are still in the MySQL shell and using the search_db database. If you are not, select the database:

USE search_db;

Let's try to find articles that contain the word "MySQL" and either the word "indexing" or "optimizing". We can use parentheses to group the optional terms in boolean mode.

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL +(indexing optimizing)' IN BOOLEAN MODE);

This query requires "MySQL" (+MySQL) and requires at least one of the terms within the parentheses (+(indexing optimizing)).

The output will be:

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

The first article is excluded because it contains "MySQL" but neither "indexing" nor "optimizing".

You can also combine boolean mode with relevance scoring. Let's find articles containing "MySQL" and prioritize those that also mention "indexing" using the > operator.

SELECT id, title, content, MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE) AS relevance FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE) ORDER BY relevance DESC;

This query requires "MySQL" and boosts the relevance score for articles containing "indexing". The results are then ordered by the calculated relevance.

The output will be:

+----+--------------------------+---------------------------------------------------------------------+-----------+
| id | title                    | content                                                             | relevance |
+----+--------------------------+---------------------------------------------------------------------+-----------+
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |  1.6931  |
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |  0.3068  |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |  0.3068  |
+----+--------------------------+---------------------------------------------------------------------+-----------+
3 rows in set (0.00 sec)

Notice that the "Indexing in MySQL" article is ranked highest due to the >indexing operator.

By combining basic search with boolean operators and relevance scoring, you can create powerful and flexible full-text search queries tailored to your specific needs.

This concludes the lab on MySQL full-text search capabilities. You can now exit the MySQL shell by typing:

exit

You have learned how to set up a table with a full-text index, perform basic searches, and use boolean mode for more advanced queries.

Summary

In this lab, you have learned about MySQL's full-text search capabilities. You started by creating a database named search_db and an articles table to store textual data. You then populated the table with sample articles.

The key step was adding a full-text index named article_index to the title and content columns of the articles table using the ALTER TABLE statement. This index is essential for efficient full-text searching. You verified the index creation using SHOW INDEXES.

Finally, you explored how to perform full-text searches using the MATCH AGAINST clause. You learned how to perform basic searches, retrieve relevance scores, and order results by relevance. You also delved into boolean mode, using operators like +, -, "", and * to create more precise and complex search queries. By combining these techniques, you can effectively search and retrieve relevant information from text data stored in your MySQL database.