MySQL Full text index

Full text index

The full-text index in the table isused to search the full text of the data. This index assigns to the table using a "FULLTEXT" keyword. First, the table column changes, then the full-text index updates or recreate. MySQL version 5.6 allows using a full-text index in the table. The full-text index is used to create a table, create an index, and alter table statements.

Syntax

The basic syntax of the full-text index shows below.

FULLTEXT (column1, column2, … column)

The syntax of the full-text index with the create table command shows below.

 CREATE TABLE table_name (Column1 data type NOT NULL PRIMARY KEY,
 Column2 data type NOT NULL,
 Column3 data type NOT NULL,
 FULLTEXT (column1,column2,..)) 

The syntax of a full-text index with create index command shows below.

 CREATE FULLTEXT INDEX iname
 ON table name
 (column1,column2,..)) 

The syntax of the full-text index with alter table command shows below.

 ALTER TABLE tname
 ADD INDEX (column1,column2, …); 

The syntax of drop full-text index with alter table command shows below.

ALTER TABLE tnameDROP INDEX iname;

Examples of the FULLTEXT Index

1) Example: The basic full-text index example shows below.

Execute the following query to know about the full-text index. Here, we use a single column for the full-text key.

 mysql> create table index_table(
 roll_number INT NOT NULL AUTO_INCREMENT,
 phone INT NOT NULL,
 name VARCHAR(10) NOT NULL,
 mark text NOT NULL,
 comment text NOT NULL,
 img blob NOT NULL,
 PRIMARY KEY (roll_number),
 FULLTEXT KEY (mark)); 

OUTPUT

Execute the below query to get the full-text index and its information.

mysql> show index from index_table;

2) Example: The multiple full-text index example shows below.

Execute the following query to know about the "FULLTEXT" index. Here, you use multiple columns for the FULLTEXT key.  It would be best if you created a new table with the multiple "FULLTEXT" indexes.

 mysql> create table index_table(
 roll_number INT NOT NULL AUTO_INCREMENT,
 phone INT NOT NULL,
 name VARCHAR(10) NOT NULL,
 mark text NOT NULL,
 comment text NOT NULL,
 img blob NOT NULL,
 PRIMARY KEY (roll_number),
 FULLTEXT KEY (name, mark, comment)); 

OUTPUT

Execute the below query to get the full-text index and its information.

mysql> show index from index_table;

The above table image displays the index and its type. For example, the BTREE index type is represented by the primary index. The mark, name, and comment index show the "FULLTEXT" index type.

3) Example: The full-text index with the existing table example shows below.

Execute the following query to know about the "FULLTEXT" index. Here, you use single columns tothe FULLTEXT index on the existing table. You can create either single or multiple indexes on the available table.

 mysql> CREATE FULLTEXT INDEX name
 ON index_table (mark); 

OUTPUT

Execute the below query to get the full-text index and its information.

mysql> show index from index_table;

The above table shows the mark and name column as a full-text index type.  Here the mark index is already available, and the name index is created into an existing table.

4) Example: The full-text index with the existing table example shows below.

Execute the following query to know about the "FULLTEXT" index with ALTER table command. Here, you use multiple columns tothe FULLTEXT index on the existing table.It allows us to create either single or multiple indexes on the available table.

mysql> ALTER table index_table
ADD FULLTEXT (name, mark, comment);

OUTPUT

Execute the below query to get the full-text index and its information.

mysql> show index from index_table;

The above image shows an index with a full-text index type. The name and mark columns assign multiple times with different key names. You either create a full-text index or add an index on an existing table.

5) Example: The drop full-text index with the existing table example shows below.

Execute the following query to know about delete the "FULLTEXT" index with the ALTER table command. Here, you will deletemultiple columns ofthe FULLTEXT index from the existing table.

 mysql> ALTER table index_table
 DROP index name; 

OUTPUT

Execute the below query to get the full-text index and its information.

mysql> show index from index_table;

The output images displayed the available index and its type. Here the name_2 index is deleted from the table.