By A Web Design

Creating MySQL Index on New Table

Indexes help to speed up the retrieval of data from a MySQL database. When retrieving data, MySQL first checks whether indexes exist. If they exist MySQL will use an index to identify and select exact physical corresponding data rows without scanning the whole table.

In general, it is suggested that you should put indexes on columns you usually use in retrieval such as primary key columns and columns used in join and sorts. Why not index every column? The most significant is that building and maintaining an indexes table’s take time and storage space on database.

Usually you create indexes when creating tables. Any column in creating table statement declared as PRIMARY KEY, KEY, UNIQUE or INDEX will be indexed by MySQL automatically. In addition, you can add indexes to the tables which have data. The statement to create index in MySQL as follows:
Syntax:
<System prompt> CREATE TABLE <Table Name>(
<column1> <datatype>,
<column2> <datatype>,
<columnN> <datatype>,
INDEX(<column1>)
)

Example:
mysql> CREATE TABLE client_details (firstname VARCHAR(50), clientid INT, INDEX (clientid));

Diagram 1

Testing whether the index was created:

To test whether the index has been bound to the client_details table, 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 client_details \G;

Diagram 2