MYSQL FULLTEXT INDEX AND SEARCH

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:

1 2 3 4 5 6 7 8

CREATE TABLE table_name( column data_type, … PRIMARY_KEY(column), FULLTEXT (column1,..) );

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

1 2 3

ALTER TABLE tablename ADD FULLTEXT (column1, column2)

FULLTEXT index on CREATE INDEX statement

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

SYNTAX:

1 2 3

CREATE FULLTEXT INDEX index_name ON table_name(column_index1,...)

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: 　　

1 2 3

SELECT * FROM tablename WHERE MATCH (column1, column2) AGAINST ('word1', 'word2', 'word3')

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