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_id | cust_name | address | city | postal_code | country |
---|---|---|---|---|---|
1 | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
2 | Fran Wilson | C/ Araquil, 67 | Madrid | 28023 | Spain |
3 | Dominique Perrier | 25, rue Lauriston | Paris | 75016 | France |
4 | Martin Blank | Via Monte Bianco 34 | Turin | 10100 | Italy |
5 | Thomas Hardy | 89 Chiaroscuro Rd. | Portland | 97219 | USA |
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