MySQL - Query Expansion Full-Text Search

Table of content

Query Expansion Full-Text Search
Query Expansion Full-Text Search Using Client Program

In relational databases like MySQL, Full-text search is a technique used to retrieve result-sets that might not perfectly match the search keyword. This type of search is useful in cases where the keywords used for searching do not match the results a user expects. So, this searching technique is designed to focus on increasing search relevance in order to reduce the accuracy gap between search queries and search results. Thus, search results are displayed in the order of highest to the lowest relevancy to the search keyword.

There are three types of search modes used with Full-text search −

Natural Language Mode
Query Expansion Mode
Boolean Mode

Query Expansion Full-Text Search

Search is always done by the user with the limited knowledge they possess. Thus, there are cases when the search keywords are way too short to conduct a proper search. This is where Blind Expansion Search technique comes into picture.

Blind Expansion Search, also known as Automatic Relevance Feedback, is used to widen the search results based on additional keywords that are closely related to the original keywords. It is enabled using the 'WITH QUERY EXPANSION' search phrase.

The search is performed twice in this cases by following the steps given below −

Step 1 − All the rows that match the given search keyword are searched first.

Step 2 − These obtained rows are then checked for relevant words to the original keyword in them.

Step 3 − Finally, the rows are searched again based on these relevant words instead of the original keywords specified by the users.

To perform the query expansion full-text search on a database table, the WITH QUERY EXPANSION or IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION search modifiers must be specified in the AGAINST() function.

Example

Let us understand how to perform Query Expansion Full-text Search on a database table in the following example.

For that, we will first create a table named DBMS_TUTORIALS containing the title and description of an article. The FULLTEXT index is applied on text columns TUTORIAL_TITLE and DESCRIPTIONS as shown below −

CREATE TABLE DBMS_TUTORIALS(
   TUTORIAL_TITLE VARCHAR(200), 
   DESCRIPTIONS TEXT, 
   FULLTEXT(TUTORIAL_TITLE, DESCRIPTIONS)
);

Now, let us insert details about tutorials, like their titles and descriptions, into this table using the following queries −

INSERT INTO DBMS_TUTORIALS VALUES 
('MySQL Tutorial', 'MySQL is an RDBMS that uses SQL to structure the data stored'),
('ORACLE Tutorial', 'ORACLE is an RDBMS that uses SQL to structure the data stored'),
('MySQL Security', 'MySQL Database can store sensitive data, so security is required'),
('MySQL vs MariaDB', 'Comparing two databases...'),
('JDBC Tutorial', 'In this Java-based database connectivity...');

The table is created as −

TUTORIAL_TITLE	DESCRIPTIONS
MySQL Tutorial	MySQL is an RDBMS that uses SQL to structure the data stored
ORACLE Tutorial	ORACLE is an RDBMS that uses SQL to structure the data stored
MySQL Security	MySQL Database can store sensitive data, so security is required
MySQL vs MariaDB	Comparing two databases...
JDBC Tutorial	In this Java-based database connectivity...

Using the Query Expansion Mode in full-text search, we search for records of articles relevant to data, with the keyword ‘RDBMS’

SELECT * FROM DBMS_TUTORIALS 
WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) 
AGAINST ('RDBMS' WITH QUERY EXPANSION);

Output

The output is obtained as −

TUTORIAL_TITLE	DESCRIPTIONS
ORACLE Tutorial	ORACLE is an RDBMS that uses SQL to structure the data stored
MySQL Tutorial	MySQL is an RDBMS that uses SQL to structure the data stored
MySQL Security	MySQL Database can store sensitive data, so security is required
MySQL vs MariaDB	Comparing two databases...
JDBC Tutorial	In this Java-based database connectivity...

IN NATURAL LANGUAGE MODE

In the result-set obtained above, all tutorial records are about databases, which is why the query retrieved all the records ordered based on relevance.

SELECT * FROM DBMS_TUTORIALS 
WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) 
AGAINST ('Security' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);

Output

The output is obtained as −

TUTORIAL_TITLE	DESCRIPTIONS
MySQL Security	MySQL Database can store sensitive data, so security is required
JDBC Tutorial	In this Java-based database connectivity...
MySQL Tutorial	MySQL is an RDBMS that uses SQL to structure the data stored
ORACLE Tutorial	ORACLE is an RDBMS that uses SQL to structure the data stored
MySQL vs MariaDB	Comparing two databases...

