MySQL Full-Text Search

Have you ever wondered how websites like blogs, news, e-commerce perform search queries? MySQL supports partial text searching by using the LIKE operator and regular expression.

However, when the text column is large and the number of rows in a table is increased, using LIKE or regular expressions has some limitations:

Following were some limitations:

  • both like and regex can search based on patterns it does not support flexible search for example: a product description contains car word but not classic.
  • there is no way to set score based on the result and rank perticular search terms

Because of these limitations, MySQL started supported full-text search in 5.6 or later. Followings are some of the advantages of full text search:

  • mysql support sql-like statement to perform full-text search
  • mysql update the index dynamically with the data changes
  • size of the fulltext index is relatively small
  • it is fast based on complex search queries
  • it support search throughout multiple fields at the same time

What is FULLTEXT index in MySQL?

You can create FULLTEXT index on a table where you want to run full text search queries. MySQL lets users run full-text queries against character based columns.

The full-text index can include one or more character-based columns in the table:

  • InnoDB and MyiSAM tables use FULLTEXT index
  • FULLTEXT index can only be defined for CHAR, VARCHAR or TEXT columns
  • It is advised to load data before creating FULLTEXT index for performance reasons
    • Large data set can be easier and faster to load without FULLTEXT index
    • Therefore FULLTEXT index should be applied after loading data
  • FULLTEXT index is supported in MYSQL version 5.6 or later

Types of FULLTEXT searches

There are three types of full-text searches :

  • Boolean Full-Text searches
  • Query expansion searches
  • Natural Language Full-Text Searches

Note: Some words are ignored in full-text searches. Check list here.

Creating FULLTEXT Index

First of all we need to create an index of type FULLTEXT in order to perform full-text search query, you must index data of the given columns.

MySQL will recreate the full-text index whenever data changes within specified columns.

MySQL allows you to define the FULLTEXT index by using the CREATE TABLE statement when you create the table or ALTER TABLE or CREATE INDEX statement for the existing tables.

Defining fulltext index using CREATE TABLE: 

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

Create FULLTEXT index using ALTER TABLE statement: 

ALTER TABLE posts  
ADD FULLTEXT(title, body);

Create FULLTEXT index using CREATE INDEX statement: 

CREATE FULLTEXT INDEX title_body_index
ON posts(title, body);

Drop a FULLTEXT index

To drop existing full-text index use following syntax: 

ALTER TABLE posts
DROP INDEX title_body_index;

Summary

In this tutorial you learned about:

  • What is fulltext index?
  • What are the different types of fulltext indexes?
  • How to create fulltext index?
  • How to drop fulltext index?

Next, tutorial we will learn about different types of fulltext search indexes.

↑