Creating MySQL Simple Index

An index can be created in one or more columns. Based on the number of columns included in the index, an index can be: a Simple index or a Composite index.

Simple index:

An index created on single data column is called a simple index.
The following is the syntax for creating simple index as shown in Diagram 1.

Syntax:
<System prompt> CREATE INDEX <IndexName> ON <Table Name> (<Column Name>);

Example:
mysql> CREATE INDEX index_id ON clientmaster (Client_ID);

Diagram 1

Testing whether the index was created:

SHOW CREATE TABLE command:
To test whether the index has been bound to the clientmaster table, you can execute the SHOW CREATE TABLE statement as shown in Diagram 2:

The following is the syntax for verifying the index created on a column(s) of a table
Syntax:
<System prompt> SHOW CREATE TABLE <Table Name> \G;

Example:
mysql> SHOW CREATE TABLE clientmaster \G;

Diagram 2

In Diagram 2, KEY ‘index_id’ (‘Client_ID’) indicates that the index is successfully created on the column ‘Client_ID’.

• < Prev
• Next >