MySQL Full Text Search

MySQL Full-Text search provides a way to implement various advanced search technique such as natural language search, Boolean text search, and query expansion.Suppose you have a database called product with different column such as product_id, product_name, description and price. Let us say you now want to search for the products that have “smartphone” in their name or description. Below is the SQL query for the same:

SELECT *
FROM products
WHERE MATCH(product_name, description) 
AGAINST('smartphone' IN BOOLEAN MODE);

Here, 

• MATCH function is used to perform full text search on product_name and description column. 
• AGAINST specifies the search term “smartphone”  and in boolean mode.
  

You all have search something on Google , Bing or Yahoo!  and all have gone through different sites that provides functionality of search. These are common application where FULL-TEXT is being used.

Implementation

LIKE operator or REGULAR expression  is begin used by MySQL for implementing of partial search. Based on the pattern in LIKE or regular Expression MySQL search in  entire table.

To create a full-text search you must create a full text index on each column you want to be indexed. In MySQL it is done through FULLTEXT keyword. MySQL creates a full-text search from text data and lookup on this index using an algorithm to determine rows that matches against the search query.

But it has some limitation when text column is large and table has large number of rows.

Features of MySQL Full-Text Search

1. Natural Language Search: Means it can understand user query in more human-like ways, taking into account the word proximity , synonyms, and stop words.
2. Moderate Index size: The size of a FULL-TEXT index is relatively small.
3. Boolean Search: MySQL full-text search also supports boolean operators like “AND”, “OR” and “NOT” which allows users to create more complex search queries.
4. Native SQL like interface: MySQL provides the SQL- like statement to perform full-text search.
5. Rich-Querying Capabilities

Creating FULL-TEXT Indexes

If you want to perform a FULL-TEXT search in a column of a table, you must index its data.Whenever the data of the column changes MySQL will recreates the full-text index . MySQL supports indexing and re-indexing data automatically for full-text enable columns.

Different ways to create a FULLTEXT  Search index :

• Using CREATE TABLE  Statement

CREATE TABLE table_name(

          column_list,

          …,

          FULLTEXT (column1, column2, …)

);

Example: 

CREATE TABLE posts (
    id INT NOT NULL AUTO_INCREMENT, 
    title VARCHAR(255) NOT NULL,
    body TEXT,
    PRIMARY KEY (id),
    FULLTEXT KEY(body)
);

INSERT INTO posts (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 ...');
       
 SELECT * FROM posts
     WHERE MATCH (title,body)
     AGAINST ('database' IN NATURAL LANGUAGE MODE);
     

• Using ALTER TABLE Statement

ALTER TABLE TableName
        ADD FULLTEXT INDEX FTIndexName (ColumnName1, ColumnName2, …);

Example:

CREATE TABLE posts (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT
     ) ENGINE=InnoDB;
     
ALTER TABLE Books
    ADD FULLTEXT INDEX FT_Books_Title_Description (title, body)
    WITH STOPLIST = SYSTEM;
    
SELECT * FROM posts
     WHERE MATCH (title,body)
     AGAINST ('database' IN NATURAL LANGUAGE MODE);

• Using CREATE INDEX Statement

CREATE FULLTEXT INDEX FTIndexName
        ON TableName (ColumnName1, ColumnName2, …);

Example:

CREATE TABLE posts (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT
     ) ENGINE=InnoDB;
     
CREATE FULLTEXT INDEX FTIndexName
        ON posts (title, body);
    
INSERT INTO posts (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 ...');
       
SELECT * FROM posts
     WHERE MATCH (title,body)
     AGAINST ('database' IN NATURAL LANGUAGE MODE);

Drop a FULLTEXT index

ALTER TABLE table_name

DROP INDEX index_name;

Example: 

ALTER TABLE offices
DROP INDEX address;

Output:

Output

Conclusion

Full-text search is a powerful searching features of MySQL that allows fast and efficient searching in database.Users can search a word or phrase in multiple table column and also works in case the text is not exact.It greatly improve the performance and accuracy of text-based search queries in SQL.