What is the difference between FULLTEXT and FULLTEXT KEY/INDEX?

Question

MySQL documentations gives this format for creating a FULLTEXT index:

| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option]

To implement full-text search with MySQL you can either create index by writing :

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

OR

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

with my_index_name a user defined name and not a field name.

• What is the difference between them?
• Is there any consequence for the system itself and for the developer ?

I am not able to find any clue in the docs:

• http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html
• http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Answer 1

There is no difference, if you use the first syntax then mysql will choose the name of the index, and in most cases the column name, since you have 2 FULLTEXT (title, content) it most likely to choose the index name as title

And in the 2nd case you are explicitly providing a name.

You can run the command show create table posts after creating the tables and see what index names are for both cases.

Answer 2

Omitting the index name

If you add an index/key to a table MySQL will store additional information on the specified column (set of columns) to speed up searches.

In your first example MySQL will generate an index and name it my_index_name. If you omit the name, MySQL will choose one for you. I could not find documentation on how the name is chosen but in my experience the name of the first column is usually reused as index name.

For this part of the discussion the fulltext option is irrelevant. It just defines the type of index/key you are creating:

You can create special FULLTEXT indexes, which are used for full-text searches.

Omitting the index/key keyword

It is sufficient to just give fulltext:

| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...

but it is probably a good idea to use the fulltext index form, since the reader is reminded of an index.

index and key are synonyms

Note that there is no difference between index and key (see create table):

KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.

Example

use test;

create table u (
    id int primary key,
    a varchar(10),
    b varchar(10),
    fulltext index (a, b)
);

show index from u;

will print (I have omitted the primary index from this output as well as some additional columns):

table key_name  seq_in_index  column_name
u     a         1             a
u     a         2             b

You need the index name for example if you want to delete it:

alter table u
  drop index a;

References

• You can find the relevant documentation at create index and create table.
• Some more information are available at : http://dev.mysql.com/doc/refman/5.0/en/index-hints.html