ALTER TABLE - ADD clause for standard indexes

Use the ADD INDEX clause of the ALTER TABLE statement to add an index to a MySQL table.

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 {INDEX|KEY} [index] [USING index_type] (column,...)

Explanation

Use the ADD INDEX clause to add an index to a table. If you omit the name of the index, MySQL will set it to the name of the first column on which the index is based. The type of index may be stated, but usually it's usually not necessary. The names of one or more columns for indexing must be given within parentheses, separated by commas.

Examples

Here is an example of how you can add an index using the ALTER TABLE statement, followed by the SHOW INDEXES statement with the results:

ALTER TABLE clients
ADD INDEX client_index
(client_name(10), city(5)) USING BTREE;

SHOW INDEXES FROM clients G

*************************** 1. row ***************************
       Table: clients
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: client_id
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
*************************** 2. row ***************************
       Table: clients
  Non_unique: 1
    Key_name: client_index
Seq_in_index: 1
 Column_name: client_name
   Collation: A
 Cardinality: NULL
    Sub_part: 10
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 
*************************** 3. row ***************************
       Table: clients
  Non_unique: 1
    Key_name: client_index
Seq_in_index: 2
 Column_name: city
   Collation: A
 Cardinality: NULL
    Sub_part: 5
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 

As you can see in the results, there was already an index in the table clients (see row 1). The index we've added is called client_index. It's based on two columns: the first 10 characters of the client_name column and the first five characters of the city column. Limiting the number of characters used in the index makes for a smaller index, which will be faster and probably just as accurate as using the complete column widths. The results of the SHOW INDEXES statement show a separate row for each column indexed, even though one of the indexes involves two rows.

The table in the example above uses the MyISAM storage engine, which uses the BTREE index type by default, so it was unnecessary to specify a type. Before MySQL version 5.1.10, the USING subclause could come either before or after the column list, but as of version 5.1.10, it must follow the column list.