---
title: "Accurate Database Searches with MySQL Full Text Searching"
description: "Learn to search effectively and quickly with the MySQL full text searching ability in this ATA Learning tutorial!"
canonical: "https://adamtheautomator.com/mysql-full-text-searching/"
---

# Accurate Database Searches with MySQL Full Text Searching

> Learn to search effectively and quickly with the MySQL full text searching ability in this ATA Learning tutorial!

Source: https://adamtheautomator.com/mysql-full-text-searching/

---

ATA Learning

Tap to hide

[

ATA Learning

](/)

*   [Home](/)
*   [Tutorials](/tutorials/)
*   [Instructors](/author/)
*   [Advertising](/advertising/)
*   [Recommended Resources](/resources/)
*   [About Adam](/about-adam/)

Search for:  

*   [](https://twitter.com/adbertram)
*   [](https://github.com/Adam-the-Automator)
*   [](https://www.linkedin.com/company/adam-the-automator-llc)
*   [](/feed/)

![Accurate Database Searches with MySQL Full Text Searching](https://adamtheautomator.com/wp-content/uploads/2022/04/Accurate-Database-Searches-with-MySQL-Full-Text-Searching.jpg)

# Accurate Database Searches with MySQL Full Text Searching

[![](https://secure.gravatar.com/avatar/2788bb1a3f735603f81eca51d68daec56a9d97e805a10268fb2c20afcc76b81b?s=192&d=mm&r=g)Nicholas Xuan Nguyen](https://adamtheautomator.com/author/nicholas-xuan-nguyen/)3 May 20229 min. read

Categories: [IT Ops](/category/it-ops/)

Tags:[MySQL](/tag/mysql/)

Table of Contents

*   [Prerequisites](#prerequisites)
*   [Creating the MySQL Full Text Searching Index](#creating-the-mysql-full-text-searching-index)
*   [Option 1: Using the CREATE TABLE Statement (New Table)](#option-1-using-the-create-table-statement-new-table)
*   [Option 2: Using the ALTER TABLE Statement (Existing Table)](#option-2-using-the-alter-table-statement-existing-table)
*   [Option 3: Using the CREATE INDEX Statement (Existing Table)](#option-3-using-the-create-index-statement-existing-table)
*   [Inserting Sample Data into the Table](#inserting-sample-data-into-the-table)
*   [Using MySQL Full-Text Searching](#using-mysql-full-text-searching)
*   [Returning Search Results by Order of Relevance](#returning-search-results-by-order-of-relevance)
*   [Full-Text Searching with Query Expansion](#full-text-searching-with-query-expansion)
*   [Conclusion](#conclusion)

Are you looking for a guide on implementing the MySQL full text searching feature? Do you want to know how to accurately find documents in your database that match a user’s search query?

If you answered yes to any of these questions, this tutorial is for you! This guide will show you how to enable and configure the MySQL full-text searching feature to find contents in your database.

Read on to get started!

## Prerequisites

This tutorial will be a hands-on demonstration. If you’d like to follow along, you must have the following requirements.

*   You must have a [MySQL](https://www.mysql.com/) server already installed and configured on a Linux computer. This tutorial uses a _MySQL 8.0.28_ on _Ubuntu 20.04_.

Related:[How to Install Ubuntu 20.04 \[Step-by-Step\]](https://adamtheautomator.com/install-ubuntu/)

*   You must have an empty database or [create a new one](https://www.w3schools.com/mysql/mysql_create_db.asp) if there is none. This database is where you’ll create the table and insert sample data for indexing. This tutorial will be using a database named `ata_db`.

## Creating the MySQL Full Text Searching Index

There are three methods to add a full-text search index to a table. Each method, while using different statements, achieves the same result.

### Option 1: Using the CREATE TABLE Statement (New Table)

Typically, you would create a new table and already know which columns to add to the full-text index. Suppose you need to create a table containing article posts with columns such as:

*   `id`: The primary key that uniquely identifies each row in the table. The integer value in this column increments with each new row.
*   `title`: This column stores the article titles.
*   `content`: This column stores the content or body of the articles.
*   `author`: This column stores the author’s name.

In this case, you’ll want to include the `title`, `content`, and `author` into the MySQL full text searching index when you create the table. Follow the below steps to create the new table.

1\. First, log in to your server using your preferred [SSH](https://adamtheautomator.com/ssh-command-in-linux/) client.

Related:[A Windows Guy in a Linux World: Setting up SSH Command in Linux](https://adamtheautomator.com/ssh-command-in-linux/)

2\. Now, connect to the MySQL server. This example logs in to MySQL using the default MySQL root user.

```bash
sudo mysql
```

![Connecting to the MySQL server](https://adamtheautomator.com/wp-content/uploads/2022/04/image-634.png)

Connecting to the MySQL server

If you have a different MySQL user account, run the below command instead. Make sure to replace USERNAME with your MySQL user account.

```basic
mysql -u USERNAME -p
```

3\. Now, create a new table named ata\_tb in the ata\_db database by running the following statement:

The PRIMARY KEY(id) specifies that the table’s [primary key](https://www.lifewire.com/primary-key-definition-1019179) will be the id column.

The FULLTEXT ata\_post\_index (title,content,author) creates a full-text search index called ata\_post\_index. The full-text index includes the title, content, and author columns.

```sql
-- Select the ata_db database
USE ata_db;

-- Create the ata_tb table with indexing
CREATE TABLE ata_tb (
    id INT NOT NULL AUTO_INCREMENT,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    author TEXT NOT NULL,
    PRIMARY KEY (id),
    FULLTEXT ata_post_index (title, content, author)
);
```

The below screenshot shows the result of the successful table creation.

![Creating the new table with MySQL full text searching index](https://adamtheautomator.com/wp-content/uploads/2022/04/image-635.png)

Creating the new table with MySQL full text searching index

### Option 2: Using the ALTER TABLE Statement (Existing Table)

What if you previously created a table and did not include any full-text search indexing? Or perhaps there’s an existing table, and you want to add a full-text search index? You can do so by running the [ALTER TABLE](https://www.w3schools.com/mysql/mysql_alter.asp) statement.

> _MySQL only supports full-text indexing on tables whose storage engine is either [InnoDB](https://dev.mysql.com/doc/refman/8.0/en/innodb-storage-engine.html) or [MyISAM](https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html). InnoDB is the default MySQL storage engine._

The full syntax below shows how to alter a table to add the full-text index.

```sql
ALTER TABLE table_name ADD FULLTEXT index_name (column_name1, column_name2,…)
```

To add the full-text index to the `ata_tb` table, including the `title`, `content`, and `author` columns, execute the below statement. The resulting index name will be `ata_post_index`.

```sql
-- ALTER the ata_tb table to create a full-text search index
ALTER TABLE
    ata_tb
ADD
    FULLTEXT ata_post_index (title, content, author);
```

After running the statement, you will likely get a warning, as you can see below.

![Creating a full-text search index using the ALTER TABLE statement](https://adamtheautomator.com/wp-content/uploads/2022/04/image-636.png)

Creating a full-text search index using the ALTER TABLE statement

Should you be worried about this warning? To answer that, show the warning message on the screen like so.

```sql
SHOW WARNINGS;
```

The warning below with the message `InnoDB rebuilding table to add column FTS_DOC_ID` is normal when you first create the full-text index on a table. The storage engine is rebuilding the table with a new `FTS_DOC_ID` column for indexing. This warning is informational.

![Showing the warnings](https://adamtheautomator.com/wp-content/uploads/2022/04/image-637.png)

Showing the warnings

### Option 3: Using the CREATE INDEX Statement (Existing Table)

Another way to create a full-text index on an already existing table is with the [CREATE INDEX](https://dev.mysql.com/doc/refman/8.0/en/create-index.html) statement. The full statement syntax is shown below.

```sql
CREATE FULLTEXT INDEX index_name ON table_name (column_name1, column_name2, ... );
```

Following that syntax, run the statement below to create a full-text index called `ata_post_index` on the `ata_tb` table. This full-text index will include the `title`, `content`, and `author` columns.

```sql

-- Creating a full-text index on a table
CREATE FULLTEXT INDEX ata_post_index ON ata_tb(title, content, author);
-- Show the warning messages on the screen
SHOW WARNINGS;
```

If you create the full-text index for the first time in this table, you will get the same warning message that says `InnoDB rebuilding table to add column FTS_DOC_ID`, which is informational. This warning message does not require any action from you.

![Creating a full-text search index using the CREATE INDEX statement](https://adamtheautomator.com/wp-content/uploads/2022/04/image-638.png)

Creating a full-text search index using the CREATE INDEX statement

### Inserting Sample Data into the Table

The new table is empty, and you must insert new rows of data for indexing. To insert new sample data into the table, run the below [INSERT INTO](https://www.w3schools.com/mysql/mysql_insert.asp) statement. The below statement inserts three rows into the title, content, and author columns.

> _You don’t need to include the id column because MySQL inserts an autoincrementing integer value with new each row._

```sql
INSERT INTO
    ata_tb (title, content, author)
VALUES
    (
        'Get Paid to Write',
        'ATA Learning is always seeking instructors of all experience levels.
         Regardless if you''re a junior admin or system architect,
         you have something to share.',
        'Adam'
    ),
    (
        'Automate your Java Builds',
        'If you''re a developer, you''d typically prefer to automate
         the tedious manual build deployment process.',
        'Sagar'
    ),
    (
        'Tapping Cloudflare Load Balancing to Route Server Traffic',
        'Are you tired of applications and system outages whenever
         your site detects a spike in traffic?',
        'Obare'
    );
```

As you can see below, the MySQL statement inserted three rows into the table.

![Inserting new rows](https://adamtheautomator.com/wp-content/uploads/2022/04/image-639.png)

Inserting new rows

You now have a demo table with sample data for indexing.

## Using MySQL Full-Text Searching

Now that you have created a full-text index in the table, you can start running full-text searches on your data. MySQL provides the [MATCH() AGAINST() function](https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html#function_match) explicitly for doing full-text searches.

*   `MATCH()` takes a list of columns as an argument to search for the keyword you specify.
*   `AGAINST()` accepts the keyword to search and an optional search modifier.

> _By default, if you do not specify a modifier, the function uses the [IN NATURAL LANGUAGE MODE](https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html) modifier. This modifier interprets the keyword as a phrase in natural human language._

For example, run the below statement to search for the phrase ‘`are you`’. The

Run the command below to see all the rows where the columns contain the phrase `Are you`. The `\\G` modifier tells MySQL to break the output into multiple lines instead of one long line with the semi-colon (`;`) modifier.

```sql
SELECT
    *
FROM
    ata_tb
WHERE
    MATCH (title, content, author) AGAINST ('Are you' IN NATURAL LANGUAGE MODE)\\G
```

As you can see in the output below, there is only one entry containing the phrase “`Are you`”, but you get three results since the “`you're`” string is also considered a match.

This behavior is helpful for typos when users search your site. When your users search for a term and don’t find what they are looking for, they will usually search for a similar term.

![Running a full-text search](https://adamtheautomator.com/wp-content/uploads/2022/04/image-640.png)

Running a full-text search

### Returning Search Results by Order of Relevance

MySQL does not consider the relevance of each search result when returning them. You can make MySQL return results based on relevance by ranking them based on scores.

_Rank_, also known as relevance rank, is a score that MySQL gives to each row that matches the search condition. Scores are [floating-point](https://www.freecodecamp.org/news/floating-point-definition/) values, where 0 is the lowest relevance score.

Run the below command to see how MySQL ranks each row based on the query “`Adam You`”. The condition `> 0` in the `WHERE` clause will filter out all rows with a score of 0 (no relevance).

The `ORDER BY score DESC` keyword sorts the results by relevance in descending order so that the most relevant results are on top.

```sql
SELECT
    id,
    MATCH (title, content, author) AGAINST ('Adam You') as score
FROM
    ata_tb
WHERE
    MATCH (title, content, author) AGAINST ('Adam You') > 0
ORDER BY
    score DESC;
```

You will get an output similar to the one below. You can see in the output that the row with id `1` has the highest score in the set, which makes it the most relevant to the search. The reason is that this row has both search terms `Adam` and `You` in the `content` and `author` columns, respectively.

The row with id `2` is in the second position because it contains only one of the search terms, “`You`” in the `content` column.

The row with id `3` is the least relevant because it only contains the term `You`.

This behavior can be helpful when your users search for multiple terms and you want to prioritize results that contain all of the search terms.

![Ordering results by relevance](https://adamtheautomator.com/wp-content/uploads/2022/04/image-641.png)

Ordering results by relevance

### **Full-Text Searching with Query Expansion**

Now that you know how to order the results by relevance, you may be wondering if you can make the results even more relevant. Yes, you can — with [query expansion](https://dev.mysql.com/doc/refman/8.0/en/fulltext-query-expansion.html). With query expansion, you can tell MySQL to automatically add related terms to the search phrase that the user entered.

This technique will give you more relevant results because it will find entries that contain the related terms even if they don’t contain the exact search phrase. For example, the search “database” could be expanded to “MySQL”, “Oracle”, “RDBMS”, etc.

The trade-off is that you may get more results than you want, even results not relevant to the original search. But, you can always include the `ORDER BY score DESC` to rank the results by relevance and show only the most relevant ones.

The statement below shows the syntax that includes the `WITH QUERY EXPANSION` modifier.

```sql
SELECT * FROM ata_tb WHERE MATCH (column_name1,column_name2,column_name3) AGAINST ('The_search_phrase' WITH QUERY EXPANSION);
```

In this example, you’ll create another table in the same database, insert data, and perform full-text searches with query expansion.

1\. Create a new table named articles that includes the title and id columns in the full-text search index.

```sql
CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title, body)
);
```

2\. Now, insert some data into the articles table.

```sql
INSERT INTO articles (title,body) VALUES
    ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ('How To Use MySQL Well','After you went through a ...'),
    ('Optimizing MySQL','In this tutorial, we show ...'),
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ('MySQL vs. YourSQL','In the following database comparison ...'),
    ('MySQL Security','When configured properly, MySQL ...');
```

3\. Now, Run the below command to search for the term “database” in the title and body columns.

```sql
SELECT
    *
FROM
    articles
WHERE
    MATCH (title, body) AGAINST ('database');
```

As you can see below, the search two results; MySQL Tutorial and MySQL vs. YourSQL.

![Searching for the term database](https://adamtheautomator.com/wp-content/uploads/2022/04/image-642.png)

Searching for the term database

4\. To further improve the relevance of the results, rerun the command above with the WITH QUERY EXPANSION modifier.

```sql
SELECT
    *
FROM
    articles
WHERE
    MATCH (title, body) AGAINST ('database' WITH QUERY EXPANSION);
```

This time, you have six results instead of the previous two. Why? MySQL conducts the search using two different search phrases. The first phrase only contains “database”. The second phrase is “database,” plus the most relevant documents from the first search.

In this case, one of these documents contains the word “database” and “MySQL”. MySQL uses this second phrase to retrieve more documents that contain the word “MySQL” that might be relevant to the search.

This behavior is useful when users make typographical errors or use different terms to mean the same thing.

![MySQL Full-Text Searching with Query Expansion](https://adamtheautomator.com/wp-content/uploads/2022/04/image-643.png)

MySQL Full-Text Searching with Query Expansion

## Conclusion

In this guide, you have learned how to use the MySQL full-text searching function to find accurate results. You have also learned how to use the `WITH QUERY EXPANSION` modifier to retrieve more relevant results even if they don’t contain the exact search phrase.

At this point, you should have a good understanding of how to use MySQL full-text searching to your advantage. Perhaps add full-text search functionality to your [JavaScript](https://blog.couchbase.com/how-to-full-text-search-javascript-app/) applications and return the most relevant results in [Node.JS](https://www.javaniceday.com/post/full-text-search-in-node-js-search-related-data).

Related:[How to Create an HTTPS NodeJS Web Sevice with Express](https://adamtheautomator.com/https-nodejs/)

Share this article

[Share on X](https://twitter.com/intent/tweet?url=https%3A%2F%2Fadamtheautomator.com%2Fmysql-full-text-searching%2F&text=Accurate%20Database%20Searches%20with%20MySQL%20Full%20Text%20Searching)[Share on Facebook](https://www.facebook.com/sharer/sharer.php?u=https%3A%2F%2Fadamtheautomator.com%2Fmysql-full-text-searching%2F)[Share on LinkedIn](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fadamtheautomator.com%2Fmysql-full-text-searching%2F)

## Related Posts

![](https://adamtheautomator.com/wp-content/uploads/2022/04/How-to-Install-MySQL-on-Ubuntu.jpg)

### [Install MySQL on Ubuntu: Get Your Database Server Up and Running](/install-mysql-on-ubuntu/)

Want a robust, secure database for your projects? Discover how to install MySQL on Ubuntu and implement essential security measures with this guide.

![](https://adamtheautomator.com/wp-content/uploads/2024/01/mysql-indexes.jpg)

### [How To Use MySQL Indexes Through Examples](/mysql-indexes/)

Supercharge your database with MySQL indexes. Optimize queries, boost speed—master the art effortlessly, and elevate performance in this tutorial!

![](https://adamtheautomator.com/wp-content/uploads/2023/12/mysql-stored-procedures.jpg)

### [Advanced Database Techniques with MySQL Stored Procedures](/mysql-stored-procedures/)

Learn how to use MySQL Stored Procedures effectively to make database operations more efficient in this ATA Learning tutorial!

## Categories

*   [IT Ops](/category/it-ops/)
*   [Cloud](/category/cloud/)
*   [DevOps](/category/devops/)
*   [Home Ops](/category/home-ops/)
*   [Information Security](/category/infosec/)
*   [Software Development](/category/software-development/)

## Site

*   [Home](/)
*   [Tutorials](/tutorials/)
*   [Instructors](/author/)
*   [Advertising](/advertising/)
*   [Recommended Resources](/resources/)
*   [About Adam](/about-adam/)

Copyright 2026© ATA Learning | [Privacy Policy](/privacy/)