Creating an Index

Syntax

CREATE INDEX index

ON TABLE column;

where the index is the name given to that index TABLE is the name of the table on which that index is created and column is the name of that column for which it is applied. 

For Multiple Columns

Syntax:

CREATE INDEX index

ON TABLE (column1, column2,…..);

For Unique Indexes

Unique indexes are used for the maintenance of the integrity of the data present in the table as well as for fast performance, it does not allow multiple values to enter into the table.

Syntax:

CREATE UNIQUE INDEX index

ON TABLE column;

When Should Indexes be Created?

• A column contains a wide range of values.
• A column does not contain a large number of null values.
• One or more columns are frequently used together in a where clause or a join condition.

When Should Indexes be Avoided?

• The table is small
• The columns are not often used as a condition in the query
• The column is updated frequently

An index is a schema object. It is used by the server to speed up the retrieval of rows by using a pointer. It can reduce disk I/O(input/output) by using a rapid path access method to locate data quickly.

An index helps to speed up select queries and where clauses, but it slows down data input, with the update and the insert statements. Indexes can be created or dropped with no effect on the data. In this article, we will see how to create, delete, and use the INDEX in the database.