If knowledge is power, then sharing knowledge is ultimate powertrip
Latest Updates
You are here: Home >> MySql >> Full Text Search in MySQL

Full Text Search in MySQL

MySQL supports text searching by using the LIKE statement and regular expression. However, when the text column is large and the number of rows in a table is increased, using those methods has limitations:

  • Performance: MySQL has to scan the whole table to find the exact text based on pattern in the LIKE statement or pattern in the regular expressions.
  • Flexible search: with the LIKE statement and regular expression search, it is difficult to have flexible search query e.g., to find product whose description contains car but not classic.
  • Relevance ranking: there is no way to specify which row in the result set that is more relevant.

Because of those limitations, MySQL extended a very nice feature so-called full-text search. Full text search in MySql technically, MySQL indexes words of enabled full-text search column and performs searches effectively based on this index. MySQL uses a sophisticated algorithm to determine which row is matched against the search query most.

The following are some important features of MySQL full-text search:

  • Native SQL-like interface: you use SQL-like statement to use the full-text search.
  • Fully dynamic index: MySQL automatically index the text column whenever the data changes. You don’t need to run the index periodically.
  • Moderate index size: it does not take much memory to store the index.
  • Last but not least, it is fast to search based on complex search query.

Notice that not all storage engines support full-text searching. In MySQL version 5.6 or later, only MyISAM and InnoDB storage engines support full-text search.

 Defining FULLTEXT Indexes for Full-Text Searching

Before you can perform full-text search in a column of a table, you must index its data and re-index its data whenever the data of the column changes. In MySQL, the full-text index is a kind of index namedFULLTEXT.

MySQL supports indexing and re-indexing data in the full-text search enabled column automatically. MySQL version 5.6 or later allows you to define full-text index for a column with CHARVARCHAR or TEXTdata type in MyISAM or InnoDB table type. Notice that MySQL supported full-text index in the InnoDB tables since version 5.6.

MySQL allows you to define FULLTEXT index by using the CREATE TABLE statement when you create the table or ALTER TABLE statement and CREATE INDEX statement for the existing table

Defining FULLTEXT index using the CREATE TABLE statement

Typically, you define the FULLTEXT index for a column when you create a new table by using theCREATE TABLE statement as follows:

CREATE TABLE table_name(

 
1
2
3
4
5
6
7

    column1 data_type,
        column2 data_type,
        column3 data_type,
    …
PRIMARY_KEY(key_column),
FULLTEXT (column1,column2,..)
) ENGINE=MyISAM

You put the a list of comma-separated columns that you want to create full-text index in parentheses after the FULLTEXT statement.

The following statement creates a new table named posts that has FULLTEXT index for the post_content column.

 
1
2
3
4
5
6
7

CREATE TABLE posts (
  id int(4) NOT NULL AUTO_INCREMENT,
  title varchar(255) NOT NULL,
  post_content text,
  PRIMARY KEY (id),
  FULLTEXT KEY post_content (post_content)
) ENGINE=MyISAM;

 Defining FULLTEXT index for existing tables

In case you already have an existing tables and want to define full-text indexes, you can use the ALTER TABLE statement or CREATE INDEX statement.

Defining FULLTEXT index using ALTER TABLE statement

The following illustrates the syntax of define a FULLTEXT index using the ALTER TABLE statement:

 
1
2

ALTER TABLE  table_name
ADD FULLTEXT(column_name1, column_name2,…)

The syntax is straightforward. Followed the table_name is the ADD FULLTEXT clause that defines aFULLTEXT index for one or more columns.

For example, we can define a FULLTEXT index for the productDescription and productLinecolumns in the products table in the sample database as follows:

 
1
2

ALTER TABLE products
ADD FULLTEXT(productDescription,productLine)

 

Defining FULLTEXT index using CREATE INDEX statement

You can also use CREATE INDEX statement to create FULLTEXT index for existing tables. See the following syntax:

 
1
2

CREATE FULLTEXT INDEX index_name
ON table_name(idx_column_name,…)

The following statement creates a FULLTEXT index for the addressLine1 and addressLine2columns of the offices table.

 
1
2

CREATE FULLTEXT INDEX address
ON offices(addressLine1,addressLine2)

Notice that for a table that has many records, it is faster to load the data into the table that has noFULLTEXT index first and then create the FULLTEXT index, than load a large amount of data into a tablethat has existing FULLTEXT index.

Removing full-text search columns

To remove a FULLTEXT index, you just delete the index using the ALTER TABLE … DROP INDEXstatement. For example, the following statement removes the address FULLTEXT index in theoffices table:

 
1
2

ALTER TABLE offices
DROP INDEX address

About Jawed Shamshedi

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Scroll To Top