Search code examples
mysqlfull-text-indexing

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

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:

Solution

  • 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