login

Subscribe to MySQL Tutorial websiteRSS Feed
 » MySQL Full-Text Search » Activating Full-text Searching

Activating Full-text Searching

Earn extra cash at home! Thousands of MySQL related projects are waiting for you to finish.

Summary: In this tutorial, you will learn how to activate full-text searching feature in MySQL before you can perform different full-text searches.

To perform full-text search, the column to be searched must be indexed and re-indexed whenever the data of that column changes. MySQL supports indexing data automatically whenever the column is full-text search enabled. There are two ways to enable full-text search for a column of a table: in CREATE TABLE and ALTER TABLE statements.

Support full-text search by using CREATE TABLE statement

Let’s take a look at the most typical way to make a column full-text search enabled in the CREATE TABLE statement when you create a new table.

CREATE TABLE table_name(
	‘column1’ data_type,	
…
PRIMARY_KEY(‘key_column’),
FULLTEXT (‘column_name1’,’column_name2’,..)
) ENGINE=MyISAM

There is nothing new except the keyword FULLTEXT . FULLTEXT is the keyword you use to enable full-text search for columns when you create a new table. When you execute the query, MySQL engines will take the columns followed by the FULLTEXT keyword to index data whenever data of these columns changes.

Here is an example of creating a new table with three enabled full-text search columns:
 

CREATE TABLE `products` (                                    
            `productCode` varchar(15) NOT NULL,                        
            `productName` varchar(70) NOT NULL,                        
            `productLine` varchar(50) NOT NULL,                        
            `productScale` varchar(10) NOT NULL,                       
            `productVendor` varchar(50) NOT NULL,                      
            `productDescription` text NOT NULL,                        
            `quantityInStock` smallint(6) NOT NULL,                    
            `buyPrice` double NOT NULL,                                
            `MSRP` double NOT NULL,                                    
            PRIMARY KEY (`productCode`),                               
            FULLTEXT (`productDescription`,
`productLine`,
`productVendor`)  
          ) ENGINE=MyISAM

Support full-text search by using ALTER TABLE statement

What if you already have an existing table and want to support full-text search on some columns? The ALTER SQL statement comes to the rescue. Here is the syntax of making a column in existing table full-text search.

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

Followed by the table_name is again the new keyword: FULLTEXT. You need to put add column names which you want to support full-text search inside the parentheses and separated by comma.

Let's take a look at an example of making the following columns in products table full-text search: productDescription, productLine and productVendor .
 

ALTER TABLE products  
ADD FULLTEXT(productDescription,
            Productline,
            productVendor)

Note that do not use FULLTEXT when you importing data. The reason is the time consuming of indexing and re-indexing during data load. You should import data first and use ALTER TABLE statement to activate full-text search.

Removing full-text search columns

What if you want to remove column which has full-text search enabled? Note that MySQL actually creates an additional index on each enabled full-text search column, so you just remove that index by using ALTER TABLE … DROP INDEX statement. For example, if you want to remove enabled full-text search column productLine, use the following query:

ALTER TABLE ‘classicmodels’.’products’ 
DROP INDEX ’productDescription’;

In this tutorial, you’ve learned how to activate and deactivate full-text search of columns in a database table. In the next tutorial, you will learn how to perform search by using different search techniques.

Search