Creating FULLTEXT Indexes for Full-Text Search
Summary: in this tutorial, you will learn how to define the full-text index for performing various full-text searches in MySQL.
Before performing a full-text search in a column of a table, you must index its data. MySQL will recreate the full-text index whenever the data of the column changes. In MySQL, the full-text index is a kind of index that has a name FULLTEXT
.
MySQL supports indexing and re-indexing data automatically for full-text search enabled columns. MySQL version 5.6 or later allows you to define a full-text index for a column whose data type is CHAR
, VARCHAR
or TEXT
in MyISAM and InnoDB table types.
Notice that MySQL only supported the full-text index for InnoDB tables since version 5.6.
MySQL allows you to define the FULLTEXT
index by using the CREATE TABLE
statement when you create the table or ALTER TABLE
or CREATE INDEX
statement for the existing tables.
Create FULLTEXT
index using CREATE TABLE
statement
Typically, you define the FULLTEXT
index for a column when you create a new table using the CREATE TABLE
a statement as follows:
CREATE TABLE table_name(
column_list,
...,
FULLTEXT (column1,column2,..)
);
To create the FULLTEXT
index, you place a list of comma-separated column names in parentheses after the FULLTEXT
keyword.
The following statement creates a new table named posts
that has a FULLTEXT
an index that includes the post_content
column.
CREATE TABLE posts (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
body TEXT,
PRIMARY KEY (id),
FULLTEXT KEY (body )
);
Create FULLTEXT
index using ALTER TABLE
statement
The following syntax defines a FULLTEXT
index using the ALTER TABLE
statement:
ALTER TABLE table_name
ADD FULLTEXT(column_name1, column_name2,…)
In this syntax,
- First, specify the name of the table that you want to create the index after the
ALTER TABLE
keywords. - Second, use the
ADD FULLTEXT
clause to define theFULLTEXT
index for one or more columns of the table.
For example, you can define a FULLTEXT
index for the productDescription
and productLine
columns in the products
table of the sample database as follows:
ALTER TABLE products
ADD FULLTEXT(productDescription,productLine)
Create FULLTEXT
index using CREATE INDEX
statement
You can also use the CREATE INDEX
statement to create a FULLTEXT
index for existing tables using the following syntax:
CREATE FULLTEXT INDEX index_name
ON table_name(idx_column_name,...)
For example, the following statement creates a FULLTEXT
index for the addressLine1
and addressLine2
columns of the offices
table:
CREATE FULLTEXT INDEX address
ON offices(addressLine1,addressLine2)
Notice that for a table that has many rows, it is faster to load the data into a table that has no FULLTEXT
the index first and then create the FULLTEXT
index, then loading a large amount of data into a table that has an existing FULLTEXT
index.
Drop a FULLTEXT
index
To drop a FULLTEXT
index, you use the ALTER TABLE DROP INDEX
statement.
ALTER TABLE table_name
DROP INDEX index_name;
For example, the following statement removes the address
index from the offices
table:
ALTER TABLE offices
DROP INDEX address;
In this tutorial, you have shown how to create FULLTEXT
indexes that support full-text search in MySQL.
0 Comments
CAN FEEDBACK
Emoji