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

I want to create multi column full-text index in my table. Can you help me to explain, how to create multi columns full-text index in MySQL?

Creating a multi columns full-text index in MySQL is much like creating indexes of other types. To create multi columns full-text index, MySQL provides CREATE TABLE, ALTER TABLE and CREATE INDEX statements. So here, we can use them to create multi columns full-text index. To take examples of multi columns full-text index creation, let’s create a table namely products. While creating the table, we can also ask the MySQL to index combination of columns like "psku" and "pdescription" columns using the full-text variant:

CREATE TABLE products (
 pid INT UNSIGNED NOT NULL AUTO_INCREMENT,
 pname VARCHAR(75) NOT NULL,
 psku  VARCHAR(75) NOT NULL, 
 pdescription MEDIUMTEXT NOT NULL,
 FULLTEXT ft_index_name (pname, psku, pdescription),
 PRIMARY KEY(pid)
);
/* 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 */

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 (pname,psku, pdescription);
/* 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 multi columns full-text index in an existing table using CREATE INDEX statement:

CREATE FULLTEXT INDEX ft_index_name ON products (pname, psku, pdescription);