SQL-Create Index

Index

Index in sql is created on existing tables to retrieve the rows quickly. When there are thousands of records in a table, retrieving information will take a long time. Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row.

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.
           OR
                   Indexes can be created against a table to make searches more efficient. A database index is similar to an index of a book.A database index enables the database application to find data quickly without having to scan the whole table.
You create an index using the CREATE INDEX command.



CREATE INDEX index_name ON table_name (column_name)

yr    city
2000    Sydney
2004    Athens
2008    Beijing
2012    London

CREATE INDEX gamesIdx ON games(city,yr);
SELECT yr, city FROM games

Result:
yr    city
2004    Athens
2008    Beijing
2012    London
2000    Sydney

Single-Column Indexes:

A single-column index is one that is created based on only one table column. The basic syntax is as follows:

CREATE INDEX index_name ON table_name (column_name);

Unique Indexes:

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows:

CREATE INDEX index_name on table_name (column_name);

Composite Indexes:

A composite index is an index on two or more columns of a table. The basic syntax is as follows:

CREATE INDEX index_name on table_name (column1, column2);

Whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very frequently in a query's WHERE clause as filter conditions.
Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, the composite index would be the best choice.

Implicit Indexes:

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

The DROP INDEX Command:

An index can be dropped using SQL DROP command. Care should be taken when dropping an index because performance may be slowed or improved.
The basic syntax is as follows:

DROP INDEX index_name;