Skip to main content

Using Indexes

Indexes in SQL are used as a way to quicken the rate of retrieval operations on a database table. Much like the index in a book, SQL indexes allow the database program to find the data without needing to go through every row in a table and thus improves performance.

Types of Indexes:​

  1. Single-Column Indexes:

These are created based on only one table column. The syntax for creating a single column index is as follows:

CREATE INDEX index_name
ON table_name (column1);
  1. Unique Indexes:

They ensure the data contained in a column or a combination of two or more columns is unique. Syntax to create unique index is as follows:

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2...);
  1. Composite Indexes:

These are based on two or more columns of a table. It's important to note that, the order of columns in the definition of an index is important. Syntax to create a Composite Indexes is as follows:

CREATE INDEX index_name
ON table_name (column1, column2);
  1. Implicit Indexes:

These are indexes that are automatically created by the database server when an object is defined. For example, when a primary key is defined.

How Indexes Work​

SQL indexes work by storing a part of a table's data in a place where it can be accessed extremely swiftly. The index holds the column value, and the location of the record itself. This is similar to how an index in a book stores the word, and the page number on which the word can be found.

Considerations​

While they do provide a significant advantage, they also require additional storage and can slow down the rate of updates and inserts into a database. As such, indexes should be used judiciously, taking into consideration the nature of the data in the table and the kinds of queries that will be used.