SQL INDEX

SQL INDEX are database objects used by the database to speed up the search results in database tables.

It reduces the I/O count in database thus improving performance of queries like SELECT and WHERE

We can create and drop indexes without any impact to the data in tables.

But it reduces the performance of queries like UPDATE and INSERT

CREATE INDEX Command

CREATE INDEX index_name ON table_name;

Single Column INDEX Command

It is created based on only one column in table

Syntax:

CREATE INDEX index_name ON table_name (column_name);

Example:
CREATE INDEX index_city ON customers (city);

Where customers table is as given below:

cust_idcust_nameaddresscitypostal_codecountry
1Maria AndersObere Str. 57Berlin12209Germany
2Fran WilsonC/ Araquil, 67Madrid28023Spain
3Dominique Perrier25, rue LauristonParis75016France
4Martin BlankVia Monte Bianco 34Turin10100Italy
5Thomas Hardy89 Chiaroscuro Rd.Portland97219USA

Multi Column INDEX Command or Composite INDEX

It is created based on multiple column in a table.

Syntax:

CREATE INDEX index_name on table_name (column1, column2);

Example:

CREATE INDEX index_city_country on customers (city, country);

Unique INDEX

It does not let you insert duplicate values in to the table and maintains data integrity.

Syntax:

CREATE UNIQUE INDEX index_name on table_name (column_name);
Example:
CREATE UNQUE INDEX index_city ON customers (city);

DROP INDEX Command

DROP INDEX index_name;

Learn more : SQL Tutorial , SQL Interview Questions