MySQL – Full-Text Search
Table of content
- MySQL Full-Text Search
- Key Points of MySQL Full-Text Search
- Types of Full-Text Searches
- Creating MySQL FULLTEXT Index
- Dropping MySQL FULLTEXT index
- Full-Text Search Using Client Program
”;
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, ...) );
Example
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 −
ID | NAME | DIRECTOR |
---|---|---|
1 | RRR | Directed by Rajamouli |
2 | Bahubali | Directed by Rajamouli |
3 | Avatar | Directed by James Cameron |
4 | Robot | Directed by Shankar |
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'');
Output
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.
ID | NAME | DIRECTOR |
---|---|---|
1 | RRR | Directed by Rajamouli |
2 | Bahubali | Directed by Rajamouli |
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,...)
Example
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'');
Output
Following is the output −
ID | NAME | DIRECTOR |
---|---|---|
4 | Robot | Directed by Shankar |
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,...)
Example
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'');
Output
Following is the output −
ID | NAME | DIRECTOR |
---|---|---|
3 | Avatar | Directed by James Cameron |
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.
Syntax
Following is the syntax −
ALTER TABLE table_name DROP INDEX index_name;
Example
In the following query, we will delete the previously created full-text index −
ALTER TABLE FILMS DROP INDEX INDEX_FULLTEXT;
Verification
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
To perform the Fulltext Search on a MySQL database through a PHP program, we need to execute the CREATE TABLE statement using the mysqli function query() as follows −
$sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )"; $mysqli->query($sql);
To perform the Fulltext Search on a MySQL database through a JavaScript program, we need to execute the CREATE TABLE statement using the query() function of mysql2 library as follows −
sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )"; con.query(sql);
To perform the Fulltext Search on a MySQL database through a Java program, we need to execute the CREATE TABLE statement using the JDBC function execute() as follows −
String sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )"; statement.execute(sql);
To perform the Fulltext Search on a MySQL database through a python program, we need to execute the CREATE TABLE statement using the execute() function of the MySQL Connector/Python as follows −
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();
Output
The output obtained is as shown below −
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); }); });
Output
The output obtained is as shown below −
[ { 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); } } }
Output
The output obtained is as shown below −
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()
Output
The output obtained is as shown below −
Full-text search results: (1, ''RRR'', ''The film RRR is directed by Rajamouli'') (2, ''Bahubali'', ''The film Bahubali is directed by Rajamouli'')
”;