MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Indexes / Full Text Indexes / Question No: 151

How to create multi columns full-text index in MySQL?

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?

Answer No: 151

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

Related MySQL FAQs to the Above FAQ

Main-features-and-basic-information-about-full-text-search Main features and basic information about full-text search

What-are-full-text-restrictions-in-MySQL What are full-text restrictions in MySQL?

On-what-data-types-fulltext-index-can-be-created On what data types fulltext index can be created?

How-to-create-full-text-index-in-MySQL How to create full-text index in MySQL?

What-are-natural-language-and-boolean-and-query-expansion-full-text-searches What are natural language and boolean and query expansion full-text searches?

How-to-use-boolean-full-text-search-in-MySQL How to use boolean full-text search in MySQL?

How-to-use-full-text-searches-with-query-expansion-in-MySQL How to use full-text searches with query expansion in MySQL?

How-to-use-natural-language-full-text-search-in-MySQL How to use natural language full-text search in MySQL?

What-are-stopwords-in-MySQL What are stopwords in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.