Creating A MySQL Composite Index

An index can be created in one or more columns. IF the number of columns included in an index is greater than one it’s known as a: Composite index

Composite index:

An index created on multiple table data columns is called a composite index as shown in Diagram 1.
The following is the syntax for creating composite index.

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

Example:
mysql> CREATE INDEX product_id ON productdetail (Serial_NO, ID);

Diagram 1

Testing whether the index was created:

SHOW CREATE TABLE command:
To test whether the composite index has been bound to the productdetail 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 productdetail \G;

Diagram 2

In Diagram 2, KEY ‘product_id’ (‘Serial_NO, ID’) indicates that the index is successfully created on the columns ‘Serial_NO’ and ‘ID’.

• < Prev
• Next >