MySQL - Full-Text Search

The MySQL Full-Text Search allows us to search for a text-based data, stored in the database. Before performing the full-text search in a column(s) of table, we must create a full-text index on those columns.

The FTS (full-text search) provides the capability to match the searched string value through large text content such as blogs, articles, etc.

MySQL Full-Text Search

To perform a Full-Text Search on a MySQL table, we use MATCH() and AGAINST() functions in a WHERE clause of an SQL SELECT statement.

Stop words are words that are commonly used (such as 'on', 'the', or, 'it') in sentences and will be ignored during the searching process.

The basic syntax to perform a full-text search on a MySQL is as follows −

SELECT column_name(s) FROM table_name 
WHERE MATCH(col1, col2, ...) 
AGAINST(expression [search_modifier])

Here,

• MATCH() function contains one or more columns separated by commas to be searched.
• AGAINST() function contains a search string to use for the full-text search.

Key Points of MySQL Full-Text Search

Following are some key points about the full-text search in MySQL −

• Either InnoDB or MyISAM tables use the full-text indexes. The minimum length of the word for full-text searches is three characters for InnoDB tables and four characters for MyISAM tables.
• Full-Text indexes can be created on text-based columns (CHAR, VARCHAR or TEXT columns).
• A FULLTEXT index can be defined while creating the table using CREATE TABLE statement or can be defined later using the ALTER TABLE or CREATE INDEX statements.
• Without FULLTEXT index, it is faster to load large data sets into a table than to load data into a table which has an existing FULLTEXT index. Therefore it is recommended to create the index after loading data.

Types of Full-Text Searches

There are three types of full-text searches. The same is described below:

• Natural Language Full-Text Searches: This allows the user to enter the search query in a natural human language without any special characters or operators. The search engine will examine the query entered by the user and returns the relevant results based on the user's intent.
• Boolean Full-Text Searches: This allows us to perform a full-text search based on very complex queries in the Boolean mode along with Boolean operators such as +, -, >, <, (), ~, *, "".
• Query Expansion Searches: This expands the user's query to widen the search result of the full-text searches based on automatic relevance feedback or blind query expansion.

Creating MySQL FULLTEXT Index

In MySQL, we can define a full-text index on particular column while creating a new table or on an existing table. This can be done in three ways:

• Using the FULLTEXT Keyword

• Using the ALTER TABLE Statement

• Using the CREATE INDEX Statement

Using the FULLTEXT Keyword

To define full-text index on a column while creating a new table, we use the FULLTEXT keyword on that column within the CREATE TABLE query. Following is the syntax −

CREATE TABLE table_name(  
   column1 data_type,  
   column2 data_type,
   ...,
   FULLTEXT (column1, column2, ...)  
);

Let us create first a table named FILMS and define the full-text index on NAME and DIRECTOR columns, using the following query −

CREATE TABLE FILMS (
   ID int auto_increment not null primary key,
   NAME varchar(50),
   DIRECTOR TEXT,
   FULLTEXT (NAME, DIRECTOR)
);

Now, let us insert values into this table using the following query −

INSERT INTO FILMS (NAME, DIRECTOR) VALUES 
('RRR', 'Directed by Rajamouli'),
('Bahubali', 'Directed by Rajamouli'),
('Avatar', 'Directed by James cameron'),
('Robot', 'Directed by Shankar');

The table will be created as −

Here, we are fetching all the rows from the FILMS table where the NAME or DIRECTOR column matches the string ‘Rajamouli’ using the MATCH and AGAINST functions as shown below −

SELECT * FROM FILMS 
WHERE MATCH (NAME, DIRECTOR) 
AGAINST ('Rajamouli');

As we can see in the output below, the full-text search has been performed against a string ‘Rajamouli’ and it returned the rows which contains this string.

Using the ALTER TABLE Statement

In MySQL, we can create full-text index on particular columns of an existing table using the ALTER TABLE statement. Following is the syntax −

ALTER TABLE table_name    
ADD FULLTEXT (column1, column2,...)

In this example, we are defining a full-text index named FULLTEXT on NAME and DIRECTOR columns of the previously created FILMS table −

ALTER TABLE FILMS ADD FULLTEXT (NAME, DIRECTOR);

Now, let us retrieve all the rows from the FILMS table where the NAME or DIRECTOR column matches the string 'Shankar'.

SELECT * FROM FILMS 
WHERE MATCH (NAME, DIRECTOR) 
AGAINST ('Shankar');

Following is the output −

Using the CREATE INDEX Statement

In MySQL, we can also create a full-text index for an existing table using the CREATE INDEX statement. Following is the syntax −

CREATE FULLTEXT INDEX index_name  
ON table_name (index_column1, index_column2,...)

