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