We create INDEX in a table to find data data fast and efficiently. It allows the database to find data without reading all the records of the table. When we apply search and queries to table INDEX are used to make them fast.

To create indexes in tables we use CREATE INDEX statement.

You should only create indexes on columns that you frequently use for searching purpose, because updating a table with indexes takes more time than updating a table without indexes.

CREATE INDEX Syntax

Creates an index on a table. Duplicate values in columns are allowed:

CREATE INDEX index_name
ON table_name (column_name);

CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values in columns are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

CREATE INDEX Example

The statement below creates an index named "person_user_name_idx" on the "user_name" column in the "user" table:

CREATE INDEX person_user_name_idx
ON user (user_name);

We can create index on a combination of columns, for we just need to pass column names as below:

CREATE INDEX user_index
ON user (user_name, user_email);

Hope this will help you :)