Creating A MySQL Index Using ALTER

You can use the ALTER TABLE command to add a Simple Index to a table column as shown in Diagram 1.

The following is the syntax for creating a simple index using Alter command.

Syntax:
<System prompt> ALTER TABLE <Table Name> ADD INDEX <Index Name> (<Column Name>);

Example:
mysql> ALTER TABLE clientmaster ADD INDEX index_id(Client_ID);

Diagram 1

To add Composite Index to the column, you can use the following syntax as shown in Diagram 2.

Syntax:
<System prompt>ALTER TABLE <Table Name> ADD INDEX <Index Name> (<Column Name>, <Column Name>);

Example:
mysql> ALTER TABLE productdetail ADD INDEX product_id(Serial_NO,ID);

Diagram 2

To add Unique Composite Index to the column, you can use the following syntax as shown in Diagram 3.

Syntax:
<System prompt>ALTER TABLE <Table Name> ADD UNIQUE INDEX <Index Name> (<Column Name>, <Column Name>);

Example:
mysql> ALTER TABLE productdetail ADD UNIQUE INDEX product_id(Serial_NO, ID);

Diagram 3

SHOW INDEX command:

You can use SHOW INDEX command to list out all the indexes associated with a table. Vertical-format output (specified by \G) is often useful with this statement as shown in Diagram 4.

The following is the syntax for displaying a list of all the indexes created on column(s) of a table

Syntax:
<System prompt> SHOW INDEX from <Table Name> \G;

Example:
mysql> SHOW INDEX from clientmaster \G;

Diagram 4

• < Prev
• Next >