ALTER TABLE - ADD clause for FULLTEXT indexes

The ADD FULLTEXT clause of the ALTER TABLE statement adds an index on a TEXT column within an existing MyISAM table in MySQL.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

ALTER [IGNORE] TABLE table
ADD FULLTEXT [INDEX|KEY] [index] (column,...) [WITH PARSER parser]

Explanation

The ADD FULLTEXT clause adds an index on a TEXT column within an existing MyISAM table. A FULLTEXT index can also index CHAR and VARCHAR columns. This type of index is necessary to use the FULLTEXT functionality (the MATCH()...AGAINST() function). The INDEX and KEY keywords are optional as of MySQL version 5.

With this index, the whole column will be used for each column given. Although you can instruct it to use only the first few characters of a table, it will still use the full column for the index. The WITH PARSER clause may be used to give a parser plugin for a FULLTEXT index.

Examples

ALTER TABLE workreq
ADD FULLTEXT INDEX notes_index
(client_description, technician_notes);

SHOW INDEXES FROM workreq G

*************************** 2. row ***************************
       Table: workreq
  Non_unique: 1
    Key_name: notes_index
Seq_in_index: 1
 Column_name: client_description
   Collation: NULL
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: FULLTEXT
     Comment: 
*************************** 3. row ***************************
       Table: workreq
  Non_unique: 1
    Key_name: notes_index
Seq_in_index: 2
 Column_name: technician_notes
   Collation: NULL
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: FULLTEXT
     Comment: 

I've eliminated the first row from these results because it relates to the primary index, not the one created here.

As of version 5.1 of MySQL, you can use the WITH PARSER clause to specify a parser plugin for a FULLTEXT index. This option requires that the plugin table be loaded in the mysql database. This table is part of the current installation of MySQL. If you've upgraded MySQL and the plugin table is not in your system's mysql database, use the mysql_upgrade script to add it. Use the SHOW PLUGINS statement to see which plugins are installed.