Back to Top

MySQL FULLTEXT Index and Search

FULLTEXT Index and Search in MySQL

FULL TEXT index is the index of the text field can only speed up the string and it come in front of the field contents when performing data retrieval operation. If the field contains large text consisting of more than one word, the general index not effect on it. Full-text indexing is known as FULLTEXT index type in MySQL.

We often perform search operation LIKE%word% form which MySQL is very complicated and when the amount of data to be processed is large, the response time will be very long.At that time, the full-text index can be useful.

Mysql FULLTEXT index is designed to solve complex queries which is for fetching 100,000 or excessive number of records and result in a crash so with Mysql FULLTEXT indexing, you can do indexing for high search efficiency with capacity of supporting millions of data retrieval.

MySQL version 5.6 and above version support full-text index for a data type CHAR, VARCHAR or TEXT in MyISAM or InnoDB storage engine type.

MySQL allows to define FULLTEXT index on the CREATE TABLE statement when you create the table.Also, you can define FULLTEXT index on ALTER TABLE or CREATE INDEX statement for the existing tables.

FULLTEXT index on CREATE TABLE statement

Let’s see the example of FULLTEXT index on CREATE TABLE statement:

Here, You can use comma-separated columns that you want to create the full-text index after the FULLTEXT
statement. After defining, MySQL automatically maintains the index. In add, update, or delete rows, the index is automatically updated.

FULLTEXT index on ALTER TABLE statement

Let’s see the statement for generating the full-text index in MySQL.This command is useful when you want to re-indexing on column or update indexing of table

FULLTEXT index on CREATE INDEX statement

CREATE INDEX statement is useful to create FULLTEXT index for existing tables. Let’s see the syntax:

SYNTAX:

With the full-text index, you can use the SELECT query command to retrieve one or more of a given word of data records.

NOTE: By default, full-text searches are not case sensitive.

Here is the basic syntax for this type of query command:   

You can see the above syntax MATCH (…) AGAINST (…) will actually calculate a correlation value.The above command has field column1 and column2 and words are word1, word2, and word3 to check out the entire data record. When MATCH () is used with WHERE clause, the rows returned are automatically sorted by relevance to the order from high in the end.

Notes : InnoDB storage engine support full-text indexing from MySQL version 5.6.

Query and index optimization

When the database has a large number of data records, query performance matters.If the database has a few data records, don’t need a query and index optimization because query easily executed very quickly with or without the use of the index. When the database records are more than 1000, require more amount of data memory on the MySQL server so at that time database performance is important.

Limitation:

1. Partition table does not support FULL TEXT index.
2. FULL-TEXT indexing for most multi-byte character sets.
3. Currently, only InnoDB and MyISAM storage engine supports FULL TEXT search
4. MySQL comes with a built-in stopword list means these words in the full-text index data is always ignored. If desired, you can override this list.

Advantages

1. Search accuracy is very high
2. Increase query speed
3. Support for MySQL Slave distributed system

I am ending tutorial over here but you have any question, post in comment section. What do you suggest FullText Index is good to use with other Storage Engine?

Comments (1)

  1. Reena dhanani says:

    Thanks for finally talking about full-text index and search.I can’t wait to learn much more from you. This is actually a wonderful website.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts

How to handle Exception in PHP5

Posted on 3 years ago

Bhumi

Table layout using DIV tags

Posted on 5 years ago

Bhumi

How to change Base Path in Typo3

Posted on 5 years ago

Bhumi

How to create Bootstrap Switch using CSS

Posted on 2 years ago

Bhumi