We are creating a full-text index with the name INDEX_FULLTEXT on the NAME and DIRECTOR column of the FILMS table −

CREATE FULLTEXT INDEX INDEX_FULLTEXT ON FILMS (NAME, DIRECTOR);

Now, let us retrieve all the rows from the FILMS table where the NAME or DIRECTOR column matches the string value as shown in the below query −

SELECT * FROM FILMS 
WHERE MATCH(NAME, DIRECTOR) 
AGAINST ('James Cameron');

Following is the output −

Dropping MySQL FULLTEXT index

In MySQL, we can remove or drop a full-text index from a table using the ALTER TABLE DROP INDEX statement.

Following is the syntax −

ALTER TABLE table_name DROP INDEX index_name; 

In the following query, we will delete the previously created full-text index −

ALTER TABLE FILMS DROP INDEX INDEX_FULLTEXT;

Let us verify whether the index is dropped or not by executing the below query −

SELECT * FROM FILMS 
WHERE MATCH(NAME, DIRECTOR) 
AGAINST ('James Cameron');

As we can see in the output, the full-text index is removed on the NAME and DIRECTOR columns.

ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list

Full-Text Search Using Client Program

In addition to performing the full-text search using MySQL Query, we can also do so using the client program.

Syntax

$sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
$mysqli->query($sql);

sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
con.query(sql);

String sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
statement.execute(sql);

fulltext_search_query = "SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST ('Rajamouli')"
cursorObj.execute(fulltext_search_query)

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.
');    
//creating a table films that stores fulltext.
$sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!\n");
}
//insert data
$q = "INSERT INTO FILMS (NAME, DIRECTOR) VALUES ('RRR', 'The film RRR is directed by Rajamouli'), ('Bahubali', 'The film Bahubali is directed by Rajamouli'),
      ('Avatar', 'The film Avatar is directed by James cameron'), ('Robot', 'The film Robot is directed by Shankar')";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!\n");
}
//now display the table records    
$s = "SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST ('Rajamouli')";
if ($r = $mysqli->query($s)) {
    printf("Table Records: \n");
    while ($row = $r->fetch_assoc()) {
        printf(" ID: %d, Name: %s, Director: %s", $row["ID"], $row["NAME"], $row["DIRECTOR"]);
        printf("\n");
    }
} else {
    printf('Failed');
}
$mysqli->close();

Table created successfully...!
Data inserted successfully...!
Table Records:
 ID: 1, Name: RRR, Director: The film RRR is directed by Rajamouli
 ID: 2, Name: Bahubali, Director: The film Bahubali is directed by Rajamouli  

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
  con.query(sql);

  //insert data into created table
  sql = `INSERT INTO FILMS (NAME, DIRECTOR)
  VALUES ('RRR', 'The film RRR is directed by Rajamouli'),
  ('Bahubali', 'The film Bahubali is directed by Rajamouli'),
  ('Avatar', 'The film Avatar is directed by James cameron'),
  ('Robot', 'The film Robot is directed by Shankar')`;
  con.query(sql);

  //display the table details!...
  sql = `SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST ('Rajamouli')`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});  

[
  {
    ID: 1,
    NAME: 'RRR',
    DIRECTOR: 'The film RRR is directed by Rajamouli'
  },
  {
    ID: 2,
    NAME: 'Bahubali',
    DIRECTOR: 'The film Bahubali is directed by Rajamouli'
  }
]   

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class FulltextSearch {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //creating a table that takes fulltext column...!
         String sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
         statement.execute(sql);
         System.out.println("Table created successfully...!");

         //inserting data to the tables
         String insert = "INSERT INTO FILMS (NAME, DIRECTOR) VALUES ('RRR', 'The film RRR is directed by Rajamouli'), ('Bahubali', 'The film Bahubali is directed by Rajamouli')," +
                 "('Avatar', 'The film Avatar is directed by James cameron'), ('Robot', 'The film Robot is directed by Shankar')";
         statement.execute(insert);
         System.out.println("Data inserted successfully...!");

         //displaying the table records...!
         ResultSet resultSet = statement.executeQuery("SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST ('Rajamouli')");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2)+ " "+resultSet.getString(3));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}      

Connected successfully...!
Table created successfully...!
Data inserted successfully...!
1 RRR The film RRR is directed by Rajamouli
2 Bahubali The film Bahubali is directed by Rajamouli  

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
fulltext_search_query = f"SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST ('Rajamouli');"
cursorObj.execute(fulltext_search_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Full-text search results:")
for row in results:
    print(row)
cursorObj.close()
connection.close()         

Full-text search results:
(1, 'RRR', 'The film RRR is directed by Rajamouli')
(2, 'Bahubali', 'The film Bahubali is directed by Rajamouli')