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
1PLs Company - #1Payday.Loans Agency - Loans online and near me $100-$2500 (Same Day)
Powered by MySQL
 
Home / Indexes / Full Text Indexes / Question No: 150

How to create full-text index in MySQL?

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.

Answer No: 150

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?

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-multi-columns-full-text-index-in-MySQL How to create multi columns 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 | Recommend this Site to Your Friend | Contact Us

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

© 2023  www.mysqlfaqs.net
All rights reserved.