Home / Indexes / Full Text Indexes / Question No: 150

I am getting some error while creating FULLTEXT index in MySQL. Can you help me to explain that how to create full-text index in MySQL?. Any example in this regard will be highly appreciated.

To create FULL-TEXT index in MySQL is very simple. It is much like creating indexes of other types. As an example, let’s create a sample table named "books", indexing its description column using the full-text variant:

CREATE TABLE books 
 (id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(75) NOT NULL,
  description MEDIUMTEXT NOT NULL,
  FULLTEXT(description), /* applying full-text index*/
  PRIMARY KEY(id)
 );

Take another example to add full-text index in an existing table. We can do this with ALTER TABLE statement:

ALTER TABLE products ADD FULLTEXT ft_index_name (description);
/* Note: here "ft_index_name" is the name of index being created. 
It is optional, so you can omit this if you don't require */

Finally, take another example to add full-text index in an existing table using CREATE INDEX statement:

CREATE FULLTEXT INDEX ft_index_name ON products (pdescription); 

MySQL also allows to create fulltext index on multi columns. So you can create a Full-Text index on individual column or combination of columns of non-binary string data types. May be you would like to learn On what data types Full-Text Index can be created?