In this result-set, even if the search keyword is 'Security', the actual security related tutorials are just 'MySQL Security' and 'JDBC Tutorial', so they are retrieved first. These records are then followed by database related records as an expanded query.

Query Expansion Full-Text Search Using Client Program

We can also Perform Query expansion full-text search operation using the client program.

Syntax

PHP NodeJS Java Python

To perform the Query Expansion Full-Text Search through a PHP program, we need to execute the SELECT statement using the mysqli function query() as follows −

$sql = "SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST ('RDBMS' WITH QUERY EXPANSION)";
$mysqli->query($sql);

To perform the Query Expansion Full-Text search through a JavaScript program, we need to execute the SELECT statement using the query() function of mysql2 library as follows −

sql = "SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST ('RDBMS' WITH QUERY EXPANSION)";
con.query(sql);

To perform the Query Expansion Full-Text Search through a Java program, we need to execute the SELECT statement using the JDBC function executeQuery() as follows −

String sql = "SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST ('RDBMS' WITH QUERY EXPANSION)";
statement.executeQuery(sql);

To perform the Query Expansion Full-Text Search through a Python program, we need to execute the SELECT statement using the execute() function of the MySQL Connector/Python as follows −

queryexpansionfulltext_search = 'SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS)  AGAINST ('RDBMS' WITH QUERY EXPANSION)'
cursorObj.execute(queryexpansionfulltext_search)

Example

Following are the programs −

PHP NodeJS Java Python

$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.
');
/*CREATE Table*/
$sql = "CREATE TABLE DBMS_TUTORIALS(TUTORIAL_TITLE VARCHAR(200), DESCRIPTIONS TEXT, FULLTEXT(TUTORIAL_TITLE, DESCRIPTIONS))";
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!\n");
}
//insert data
$q = "INSERT INTO DBMS_TUTORIALS (TUTORIAL_TITLE , DESCRIPTIONS) VALUES 
('MySQL Tutorial', 'MySQL is an RDBMS that uses SQL to structure the data stored'),
('ORACLE Tutorial', 'ORACLE is an RDBMS that uses SQL to structure the data stored'),
('MySQL Security', 'MySQL Database can store sensitive data, so security is required'),
('MySQL vs MariaDB', 'Comparing two databases...'),
('JDBC Tutorial', 'In this Java-based database connectivity...')";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!\n");
}
//Using the Query Expansion Mode in Full-text search, try to search for records of DBMS_TUTORIALS relevant to data, with the keyword 'RDBMS'
$s = "SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST ('RDBMS' WITH QUERY EXPANSION)";
if ($r = $mysqli->query($s)) {
    printf("Table Records: \n");
    while ($row = $r->fetch_assoc()) {
        printf("Tutorial_title: %s, Descriptions: %s", $row["TUTORIAL_TITLE"], $row["DESCRIPTIONS"]);
        printf("\n");
    }
} else {
    printf('Failed');
}
$mysqli->close();

Output

The output obtained is as shown below −

Table created successfully...!
Data inserted successfully...!
Table Records:
Tutorial_title: ORACLE Tutorial, Descriptions: ORACLE is an RDBMS that uses SQL to structure the data stored
Tutorial_title: MySQL Tutorial, Descriptions: MySQL is an RDBMS that uses SQL to structure the data stored
Tutorial_title: MySQL Security, Descriptions: MySQL Database can store sensitive data, so security is required
Tutorial_title: MySQL vs MariaDB, Descriptions: Comparing two databases...
Tutorial_title: JDBC Tutorial, Descriptions: In this Java-based database connectivity...

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);

  //create a table...
  sql =
    "CREATE TABLE DBMS_TUTORIALS(TUTORIAL_TITLE VARCHAR(200), DESCRIPTIONS TEXT, FULLTEXT(TUTORIAL_TITLE, DESCRIPTIONS))";
  con.query(sql);

  //insert data
  sql = `INSERT INTO DBMS_TUTORIALS (TUTORIAL_TITLE , DESCRIPTIONS) VALUES 
  ('MySQL Tutorial', 'MySQL is an RDBMS that uses SQL to structure the data stored'), 
  ('ORACLE Tutorial', 'ORACLE is an RDBMS that uses SQL to structure the data stored'), 
  ('MySQL Security', 'MySQL Database can store sensitive data, so security is required'), 
  ('MySQL vs MariaDB', 'Comparing two databases...'), 
  ('JDBC Tutorial', 'In this Java-based database connectivity...')`;

  con.query(sql);

  //Using the Query Expansion Mode in Full-text search, try to search for records of DBMS_TUTORIALS relevant to data, with the keyword 'RDBMS'
  sql = "SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST ('RDBMS' WITH QUERY EXPANSION)";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Output

