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);