MySQL – Query Expansion Full-Text Search
Table of content
”;
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
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 −
$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...'')
”;