The output obtained is as shown below −

[
    {
      TUTORIAL_TITLE: 'ORACLE Tutorial',
      DESCRIPTIONS: 'ORACLE is an RDBMS that uses SQL to structure the data stored'
    },
    {
      TUTORIAL_TITLE: 'MySQL Tutorial',
      DESCRIPTIONS: 'MySQL is an RDBMS that uses SQL to structure the data stored'
    },
    {
      TUTORIAL_TITLE: 'MySQL Security',
      DESCRIPTIONS: 'MySQL Database can store sensitive data, so security is required'
    },
    {
      TUTORIAL_TITLE: 'MySQL vs MariaDB',
      DESCRIPTIONS: 'Comparing two databases...'
    },
    {
      TUTORIAL_TITLE: 'JDBC Tutorial',
      DESCRIPTIONS: 'In this Java-based database connectivity...'
    }
]

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

public class QeFSearch {
   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 DBMS_TUTORIALS(TUTORIAL_TITLE VARCHAR(200), DESCRIPTIONS TEXT, FULLTEXT(TUTORIAL_TITLE, DESCRIPTIONS))";
         statement.execute(sql);
         System.out.println("Table created successfully...!");

         //inserting data to the table
         String insert = "INSERT INTO DBMS_TUTORIALS (TUTORIAL_TITLE , DESCRIPTIONS) VALUES" + 
         "('MySQL Tutorial', 'MySQL is an RDBMS that uses SQL to structure the data stored')," + 
         "('ORACLE Tutorial', 'ORACLE is an RDBMS that uses SQL to structure the data stored')," + 
         "('MySQL Security', 'MySQL Database can store sensitive data, so security is required')," + 
         "('MySQL vs MariaDB', 'Comparing two databases...')," + 
         "('JDBC Tutorial', 'In this Java-based database connectivity...')";
         statement.execute(insert);
         System.out.println("Data inserted successfully...!");

         //Using the Query Expansion Mode in Full-text search, try to search for records of DBMS_TUTORIALS relevant to data, with the keyword 'RDBMS'...!
         ResultSet resultSet = statement.executeQuery("SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST ('RDBMS' WITH QUERY EXPANSION)");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Output

The output obtained is as shown below −

Connected successfully...!
Table created successfully...!
Data inserted successfully...!
ORACLE Tutorial ORACLE is an RDBMS that uses SQL to structure the data stored
MySQL Tutorial MySQL is an RDBMS that uses SQL to structure the data stored
MySQL Security MySQL Database can store sensitive data, so security is required
MySQL vs MariaDB Comparing two databases...
JDBC Tutorial in this Java-based database connectivity...

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()
queryexpansionfulltext_search = '''
SELECT * FROM DBMS_TUTORIALS 
WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) 
AGAINST ('RDBMS' WITH QUERY EXPANSION)
'''
cursorObj.execute(queryexpansionfulltext_search)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Query expansion Fulltext search results:")
for row in results:
    print(row)
cursorObj.close()
connection.close()

Output

The output obtained is as shown below −

Query expansion Fulltext search results:
('ORACLE Tutorial', 'ORACLE is an RDBMS that uses SQL to structure the data stored')
('MySQL Tutorial', 'MySQL is an RDBMS that uses SQL to structure the data stored')
('MySQL Security', 'MySQL Database can store sensitive data, so security is required')
('MySQL vs MariaDB', 'Comparing two databases...')
('JDBC Tutorial', 'In this Java-based database connectivity...')